Forum Discussion

CharlotteM's avatar
CharlotteM
Qrew Assistant Captain
7 years ago

How to create/define child record relationship on import

I am importing tables from an outside application through the Sync feature.

There are several one-to-many relationships between them, which QuickBase needs to be able to work with.

However, the key of the main parent does not exist in the child tables, which would allow quick definition of the table relationships within QuickBase.

Rather, each main parent record contains a formatted text field which includes a list of keys to all of its children.  To make it more complicated, the order of the keys listed in the string is actually also relevant because the order defines part of the key of one child table we must relate to. 

So I think that  each main parent record would have to have this field parsed to derive the list of child keys, and the order of them, and then using this list matched with the parent's own key, one or more (usually more) child records could be generated to express the relationships needed.

There could be up to 60 of these children per parent.

I know this can be done in code/script - can it be done natively?

Will the new automations feature help?  I didn't see anything that seemed directly applicable (loops), but I'm hoping I missed it.
APIs and custom code
Data import and export
  • _anomDiebolt_'s avatar
    _anomDiebolt_
    Qrew Elite
    7 years ago
    >I know this can be done in code/script - can it be done natively?

    You must dispossess yourself of this aversion to solving problems with script,

    From you description I take it that within each imported record there is (1) information that define the parent fields that you want to import and additionally there are (2) one or more string fields that implicitly define a set of child records that need to be created and related to the parent record.

    For example if this was one of the records in the connected table:
    John Doe, john@doe.com, (800) 555-1212, Alpha|Beta|Gamma|Delta
    I assume you want to ultimately import [Name], [Email], [Phone] into the parent table:
    John Doe, john@doe.com, (800) 555-1212
    and simultaneously (1) parse, (2) import and (3) relate the following information into the child table:
    Alpha
    Beta
    Gamma
    Delta
    When importing the child records you want to relate them to the corresponding newly created parent record.

    You may have some additional issue associated with the key field being used in the parent or child tables but I don't think that changes the general solution I have in mind.

    This can easily be done with script but an implementation depends you your specific tables and fields and other minor details that you did not describe.
  • CharlotteM's avatar
    CharlotteM
    Qrew Assistant Captain
    7 years ago
    Back from vacation!

    Yes, your understanding is correct.  Although there are actually four tables being brought in.


    Parent ("Invoice")

    Child ("Invoice Line") - which can be correctly matched to the parent with the information given

    3rd table ("Timesheet") - which has information which must be related to an Invoice Line.  Note that the relationship is (0,1:1) here - that is, each invoice line may have 0 to 1 related timesheet lines, and each timesheet line relates to 1 and only 1 Invoice Line.


    This would be simple to do if they included any info on Timesheet line to relate to the other tables, but they don't.  [I thought I had a way to force this but - no.]  What they do is introduce another table:


    4th table  ("Invoice Link").  This is the one with the strings to parse.

    This table can have the same key as "Invoice."  Within each record, it then has, among many other fields, a field which they call the "Transaction Link."  This field has a string which looks, for example, like this:

    [('7209','TimeActivity',null), ('7284','TimeActivity',null), ('19006','Payment',null)]
    Let's say this string above goes with invoice 12345

    The numbers at the beginning of each ( ..... ) are the keys to all types of linked transactions -- including timesheets.  So we need to loop through the (.....), and if we see 'Time Activity' We need to grab the number right before it, and then create a new record with

    Invoice Key |    Invoice Line Item # (from the order)     |   Timesheet Key
    Thus....
    12345  |  1  |  7209
    12345  |  2  |  7284
    I know this is probably not difficult to accomplish, but I haven't done any coding in QuickBase and am not sure where to start.
  • _anomDiebolt_'s avatar
    _anomDiebolt_
    Qrew Elite
    7 years ago
    Normally when I file input element is on a form the user selects a file from their local file system and when the form is submitted the file is sent unmodified to the server. In your case you want to parse and manipulate the selected file and send the modified contents to multiple tables. This can be done in JavaScript using a feature called a FileReader. In a nutshell, after a file is selected and the FileReader objectis  created the CSV contents would be parsed into an array. Next, portions of this array are selected in sent to QuickBase using API_ImportFromCSV.

    When calling API_ImportFromCSV you would specify the parameter clist_output to obtain the [Record ID#] and other fields from the newly created records. Knowing the [Record ID#] of the newly created records allows  you to import secondary and tertiary data to other tables and relate them appropriately.

    The key to this whole procedure is using the FileReader object and the clist_output parameter.

    Unfortunately I can't create a generic demo of this process as it depends on a lot of small details of your tables, fields and the precise format of the CSV file being used. If you want to pursue a solution feel free to contact me off-world using the information in my profile:

    https://getsatisfaction.com/people/dandiebolt