Thanks for this discussion - we’ve made good progress with this guidance. One followup question:
Thank you for this suggestion! I’m building an automation along these lines and I have a question. I’m wondering how we best prepare data from a Formula cell array for a Link to another record field. Here’s a scenario to describe what we’re attempting:
Imagine a table listing fruit markets, and the fruits they carry.
A table of Fruit Types
A table of Fruit Markets. This is where we are working; imagine each line is a different fruit seller.
A Formula-type field: Fruits Array. This pulls in data from the Fruit Market into an array, to show us which stores have which fruit. The Array results in this field look like this:
- apple, banana, kiwi, , orange, ,
- , , kiwi, mango, , ,
- , banana, kiwi, , , watermelon
Due to the Formula design, there’s “, “ where a fruit is not available at that store.
A Link to another record-type field: Fruits Available, linking to lines in the Fruit Types spreadsheet.
If I manually copy the contents of Fruits Array into Fruits Available, I get working links to each of the different fruits. Now I’m looking to automate this: whenever a Fruits Array field record is updated, copy the contents into the Fruits Available field.
I set up an Automation triggered by an update, then I attempted to get an Update Record action working. The Fruits Available field in the Update Record script asks me for a record to link to by name or ID. I don’t have that information, and I suspect I will need to run a script to prepare the data to be passed from the Formula field into the Link to another record field. I suspect the data cleanup required is relatively simple, but beyond me just now.
Does anyone have any guidance on the best way to prepare an array from Formula cell data like this for a Link to another record field? Thank you!
