Automation: Value changes FROM and to

A Rose

Hi,

Currently as far as I know we can set an automation when a column changes to any value,

Is there any way to set it if it changes From a value To another value?

Meaning if a column changes from Blank or #NO MATCH to any value it should record a date.

Or, if it changes from 10 to 20 it should Check a box.

Thank you,

Lucas Rayala

It depends on how specific you need to be. If you have a very generic situation, such as going from "blank" to "some value", you don't need anything fancy, just record a date when the cell value changes. If you need to get specific about what a cell in Column A can change FROM and TO, then you'll need a helper column that records the "from". Again, how you do that depends on how variable your data is. If you only have a few options you need to cover (such as the possibilities of 10, 20, and 30), you can create automations to write the "FROM" data to your helper column based off those options, with the additional condition that the helper column "is blank". That way, if someone types in 10 to Column A, and the helper column is blank, the first automation will write "10" to the helper column. But if you then change the value of Column A to 20, the helper column won't be updated because the condition "helper column is blank" is not met. Then you have another automation that only triggers when the helper column is "10" and Column A is "20", jotting a date somewhere.

Anything is possible. Your setup will depend entirely upon how complex your need is.

A Rose

Hi,

That is a good idea as you said if it's a only a choice of a few numbers, but being that it may vary a lot that won't work,

What I really need is somehow get a helper column (or a few) to indicate how much was it changed from before,

Meaning whenever "Column-A" changes to any value, 1 helper column can show the old value, and another column the new value so that I can get a third column to show by how much it increased or decreased.

Bottom line is, I'm not aware of a automation to record the old value, or to show how much it changed.

Thanks,

sharkasits

@A Rose The only way I've found to do this is to copy the row to a helper table when a change happens and then use an Index(collect()) to look up the value in the last row for that row (need a unique identifier) in the helper table.

A Rose

Hi,

That's a good idea for small sheets with little updates,

but being that by me it's a larger sheet with more frequent changes copying a row would not be beneficial,

Let's hope someone will come up with a better solution :)

Thanks,

Lucas Rayala

If you have datamesh you can create an automation to copy the information from one column to another, with the stipulation that it doesn't overwrite. You'll need to have a unique ID for each row, which you can get by creating a Row ID column. In the datamesh config, have the "source" and "target" sheet the same. I used "lot number" as a lookup value in this example but you would use the row ID column. The source data field is your initial value, the target data field is a copy of that initial value. Set the config up so it won't overwrite data. You can use that value as a reference.

Lucas Rayala

@sharkasits , I do that, but I generally use an intervening page to clean the data, especially if you have a source page that shifts around a lot. On the intervening page, use an index/collect function to pull the data you want, formatted appropriately. You need a lookup column to mirror data. Then you have the move function execute from the intervening page to your final page. I did this when I had to combine data from three gigantic pages that were always shifting around.

sharkasits

@Lucas Rayala how do you get the automation to trigger on the change for the intervening page? I was trying to do something like that, but couldn't figure that part out.

Lucas Rayala

@sharkasits the issue is you can't trigger an automation based off a formula, so you have to use a time-based trigger. This is obviously not as ideal as datamesh or a similar solution, but to make things a little more real-time I've copied the same time-based automation and just changed the times. You can set up as many as you need. Your earlier comment spurred me to write a post about this solution -- you can take a look over here -- https://community.smartsheet.com/discussion/101590/poor-mans-datamesh

sharkasits

@Lucas Rayala, yea that was what I thought. I've done it with 2-3 helper tables where there's a "landing" table with an autonumber field that I copy the rows to. A formula table with 100+ rows with formulas that pull the data I need and then time based copy to the final table.

The formula table has the first row as the Max (autonumber field) from the final table, and the Landing table has a checkbox indicating if it's in the final table. For some, I have a delete table too, where anything in the landing table that's checked off as being in the final table get moved to the delete table on a timing triggered automation so that the only manual thing I need to do is delete all the rows in the delete table on a regular basis.

Definitely not a perfect solution, but at least it ensure I don't miss any changes that occur in the same hour.

Lucas Rayala

@sharkasits that's a clever process!