Formula with linked column? (i.e. two column categories)


#1

Hi All,

I am testing if airtable will work better than excel. I have a “helper” column in my table that concatenates data from three other columns (ex: “date”&“city”&“person”). I would like to be able to use this helper to lookup data from another table (there are a finite number of “helper” strings, so the lookup table lists all of them and the corresponding return data). So I can use formula to create the string in the current table but when I change it to a link reference the concatenate formula disappears. Is there a work around? Can a column be both linked and be populated with a formula?

Thanks! Please let me know if you need more info
Art


Help please – turning "Formula" field types into "Link to"
Linking tables with columns that have a formula
#2

I want the same feature!


#3

Just a thought…

Is there any reason to calculate the column in the main table - why not do that in the table you are going to look it up from (in the Name / Primary Key field) and then simply select it in your main table in a link field? If you wanted the component parts separately in the main table you could use lookup fields to ‘bring them over’.

Hope this helps?


#4

I’ve discovered that Airtable is a great way to compare and sync data from different systems. Load CSV extract from system A into one table and system B into another, then link to create a cross reference table.

However…

Sometimes I need to cross reference on a key that’s not available in the A or B extract. So I create it (the key) in Airtable using a formula, and then convert that field to a “Link to” field.

Problem is, I want to re-run the sync periodically, loading new extracts into my already created base. If the required keys are in the extract then fine, nothing more needs to be done. But if not, I need to do a intermediate step to create the keys.

Can that intermediate step be avoided some how?

My current workaround is to load the CSV into a staging table, which applies the formula, then manually copy the whole table contents into the actual sync table where the corresponding key field is now a “link to” field. Seems a bit dumb, but is there a better way?


#5

Hello Peter,

May be your post should be merged with this one:

or visceversa…


#6

Thanks @Octavio_Lopez_Sierra, that makes sense.

Perhaps this should go into the “feature requests” section. What do you think @Art_Aghourian? I presume you would need to move it, as it’s your post. It is not a current feature. A column cannot be both a formula and a link field.

Also, I’m still seeking any smart work arounds. Mine is quite cumbersome. Has anyone thought of something better? Julian’s suggestion above is the only feedback so far. Useful, but doesn’t apply in all situations, and not my situation.


#7

You can use a variant of your workaround that would be a little less cumbersome than copying the entire table: namely, simply include both the Formula field that constructs the key value and the Linked Record field. You can then copy-and-paste the values from the Formula field into the Link field, either as the entire column or, for individual records, using the three-keystroke sequence [Ctrl-C], [Right-Arrow], [Ctrl-V] (assuming the fields are adjacent).


#8

Yeah, not being able to link a formula column (on an ongoing basis) with another table is a deal breaker for me.

Use case:

  • I am using Zapier to automatically add data to table Foo through Airtable’s API.

  • Records in table Foo have a timestamp.

  • I need to be able to rollup and analyze aggregate monthly numbers. A pivot table could work well for this, but they are currently not available through the API, so I created table Bar with primary key Month (1-12).

  • In Foo, I created a Formula field that is an integer representing the month from the timestamp. This would work perfectly to link to the primary key of Bar, and it does… until Zapier adds more data, because the formula field is now gone, and so the month is no longer extracted in the formula field in Foo. :frowning: