Forum Discussion

BradElmore's avatar
BradElmore
Qrew Assistant Captain
5 months ago

Pipeline to add Parent record from child import

I have two tables -- Parent Table A and Child Table B. When importing records into Child Table B --- if there is not a Parent Table A "related record" -- I would like to create a new "Related Record...
Pipelines
  • MarkShnier__You's avatar
    MarkShnier__You
    5 months ago

    OK great.

    Trigger: When Record Added on Child Table

    Step: Create a Bulk Upsert (for Parent table)

    Step:  Add Row

    Step: Commit Upsert.

    It would be nice if they gave us a new Quickbase Pipeline step which would automatically merge in a single record into a  table where the key field may already exist but they don't so we have to go through the hoops of creating a Bulk Upsert with just one row in it and Committing it one row at a time. 

MarkShnier__You's avatar
MarkShnier__You
Icon for Qrew Legend rankQrew Legend

OK great.

Trigger: When Record Added on Child Table

Step: Create a Bulk Upsert (for Parent table)

Step:  Add Row

Step: Commit Upsert.

It would be nice if they gave us a new Quickbase Pipeline step which would automatically merge in a single record into a  table where the key field may already exist but they don't so we have to go through the hoops of creating a Bulk Upsert with just one row in it and Committing it one row at a time. 

BradElmore's avatar
BradElmore
Qrew Assistant Captain
5 months ago

Mark,

I'm running into an issue -- where the Parent record is not being created -- is there a record limit (for using this pipeline method)? Let's say I have 15,000 records created -- and the pipeline is triggered -- with a filter (on the "When record created") that limits the 15,000 -- down to 8,000 -- out of the 8,000 -- I need 4 Parent records created -- however -- only 2 of the 4 Parent records are created -- any ideas to why? Thank you.

  • MarkShnier__You's avatar
    MarkShnier__You
    Icon for Qrew Legend rankQrew Legend
    5 months ago

    There's no limit.  I'm confused though about what's going on here. You say you are importing 15,000 child records.  

    Where does the 8,000 number come in to play.  Maybe you are saying that the child records have a custom key field and while you are importing 15,000 records, 8,000 records are new records and the balance are going to be updates to existing records? 

    Then are you saying of the set of 8,000 records which are being created by the import, you happen to know that you only need 4 parent records created but some of them are not getting created. Is that what you were saying?  

    • BradElmore's avatar
      BradElmore
      Qrew Assistant Captain
      4 months ago

      Mark,

      I’m working on a process where 15,000 records are being created. However, with the filter I’m using on the trigger (“When record created”) to focus only on records without a parent relationship, the pipeline should ideally only process 8,000 records. The “Custom Key” field is being used to create the Parent records.

      Here’s the workflow I’m following:

      1. Trigger: (8,000 records) When a record is added to the Child Table, filtered to only include records without a parent relationship.
      2. Bulk Upsert: Create or update records in the Parent Table using the [Parent Key Field].
      3. Add Row: The [Custom Key Field] from the Child Table is used to create the Parent record in the [Parent Key Field].
      4. Commit Upsert: Commit the upsert to finalize the changes.

       

      I just ran the pipeline again, and all four records were added successfully. However, the bulk upsert continues to run even after the Parent record is created. Is there a way to set the pipeline to end after the Parent record is created? For instance, if 2,000 out of the 8,000 records belong to Parent A, the pipeline should stop processing once Parent A is created from the first record, rather than continuing to re-trigger for the remaining 1,999 records. Once Parent A is created, its relationship with the remaining 1,999 records should be established automatically.

      Could you please advise on how to configure the pipeline to stop after the initial creation of the Parent record?

      Thank you

      • MarkShnier__You's avatar
        MarkShnier__You
        Icon for Qrew Legend rankQrew Legend
        4 months ago

        As far as I know that's the only way to do it. These are all native Quickbase steps so depending on your plan they may not count against any Step Run usage, so "who cares" if it has to needlessly waste its time.  The challenge would be that you would also be incurring step runs if they were processed sequentially to check if the parent exists. And if they were protest sequentially it might take a lot longer to check all 8,000 records.

        When you import 8,000 records now it triggers the pipeline 8,000 times and Quickbase will spawn those off into about maybe 20 or so parallel processing channels using the AWS cloud engine.  

        I can see that it's needlessly perhaps affecting the performance of your app and impacting regular users response time it does those 8,000 upserts but I don't see away around that.