Hi, I’ve been using Airtable for quite a bit since I started my new role and I need help with this particular thing:
I have a table that has the following fields:
Line items (Linked Record to Products - Multiple allowed)
Customer Type (Single Select with options A, B, and C)
Payment Type (Single Select with options CASH and CARD)
Lookup Value (Formula)
Charged Items (Linked Record to Pricelist - Multiple allowed)
Products has the sample data:
Pricelist contains prices for all permutations of Product-Customer Type-Payment Type and has the following fields:
Transaction (Key, Formula - concatenation on Customer Type, Payment Type, and Product)
Price (varies per permutation)
For example, a transaction with Product1, Customer Type A, Payment Type CASH would yield “CASH A Product1” as the key for the record.
Now back to the very first table - I’d like to customize the formula in Lookup Value to create comma-separated permutations of each linked record in Line Items so that I can just copy-paste the output to Charged Items and have the records automatically link to the correct records in Pricelist and lookup the associated prices.
For example, if I linked “Product1” and “Product2” in Line Items, selected “A” in Customer Type, and “CARD” in Payment Type, I should get "“CARD A Product1”, “CARD A Product2"” in Lookup Value.
Any ideas on how to make this happen? I feel like it should be fairly simple but the logic to make it happen has me stumped! Thank you in advance!