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...
Forum Discussion
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
------------------------------
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
------------------------------
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
------------------------------
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
------------------------------
- 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
------------------------------ - 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
------------------------------ - Kris, This idea of doing Add a bulk upset in a loop and then committing upsert outside the loop is genius !
------------------------------
Prashant Maheshwari
------------------------------ 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
------------------------------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
------------------------------
