Data Shuttle Upload - Merge & Update based on key column value causing errors

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!

Find more posts tagged with

Integrations

Data Shuttle

Accepted answers

jessica.smith

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.

All comments

jessica.smith

@H Brian does your organization also have access to DataMesh or only Data Shuttle?

H Brian

https://community.smartsheet.com/profile/jessica.smith 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!

jessica.smith

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.

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.