Import Pipeline Workflow

A lot to unpack there so let me repeat back how I understand it. 

Quickbase: 

Employees enter singular time entries targeted to a specific client. One entry for each timestamp.

Accounting System: 

Quickbase data is imported in, and in some cases that time entry is split between 2 or more clients/projects. The original Quickbase RID is retained on both splits since they both stemmed from the original Quickbase entry

Back to Quickbase: 

Accounting data is being reimported into a child table underneath the original time entry. So if you change nothing about this process - then a time entry might have more than 1 split associated to it upon import. 

Is all of that accurate? Based on the end of your comment, what it sounds like your then needing is a process to handle those splits, and in the case where the split is to another client / project - that another Quickbase Time entry needs to be created to act as the Quickbase parent only - and to act as the effective parent for that split to roll it up to the proper client/project? 

IF I'm at least close on the above - my suggestion would be to basically do the import exactly as is. Just load the data. Once that spreadsheet is loaded - have a process that in some way shape or form does something like the below: 

1. Target the list of time entries that have more than 1 split. A simple summary numeric counter can tell you in your relationship if it's 2 or more. That same relationship can summarize the Max Accounting System RID so you have that too in your time card entry. Essentially we want to loop only the Timecards that this matters for
2. For each of these timecards - take the information from the split(s) that you need and create the 'New' Timecard. When you do this - you should probably stamp it somehow that it was a system reconciliation record so that you don't accidentally re-download it to upload into your accounting system as a new entry. Also helps for reporting
3. For that new parent record - copy over the 'split' records that should move over to the new parent. Just a straight copy
4. On the old splits - the ones that you're effectively trying to move out from under the original time card, mark a checkbox field or something that says 'split' and put the Record ID of the new parent. You don't need a relationship necessarily, just something that lets you know for reporting that the split is invalid
5. On your timecard - you can then roll up the 'effective' time that actually gets billed. So if in your example Time Card was for 4 hours to Client A, Accounting splits that 2/2 between Client A & B. This process would create a new time card for Client B - that time card copies over a 2 hour split. You end up with (3) split records in total since you're copying one of them in Quickbase only, but if you do a summary field of the 'total time billed' - the original timecard would only summarize 2 hours in total because you can ignore the split that you marked as having been moved/copied. 

The advantage of this type of approach is that 1) you always have the full data set. You're not trying to intercept of step in before you have all the info in Quickbase. The full history is always available. 2) By creating 'new' splits and marking off the ones that moved - you can still clearly tell the story for each time card of what happened. It's like a built in log that says I show entries for Client A of 4 hours but we're only billing 2. Well here is the reason is that it was split halfway and was moved over to Client B.