Forum Discussion

BradElmore's avatar
BradElmore
Qrew Assistant Captain
2 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" for Parent Table A --- Note: there may be multiple child records that have the same ParentID that need a "new" related parent record --- I'm not sure I would go about creating a Unique List of Parent records that need to be created. - appreciate any help.

  • 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. 

  • 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

      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

        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?  

  • I can think of a way to do this but it requires having a custom key field on the parent table. ...   i.e. a field other than the Record ID which can be the key field.

    Do you have any way to contrive a standardized format for the Custom Key field? Obviously it would have to be based on fields in the imported child records which could be combined to make a custom key field to uniquely identify the parent. 

    Assuming that you can come up with a custom Key field, then what the process will be is that when each individual child record is  imported it will trigger a pipeline which will create a Bulk Upsert, add just  that 1 row, and then Commit the Upsert.

    That way when two children essentially trigger at the same time both seeking to create the same parent record, the one that wins the race (Noah Lyles @ 9.784 seconds) will do the create and the one that comes in second (Kishane Thompson @ 9.789 seconds) in that race will end up doing an upsert merge.

     

    • BradElmore's avatar
      BradElmore
      Qrew Assistant Captain

      Mark, hope you are doing well.

      I do have a custom key -- 

      When creating my pipeline -- do I first create a Trigger? "Record Created" - on the child table? And then add the Action: "Prepare Bulk Record Upsert" or "Add a Bulk Upsert Row" followed by the action: "Commit Upsert"