I have a problem and then I have a probable solution, please let me know if this solution works at all. :). Here is the doc I am working with (might need to request access since I don’t have ‘sharing to outside org’ enabled)
So what I am trying to do is making notifications for project changes. For example, when Priority formula column changes from P0 to P1, I would send a notification out saying something like “hi there, project xxx’s priority has changed from P0 to P1”
Now, I would like to expand this out to another column as well so instead of priority column, I would send out notifications for priority and RAG formula columns. What I was thinking about was creating a time-based automation (because automation can’t be done for formula columns). This time-based automation would check the “Priority changed” or “RAG changed” checkbox columns to see if it’s a column that has been edited. It will then send out a notification.
because there are 2 columns I would like to check for , I would need to make 3 automation rules:
priority changed == true && RAG changed == true
priority changed == true && RAG changed == false
priority changed == false && RAG changed == true
If I want to expand even more to 5 columns, I would need to make 432*1 automation rules. Is this the correct thinking and if it is not, how can I improve on this? Thank you!
Do you have three different messages or destinations for these three rules? If not, you can simply use an OR operation. In this case, if either of the two columns changes, the automation will be triggered.
Additionally, I would recommend adding a ‘Changed’ column, which would be a checkbox that automatically calculates whether the row has changed based on the relevant columns. This would simplify the automation formula to just check this column.
Thanks for the response @Felipe_Arnhold1 ! I could do an OR statement or a switchif. It just becomes very unwildly when I have 13 if statements . Is there a better way to do this or is this the best way?
NIce, we have the same thought process! In the screenshot below, I have a “changed” checkbox whenever the previous value != the current value. Is this what you were thinking?
The approach you’re describing is essentially what you’re implementing. You’ll need to create a copy of the columns containing formulas to monitor modifications. Thus, the number of columns will be significant if you have many to track; for example, 26 columns in your scenario. I replicated this use case in the document below and didn’t experience any slowdowns. This may be due to the majority of the columns being number and text types.
I utilized a single column to detect changes in any column using a switchIf formula, which tests each tracked column. If there are specific columns known to change frequently, you can put these first inside the formula. This way, if a change is detected early, the switchIf formula will not evaluate further, saving processing time.
Additionally, I prefer setting up buttons for each action in the document and using automations and other buttons to activate them. In this instance, I configured two buttons: one to notify someone with a message and another to copy current values to previous ones. The latter button filters the table to update only the rows where “changed?” is true. Both buttons are set to disable if no rows have “changed?” set to true. This configuration allows you to simply press both buttons; if they are disabled, no action will be performed.
. Is there a better way to do this or is this the best way?