Linked fields linking wrong field

[Michael_Morris1]

I have two tables I need to link. Ideally, I’d link them on their primary keys, but Airtable doesn’t allow you to link on primary fields. So I created a duplicate field for each table with the same content as the primary field. When I link the tables, however, Airtable doesn’t let me choose which fields to link on. I choose the field in Table A, but Airtable chooses the field in Table B to link to. Unfortunately, instead of linking to the appropriate field in Table B, Airtable goes ahead and makes a new field in Table B with a new row for each row in Table A. I’ve done this before in other bases and Airtable was smart enough to choose the correct field to link on. I tried making the field names the same. No success. I tried having the duplicate field in only one of the two tables as well. Nope.

What in the world is going on, and how do I get Airtable to link the correct fields?

[Julian_Kirkness]

Hi Michael

The links between table are not really from one field to another - but from one record to another. What is displayed in the field is always the contents of the Primary Field (the first one) but what is actually stored is a record ID.

If you want to display the content of another field from a linked table then you can use a Lookup field via the Link to do this.

Under the skin, the linking process creates a field in each table containing the ID of the connected record in the other table.

I hope this helps?

[Michael_Morris1]

Hello Julian,

Thank you for responding. In Airtable, unless I am missing something, it is not possible to have a linked single record. Only an entire field (i.e., all records in that field) can be linked or not. When I create a linked field (which I will later use to create a lookup field), I select the field in the current table (Table A) and then use the menu to link Table A to Table B. I can choose which field in Table A I am linking on, but I cannot choose the field in Table B. In the past, Airtable has appeared to use what it calls the Primary Field of Table B. However, for some unknown reason, it is not doing that in this case. Rather, it is creating a complete list of duplicate records with the Primary Field of Table B plus a new field also in Table B. The result is twice as many records, half of them all blank except for the primary field, which contains the duplicate data. For all intents and purposes, Airtable is failing to link the fields of the two tables.

I have confirmed that the data in the primary field of Table B precisely match the data in the linked field in Table A. There are no extra spaces or other invisibles. I’ve re-done the process multiple times and it always fails, for no apparent reason.

[Michael_Morris1]

Hallelujah, I did it one last time, and this time it worked. Ugh.

[Julian_Kirkness]

Great - would love to know what went wrong!!

[Elias_Gomez_Sai]

I’d wish to have a screenshot of what you have explained :grinning_face_with_sweat:

[hilary_coolidge]

Hi Julian - I think I am trying to do what you instruct here but not sure how to implement it. I have 2 tables that have a column with the same data. For example, a prospect table and a customer table. In both I have a column that is “status” which has a set of individual states, for example: do not contact, in process, on hold, converted. How can I get the information from the status column that is in prospect table to be duplicated and updated in the customer table without having to do double entry on that column? So I want to link a column in one table to a column to another table. When I label the column as a lookup function, it only allows me to select a full record, not a single column…

[Julian_Kirkness]

Hi Hilary

I’m not really understanding what your’e trying to do - are you able to share your base so that I can see more clearly?

In general, I would normally start from the basis that I would use the same table for both customers and prospects (unless there’s a good reason not to) after all they are all companies or people (depending on your market). I would use a status field and filtered views to differentiate. This prevents the need to copy data from one to the other and properly caters for the situation where a customer is also a prospect (I’ve certainly been in this situation before).

Julian

[hilary_coolidge]

From what I understand, we’d want some separation since we may have a way to have “prospects/leads” and leads generated from other processes that might need to be groomed or catalogued separately, and that some of the data that is attached to a lead or prospect may be different than what we want to filter on once they become a “customer.” (and some will be the same).

At least that’s what I gather from our group.

How would I be able to share it?

[Julian_Kirkness]

Hi Hilary

I can understand that this may be so - it’s just that I’ve tended to stick to a single Company (B to B) table always when I’ve built CRM databases for my own businesses.

I still don’t really understand what you mean by having a column in 2 tables with the same data. Do you mean you have a record for the same company in each table and the values in a particular column should be the same in both?

If so, there are 2 ways to deal with this - one would be to have Zapier update one from the other - but this would involve quite a few Zaps and views - and you would have issues around which is the master record etc. Alternatively, follow my suggestion and merge the data into one table - you can the use Views to filter data by Lead/Prospect/Customer and each view could display just the columns you need for the category concerned.

Julian