Capture cell changes in automation

Andrea Zenner

Hello,

We have an application that tracks users' PTO requests. Sometimes they need to change the date on which they are taking vacation. We have an automation that runs, when a date is changed, to notify their approver to re-approve the request.

I can change the message for changes so the approver knows it's for a change but they don't know what the change is.

Is there a way to capture the cell contents before and after the change so the approver can see what has changed? Or are we going to have to rely on the employee to type that into a comments field?

markkrebs

The only way I can think of is to copy the row everytime the request is made and in the 'copied row' sheet do a counter by person and grouped request ID then look at the most recent row-1 for the request and Index match that back into the original sheet. Somewhat complicated.... Other that that I think you'd need to rely on the requestor. I hope someone has a simpler solution because this is a common need. Smartsheet should put this as a standard feature as they capture all cell history but dont really do much with it in reports or use case like this.

Andrea Zenner

Thanks for the suggestion!

For now we'll rely on the requestor. Personally I don't think that's a big ask as today they have to send an email so they have to address it, put all the text in about changing their vacation and send it. In this app they just have to change a date and put a comment that they are changing from X to Y.

But then again, I'm not one of our users. :-)

markkrebs

Are you using a form to request? or are they doing this in the sheet itself?

markkrebs

Also, do you by chance own Dynamic view?

Andrea Zenner

The initial request is a form. Updates are done in the sheet (via a report in a WorkApp). They didn't want to have to send another form request in for changes, they just wanted to be able to make them.

I don't want them to have access to the sheet itself but if I put a report on the WorkApp they can edit but don't need access to the actually sheet behind the scenes.

Andrea Zenner

We do not own Dynamic view

Andrée Starå

Hi @Andrea Zenner

I hope you're well and safe!

To add to Mark's excellent advice/answer.

Could you use the new Record a date feature to have the original date stored?

Another option if it's not a date could be,

Please have a look at my post below with a method I developed.

More info: 

https://community.smartsheet.com/discussion/68589/lock-or-store-date-value-solution-without-using-zapier

Would any of those options work/help?

I hope that helps!

Be safe and have a fantastic weekend!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!