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

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

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.

Yeah, unlike 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 automations, you can automate the process of moving a formula field’s result into a linked record field.

Hope this helps! If you’d like to hire an expert Airtable consultant to help you with anything Airtable-related, please feel free to contact me through my website: Airtable consultant — ScottWorld 

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!

Hey @ScottWorld,! How do I move a formula field's result into a linked record field using Automation? I couldn't figure out how.

For instance below, I want to move the result of "Week Number", which is a formula field, into a new field called "Linked_Week Number" which is essentially just for the purpose of of linking to another table. However, when I tried to do this, it does not work. 

Thank you in advance for any help you can provide! Cheers!

Your setup looks fine, but It says you have insufficient permissions. Is the primary field in the *OTHER* table a formula field? If so, you can only update your linked record field in the *CURRENT* table with pre-existing values that already exist in that *OTHER* table.

Hope this helps! If you’d like to hire an expert Airtable consultant to help you with anything Airtable-related, please feel free to contact me through my website: Airtable consultant — ScottWorld 

Thanks for pointing me to the right direction Scott! I have figured it out. Cheers!