Data Shuttle Upload - Merge & Update based on key column value causing errors
![H Brian](https://us.v-cdn.net/6031209/uploads/avatarstock/nSKJ2BAYVP7XH.png)
Hello!
I have a few Data Shuttle automations running between SharePoint / Power Automate created .xlsx files and a central repository in Smartsheet. Our key identifying point is a unique profile ID number, but we do need to use that ID number for our contractees when requesting a contract renewal. Due to this, during the renewal process, the "key column value" is actually located twice in the Smartsheet repository.
In testing, it seemed that ALL rows with that value would be updated, but in practice that is not occurring. This is causing some major data duplication and notification errors. Is there a way to tell Data Shuttle to update every row on the target sheet that matches the key column value? The only filtering available is on the Source side and that is not what I want to filter; I need to filter what is mapped to Smartsheet. (either based on the modified date or the Status dropdown).
Thank you!
Best Answer
-
Both configs rely on a 1:1 mapping, so if there are multiple rows that match the uniqueID, its only going to update one of them.
I have a similar solution in place at my company to handle this type of workflow.
I go from excel to a helpersheet with a DataShuttle Config. The config completely replaces the data in the helpersheet upon upload.
I then go from the helpersheet to my central Smartsheet repository using Datamesh with a copy/add relationship. The datamesh target only has a single row for each unique identifier.
With this method, your contract renewals would be treated as updates to the original parent contract that is already on the datamesh target, rather than added as new rows.
Jessica Selano | Selano Consulting
jessica@selanoconsulting.com
Answers
-
@H Brian does your organization also have access to DataMesh or only Data Shuttle?
Jessica Selano | Selano Consulting
jessica@selanoconsulting.com
-
Thanks for your reply! We also use DataMesh as well. I'm currently trying to create a workaround with a "helper sheet" and building a DataMesh to update the original sheet from the helper sheet using the Row ID as a key column identifier, but I would of course prefer a simpler solution!
-
Both configs rely on a 1:1 mapping, so if there are multiple rows that match the uniqueID, its only going to update one of them.
I have a similar solution in place at my company to handle this type of workflow.
I go from excel to a helpersheet with a DataShuttle Config. The config completely replaces the data in the helpersheet upon upload.
I then go from the helpersheet to my central Smartsheet repository using Datamesh with a copy/add relationship. The datamesh target only has a single row for each unique identifier.
With this method, your contract renewals would be treated as updates to the original parent contract that is already on the datamesh target, rather than added as new rows.
Jessica Selano | Selano Consulting
jessica@selanoconsulting.com
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 66.1K Get Help
- 430 Global Discussions
- 149 Industry Talk
- 489 Announcements
- 5.2K Ideas & Feature Requests
- 85 Brandfolder
- 154 Just for fun
- 74 Community Job Board
- 499 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 305 Events
- 36 Webinars
- 7.3K Forum Archives