Use Lookup Fields to Append a Multiple Select Linked Record Field Type using Automations
Hi all, I’m trying to create an automation that will update a linked record field (which sometimes starts empty, sometimes starts with records already in it) based on whether a lookup field is not empty.
The original data I am using is sensitive so I created a mock base with the same structure. I have five tables that are all linked to each other, one each for Dishes, Restaurants, Menus, Customers, and Order Instances. There are three make-belief restaurants: Satchel’s, The Forest, and Hidden Agenda.
The automation I want to make is for the Customer table:
A customer can order dishes either as an individual dish or as part of a menu-set. Though each dish was designed by a Chef at one Restaurant (i.e. a Dish row is connected to Restaurant through Linked Record where only one can be selected), customers can order menu-sets consisting of dishes by different restaurants or place orders for individual dishes by different chefs.
I want to be able to link each customer to a restaurant. Right now, I am using look-up fields to get restaurant info from a customer’s menu-set order or individual dish order (so there is still no link between customer and restaurant). So in the Customer table there are 6 fields, 2-each for each restaurant, and all they do is not be empty if the restaurant in question appears in that row in Orders.
My thought is to build an automation where, if the fields “Satchel’s Individual Dishes” and “Satchel’s Menu Dishes” is not empty, the field titled Ordered From will be filled with “Satchel’s,” and so forth. In the screenshot above, Clothilde Marcos is an example of a customer where all the lookup fields are not empty, so my wish is for the automation to add all three restaurants linked records in the “Ordered From:” field. Likewise, whenever a new Order Instance record is made and the order details inputted affects the lookup fields in the Customer table, it should also APPEND a new Restaurant record (if the customer has never ordered a dish by that restaurant before) in the “Ordered From:” field.
Hope this makes sense, any help is appreciated!
I may very well be misinterpreting your suggestion or am unfamiliar with the formula you are recommending, but what I gathered from your description of the formula is to make a formula that goes like:
IF(NOT({Satchel’s Menu Dishes}),“Satchel’s”,IF({Satchel’s Menu Dishes}, {Satchel’s Menu Dishes}))
But then I would have to add an IF for every possible combination of those 12 restaurants, which are discrete and unordered. A row in the Customer table can have ordered from either just 1, or 2, or … etc. until 12 restaurants, which would not quite be 12! (4 million possible permutations) but still in the thousands of possible combinations. Hence, I understood it as having to write a formula with thousands of possible combinations… Glad to hear that you seem to not share my concern, which means that there is a more efficient formula. Would you mind showing me how that would look like?
I believe this is a known issue in Airtable. Whether lookup, rollup, or a concatenated formula field, the commas disappear when that field is used as an output in another formula field, but if I’m wrong, would be happy to learn of a solution:
The field calculation 1 is a concatenation of two rollup fields with a UNIQUEARRAY formula in them
The field calculation 2 is just an output (not concatenated) of a lookup field for the restaurant names
I made other formula fields that outputted or concatenated different Array formulas in rollups or looked directly at the lookup field and still had the same issue.
