recording deleted timestamp on a data uploader process

Kenneth Green

I am running data uploader from a legacy system to keep status of a list of items. When the data is loaded I am using a merge with a time added option on the sheet so I know when a row was added. I am wondering if there is anyway I can note the first time that a row is not on the uploaded file so I can keep track of when it was dropped off the legacy system.

Genevieve P.

Hi @Kenneth Green

There isn't a way to configure this directly from within Data Uploader because the row you're looking to add a value for (a date) no longer exists in the source sheet.

I've thought of a way to do this but it requires a helper sheet, a helper column, and a second Data Uploader configuration:

1 . Helper Lookup Sheet

You could have a separate sheet in Smartsheet that uses Data Uploader to list all of the current values. This sheet would update and delete out the deleted rows when the source data no longer has those rows.

2 . Column Formula to indicate Current Values

Then what you can do is have a column in your main sheet which uses this lookup sheet to confirm if a value has been removed. Set a column formula in this column to check a box if the value no longer appears in the helper lookup sheet. (You can even Hide this column so it's not visible on the sheet.)

This is the formula I used when testing the process above:

=IF(COUNTIF({Lookup Value}, Value@row) >= 1, 0, 1)

3 . Modified Date updates with Formula

When the formula changes, it updates a "Modified Date" System Column to indicate the date when the value disappeared from the lookup sheet because it's no longer in the source data. Does that make sense?

Let me know if this makes sense or if I can clarify anything further.

Cheers,

Genevieve

Kenneth Green

Thanks Genevieve. I will try this to see if it works for me.