Discussions

Expand all | Collapse all

Importing data -

  • 1.  Importing data -

    Posted 29 days ago

    I am trying to Import a large data set that would update existing records in my 'Leads' table. The problem is that some of the Record ID#'s that I am trying to import do not exist. It may be for instance that the record was previously deleted. So, because the record ID doesn't exist anymore, I get an error message:
    "Error: Some record IDs specified were not found in the table."

    And I have to manually search for the Record ID that doesn't exist, and remove it, before I'm able to upload.

    Aside from going through one-by-one and locating which record ID is not valid, is there a way to update my records and either 1) create new records for the ones that aren't already present, or skip those and do the rest?

    (screenshots attached)

    Error - Record ID# not found

    Screenshot of Unique Record ID# used to update existing records



    ------------------------------
    Mathew Crist
    ------------------------------


  • 2.  RE: Importing data -

    Posted 29 days ago

    No problem.

    Set up a scratch pad import table by creating a new table and importing your data.  It will create the fields in the first import. 

    Make a Relationship to the real table where one real table entry has many scratchpad entries based on the Record ID which I assume is the Key field.

    On the real table make  a formula checkbox field with a formula of

    true

    Look that up down to scratch pad.

    Create  a saved table to table import with a filter where the lookup checkbox is checked.  ie the real entry exists  

    Next create a single record in a new table called Admin Import  it will be Record  ID  #1

    Make  formula URL button to delete  the entries in the scratchpad table  post back if you need help..

    Make a URL formula field button to run the saved import.  Post back if you need help.

    Make a url formula button to call up the import process  

    i suggest that you create a Report Link field from the Admin Record to the scratchpad table so you can have an embedded report if the scratch pad entries on the admin import Record. Or create a relationship so you can count the number of scratchpad entries  

    So the typical process will be to click to clear the scratchpad, then click to invoke the import tool, then click to run the saved table to table copy.

    So a bit of setup, but then the import routine will be stupid should mole



    ------------------------------
    Mark Shnier (YQC)
    Quick Base Solution Provider
    Your Quick Base Coach
    http://QuickBaseCoach.com
    mark.shnier@gmail.com
    ------------------------------



  • 3.  RE: Importing data -

    Posted 28 days ago

    Working now. Thanks!



    ------------------------------
    Mathew Crist
    ------------------------------



  • 4.  RE: Importing data -

    Posted 28 days ago
    Edited by Mike Tamoush 28 days ago

    If this is a one time thing, or a rare occurrence, you can also remove the non existing entries before import, using the Vlookup function in excel (you can do it anytime, but it requires a little work each time, but you could make a template in excel).

    You would need to open your import file, and add another worksheet (tab). Then also export all the existing record IDs to a spreadsheet. Copy those Existing Record IDs to your new tab (in your import file). Put those in column A. In column B, just write the word 'Exists' and fill down.

    Now, back on the main import worksheet, go to the first blank column you have and in the first cell (i am assuming you are in row 1) use this formula:

    =vlookup(A1,$X$1:$Y$200,2,FALSE) and drag that down your list of values.

    This is where A1= The record ID for import

    X1= the first cell on the new tab where your project ID is (so this really will be something like Worksheet1A1, but just click on that cell and excel will fill in the words)

    Y200= the last cell with the word 'Exist' in it (so this will be something like Worksheet1B200)

    (Really, X1:Y200 is a range, you are just dragging and highlighting everything in your new tab)

    On your main import page you will see that your new column with the formula will say 'Exist' in most places, and be blank in others. Sort your data by that column and then just copy and paste to import anything with the exist label.

    If you needed to repeat this weekly or monthly, you would make a template for your import where you keep that vlookup formula in there. Then each time you need to copy and paste your import date into the first sheet, and copy and paste the existing record id's to your second tab. So, perhaps not the best solution if this is a frequent occurrence?



    ------------------------------
    Mike Tamoush
    ------------------------------



  • 5.  RE: Importing data -

    Posted 28 days ago
    Thanks Mike. This is a good alternative, however, in this case it happens enough that having a separate table in Quick Base might make more sense. I appreciate the response!

    ------------------------------
    Mathew Crist
    ------------------------------