Forum Discussion

MichelleCoslet's avatar
MichelleCoslet
Qrew Assistant Captain
6 years ago

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

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
  • QuickBaseCoachD's avatar
    QuickBaseCoachD
    Qrew Captain
    6 years ago
    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
    • MichelleCoslet's avatar
      MichelleCoslet
      Qrew Assistant Captain
      6 years ago
      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
    • QuickBaseCoachD's avatar
      QuickBaseCoachD
      Qrew Captain
      6 years ago
      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