Work around for Data Shuttle not recognizing identical data

JAmirali

Hello! This is pretty hard to explain but I am hoping someone can help me with this dilemma:

I am using Smartsheet to build a tracking system that monitors where field project workers are showing up on a weekly basis. I get the job addresses as a spreadsheet from another department. Since I am working with hundreds of employees, I made a workflow in Data Shuttle to import the job address into my sheets.

A big component of this is monitoring how long each employee has been at their current job site. I have a column for "Job Start Date", "Current Date" and a third to calculate the difference to give me "Time on Site" in days.

I set up an automation in the sheet that would date stamp the 'Job Start Date' column whenever an employee's job address changes. It reads:

'When rows are added or changed and 'Job Address' equals any value, record date in Job Start'

This works, HOWEVER, Data Shuttle recognized every address as "new data" even if the old address was exactly the same as the new address (ie the employee didn't move locations). Therefore, any time I try to update the information with new reports, Data Shuttle sees everything as new and will reset all dates.

My work around for this was as follows:

1. Create a helper column for old addresses (column A)
2. Data Shuttle the most recent addresses into a different column (column B)
3. Make another helper column to read the differences and mark as "same" or "different"
4. Make an automation to date stamp the Job Start Date if it reads "different"

This also works BUT I realized that in order to continue to update, I will eventually have to manually move the information from column B to column A in order to shuttle the most recent info into column B.

The problem with this is when that information gets changed the automation will reset my date and that will ruin the tracking process.

Does anyone have a solution for this? I am completely stumped!

Brian_Richardson

Question - when you Data Shuttle the job addresses in, are you doing a replace or an update? If update, what is your key field? Because I believe Data Shuttle won't update data that hasn't changed in the source. Which means it thinks the data in your source file is different from the data in your Smartsheet…

Is it possible that post-import formatting is altering the addresses such that DS sees them as different than the source?

JAmirali

Hi, thanks for taking a look into this!

For the update, I set the workflow action to 'merge data into the target based on key column value' and the row options are set to 'add rows' and 'update rows as they change in the source file'

I attempted to make a helper column as I had stated above just to compare the old data versus the newly shuttled data to see if there were differences and there did not seem to be.

Now that I am rethinking it though, maybe there were some periods or commas that were in there somewhere that is making it identify the whole address as different. What do you think?

Brian_Richardson

Yes that’s what I’m wondering too- because the behavior you’re describing shouldn’t happen unless DS is seeing the values in the destination and source as different from each other. Even an extra space at the end or a comma or something will throw it off and be seen as new data.

In your source would it be possible to introduce some kind of address unique ID that you could use instead of relying on long text strings?

JAmirali

I don't think the address can be the unique identifier because some employees are at the same addresses as one another. I have been using the Employee ID as the key column value because it's the only thing besides the employee name that would be a good differentiator.

I will try to test this again and be more careful of the commas. Now I am thinking you must be correct because initially I had to merge several columns into one on the Excel report to be the 'address' that I use and I probably made the formula to include a ", " between street and city. Perhaps I did not do that with the new report and did not realize!

It's good to know that it is not supposed to do this though. I had talked to our company's account manager on this issue and he wasn't positive on what was really supposed to happen.

Thank you!