Import data into text (Look up) field, numeric (reference) & build in Date Created fields

  • 0
  • 1
  • Question
  • Updated 2 months ago
  • In Progress
I have a spreadsheet full of data that I need to import into my App.
The three fields that are NOT available to import into are:
  • Assembly ID [text (look up)] field
  • Related Operator [Numeric (reference)] field
  • Date Created (Build in Date/Time) field
Is there a way around this? I have over 7k entries that need to be imported so doing so by hand is not really an option.

TIA
Photo of mkosek

mkosek

  • 918 Points 500 badge 2x thumb

Posted 2 months ago

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

QuickBaseCoach App Dev./Training, Champion

  • 69,814 Points 50k badge 2x thumb
It is not possible to update the [Date Created] field.  That is a built in system field.  But you can always create a new field to accept the one time import value for the date created and then have your own formula field to use the one time import date field if present or else the native [Date Created] field.

To get the [Related Operator] you will need to download the operator table into excel with the operator name and the Record ID# and then use a VLOOKUP (....,false) to lookup the record ID# of the operator.

same with Assembly ID
Photo of mkosek

mkosek

  • 918 Points 500 badge 2x thumb
What do i do with this information though. I saved the Operators table as a spreadsheet. I see the record ID (Operator ID in my case).
Not sure on the next step.
Thank you
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 69,814 Points 50k badge 2x thumb
On your main excel file with 7,000 record you will have a tab for data imports.
Then you will have a separate sheet (tab) for the operators and their Operators IDs.

Presumably your data imports sheet has all the names of the Operators, but what you need to do the import is the corresponding [Record ID#] in Quick Base.  You will then add a column to your data imports sheet with a formula something like this

=vlookup(select the column with the operator name, select the table range of the sheet with the operators and mark the cell references with $ to make them absolute, then type the column off set, and then type false to ensure you get an exact hit).

You can contact me directly if you need help with that data manipulation.

Mark Shnier
www.QuickBaseCoach.com