Trigger automation based on a count or multiples of a count
Hello. Im testing out triggering an automation based on a countif, but I cant figure out the logic to apply the automation as the countif increases. For instance. I want the automation to run each time we get 5 submittals via a form for the same category. Right now I'm using a metric sheet and a countif with a mod formula.. but that only works if we get exactly 5 submitted before the automation runs.. if we get 6, it fails. Any ideas on a formula that would account for that?
Best Answers
-
Hi @TimMelton
Different numbers of comments come each time in the Comments columns, like 1,3,2,3,4,1, and so on.
First, I added a helper column, "Cumulative Comments," with the following formula, assuming we have a Row ID or Row No columns.
=SUMIF([Row ID]:[Row ID], <=[Row ID]@row, Comments:Comments)
Then, the "INT( /5)" helper column with the following formula returns results like 0, 0, 1,1, 2, and so on.
=INT([Cumulative Comments]@row / 5)
To determine if you need to trigger the automation, you can check if the INT number changed from the one row before.
The formula to determine this is the following;
=IF([Row ID]@row > 1, IF(INDEX([INT( /5)]:[INT( /5)], [Row ID]@row - 1) < [INT( /5)]@row, 1, 0), 0)
The first IF prevents an error at the first row when we convert the formula to a column formula. Then, the INDEX([INT( /5)]:[INT( /5)], [Row ID]@row - 1) returns the INT column value one row before. So, the formula checked if the value is less than the current row's value. If we get more than five comments, we trigger the automation.
You can get the value in the previous row by combining Row ID or Row Number and the INDEX.
https://app.smartsheet.com/b/publish?EQBCT=f2fca89d42d94c1ba9df16302eac61b5
-
Hello.. that makes sense.. thanks much, I'll give that a try
Answers
-
Hi @TimMelton
Different numbers of comments come each time in the Comments columns, like 1,3,2,3,4,1, and so on.
First, I added a helper column, "Cumulative Comments," with the following formula, assuming we have a Row ID or Row No columns.
=SUMIF([Row ID]:[Row ID], <=[Row ID]@row, Comments:Comments)
Then, the "INT( /5)" helper column with the following formula returns results like 0, 0, 1,1, 2, and so on.
=INT([Cumulative Comments]@row / 5)
To determine if you need to trigger the automation, you can check if the INT number changed from the one row before.
The formula to determine this is the following;
=IF([Row ID]@row > 1, IF(INDEX([INT( /5)]:[INT( /5)], [Row ID]@row - 1) < [INT( /5)]@row, 1, 0), 0)
The first IF prevents an error at the first row when we convert the formula to a column formula. Then, the INDEX([INT( /5)]:[INT( /5)], [Row ID]@row - 1) returns the INT column value one row before. So, the formula checked if the value is less than the current row's value. If we get more than five comments, we trigger the automation.
You can get the value in the previous row by combining Row ID or Row Number and the INDEX.
https://app.smartsheet.com/b/publish?EQBCT=f2fca89d42d94c1ba9df16302eac61b5
-
Hello.. that makes sense.. thanks much, I'll give that a try
-
Happy to help!😁
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.9K Get Help
- 379 Global Discussions
- 210 Industry Talk
- 441 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 300 Events
- 33 Webinars
- 7.3K Forum Archives