I have setup a table of contracts running, that contain a contract START and an END date. I’ve managed to create an Automation that notifies users in the Responsible column, 31 days before the contract end date. But it’s not working as expected. Here’s what I need:
Send an email ONCE to people in responisble column 31 days before the END date.
Currently it sends every day, for 31 days up to the end date. I only want x1 notification to be sent 31 days beforehand.
The email should only contain the information of the ROW the rule relates to.
Currently, the email that comes through (see here) summarises all the columns data (x8 notifications), where what I want is only those rows(x4 notiifications), because only x4 of the END dates are within 31 day from today. The other 4 are in 2024.
Thanks so much for reaching out, @Mark_Hawkins ! It looks like we’re unable to view your doc to help you out. Would you mind adjusting the share settings, please?
@Shaina_Torgerson2 I have amended the share settings now, apologies about that. I changed to EDIT access, so you’re able to amend directly in the doc if you wish.
If you test that in a separate column, I have a hunch this will not evaluate as you expect. I think it will evalue to TRUE or FALSE, neither of which are blank.
In cases like this, I prefer setting a flag of some sort to indicate that the first email has been sent and when. If you set a flag (in a column field), you could use that in your automation to filter to be sure you send only once. This approach has the advantage of keeping a long of the triggered messages.
As to #2… I’ve got nothing. I need to finish a meeting before noodling, but I trust another user will get here before I get back.
Thanks for the steer @Bill_French. I’ve tried a few variations of the formula trying to get it to only send once but struggling. I’m not entirely sure how to go about achieving what you’re suggesting but have opened up the doc permission to EDIT , so would love for you to show me a practical example in the doc (feel free to edit) and I can then review and roll it out?
Okay - so, you want any given record to resolve true under a single condition at a single point in time. To do that, you need a formula that subtracts 31 days from the END DATE and compares it to today’s date. This formula must return true on one day - and one day only.
I placed your existing formula in the Current column and my formula in the Remedy column. Notice how your formula always returns true regardless of the presence of an END DATE that is exactly 31 days beyond today. Play with the Oct 31 date I entered and you’ll see the formula Remedy column resolve to false, except when it is exactly 31 days beyond today.
Instead of computing the value for each row in the rule trigger filter, consider simply using the value in the Remedy column like this.
@Bill_French That is perfect - thank you for enlightening me. I will use ‘helper’ columns in future for this type of thing, to simplify the approach and also to first confirm and print out what the formula is doing.
For the second issue, where the email hould only contain the information of the ROW the rule relates to, instead of printing every single value, do you have any ideas there? That one is baffling. I have tried various states of con concatenation vs joining without luck… cc @Shaina_Torgerson who also was trying to assist.
Yep - this is a technique I use when my formula confidence is not 100%. It helps you step into the final solution in a methodical way, almost like unit-testing automation steps. In all no/low-code tools, you want to focus on functionality at the expense of inelegance. Elegance comes next - it is far easier to refine functional systems into elegant ones.
The answer is probably attained in similar fashion. Instead of binding the logic to the rule, take a baby step by creating the output for the email message in each row, thus isolating the fields in a concatenation that exists in an intermediate field. Then modify the rule to use the intermediate field. You might also benefit from using the new Compose field type for building your intermediate tests
I need to finish a meeting before noodling, but I trust another user will get here before I get back.
but have opened up the doc permission to EDIT , so would love for you to show me a practical example in the doc (feel free to edit) and I can then review and roll it out?
