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.
Question
How to create/define child record relationship on import
+7This topic has been closed for replies.
>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:
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.
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|DeltaI assume you want to ultimately import [Name], [Email], [Phone] into the parent table:
John Doe, john@doe.com, (800) 555-1212and simultaneously (1) parse, (2) import and (3) relate the following information into the child table:
AlphaWhen importing the child records you want to relate them to the corresponding newly created parent record.
Beta
Gamma
Delta
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.
+7Back 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.
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.
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
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
Sign up
Already have an account? Login
Login to the community
Login with Quickbase US – quickbase.com Login with Quickbase EU – quickbase.eu
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.