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!
Linking records on import when the primary fields don't match...
+21Best answer by ibayub
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.
+10would 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
+21would 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.
+311. 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:
CSV:
Name,Stores
A,1
B,2
C,1And when importing the CSV it created 3 new records in 'Data', linked to the Store records appropriately
+21Hmm, 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:
CSV:
Name,Stores
A,1
B,2
C,1And when importing the CSV it created 3 new records in 'Data', linked to the Store records appropriately
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.
+10i 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.
+31Thanks 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.
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
+21@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!
Login to the community
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.