Discussions

 View Only
Expand all | Collapse all

Looping through field values in Pipeline

  • 1.  Looping through field values in Pipeline

    Posted 10-02-2021 11:49
    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 10-02-2021 18:17
    Edited by Mark Shnier (Your Quickbase Coach) 10-03-2021 08:43
    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 10-04-2021 12:13
    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 10-04-2021 12:35
    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 10-04-2021 13:44
    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
    ------------------------------



  • 6.  RE: Looping through field values in Pipeline

    Posted 04-01-2022 12:34
    @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
    ------------------------------



  • 7.  RE: Looping through field values in Pipeline

    Posted 04-01-2022 16:40
    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
    ------------------------------



  • 8.  RE: Looping through field values in Pipeline

    Posted 04-01-2022 17:46
    Edited by Paul Peterson 04-04-2022 07:47

    Thank you!  This is creating the results I need.  It's painfully slow and that is a concern since this was run in a test instance of the app with about 10% of the data in the production app.  Fortunately, I have a meeting with an SA soon and will use this as the starting point and will share their suggestions.

    Thanks again for your help!!



    ------------------------------
    Paul Peterson
    ------------------------------



  • 9.  RE: Looping through field values in Pipeline

    Posted 08-23-2022 10:42
    Sorry to jump in this conversation, but that's the only one that seems close to my dilemma!

    I need to have the pipeline loop through 2 fields ( from the updated record). 
    The fields are 2 dates and I need to create a record for each date in an unrelated table. 

    Any suggestions?
    Thank you!


    ------------------------------
    Francesco Spiga
    ------------------------------



  • 10.  RE: Looping through field values in Pipeline

    Posted 08-23-2022 10:54
    Hey Francesco-
    How are the date fields separated? (with a semi-colon or comma or another delimiter?)

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



  • 11.  RE: Looping through field values in Pipeline

    Posted 08-23-2022 11:02
    Thanks for your quick reply Kris. 

    Well, I have the two dates in 2 separate fields. 
    But I could concatenate them in a formula text field with either a comma or a semi-colon... doesn't really matter at this point. 
    But it seems that in order to use "Find All Matches to a Regex" I need to have a single string to work with, correct?

    Although, the real question is: How do I loop between those 2 values?

    Thanks again for your help. 


    ------------------------------
    Francesco Spiga
    ------------------------------



  • 12.  RE: Looping through field values in Pipeline

    Posted 08-23-2022 11:11
    If it's just a single date in 2 different fields, then I would just have 2 Create Record steps, 1 step for the first field and create a new record in Table A, and another step for the 2nd field and create a new record in Table B.

    You could also have 2 different pipelines be triggered off the two different fields and then the next step creates a different record based on the respective date fields.

    However, if you are looking to loop through a concatenated field and say the data is separated via a semi-colon, then 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.

    Hope that helps,
    Kris

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



  • 13.  RE: Looping through field values in Pipeline

    Posted 08-23-2022 11:21
    I probably should have clarified earlier in my 1st message :( sorry... 
    I am trying to loop through those 2 dates and create new records for all the dates in between them. 

    Example: 8-23-2022, 8-27-2022
    This will create 5 records in a new table (8/23,8/24,8/25,8/26,8/27) 

    How would you manage this use case via pipelines?

    ------------------------------
    Francesco Spiga
    ------------------------------



  • 14.  RE: Looping through field values in Pipeline

    Posted 08-23-2022 11:40
    Oh that makes more sense!

    I recommend creating a "template days" table with every date listed 1/1/22, 1/2/22, 1/3/22 all the through say ....12/30/2030, 12/31/2030  (Feel free to go farther in time, but at any rate, I would create a quick excel table to list all the dates and import them to your template table)

    ...

    From there when your pipeline is triggered, you could search through all the records in between the 2 dates, I believe the Query with Date "is between" includes your two dates


    Then have the pipeline create a new record for every date that it finds.  That's the short of it.

    However, even more efficient would create a bulk upsert, which is probably what you were originally thinking in this thread.  So when all the dates are found in between and added to your bulk upsert, then you commit the upsert/import all of your dates.  See screenshot below for a list of your steps:


    Hope that helps even more :)
    -Kris

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



  • 15.  RE: Looping through field values in Pipeline

    Posted 08-23-2022 11:52
    Kris, This idea of doing Add a bulk upset in a loop and then committing upsert outside the loop is genius !

    ------------------------------
    Prashant Maheshwari
    ------------------------------



  • 16.  RE: Looping through field values in Pipeline

    Posted 08-23-2022 12:25

    Thanks for the suggestions/ideas!
    I ended up going with the "Find All Matched to a Regex"


    I built a formula field referencing the table with the calendar days, which I already had. 
    Anyway, I am very glad I run into this post!
    Have a great day!



    ------------------------------
    Francesco Spiga
    ------------------------------



  • 17.  RE: Looping through field values in Pipeline

    Posted 08-25-2022 15:10

    You can also use Jinja to create the dates in between a start and end date:

    {% set startDate = "08/25/2022" %}
    {% set endDate = "09/05/2022" %}
    {% set days = ((time.parse(endDate) - time.parse(startDate))|string).split(' ')[0]|int %}
    {% for x in range(days+1) %}{{ (time.parse(startDate) + time.delta(days=x))|date_mdy}};{% endfor %}


    Then use the Regex step to create a list you can iterate:


    Hope that's helpful!



    ------------------------------
    Doug Henning
    ------------------------------



  • 18.  RE: Looping through field values in Pipeline

    Posted 11-30-2022 17:09
      |   view attached
    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
    ------------------------------



  • 19.  RE: Looping through field values in Pipeline

    Posted 11-30-2022 17:50
    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
    ------------------------------



  • 20.  RE: Looping through field values in Pipeline

    Posted 11-30-2022 18:00
    Hi Kristoffer,
    Thanks for your response. See my answers to your questions below:
    • that you grabbed the correct list-user field? Yes
    • there is more than 1 user listed in that field for the record you found. Yes, I've tried multiple various selections and triggered the pipeline several times.
    • do you have the for each loop after this step? Yes. See the attached screenshots.


    ------------------------------
    Brittany Scheid
    ------------------------------



  • 21.  RE: Looping through field values in Pipeline

    Posted 12-01-2022 11:52
    Hey Brittany-

    Figured it out!  Even though you could convert the UserList field into a formula text field and use the regex to split that text field.  Here's a way to not need that helper field and use the UserList field directly in pipelines.

    UserList is an array of objects so you need to extract the email attribute from all the objects and then parse that list.


    This Jinja will extract them to create a single string that you can use in regex:
    {{ a.user_list | map(attribute="email") | join(";") }}


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



  • 22.  RE: Looping through field values in Pipeline

    Posted 12-07-2023 18:41

    @Kristoffer Keene solid tip to Find All Matches to a Regex via the Text channel to parse and loop through the multi-select semi-colon delimited values! I like that approach.

    Thank you :)



    ------------------------------
    Brian Seymour
    ------------------------------