DataMesh Config : Duplicate Values in Source Sheet

Amandeepti

I am looking for a solution/workaround for DataMesh not only to return the 1st Match currently set up in the config settings: "Duplicates in Source Sheet", but also any match that follows. 

I have the source sheet in which entries are fed via Form and I have set an automation "Copy to Another Sheet" when a certain criteria is met. I created a Datamesh Config between the two sheets as I wanted the values to be updated in the Target sheet whenever there is an update in the Source Sheet.

Now, the lookup value that I selected is "Opportunity Name" but sometimes the Lookup value is duplicate i.e. we have two or more projects for same Opportunity due to which data is lost for the new projects.

Please share some workaround for this.

Bassam Khalil

Hi @Amandeepti,

Addressing your scenario with DataMesh in Smartsheet, where you want to capture and update all instances of a duplicate lookup value (like "Opportunity Name") from a source sheet to a target sheet, requires a bit of creativity. By default, DataMesh is designed to return the first match it finds for a given lookup value, which is limiting in cases like yours where multiple entries for the same lookup value need to be tracked and updated.

Here's a workaround that might help you achieve your goal:

1. Utilize a Unique Identifier

Since DataMesh relies on unique lookup values to function correctly, the challenge with duplicates can be addressed by creating a unique identifier for each entry, even if they share the same "Opportunity Name." This can be achieved by concatenating the "Opportunity Name" with another unique field (like a timestamp or entry ID) to create a unique key for every entry.

Steps to Implement:

• Add a Unique Field: In your source sheet, add a new column that combines the "Opportunity Name" with another unique piece of data (e.g., submission timestamp or a unique ID). This can be done using a formula in Smartsheet. For example, =[Opportunity Name] + " - " + [Submission Timestamp].
• Adjust Form Submission: If you're using a form to populate the source sheet, ensure it captures the necessary data (like a timestamp) to make each entry unique.
• Update DataMesh Configuration: Adjust your DataMesh configuration to use this new unique identifier as the lookup value instead of just the "Opportunity Name."

2. Modify Target Sheet to Handle Multiple Entries

With a unique identifier for each entry, the next step is to ensure your target sheet can accommodate and distinguish between these entries.

• Update Target Sheet Columns: Ensure the target sheet has columns configured to receive and display the unique identifier and any other relevant data from the source sheet.
• Revise DataMesh to Update Based on Unique Identifier: Configure DataMesh to look up and update entries based on the unique identifier rather than just the "Opportunity Name." This ensures that each entry, even if part of the same opportunity, is treated as distinct.

3. Automations for Synchronization

To keep the target sheet updated with all entries (including duplicates) from the source sheet:

• Automate Entry Duplication: Use Smartsheet's automation features to create alerts or actions that trigger when a new entry is added to the source sheet. This can help in manually adjusting the target sheet or automating updates where possible.
• Regular Checks: Regularly check both sheets to ensure synchronization is working as expected, especially if entries are updated frequently.

4. Consider Third-party Integration Tools

If the above workaround doesn't fully meet your needs, you might consider using third-party integration tools like Zapier or Workato. These tools can offer more flexibility in handling duplicates and updating sheets by allowing for custom logic and more complex workflows.

• Custom Workflows: Set up a workflow that triggers on a new or updated entry in the source sheet, checks for duplicates based on your criteria, and then updates the target sheet accordingly. This can involve custom scripts or logic that go beyond the default capabilities of DataMesh and Smartsheet automations.

Limitations and Considerations

• Manual Overhead: The workaround might require initial setup time and ongoing management to ensure everything is functioning as intended.
• Complexity with Formulas: Generating and managing unique identifiers through formulas can get complex, especially as your dataset grows.

This solution is designed to address the specific limitation of DataMesh with duplicates in a way that's manageable within the current capabilities of Smartsheet. However, always test any changes in a controlled environment before rolling them out to your live sheets to ensure they work as expected and meet your business requirements.