Forum Discussion

JennasonQuick_B's avatar
JennasonQuick_B
Qrew Trainee
4 years ago

Looping through field values in Pipeline

I have a record that contains a text field with multiple values separated by commas (which I can also turn into a multi select field if that makes things easier).     I want to loop through the values in the field and within each loop perform a QB record update.

In pipelines, I understand how I can perform a loop based off a list of records, but in this case I need the loop to be based off a list of values in a field.... any way to do this?

Thanks.

------------------------------
Jennason Quick Base Admin
------------------------------
  • I thought this was an interesting puzzle and I need to get better at looping Pipelines so I got it working as a challenge.

    There are two pipelines involved, a pipeline to initiate the process and a Callable Pipeline which will create the children.

    Because I'm not very good with Jinja, created to helper fields on the parent record. The first is a formula field which will take the text value of the multi select field and append STOP onto the end of it.  call that Processing Step 1

    Curly ; Larry; Moe ; STOP

    I created another helper field which is just an empty text field called [Processing Children]

    Then I created another helper formula field called [Processing Step 2] which was the Trim(NotLeft([Processing Children],";"))

    Then lastly I created a formula field for the [Next Remaining Child] as the Trim(Left([Processing Children]).

     The process initiates when the multi select field is updated on the parent record. The first pipeline kicks off and copies the value of the multi select field into the field called [Processing Children].   

    So now [Processing Children] contains  Curly ; Larry; Moe ; STOP

    The next and last step of the first pipeline is to call the Callable Pipeline.

    The idea of the Callable Pipeline is to operate like Pac-Man and gobble up the children that need to be created starting from the left side of [Processing children].   So the callable pipeline will look up the record where the argument of the Record ID of the trigger Parent was passed to it as an argument.

    It does a step to "Look Up a Record"  and then Branches on checking whether the name of the last remaining child is not STOP.

    If it passes that test then it goes ahead and creates the child record and then edits the parent record to put the [Processing Step 2] to the field [Processing Children].  The effect of that is to change that field [Processing Children] to have one less entry which is now been gobbled away on the left side.

    The Callable Pipeline then loops by calling itself.

    If you're still with me, you might ask why did I have to introduce STOP at the end of the string. Why didn't I just I override the field with [Processing Children] blank when the [Processing Step 2] became blank and then stop loping when the [Next Remaining Child] became blank.   

    Well I learned from other postings on this forum that when a pipeline step attempts to put a blank into a field,  by design it does not do that. clobber data based on a blank.   If you want to clear a field using a pipeline you have to use very specific syntax of

    {{CLEAR}}

    into the field.

     
    Of course I didn't realize the first time so my Callable Pipeline got into an infinite loop because when the last pipeline loop happened and Pac-Man had gobbled up all of the remaining children the pipeline would not write blank into the field.  It just kept creating more and more identical children for the last of the multi select choices.

    So that is when I had the last inspiration to introduce a specific STOP indicator.

    If you have never worked with Callable  Pipelines before https://help.quickbase.com/pipelines/callable_pipelines_channel.html  the idea is you can invent whatever name you want for the Callable Pipeline (but I think you need to stick to the regular letters of the keyboard and no spaces but an underscore is OK).

    My name was

    Iterate_Children(Record)

    You get to use any comma separated words you would like to define the parameters you want to pass.  In my  case what I needed to pass was the Record ID of the initial trigger record and I happened to use the parameter name "Record" for that. 

     As an aside, the process didn't run particularly fast but after about 15 seconds or so it did complete and get the half a dozen or so children written.  

    Feel free to post back with questions. 

    There is a whole other way to do this which is the way I would've done it using automations. My method with automations was that I knew that there would be say a maximum of 20 selections in a multi select field.  I would parse out those 20 children into separate fields on the parent record and create 20 child records.  The last step of the automation would be to delete any blank children.   



    ------------------------------
    Mark Shnier (YQC)
    mark.shnier@gmail.com
    ------------------------------
  • You can use the Text channel to "Find All Matches to a Regex", and use the following Regex: ([^;]+) if you go with the multi-select option which separates values using a semi-colon.  Otherwise, you can change the semi-colon to a comma in the middle of the regular expression, since the comma is the delimiter in your use case: ([^,]+)

    Then For each "match/Regex filtered group" (because it's split by a semi-colon or a comma in your case), you can then use each group to create a new record.

    So in the subsequent create record step, map the regex step's Group 1 to the new table: {{b.group_1}}

    See screenshots below for reference.

    Enjoy!

    ------------------------------
    Kristoffer Keene
    ------------------------------
    • JennasonQuick_B's avatar
      JennasonQuick_B
      Qrew Trainee
      Kristoffer- Many thanks for your reply.     A follow up question to see if I am understanding this method correctly:

      Does this method require there be the same # of items in the list of values everytime?     In my situation I will have  records where the multi-value field has different #s of values (E.g.   record 1 might have 1 value in that field, record 2 might have 2 values in that field, etc).   So in each case I need to make sure the loop in pipelines can be dynamic based on the # of values in that field.

      I think what was throwing me off is the 'Group_1' notation and I'm just not familiar with the Regex step.     Does the regex step provide this 'dynamic' capability?​  Or does 'Group_1' mean the first value in the group? and subsequently there would be a 'Group_2', etc if the field had multiple values? which would seem to imply it doesnt provide that dynamic capability.

      Apologies if I am not following correctly.   Thanks.​​​

      ------------------------------
      Jennason Quick Base Admin
      ------------------------------
      • KristofferKeen1's avatar
        KristofferKeen1
        Quickbase Staff
        You are correct that regex group will be dynamic, meaning if there are 3 values split by a comma (value1, value2, value3) then three records will be created.  If the next time there are 5 (value1, value2, value3, value4, value5) then five records will be created.  Group means for each match found in the regular expression (in your case a new group for every comma)

        ------------------------------
        Kristoffer Keene
        ------------------------------
    • PaulPeterson1's avatar
      PaulPeterson1
      Qrew Assistant Captain
      @Kristoffer Keene, I need to do something nearly identical, how could this be modified to iterate through each and search another table to create a bulk upsert?​

      ------------------------------
      Paul Peterson
      ------------------------------
      • KristofferKeen1's avatar
        KristofferKeen1
        Quickbase Staff
        Hey Paul, I hope the screenshot below helps direct you better:

        You could prepare the upsert as step b and have one final import/upsert at the very end of all records found, but in the example above, I showed how to prepare and upsert per split.

        I think your biggest question is around getting the search step to match the split field, so here's another screenshot setting the search query to match the Regex's group 1 response:


        Let me know if you have any additional questions :)


        ------------------------------
        Kristoffer Keene
        ------------------------------
    • BrittanyScheid's avatar
      BrittanyScheid
      Qrew Cadet
      Hi Kristoffer,
      I'm brand new to regex but I'm trying to loop through the users in a List-User field to then create a child record for each. I used the expression and process you indicated above, but it seems to only be returning the first user, not any of the others. I wasn't sure what all the other fields should be set to in the Query step, so I just left them as the defaults. Any help would be greatly appreciated! Thanks!


      ------------------------------
      Brittany Scheid
      ------------------------------
      • KristofferKeen1's avatar
        KristofferKeen1
        Quickbase Staff
        Hey Brittany-
        Looking at your screenshot, the jinja looks correct.  So I want to double check:
        • that you grabbed the correct list-user field?
        • there is more than 1 user listed in that field for the record you found
        • do you have the for each loop after this step?


        ------------------------------
        Kristoffer Keene
        ------------------------------