Help

The Community will be undergoing maintenance from Friday February 21 - Friday, February 28 and will be "read only" during this time. To learn more, check out our Announcements blog post.

Linking records on import when the primary fields don't match...

Solved
Jump to Solution
626 7
cancel
Showing results for 
Search instead for 
Did you mean: 
Ron_Daniel
8 - Airtable Astronomer
8 - Airtable Astronomer

I have a database for our retail stores. The main table's first column is a formula that combines the store's name and the store's number with a " – " in between. For example, "1234 – Joe's Discount Hardware".

I need to import data from a report that only has store numbers. I know could use the CSV Import extension by setting it to merge with existing records and set the merge field to the store's number.

However, where I really need it imported is into another table in this base. This is where my roadblock is: Since the linked store identifiers are "1234 – Joe's Discount Hardware" but the report's identifiers are "1234", is there a way for me to link them?

I did try bringing in a lookup field that show's the linked store's store number. But it can't use the lookup field as an identifier. Then I thought I'd found an answer by making the table's first column be =storeNumber. However, when I tell the extension to merge with existing records using storeNumber as the merge field, it doesn't recognize that the numbers match - and instead wants to make all new records.

My last resort will be to import all of this data into the first table, but I'm really hoping to not do that. Any ideas?

Thanks in advance!

1 Solution

Accepted Solutions
ibayub
6 - Interface Innovator
6 - Interface Innovator

i think i might've figured it out - looks like the merge has issues with comparing to a formula. i added a text field type with the store name (you could either create a new field and run an automation or just copy/paste, or edit the formula field to be text and it retains the values) and merged on that - looks like it works if you do it that way.

ibayub_0-1736955733658.png 

ibayub_1-1736955798799.png

ibayub_2-1736955819470.png

 

 

See Solution in Thread

7 Replies 7
ibayub
6 - Interface Innovator
6 - Interface Innovator

would the below work? it may also be how the data is being formatted (e.g. as strings or numbers etc)

1. in Main table add a 'Store Num Only' formula field that extracts the store number 

2. In the report table, create a linked record field that links to main table using the new Store Num Only (assuming store numbers are the same from the csv / main table)

3. when you do the csv import, set the merge field to store number in the report table and map it to the linked store in the main table 

1. I do already have a store number field in the main table.
2. Unless I'm missing a major feature, I believe the linked records field from the main table only uses the Primary field from the table. Anything else needs to be brought in as a lookup field, which I tried.
3. Did that, it's not recognizing the number as being something it can match to.

Hmm, I tried the setup you mentioned of making the primary field be the Store number and it seemed to work fine, and so I think my test is structured differently from your data (or I'm misunderstanding what we're trying to do, sorry!).  Any chance you could provide some screenshots of your structure?  

Here's what I tried:

Screenshot 2025-01-15 at 8.54.27 AM.png

Screenshot 2025-01-15 at 8.54.36 AM.png

CSV:

Name,Stores
A,1
B,2
C,1

And when importing the CSV it created 3 new records in 'Data', linked to the Store records appropriately

Screen Recording 2025-01-15 at 8.52.11 AM.gif  

Thanks for the response, Adam! In my case, your Stores table needs another field: store name. And your primary field would be the formula that combines them into (store number) - (store name).
And in the CSV import, I need it to merge with the existing records, not create new ones. The merge is where the sticking point is. Screencaps below.Screenshot 2025-01-15 at 7.53.42 AM.pngScreenshot 2025-01-15 at 9.50.06 AM.pngScreenshot 2025-01-15 at 8.00.13 AM.pngScreenshot 2025-01-15 at 9.50.48 AM.png

ibayub
6 - Interface Innovator
6 - Interface Innovator

i think i might've figured it out - looks like the merge has issues with comparing to a formula. i added a text field type with the store name (you could either create a new field and run an automation or just copy/paste, or edit the formula field to be text and it retains the values) and merged on that - looks like it works if you do it that way.

ibayub_0-1736955733658.png 

ibayub_1-1736955798799.png

ibayub_2-1736955819470.png

 

 

Ah, so sorry, I completely misunderstood what we were trying to do, thank you for explaining!  Yeap echoing ibayub's idea of just creating a text field for this instead.  I tried setting the store number to go into both the linked field and the new text field for merging and it seemed to work fine

Screenshot 2025-01-16 at 2.25.29 PM.png

Ron_Daniel
8 - Airtable Astronomer
8 - Airtable Astronomer

@ibayub & @TheTimeSavingCo  - thanks both for your help! There's a couple of internal reasons I can't use this solution, but I won't bore you with those details. In hopes that a future someone finds value in this workaround, I'll mark it as the solution. Thanks again to you both!