Skip to main content

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!

Hm, what if you created a formula field that would display the names of the restaurants based on the lookup fields?



So if say, “Satchel’s Individual Dishes” was filled, the field would output “Satchel’s”, and if both “Satchel’s Individual Dishes” and “The Forest’s Individual Dishes” were filled, it would output “Satchel’s, The Forest”



You’d then have an automation that would trigger whenever that formula field was updated, and its action would be to paste the output of that field into the “Ordered From:” field

Hm, what if you created a formula field that would display the names of the restaurants based on the lookup fields?



So if say, “Satchel’s Individual Dishes” was filled, the field would output “Satchel’s”, and if both “Satchel’s Individual Dishes” and “The Forest’s Individual Dishes” were filled, it would output “Satchel’s, The Forest”



You’d then have an automation that would trigger whenever that formula field was updated, and its action would be to paste the output of that field into the “Ordered From:” field


That would be handy if I only had three Restaurants, but I have more than a dozen!



But your response did inspire me to create two lookup fields of Restaurant names in the Order table’s “part of menu” or “individual dish” fields, and then create two Rollups of those lookup fields with ARRAYUNIQUE(values) in the Customer table. Also in the Customer table, I then made a formula field that would concatenate the two Rollups. Theoretically, I want this formula field to feed to my automation.



HOWEVER, for some reason, all the commas are gone in the formula field to concatenate two Rollup fields containing arrays… I tried different things (e.g. creating instead two formula fields to output the Rollup fields and then concatenate from those two formula fields) but same thing…



If I can’t use a formula field to trigger the automation, I’m going to now try using the “if a record enters a view” automation, but still can’t figure out how to append a linked record instead of replace.

That would be handy if I only had three Restaurants, but I have more than a dozen!



But your response did inspire me to create two lookup fields of Restaurant names in the Order table’s “part of menu” or “individual dish” fields, and then create two Rollups of those lookup fields with ARRAYUNIQUE(values) in the Customer table. Also in the Customer table, I then made a formula field that would concatenate the two Rollups. Theoretically, I want this formula field to feed to my automation.



HOWEVER, for some reason, all the commas are gone in the formula field to concatenate two Rollup fields containing arrays… I tried different things (e.g. creating instead two formula fields to output the Rollup fields and then concatenate from those two formula fields) but same thing…



If I can’t use a formula field to trigger the automation, I’m going to now try using the “if a record enters a view” automation, but still can’t figure out how to append a linked record instead of replace.




Hmm! Sorry, I don’t understand this bit, the formula would be longer but would still work fine. What’s the problem that arises from having many restaurants?







I’m not really following here, but this comma issue might be fixed by converting the relevant lookup fields to rollups, so maybe you could try that?



Hmm! Sorry, I don’t understand this bit, the formula would be longer but would still work fine. What’s the problem that arises from having many restaurants?







I’m not really following here, but this comma issue might be fixed by converting the relevant lookup fields to rollups, so maybe you could try that?




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.



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.


Hmm, I think we may be thinking of different formulas actually



At this point would you be alright with just DMing me an invite to your mockup base so that I can see whether I can figure something out in there? Reckon it might save us both some time

Reply


Terms & Conditions