Add Ons and Integrations

Add Ons and Integrations

Ask questions about Control Center, Dynamic View, DataMesh, Pivot App, Calendar App, or WorkApps. Discuss connecting Smartsheet to your other systems with integrations such as Bridge, Data Shuttle, the Jira connector, and the Salesforce connector.

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!

Best Answer

  • ✭✭✭✭✭✭
    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

  • ✭✭✭✭
    edited 02/03/25

    Site faviconjessica.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!

  • ✭✭✭✭✭✭
    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

Trending Posts