Forum Discussion

MeredithMoore5's avatar
MeredithMoore5
Qrew Assistant Captain
7 years ago

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

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.)
APIs and custom code
Ask about Quickbase functionality
Data import and export
  • QuickBaseCoachD's avatar
    QuickBaseCoachD
    Qrew Captain
    7 years ago
    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?
  • MeredithMoore5's avatar
    MeredithMoore5
    Qrew Assistant Captain
    7 years ago
    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
  • QuickBaseCoachD's avatar
    QuickBaseCoachD
    Qrew Captain
    7 years ago
    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.
  • QuickBaseCoachD's avatar
    QuickBaseCoachD
    Qrew Captain
    7 years ago
    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