Use a formula + link to another record in the same field

[Tom_van_den_Ber]

Hi,

I have some columns in my table where I want to use a formula but also link the data from that column to another record. Is that possible?

Thanks.

Tom

[JonathanBowen]

Hi @Tom_van_den_Berg - no, this isn’t possible, I’m afraid. A field can be a link OR a formula but not both simultaneously. A workaround I have used for this is to have two columns with the same value - one is the formula and one is the link to the other table. You can simply copy and paste the value in the formula field into the value in the link field and the link will be made with the value from the formula. It is a manual step, bit not too onerous. You can copy multiple cells in one go or even a whole column.

If you wanted to fully automate this process you could use Zapier or some other service to populate the link field with the formula value if the link field is empty. In Zapier, you would probably want to run this on some sort of schedule. I think the minimum cycle they offer is hourly, which may or may not be a problem for you. If this is too long, the copy and paste method gets you there a lot quicker.

JB

[Calman_Steindl]

Do you know how to upvote this as a feature request? It seems wild to be unable to convert the output of a formula to be link to another field id.

[ScottWorld]

Yeah, unlike all other database systems, record linking in Airtable is always done manually. It’s never based on matching values of fields. It’s a strange departure from the way that all other database systems operate.

However, with Airtable’s new automations feature, you should be able to automate the process of moving a formula field’s result into a linked record field.

[Justin_Hall]

Thanks for this discussion - we’ve made good progress with this guidance. One followup question:

ScottWorld:

with Airtable’s new automations feature, you should be able to automate the process of moving a formula field’s result into a linked record field.

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!