Discussions

Expand all | Collapse all

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

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

    Posted 04-24-2019 18:39
    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


  • 2.  RE: Import data into text (Look up) field, numeric (reference) & build in Date Created fields

    Posted 04-24-2019 19:06
    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


  • 3.  RE: Import data into text (Look up) field, numeric (reference) & build in Date Created fields

    Posted 04-24-2019 19:26
    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


  • 4.  RE: Import data into text (Look up) field, numeric (reference) & build in Date Created fields

    Posted 04-24-2019 19:35
    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">http://www.QuickBaseCoach.com">www.QuickBaseCoach.com