Automation Advice and Data Shuttle

JAmirali

Background:

I am creating a database to track employees working on construction sites. I need to track movement between sites, and receive reports in Excel with crew information on a weekly basis.

I need to record the date that an employee's location changes. For example, if someone works at site X for 6 months and then move to site Y, then the date needs to reflect that.

However, some departments giving me their crew rosters only give me the employee's original hire date. The hire date may not necessarily correspond with start date if the employee has been moving around frequently.

Original Resolution:

I have been using data shuttle to get all the reports into their respective Smartsheet sheets. I wrote an automation that I thought was really good that would resolve the date issue. It is as follows:

• When rows are added or changed and "Show Up Address" changes to Any Value, date stamp "Show Up Date"

This would at least give a rough estimate of when the employee moved job sites and it worked for a while

The Issue:

The issue arises when an employee had previously changed sites, so now there is a new address and date stamp in my system. My coworker gives me their updated roster which has this new address, but their system is still showing an old date (since they are not tracking what I am tracking). The data shuttle then maps the old start date and ruins my tracking progress. For clarity here is an example:

John Smith hired at 123 Main St. on 7/23/23 - moves to work at 123 2nd St. (no date provided)

Whenever I shuttle the data is when the automation will stamp John as being at the new address. Let's say this is 6/20/24.

Their info did not account for the change my database made, and therefore they would keep sending me the 7/23/23 date along with the new 2nd street address. Therefore, data shuttle sees that the addresses are the same, does not use the automation, and my previous progress gets reverted when the old and now incorrect date is imported.

Does this make sense? (hopefully!)

Question:

How can I work around this using an automation? I have thought about making helper columns and writing a formula but there is nothing I can think of that will resolve the problem. Additionally, it is helpful to be able to turn the automation off at times if I need to import data and don't want the dates to get all screwy.

I have to shuttle the dates provided incase there are new employees added to the sheet, I need a starting point to begin tracking. I also am working with thousands of employees. Some sheets are much smaller but mostly the goal is to get things less manual and more automated. I cannot go through and change dates of ever single person who moved on a sheet with 800 employees.

Can this possibly be resolved using a Smartsheet filter? Maybe I have it only map the start date from the original file if rows are added. That way I can keep my automation on to time stamp when the address changes, but not import the date.

Brian_Richardson

Perhaps you could establish a "Reference" sheet that overrides the dates in the data that comes in later?

To set this up:

1. Add a column to your main data sheet called "Import Date" (date type). Then, create a copy of your existing data sheet and call it "Reference" (or whatever you like).
2. Add a checkbox column to the main data sheet called "Reference Missing?" that checks the Reference sheet to see if you have recorded the combination of person and address already. Like = IF( COUNTIFS ({Name}, Name@row, {Address}, Address@row)=0, true). If "Reference Exists?" is checked, it means you need to capture the change to an address for that person.
3. Add a column formula to your Date column in your main data sheet = INDEX({Import Date}, COLLECT({Import Date}, {Name}, Name@row, {Address}, Address@row), 1). This will pick up the date from your reference sheet.
4. Add an automation to your main data sheet that triggers when Reference Missing? gets checked, that Copies that row to the Reference sheet.
5. In the Reference sheet, have an automation that snapshots today's date into the Import Date column if it's blank, triggered when a row is added (from the other automation).

Your regular process would then be:

1. Import the data to your "New Data" sheet, importing the date provided in the Excel to the "Import Date" column rather than the final Date column that you want to use for your reporting etc.
2. All row dates will look for a corresponding name+address match in the Reference sheet and will pull that date in, regardless of what date is provided by the report owners
3. The first time they provide a change in address for a given person, if they provided a date then it will copy that date to the Reference Sheet. If the date is blank, your Reference sheet will capture today's date instead and will then use that date going forward without it overriding.

JAmirali

Thank you for this detailed reply!

The only downside to this method is that I think I might have to have 2 different workflows going to 2 different sheets (regular and reference sheet).

I do merge other columns on the sheet and want those to continue to update, but I don't need the date to import unless there are new rows added. Unless I'm misunderstanding I think I would have to have a separate data shuttle workflow for importing the date to the reference sheet and then another to import all my other data?

Brian_Richardson

No you just need the one data shuttle. Import whatever data you like. The key is to import the data provided on the sheet into a temporary Import Date column. Then the rest of the solution basically checks to see if that address was already provided and overrides the excel date with the actual start date…or sees that the address is new and captures the date for the new adddress to be used going forward.

You aren’t shuttling anything into the date reference sheet. That’s just a holding pen for the real dates you want to use that will override whatever date is given to you, which may be wrong.