Forum Discussion

VincentVincent's avatar
VincentVincent
Qrew Trainee
10 years ago

import from table and transfer relationship

Ok so... i've checked a lot of documentation but nothing seems to answer the specifics of my circumstances (either that or it's worded in a way that i'm not connecting) and i'm not sure how to resolve this.

little background: We are using QB and had switched from our last DB a while ago. I'm now trying to bring over historic data. I've imported the data into a separate app for staging and data clean up. In there i've recreated the relationships that existed in the previous DB using their pre-existing relationship setup.

Process: I've setup an Import button to call the API_RunImport bringing over parent records that have been flagged for transfer (which is also marked as transferred afterward).

Problem: QB is already setup with Record ID# as the key fields for all tables and relationships so i cannot change key fields (right?). Is there a way to bring over the detail records and set the relationship based on the relationship that already exists?
Data import and export
Forms
Relationships
  • ArchiveUser's avatar
    ArchiveUser
    Qrew Captain
    10 years ago
    UPDATED ANSWER:

    Hi Vincent,

    The easiest way is as you have done, export both tables, import the parent mapping your old record id or key field to a field (named old record ID or something), then export it again then then do a match and index formula in excel to map the old field to the related record id in the child table record an return the new record id. 

    Then import the child records again with the child record id mapped to an old version field.


    Hide the two old record id fields from your forms, default report settings etc., but they will be there should you want to investigate any mapping issues down the line.



    Jack
  • VincentVincent's avatar
    VincentVincent
    Qrew Trainee
    10 years ago
    Thank you. I am not importing to the record id, i'm following the instructions from the online help for importing into a table from another table, so i have the SourceID/DestinationID thing going on. Additionally, the table already has a custom key field where the "record id" values were migrated from the other system. The source table is static and will not change over time.
  • VincentVincent's avatar
    VincentVincent
    Qrew Trainee
    10 years ago
    Ok, i've come up with this work around. Please let me know if there is a better way.

    Basically i split the process in two, and i first import the parent record and then get the Record ID# from the newly created record manually and paste it into a "Parent ID" field on the source table which is a look up field on all children, so when i do the 2nd step import, it puts the parent ID into the "related parent" field.

    I was hoping for a less manual process, if possible.
    • MelissaDoran's avatar
      MelissaDoran
      Qrew Cadet
      8 years ago
      This may have been the most understandable solution I've found (and least time consuming). Thanks for sharing. If you found a better solution, I would LOVE to hear it. :)