Forum Discussion

CharlotteM's avatar
CharlotteM
Qrew Assistant Captain
3 days ago

Pipeline: Create a parent record if one does not exist

I have a child table (created by form engine) which contains a field that will be the (custom) key to its parent.

The child table may have one or several records which should relate to one parent record.

I created a pipeline that does correctly create the parent records, but it seems slow, and we could have many people entering these child records at one time.  It is using an upsert (on rows created in the last five minutes, currently) to handle the fact that if there are multiple child records, only the first one should create a parent. 

I saw this discussion, below, and that's what led me to use the upsert - because of the comments about contention causing pipeline errors in this scenario if another strategy was used.

Automation to create record if does not exist | Qrew Discussions

This discussion is from several years ago though - is an upsert still the best way to go in this scenario? And is there any way to make it faster?

 

Pipelines
  • CharlotteM's avatar
    CharlotteM
    Qrew Assistant Captain
    3 days ago

    That would be quick enough. But in this case, I added 147 child records (via Import, because I'm testing reports, so the Form Engine doesn't come into it in this case, just FYI). And the pipeline then added 34 parent records - in 32 minutes.  So I think something is not right.  

    I've tried to provide a scrubbed YAML below, can you tell from this why it's so slow?

     

    A: On Insert

     

    - TRIGGER on_create -> a:
        inputs-meta:
          export_fields: '"Custom Parent Key, Related Record ID

          table: '"Child Table

    B: Prepare to insert or update parent table

     

    ACTION bulk_record_set define -> b:
        inputs-meta:
          export_fields: '"Custom Parent Key, Related Record ID1, Related Record ID2'
          merge_field: '"Custom Parent Key '
          table: '"Parent Table

    C: using only recent records as candidates (to avoid looking at  the whole table, which will get very large)

     

    - - QUERY record search -> c:
        FILTERS:
        - AND:
          - created_at less-than:
            - '5'
            - minutes
            - past
            - ''

    D: Prepare records for insert or update

     

        inputs-meta:
          export_fields: '"Custom Parent Key, Related Record ID1, Related Record ID2'
          table: 'Child Table
    - c<>LOOP:
      - DO:
        - b<>ACTION quickbase bulk_record_set create -> d:

    E: Commit

     

            inputs:
              Custom Parent Key: '{{c.Custom Parent Key}}'
              Related Record ID1: '{{c.Related Record ID1 }}'
              Related Record ID2: '{{c.Related Record ID2}}'
        - b<>ACTION quickbase bulk_record_set commit_bulk_upsert -> e: {}

     

    • MarkShnier__You's avatar
      MarkShnier__You
      Icon for Qrew Legend rankQrew Legend
      3 days ago

      I think there's a problem with your pipeline. The pipeline should be as I described in the answer in your link. When a record is created, and it doesn't have a parent, then the pipeline is triggered. It creates a bulk record upsert, then  adds the one row into the upsert,  and it commits the upsert. There is no query for records created in the last five minutes. The pipeline simply acts on the individual Orphan records as they are created. 

  • MarkShnier__You's avatar
    MarkShnier__You
    Icon for Qrew Legend rankQrew Legend
    3 days ago

    I read my answer in the link provided and it's still valid.  I would guess that the parent would be added within 5 seconds of an Orphan child being created.  Is that not quick enough?