How to set up an automation with FROM/to changes

SMacey

Hello Lovely Smartsheet Community!

Hoping you can help :) I am working on a deployment project where there are two different eligible vendors, and any of the 7500 clients may switch vendors at any time. Therefore, there are 4 options in my "Vendor" dropdown column:

• Vendor A
• Vendor B
• Ineligible Vendor
• No Vendor

I am trying to set up alert automation to notify Vendor A and Vendor B (separately) both of additions (previously the other vendor, or ineligible/no vendor) as well as those that have been removed (now the other vendor, or ineligible/no vendor). I set up the automation flow below hoping it would work, but it is also alerting them of changes to the vendor column (listed below as RDL) that were never associated with them to begin with (i.e. it shows up in the alert to Vendor A that Client X switched from ineligible to no vendor, not applicable to Vendor A)

I think the addition condition paths will work, since it is pulling the new vendor cell data, but I can't seem to figure out how to make the removals only reflect IF they were originally a certain value (i.e. only tell Vendor A if Client X changed from Vendor A to something else, not that a Client Y changed from Vendor B to No Vendor).

I know I can select when a field changes TO something, but I want to be able to indicate FROM and To, if that is possible?

Thoughts? Suggestions? Thank you so much in advance!!!

Screenshot 2024-04-11 120940.jpg

KPH

Hi @SMacey

You are right, you can add an automation to trigger when a value changes to something but it is a little more tricky to trigger when it changes from something. Tricky, but not impossible!

How about adding a column to the sheet that is automatically populated with A or B when the vendor changes to vendor A or Vendor B and then using this to "remember" them when the vendor changes. You can add this to the send alert automations.

Add column

Add a column to the sheet as an identifier that a row is currently assigned to Vendor A or Vendor B. You don't need to put anything in this column. I'm going to call it "A/B Identifier"

Then:

Automation 1

• Trigger when row is added or changed
• When Vendor column changes to Vendor A
• Change cell value in "A/B Identifier" to "A" - or any piece of text we can refer to later.

You now have a record of the vendor that will remain if the drop down is changed.

You could just add the change cell value block to the end of your send alert automation:

Automation 2

Copy automation 1 and adjust the trigger and cell value for Vendor B.

Automation 3

• Trigger when row is changed
• Where Vendor changes to Any Value

Add a condition

• If "A/B Identifier" is Vendor A
• Alert Vendor A
• Then Clear the value in the A/B Identifier as this is not a row Vendor A should be alerted on again (unless it gets assigned back to them, in which case automation 1 would take over).

You can add a second conditional path to the third automation for Vendor B

Let me know how you get on!

SMacey

@KPH Thank you so much for your response! I really appreciate the detailed steps :)

I set it up as you described, but it looks like Automations 1 and 2 are running before Automation 3, therefore what is in the A/B Identifier column gets overwritten before Automation 3 runs so it doesn't trigger the alert to let the previous vendor know one of their clients have been removed.

I tried to set Automations 1 and 2 to run weekly (to give Automation 3 time to run before the A/B Identifier column updates) but it says it must be run based on the trigger. I deactivated Automations 1 and 2 which worked (as long as I had a vendor in the A/B Identifier column), but thoughts on how else to delay the update to the A/B Identifier column until after Automation 3 has time to run? Would the below work to update it overnight daily instead of when triggered, giving Automation 3 time to run?

Worst case I could do it manually on some cadence but would love if it could be handled by automations! Thanks in advance!

KPH

I'm not sure I follow. The automations should run on trigger. Automation 1 and 2 should run as soon as vendor A or B is selected, this one lets them know they have a row and adds the marker to say this is now their row. Then when the row changes, and the vendor changes, automation 3 runs, tells them the row isn't theirs, and clears the marker so they don't hear about it again. It is automation 3 that clears the value. 1 and 2 are populating the cell. So 1 and 2 should run before 3.

There may be an issue if a row owned by A changes to B (or vice versa). In that case automation 3 should clear the A before automations 1 or 2 adds in the B. But possibly they are overlapping? Is that what you see, just a problem with A to B and B to A and not any other changes? If so we can adjust the trigger on automation 3 and make a couple of different versions, clearing the identifier if the change is to any other vendor, and updating to A or B if the change is to A or B.

Or are you adding rows to a sheet that already has data? If so, you will need to add the A/B Identifier to any row that is already an A or B vendor as the automation is only triggered when the vendor is selected. If they were in the sheet before the automation was added, it won't backfill. You could use a formula to do this.

SMacey

Yes - the middle paragraph - it is when one goes from A to B or B to A, Automation 1 or 2 is triggering at the same time as 3, so the "Vendor" matches "A/B Identifier" and it doesn't trigger the alert for Automation 3 since the columns match.

Instead of Automations 1 and 2, I created an automation to essentially copy all Vendor cell details over to A/B Identifier column overnight each night, that way Automation 3 will run when triggered during the workday, and then (the same as we were doing with Automations 1 and 2) it will update the A/B Identifier after the triggered automation successfully sent the alert.

Did some tests and it seems to work as expected! Thank you so much for your help!

KPH

Nice work! The change cell value automation is great for remembering things. Glad you have it working.