Import Issues

  • 0
  • 1
  • Question
  • Updated 10 months ago
  • Answered
I have having trouble with my latest import.  I have done this same process before but this time it changed.

Table "Companies" - Import company info from csv file
Table "Contacts" - Import contact info from csv file

Then I usually go into each contact and select the "Company Name" drop down and select the company I want to link to the contact.

This time the drop down only has a selection of "Browse Choices" and then I have to go to another screen and type in the name of the company and search and then select.

This makes the process super slow and I have to do this for about 800 contacts.

I would love a way to either have it back to the normal way or if there is a better way to import and link "company" and contacts" so it is not so manual.
Photo of Mark Comish

Mark Comish

  • 680 Points 500 badge 2x thumb

Posted 10 months ago

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

QuickBaseCoach App Dev./Training, Champion

  • 65,542 Points 50k badge 2x thumb
The drop down list will revert to that pop up Record Picker when the list of choices gets over 1000.

An idea to retain the regular drop down is to set it up as a conditional drop down where the user first enters the first letter of the choice into a new field and then the actual choice would be displays, and would then just list, for example all the choices beginning with the letter A.

As for automatically linking up to the Parent, the contacts import would need to populate the field typically called Related Company.  If the key field to the Company table is [Record ID#], then you would need to export the company table with the company name and the [Record ID#], use VLOOKUP in Excel to get the Record ID into the contacts and then import that field into Related Company.

Alternatively you might make the Key field of the company table to be the company name.  If you have never changed a key field, i suggest trying it in a copy of your app first.  Then the imported Contacts would link up automatically if the Company name was a letter perfect match.
Photo of Mark Comish

Mark Comish

  • 680 Points 500 badge 2x thumb
Can you explain the process of doing...

An idea to retain the regular drop down is to set it up as a conditional drop down where the user first enters the first letter of the choice into a new field and then the actual choice would be displays, and would then just list, for example all the choices beginning with the letter A.

Thanks,

Mark
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 65,542 Points 50k badge 2x thumb
Sure,
On the table with the 1001+ choices for Company make a new field called [Company First Letter] with the formula of

Left([Company Name],1)

Then on the details table where you do your data entry make a new field called [Company First Letter]  and let it just be a data entry free form text field ( you could also experiment with a drop down field with 26 choices A-Z plus 0-9.  (I'm not actually sure if the Conditional drop down will be case sensitive, like if you have a company name entered with a lower case first letter).

But let's start simple as a free form field.  Limit that to 1 character maximum length in field settings.

Then go to the field properties for Related Company and check the box for

Conditional values   The values in this field depend on a selection in another field

and specify that after [Company First Letter] is selected only show choice where that is equal to the value in the field [Company First Letter]
 
The auto filtering of that drop down list is extremely fast but will only begin when you make a selection from a drop down list or you tab out of a free form field. 

So I suggest to start with a free form field to choose the first letter and if you don't like the user experience, change it to a drop down field, ... but in typing this I suppose a drop down field is better as you can also just type a letter like Z into the drop down list and it will choose Z and then immediately filter the company list to your Zanzabar and Zebra customers.

If you have a customer named Zyzzyva it would be worth 43 points in scrabble and as I'm sure you aware, but for the benefit of others, is a genus of tropical American weevils often found in association with palms, and it's a snouted beetle (as opposed, I suppose, to a just regular old beetle). 
Photo of Mark Comish

Mark Comish

  • 680 Points 500 badge 2x thumb
So I THINK I can do what you said above.  I really only need the dropdown to have selections because when I import it is not showing the choices anymore (1000 limit thing). 

If when I import I do this...

As for automatically linking up to the Parent, the contacts import would need to populate the field typically called Related Company.  If the key field to the Company table is [Record ID#], then you would need to export the company table with the company name and the [Record ID#], use VLOOKUP in Excel to get the Record ID into the contacts and then import that field into Related Company.

then I really don't need to do the other stuff because it would populate the correct Company Name (related company) field in the contact table, correct?

The [Record ID#] number from the Companies table goes in the "Related Companies" field in the contacts table (import) file?

If so, it is time to brush up on my vlookup skills.
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 65,542 Points 50k badge 2x thumb
right, if you can get good at the VLOOKUP, then that will be way better than manually editing hundreds of Contacts.

Make sure that you use the the ",false" option on the VLOOKUP to ensure that you have an exact match on the company name and not just one that is close.