How can I import into a Text Lookup field?

  • 0
  • 2
  • Question
  • Updated 2 years ago
  • Answered
I have a field that is a text lookup. It shows as a drop down on my form. I'm trying to import all of my data into QB and it won't allow me to import into this field. 

So far, I've been importing the data into a new column and then doing a grid edit where I manually select the choice from the text lookup drop down box. I have thousands of entries I need to do this to. I would imagine there's a better way. 

Thank you! 
Photo of Dana

Dana

  • 532 Points 500 badge 2x thumb

Posted 2 years ago

  • 0
  • 2
Photo of Matthew Neil

Matthew Neil

  • 31,698 Points 20k badge 2x thumb
Official Response
When you import into fields that are part of a relationship, you have to use the [Related Field] for the import.  Most of the time is a numeric reference field.  

There are a few options:
One: If you are just starting out, and the application (or this particular relationship) is in its infancy.  I'd recommend changing the key field to the text field in question.  Then re-make the relationship with the new key field.  Then you can do an import directly into that field.

 Option two:
You will need to know the record ID of the parent record (the [Related Field])  then you can do a vlookup in excel to connect all your data to the correct reference field prior to the import.

Last option: includes changing the relationship to a formula, but that cannot be undone and if you don't do it right you could lose all your historical data, so if the above two options aren't a solution.  let me know and we can work through this.
Photo of Dana

Dana

  • 532 Points 500 badge 2x thumb
Unfortunately, I'm past the early stages and don't want to risk losing what I've entered.

Option two sounds like the best for me but I'm not really following with the parent record ID/import. Can you elaborate some more? Thank you so much for your help! 
Photo of Matthew Neil

Matthew Neil

  • 31,698 Points 20k badge 2x thumb
Option 2 is my favorite anyway, and I apologize if this first part is stuff you already know

baseline:
I don't know your tables or relationship, but for example sake I'm going to call the parent table "Parents" and the child table (or the table you are importing to) "Children"

You have a relationship that "each Parent has many Children"
The numeric Reference field that is used in this relationship is called something similar to [Related Parent] and is actually a field on the "Children" table.

This [Related Parent] number is actually the Record ID# from the "Parent" table, and this is what connects the records.

Connections:
You then have a Text(lookup) field on the Child table that you are trying to connect via an import.

Lets assume this field is called [Parent - Name], but it wont let you import because it is a lookup.

What you need to do:
Go to your Parent table, create a new report that has the following:
Column 1 = Parent Name
Column 2 = Record ID#
Sort a-z by the parent name.

Click "Save as a spreadsheet"

Now with your spreadsheet that you are planning to import to the child table:
Create a new column next to your [Parent - Name] column and call the new column "Related Parent"
You will then perform a vlookup from the Parent - Name to find the matching Parent Name in the new report you made, and then pull the Record ID#.  

The formula will be something similar to this:
=vlookup(click the column header for Parent Name, go to the new spreadsheet and highlight columns A & B, 2, false)

The 2 indicates the column order, and that we want the value in the 2nd column that matches the same criteria in the 1st column.  If you get stuck on the vlookup, there are a lot of YouTube videos out there.

Finally:
Once you have that new Related Parent value, you can run the import as usual, just don't use the lookup field, rather import the number into the field [Related Parent] field and it will automatically create your relationship.


Seems long, but once you get the hang of it, you could do all this in less than 5 min.
Photo of Dana

Dana

  • 532 Points 500 badge 2x thumb
You.are.a.hero!! Thank you a million times!! Your explanation was fool proof and just saved me hours of time. 
Photo of QuickBasePros_IDS

QuickBasePros_IDS, Champion

  • 4,516 Points 4k badge 2x thumb
It sounds like the Lookup field is acting as a Proxy-Field for the related reference field; that's why you see it on the screen as a drop-down even though the data comes from the parent table.

However, the "data" that you select with the drop-down actually lives in another table; so your data import must push this data to that table, not to the child table.  You will need to extract the Record ID# (or Key Field value) from the Parent table to identify which Parent-table record the data needs to be imported into.  Then do the import against the parent table for the data that you select in the child as a lookup/selectable value.
Photo of Dana

Dana

  • 532 Points 500 badge 2x thumb
Thank you so much too!! Both of your inputs solved my problem!