Capture Last Modified Before It's Modified Again

Jonna Critchley

Hi. I'm trying to figure out how to capture the last modified by and last modified date at a specific time (trigger) before it's modified again by something else.

Currently, I have a main sheet and a "helper" sheet.

On the main sheet, where all the action happens, when someone selects "Approved", I have a formula-ed column that will capture the current last modified information to get their email address, date and time they approved. But because the last modified information will change each time an action happens on that row, I've setup a workflow to copy the row to the "helper" sheet to capture the last modified information before it changes when someone else approves.

Unfortunately, when it copies it over, showing this: "automation@smartsheet.com - 07/09/24 10:40 AM" instead of something like this: "chris@domain.com - 06/13/24 8:02 AM". My assumption is because of the workflow copying it to the helper sheet, that is the last modified action.

Is there a way for me to capture the last modified information prior to it changing due to another action?

Andrée Starå

https://community.smartsheet.com/discussion/124880/capture-last-modified-before-its-modified-again

Hi,

I hope you're well and safe!

You'd use the Created date instead.

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

Make sense?

Would that work/help?

I hope that helps!

Be safe, and have a fantastic day!

Best,

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

✅ Did my comment(s) help/answer your question or solve your problem? Please support the Community and me by marking it - Insightful 💡- Vote Up ⬆️ - Aweseome ❤️ - or/and as the accepted answer. It will make it easier for others to find a solution or help to answer! I appreciate it, thanks!

Jonna Critchley

@andreestara unfortunately, no that does not work, when it copies the row over to the "helper" sheet, it put "web-form@smartsheet", not the email of the person who submitted the information on the row.

Any other thoughts?

Jonna Critchley

@Paul Newcome you've been so helpful in the past, any thoughts on this?

Paul Newcome

@Jonna Critchley I would suggest a text/number helper column or columns (depending on exactly what you need) in the source sheet that uses a formula to essentially replicate the data that you want to capture but as a text string via formula. As long as you do not put this formula into the copy sheet, it will be captured as a static string in the copy sheet which should in turn give you the ability to pull that data back to your source sheet.

Something like

=[Modified By]@row + ""

=[Modified Date]@row + ""

Jonna Critchley

@Paul Newcome Hi There Paul, I think I already have what you have suggested. Here is more detail…

I have a master sheet and a repository sheet.

In the master sheet, I have 2 columns each for the two approvers that I need to capture the information for:

Consultant Decision Date/Time (1) and Consultant Decision Date/Time (2)

President Decision Date/Time (1) and President Decision Date/Time (2)

In the Consultant (1) column, this is the formula: =IF(AND([Consultant - Decision]@row = "Approved", [Tracking Status]@row = "02. In Review (C)"), [Last Modified By]@row + " - " + [Last Modified Date]@row, "")

What this is saying is if the Consultant enters Approve and the tracking status is In Review C, then enter the current last modified by dash last modified date.

Same for when the President approves.

The automation I built says when the Consultant Decision changes to Approved, the copy the row to the Repository sheet.

In the Consultant (2) column, the formula is: =(IF(OR(CONTAINS("04", [Tracking Status]@row), CONTAINS("01", [Tracking Status]@row), CONTAINS("02", [Tracking Status]@row), CONTAINS("03", [Tracking Status]@row)), IFERROR(INDEX(COLLECT({Smartsheet Information Repository [MISOS] Range 1}, {Smartsheet Information Repository [MISOS] Range 4}, Subject@row, {Smartsheet Information Repository [MISOS] Range 5}, MIN(COLLECT({Smartsheet Information Repository [MISOS] Range 5}, {Smartsheet Information Repository [MISOS] Range 4}, Subject@row, {Smartsheet Information Repository [MISOS] Range 3}, [Company Name]@row))), 1), ""), ""))

What this formula does is return the minimum row number's Consultant Decision Date/Time (1) information (from the repository) for the match (in the master) if the tracking status contains one of these numbers indicated in the beginning of the formula in the Consultant Decision Date/Time (2) field. The President approve is setup the same, with the exception of asking for it to return the maximum row number since he approves after so the row number should be one higher than the first approval.

Now, what is happening is when the row is copied over, the information I want to capture is blank, the view cell history looks like this for Consultant Decision Date/Time (1):

What I've redacted in this picture is what I hoped I would get back in Consultant Decision Date/Time (2), but I get blank because of the automation.

This is what the last modified looks like:

It does the same for the President approve as well, returns blank.

How can I capture the Last Modified that is redacted, not the Smartsheet Automation blank?

Jonna Critchley

@Paul Newcome I guess really the long question short would be - how do you capture a specific moment in time last modified information?

Paul Newcome

@Jonna Critchley Your setup should be working. Maybe try adjusting your trigger to only trigger when the formula that outputs your string is actually outputting something. It may be that it is triggering on the status before the formula has a chance to output what it needs to output.

Otherwise, I would need to see screenshots of your sheets (sensitive data can be blocked out if needed).