Matching Text in one Table to Text in related Table to get related table ID# (Without Hard Coding Text)

  • 0
  • 1
  • Question
  • Updated 4 weeks ago
  • In Progress
Ok, So I have a text import debacle on my hands. 

I have an import coming in from an outside system, where they have Jobs that are already in QB, BUT the only way to match up these jobs, with the ones already in QB, is by matching the text of the Job [Title], [Level], and [Sub-Level]. All text. (not what I like doing, and if there were another way, I would do it. But there isn't.)

I have tables in the QB for all all these fields (Title], [Level], and [Sub-Level]), but as I import the new information, I need it to automatically match & populate the Record ID from each of these tables into the import table. This needs to happen by the only means I have, matching the text coming in.

I don't want to hard code this in because there may be text changes to the import records or additions to the tables. 

Also, I cannot just make a dropdown related because I need the records to automatically match, and the users, for the most part, will not have access to editing this table.

I know there is something for this. 

(P.S. - I understand if you may need further clarity. It is 4am in Japan and I have been up for a while. I am here on my honeymoon, and just trying not to go crazy with this import problem. All help is appreciated.)
Photo of QuickBase Girl

QuickBase Girl

  • 338 Points 250 badge 2x thumb
  • eye twitchy

Posted 4 weeks ago

  • 0
  • 1
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 52,826 Points 50k badge 2x thumb
Are you trying to import into an existing table or is this an import of new Child records needing to be matched up to the existing Parent record.  Also, are you certain that the combination of 
[Title], [Level], and [Sub-Level]) is unique in the existing Quick Base table?
Photo of QuickBase Girl

QuickBase Girl

  • 338 Points 250 badge 2x thumb
I am importing into an existing table. (It is auto uploaded from OneDrive via Zapier)The other tables are child tables. All of the info in this table will go to other tables as well, but that is not really anything to worry about at this point. I just need to be able to make these fields integrate. Right now, I just have a table of text that does nothing. :(

The combo of the child tables is as close as I can get to unique. Example:

[Job Title] Accounts Receivable Specialist
[Level] Operations
[Sub-Level] Front Line Serv Del

They know that if it changes in the import, that it will have to change in the matching child tables. That is for the users to take care of and maintain. Example: [Sub-Level] Front Line Serv Del - > [Sub-Level] Front Line Service Delivery
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 52,826 Points 50k badge 2x thumb
Would you consider changing the key field of the table to be a text field with a value of those three fields hyphenated together.

The Dara entry by hand would be enforced by a form rule which would take the value in a formula field and copy it to the Key field

Your import file would have to have this hyphenated Keyfield as well, and that could be done in EXcel.

There was also a change on this last weekend's release to allow merging of imports based on a unique field as opposed to only being able to merge import based on the record ID or the key field. It was bent to address this exact same situation for automated import. But I believe you are doing an important using the regular user important process., as opposed to by API.

I think I’m going to go back and read the release notes for the November release Tward to stay in that a bit better.
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 52,826 Points 50k badge 2x thumb
I have some more thoughts.

The first just to read this thread here.

https://community.quickbase.com/quick...

The second thought is that I assume you know you can dump your quick base table into Excel and then use Excel to do a Vlookup to find the record ID on your data being imported and then import into Quickbase.

I know that is a royal pain to do especially if it is going to happen often but it may be your only solution for now.

But you can try me in to that thread above and employer Harrison Hersh of QuickBase to advocate for the extension of that new functionality to be applied to the user interface type of data import as well. So maybe it would only be a few months away that you would have to continue to do the ugly Vlookup method