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

[Art_Aghourian]

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

[Peter_Borg]

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?

[Octavio_Lopez_S]

I want the same feature!

[Octavio_Lopez_S]

Hello Peter,

May be your post should be merged with this one:

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

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 for…

or visceversa…

[Julian_Kirkness]

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?

[Peter_Borg]

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.

[W_Vann_Hall]

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).

[Nathan_Graf]

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:

[itoldusoandso]

This is issue for me as well. I see it’s been 2 years and still no resolution. Here is a use case with inventory items and shipping rates.

Use case: I have two tables: 1) Table 1 with inventory items table with items sold on various market places (ebay, amazon, kijiji, craigslist) and 2) Table 2 with shipping rates (matrix table based on weight and size). I need to be able to link Table 1 to Table 2 based on item weight and size and look up shipping rate based on this item weight and size. Since lookups can’t be nested (and there is no replacement for nested Excel Lookup function) I have a concatenated column “Weight and Size” that says e.g. “3000g - 45cm” which is calculated based on dimension formula in Table 1. The concatenated field “Weight and Size” should allow me to use the Lookup function in Airtable to get the rate from Table 2. The way I have set it up is in Table 2 I have a list of combinations (weight by length) also with a field named “Weight and Size” and it contains text e.g. “3000g - 45cm” (this is not concatenated, it’s just plain text in first column).

All would work splendidly if the column “Weight and Size” in Table 1 (e.g. 3000g - 45cm) would allow me to set it to BOTH formula + linked field.

If I was able to do that, I would be able to look up the Shipping rates and have them displayed next to the inventory line item in Table 1.

Without that, the only workaround (as described above - thank you) … is in Table 1 to create a dummy column next to the concatenated column “Weight and Size” and name e.g. "COPY of “Weight and Size” and then manually copy the contents of the “Weight and Size” to “COPY of Weight and Size”.

That column “COPY of Weight and Size” in Table 1 will then be used as link to look up the shipping rate from Table 2 and display it Table 1.

It’s not a deal breaker but very disappointing to find out this doesn’t work at this junction… after spending many many days creating the solution being at 99% completion of my project. It’s an ugly road bump in otherwise absolutely smooth workflow with Airtable so far for me.

[itoldusoandso]

OP asking the same thing … https://community.airtable.com/t/use-a-formula-link-to-another-record-in-the-same-field/24778