Discussions

Expand all | Collapse all

Looping through field values in Pipeline

  • 1.  Looping through field values in Pipeline

    Posted 21 days ago
    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
    ------------------------------


  • 2.  RE: Looping through field values in Pipeline

    Posted 21 days ago
    Edited by Mark Shnier (YQC) 20 days ago
    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
    ------------------------------



  • 3.  RE: Looping through field values in Pipeline

    Posted 19 days ago
    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
    ------------------------------



  • 4.  RE: Looping through field values in Pipeline

    Posted 19 days ago
    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
    ------------------------------



  • 5.  RE: Looping through field values in Pipeline

    Posted 19 days ago
    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
    ------------------------------