Hi, I have a spreadsheet from another database I need to import into Quickbase on a recurring basis & need help coming up with the right steps/ if logic.

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 & use the existing QB ID to create the parent-child relationship.

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" & 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"

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. I'm working on a project to get that data back from the accounting software & 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. 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.

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

Forum Discussion

Import Pipeline Workflow

Merge Imports

Pipeline after import (heavy traffic/transient error)

Pipeline to add Parent record from child import

Three Words of Advice on Pipelines

Error listing for Pipelines?