Input expression in Data Shuttle to create a unique identifier?
Hi all! I've been trying to figure out the use of expressions in Data Shuttle but haven't been able to figure it out, so I'm hoping someone here will be able to help.
I have a sheet set up with a Data Shuttle workflow which pulls data from a report in Excel. In order to get a unique identifier, I need to combine the data in two columns (the numbers in either column might repeat, but never as a pair, so joining them together creates a unique ID for that row). Currently, I'm downloading the Excel report, opening it up, and adding a column using Excel's concatenate function to join those two columns together to make the unique identifier for Data Shuttle to use. I'm wondering if it's possible to use an expression in the data shuttle workflow to do that piece automatically?
If I were doing it in Smartsheet, I'd just be writing =JOIN(Name@row+Project@row), but I don't really understand how the expressions work with the Excel attachment and/or if this can be used for the unique identifier row.
Thank you in advance for your advice and feedback! π
Best Answer
-
Hello!
I had the exact same problem as you in a Data Shuttle I was working on. And I eventually figured out you can't really do that. When I tried to use an automatically created expression as the unique identifier, the Data Shuttle wouldn't upload any data into the sheet until I changed the unique identifier to a real field in the source. Which is a pain if you need a composite key from two columns like you need π₯²
So yes, the only way to do it is to create that composite key in Excel first before uploading it to Smartsheet. Which you could use Power Query to do for you if you were so inclined, otherwise just copy and pasting the formula into a new column for every file you need to upload works too.
Maybe someday Smartsheet can create a composite key feature in Data Shuttle, but until then, the workaround will have to do.
Answers
-
Hello!
I had the exact same problem as you in a Data Shuttle I was working on. And I eventually figured out you can't really do that. When I tried to use an automatically created expression as the unique identifier, the Data Shuttle wouldn't upload any data into the sheet until I changed the unique identifier to a real field in the source. Which is a pain if you need a composite key from two columns like you need π₯²
So yes, the only way to do it is to create that composite key in Excel first before uploading it to Smartsheet. Which you could use Power Query to do for you if you were so inclined, otherwise just copy and pasting the formula into a new column for every file you need to upload works too.
Maybe someday Smartsheet can create a composite key feature in Data Shuttle, but until then, the workaround will have to do.
-
Thanks @Janae G. ! I guess the workaround is the way to go for now. Appreciate the feedback!
