Unique Identifier Column

Daniel Liu

I'm trying to get a Datashuttle migration to work and there is no documentation on the limitations of the unique identifier column. Can this column in SS be a column formula as that is how my unique IDs are being generated.

I know that both of these columns exist and tested by creating a paste values column in SS in which data shuttle worked. I want to know if a column formula unique identifier will work because if it doesn't, that severely limits the dynamic ability of the sheet.

Genevieve P.

Hi @Daniel Liu

You cannot use column formulas or system columns as fields to map in Data Shuttle.

This is because Data Shuttle updates and adds values, but having a column formula essentially locks the column so it cannot be updated automatically.

What I would suggest doing is use Data Shuttle on a different, intake sheet. For example, you could fully upload all of the contents of your external file into one sheet. Then you could use DataMesh (if you have access to it) to bring over the relevant data, mapping your one regular Smartsheet column to the column formula column in DataMesh instead. You could also use Index(Match formulas to bring over matching data instead, depending on how many columns you have to match.

Cheers,

Genevieve

Kelly Drake

@Genevieve P. ..... I've been searching for 2 hours to find a solution to this problem... this got me soooo close and then let me down massively. (major sad panda is sad)

I have a system data export for reasons that I won't get into (but i want to eyeroll at my peers so hard for) that does not have a unique identifer for but i could create an expression that would create a match and essentially let me LEFT JOIN on the data that already exists in smartsheet..... but because of the limitations where even though I've set a workflow to only update existing rows I cannot do this and now I need to build another sheet with the column formula to create the match column & a datamesh workflow.... so at a minimum 2 additional smartsheet assets to maintain/sustain....

Would there be anyway to get this option / conditional set up for data shuttle to be able to join data sets without creating webs of sheets?

Genevieve P.

Hi @Kelly Drake

Apologies, I'm not sure I quite understand your set-up.

If it helps, in the initial Data Shuttle upload, you can add an Input Expression at the time of an upload that could join fields together?

Then you could use that as the field in your DataMesh, or is that what you're already doing?

MichaelTCA

Hello,

A unique identifier in Data Shuttle is not the same thing as a Unique Identifier in a sheet.

Same premise though.

The unique identifier is a key to tell Data Shuttle what will tie the source file to. This CAN change between workflows.

A unique identifier in a sheet is what the portal uses as a key for the collection of data within the sheet. This CANNOT change.

They can be the same or different.

The main rule to setting this up per the definition of "unique" is there can only be 1 of that value within the column being specified as a unique identifier in the data shuttle workflow.

Any column from the target file can be used as a unique identifier within data shuttle. You can map it any way you want. The only reason you set the unique identifier AND map the column for the identifier is to specify a location in the sheet to input that data.

If you're mapping expressions. Create any cross references in the sheet or develop the formula all together until it's working correctly. DO NOT set the formula to a column function. Then in the expressions tab copy and paste the function into a new expression. THEN go back to the mapping, map the function you added, and they're usually at the bottom of the list.

Expressions are where you can get clever and Genevieve was going the right direction.

Like I mentioned, in Data Shuttle, the unique identifier DOES NOT need to be the same unique identifier as what is in the sheet/location you are pointing to. BUT there can only be 1 of these values in the column you are specifying as the column you want to use for the identifier.

Ex: I have a sheet that uses a auto-numbering system and a function in the unique identifier column (within the sheet) to create the ID or "reference" as fit. When the row moves or gathers more data, the function in the identifier for the sheet will change the row's unique identifier. This also makes it easier for me when mapping data shuttle because I only need to focus on the one column instead of bouncing around from multiple sources to use multiple columns as unique identifiers within data shuttle. In the long run, some rows may have been assigned or reassigned 4-5 identifiers. Data shuttle doesn't mind as long as there's only 1 to match the data to.

If you have a column of dates from the target file you want to use as a unique identifier in data shuttle, feel free! BUT, you can only use each date 1 time or the software won't work.