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.
