first
This post explains how to trigger an automation based on a count or a multiple of a count (specifically, run an automation every time you get 5 submissions via a form) in Smartsheet.
Questionsummary of
Tim Melton asked how to trigger automations based on number of form submissions. Specifically, I wanted to run an automation every time I get 5 submissions via a form in the same category.
Answer summary
jmyzk_cloudsmart_jp suggested a way to solve this problem using the formula below.
- Cumulative comment column
=SUMIF([row ID]:[row ID], <=[row ID]@row, comment:comment)
- INT( /5) column
=INT([cumulative comment]@line / 5)
- Automation trigger determination formula (comparison with previous line)
=IF([row ID]@row > 1, IF(INDEX([INT( /5)]:[INT( /5)], [row ID]@row – 1) < [INT( /5)]@ row, 1, 0), 0)
Summary
By using the above formula, you can trigger automations based on counts or multiples of counts in Smartsheet.
Key Points
- You must use a row ID or row number column.
- You can use the INDEX function to get the value of the previous row.
Translation of correspondence
Tim Melton ✭✭✭
Hello. I'm testing and I can't trigger an automation based on countif, but I don't understand the logic to apply automation as countif increases. For example, I want to run an automation every time I get 5 submissions via a form in the same category. I'm currently using metric sheets and countif using mod expressions. But this only works if exactly 5 are sent before the automation runs. If you get six, you fail. Do you have any ideas for a formula that could account for that?
best answer
jmyzk_cloudsmart_jp ✭✭✭✭✭✭
02/10/24 Edit 02/10/24
Hello@Tim Melton
The comments column displays a different number of comments each time, such as 1, 3, 2, 3, 4, 1, etc.
First, assuming I have a row ID or row number column, I added a helper column "cumulative comment" using the following formula:
Cumulative comment column
=SUMIF([row ID]:[row ID], <=[row ID]@row, comment:comment)
Then the "INT( /5)" helper column with the following expression returns a result of 0, 0, 1, 1, 2, etc.
INT( /5) column
=INT([cumulative comment]@line / 5)
To determine if an automation should be triggered, check if the INT number has changed from the previous row.
The formula to determine this is:
Automation trigger determination formula (comparison with previous line)
=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 on the first line when converting the formula to a column formula. Then INDEX([INT( /5)]:[INT( /5)], [row ID]@row – 1) returns the value of the INT column one row before. So the formula checked if the value is less than the value in the current row. If we receive more than 1 comments, the automation will be triggered.
You can retrieve the value of the previous row by combining the row ID or row number with INDEX.
https://app.smartsheet.com/b/publish?EQBCT=f2fca89d42d94c1ba9df16302eac61b5
Tim Melton ✭✭✭
02/10/24 Edit 02/10/24
Hello. That's natural. Thank you, I'll try it
jmyzk_cloudsmart_jp ✭✭✭✭✭✭
We will be happy to help! 😁