Import Pipeline Workflow

Question

Hi, I have a spreadsheet from another database I need to import into Quickbase on a recurring basis &amp; need help coming up with the right steps/ if logic.&nbsp;
Spreadsheet contains a unique ID from the other DB, as well as a unique ID from a soon to be parent record already in QB. The majority of the records will have a 1:1 parent-child relationship, in which case it's easy to import the records &amp; use the existing QB ID to create the parent-child relationship.&nbsp;
However, there will be some records (roughly 5-10% of my spreadsheet) that contain matching soon-to-be QB parent RIDs. In these instances, there will usually be 2 records with matching RIDs but could be as many as 4 or 5 I believe. In this situation, I need a couple things to happen:

All records containing matching parent RIDs should be associated to the existing parent, AND
of those records, I want to find the record with the highest numerical "other database unique ID" &amp; create a new parent record for it. This parent record will have most of its fielded data populated based on data from the child. Child record then needs to be associated to the new parent.
Step 2 would repeat with the next highest numerical "other database unique ID", creating a new 1:1 parent-child relationship with each subsequent record with a matching QB parent RID until all records w/ matching RIDs have 2 parents: the original that already existed before this process began, and a new parent that's been created as part of this process. The only child record that would have a single parent would be the first record, the one with the lowest numerical "other database unique ID"&nbsp; &nbsp;

The context of this setup is that I have "timecard" records where employees track how they bill their time, which are created in Quickbase. These records have to get exported to an external accounting software. Once imported into the accounting software, sometimes timecard records will get split in half, in the case where time is billed to multiple clients (i.e. employee creates a time entry accounting for 4 hours, client A pays for 2/4 hours, client B pays for the other 2 because we're working on a joint project of some sort). This splitting process creates a new record that never existed in Quickbase. The newly created record gets stamped with the original QB RID from the split record.&nbsp;I'm working on a project to get that data back from the accounting software &amp; into Quickbase to have better access to the data, knowing when/if we got paid, whether some of the time wasn't actually billed to the client for a particular reason, etc.&nbsp;When receiving this data back, I'm importing it to a new child table, with the original timecard record being the parent. In the case of split time entries, we want to know which new child record(s) are tied to the original parent, AND we want to be able to see that actually it got split during the billing process, so we need a new parent record associated to the 2nd client to be able to track what actually got billed to whom. Hope that makes sense.&nbsp;------------------------------Harrison Smith------------------------------

chayceduncan · Answer

A lot to unpack there so let me repeat back how I understand it.&nbsp;
Quickbase:&nbsp;
Employees enter singular time entries targeted to a specific client. One entry for each timestamp.
Accounting System:&nbsp;
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:&nbsp;
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.&nbsp;

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&nbsp;another&nbsp;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?&nbsp;

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:&nbsp;

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
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
For that new parent record - copy over the 'split' records that should move over to the new parent. Just a straight copy
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
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 &amp; 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.&nbsp;

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.&nbsp;------------------------------Chayce Duncan------------------------------

harrisonsmith · Answer

Thanks Chayce. Your understanding of the issue is correct. Interesting idea to split this out into 2 steps instead of trying to handle it all at once during the import. I think I'll try that & see how it goes

------------------------------
Harrison Smith
------------------------------

Forum Discussion

Import Pipeline Workflow