Keeping track of original data after changes are made

sholmes

Hi, I am using a form to send to employees when items are missing. They will have the ability to update the form which directly updates the sheet once they have resolve issues. I want to be able to keep track of when they have issues so we can use later for training. How can I keep a record automatically of the items that original where mark no but get changed to yes once they review. Is that possible? I started with a report but once the info updates on the sheet the report updates, I want to keep a record of the original data. Is this possible?

Itai

Hey @sholmes ,

An idea would be to have an Archive sheet, so every line that is entered to the main sheet will be copied with automation to the Archive before any changes are made in the row.

Would that solve it?

Paul Newcome

Are you using a form or an update request? Are you able to provide some screenshots with sample data for context?

sholmes

I am using a form, I don't have data samples. This form is new. But, I am reviewing data in our system and using the form to mark yes/no if data is missing. If I mark no the assigned person will get the form and need to fix it and change it to yes once the item is fixed in the system. Ideally I want to track the original "no" and for what items.

Itai

Hey @sholmes ,

An idea would be to have an Archive sheet, so every line that is entered to the main sheet will be copied with automation to the Archive before any changes are made in the row.

Would that solve it?

sholmes

Would that work if 2-3 people are entering the information on sheet? Sounds like it might but I worry about them updating from another user while another is just now entering the data/issue.

Paul Newcome

Forms will only create a new row. A form cannot be used (directly) to update existing rows. Are you able to throw in some samples and provide a screenshot?

sholmes

Sorry, I have nothing to share since this is in the works. I have tested it but since completing test, deleted that data so I can create the new sheet. During testing the request form as updating the cell as the changes where made on the form. Someone else suggesting creating an archive sheet so that might be the route I try next. Thanks for your help!

Paul Newcome

Are you able to describe the workflow in more detail? Initially you were talking about using forms, but then in your most recent comment you are mentioning a potential update request?

If you are sing forms, an Archive sheet would not be the best option, but if you are using update requests, a secondary sheet as a recipient to a copy row automation may be what you need.

Here is a solution that I developed that provide some details on how to capture historical row data. Instead of grabbing just the date/time stamp, you could grab whatever data you needed provided you have a unique ID on every row and then can use an INDEX/MATCH leveraging that unique ID to pull in the historical data.

https://community.smartsheet.com/discussion/63561/date-time-stamp-option

sholmes

Okay, thanks!