Visible audit log instead of "view cell history" for numerous lines of data

VictoriaL

I know I can view who marked a check box through view cell history but I'm looking for a way to display the person who marked an "approved" or "Done" check box without all the extra clicks; I need a visible export format as an audit trail. I can't rely on the "modified by" field because we have users that go in after an item is marked approved in order to provide additional notes and trigger additional workflows. I also can't rely on the "approver" field because we have cases where an item is assigned to more than one person.

Is there some sort of work around with a "Change cell value" workflow I could utilize? I was trying to put a formula in the text box of the workflow but it puts a " ' " in front of my text when the workflow executes. I also can't use a contact list in this type of workflow. Any thoughts are feedback would be appreciated. Thanks!

Lucas Rayala

If you Don’t have Datamesh, you could also trigger the row to be copied to an archive page when someone checks the box. That should copy over get modified by person so it’s hardcoded in the archive. Reference the archived Modified By name using the Row ID, which will be the same on both pages.

Lucas Rayala

hi @VictoriaL do you have datamesh? If so, you could make this work. You would need a unique id for the row (I’ll assume that’s the Row ID for now). I had ChatGPT write the rest of the instructions:

Certainly! Here's how you can achieve this using Smartsheet's DataMesh feature along with helper columns to record the user who checks a box:

Step-by-Step Instructions

1. Set Up Your Sheet

1. Open Your Smartsheet:
   • Navigate to the Smartsheet where you want to implement this automation.
2. Add Columns:
   • Add a checkbox column, e.g., "Task Complete."
   • Add a "Checked By" column (Text/Number type) where the name of the person who checked the box will be recorded.
   • Add a "Helper" column (Text/Number type) to assist with the automation logic.
   • Ensure you have the "Modified By" system column visible in your sheet.
   • Ensure you have a Row ID system column

2. Create a Helper Column Formula

1. Enter the Formula:
   • =IF(AND([Task Complete]@row = true, ISBLANK([Checked By]@row)), [Row ID]@row, "")
   • This formula checks if the "Task Complete" checkbox is checked and the "Checked By" column is blank. If both conditions are true, it displays the Row ID; otherwise, it leaves the cell blank.

3. Set Up DataMesh

1. Create a DataMesh Configuration:
   • Open DataMesh and create a new configuration.
   • Source Sheet: Select the original sheet with your helper column and "Modified By" data.
   • Target Sheet: Select the same sheet to map the data back. (a datamesh configuration can work on a single sheet)
2. Configure Mapping:
   • Lookup Values: Choose the "Helper" column in the source and the Row ID in the target sheet
   • Data to Copy: Select the "Modified By" column data to be copied to the "Checked By" column.
   • Set up the DataMesh to run immediately.
3. Save and Run DataMesh:
   • Save the configuration and run it to ensure it works as expected.

What happened: someone checks the checkbox. The helper column sees the checkbox is checked but the Checked By column is blank, so it displays the RowID. The datamesh copies the name from the modified by column to the Checked By column. Now the Checked By column is not blank, so the helper column goes blank and the datamesh won’t run again, giving you the name of the person who checked the box at the time the box is checked.

Lucas Rayala

If you Don’t have Datamesh, you could also trigger the row to be copied to an archive page when someone checks the box. That should copy over get modified by person so it’s hardcoded in the archive. Reference the archived Modified By name using the Row ID, which will be the same on both pages.

VictoriaL

@Lucas Rayala Thank you for your feedback! I didn't know about Datamesh and we do not have that add on. Your second suggestion is interesting though…the data on my sheet is likely to change in order sequence but I'm thinking I could work around that with a unique identifier in conjunction workflows and other formulas. It's a workaround and an initial time consuming one but it would get the result I need/am looking for. Thank you!

Lucas Rayala

https://community.smartsheet.com/discussion/comment/438305#Comment_438305

Yeah it’s not perfect, but it works. For a unique ID, use the system Row ID column type. This is a hardcoded unique row number that’s created for every row. I wrote that answer late at night (I answer Smartsheet questions when I can’t sleep!) — let me know if you need clarification. It should be a quick setup, relatively, but check to make sure the automation captures the correct Modified By. It’s possible, but not likely, that the backend function that updates the Modified By column is slower than the automation you create to copy the row over. I don’t think that will be the case, but you’ll need a user to test the checkbox functionality who ISN’T the last person to have modified the row prior to the checkbox being checked. As long as you’re doing this, I would also add in the system column that captures the modified date/time for a sanity check. Last thing-when you create your archive sheet, you don’t have to do any setup (ie create any columns) — as soon as you copy over the the first row, the system will create the appropriate columns with titles. Okay, really the last thing—I think the system will copy over your Row ID correctly, but Smartsheet is a little funky when it comes to system columns, so if you see anything funky in the numbering or just want to make things bulletproof, create an additional helper column on your main sheet called Helper Row ID, and simply use the column formula =[Row ID]@row. When this copies over, it will for sure contain a hard copy of the correct Row ID. Use that one as your reference in the archive page. Silly and maybe unnecessary, but it would work.

To pull the name of the modifier from the archive, you can use this column formula:

=INDEX(COLLECT({Archived Modified By}, {Archived Helper Row ID}, [Row ID]@row), 1)