Primary Column to be duplicated in other sheets

Trevor Smit

Hi

I have a master sheet that contains an unique ID in the primary column. I then also have a number of sheets that also contains the same IDs in their primary columns which I use to reference data.

Sometimes the existing ID is amended in the master sheet or IDs are added and I am looking for the best way to sync the other sheets. Currently in the master sheet I have check box columns for each sheet to show me which ones need to be updated, which I then manually update. I am looking to automate this process.

I have recently received access to premium features like Data Mesh, Data Shuttle, Data Table, etc. I still need to learn more on these, so not sure if any of them can help or if I have to go for an workflow automation.

Thank you

Trevor

Jeff Reisman

https://community.smartsheet.com/discussion/comment/328089#Comment_328089

I just tested this. I added a new row with a value in my Material column (that's the primary.) After a couple minutes it showed up in my supplemental sheet. Then I updated the value on the main sheet. After a few minutes, Data Mesh added a new row with the revised Material value. It did not update the Material column for the existing row. Data Mesh's log shows it classified that row as "Unmatched." (I haven't run into this scenario with my setup, because this is a new implementation of an older process, and because changing the Material name violates the rules I gave to our Product Managers!)

In the Data Mesh config, on screen 4 "Define View Options", I have the Data Mapping Format set to "Copy and Add Data". You might try setting this to "Create Cell Links" to see if it updates that Primary column value.

Ideally though, since that is the Primary Column, that value shouldn't be changing anyway. If it's going to be changing frequently, I would recommend setting up a hidden AutoNumber column, and use that column as the value the sheets will use to match for the data mesh. Then when your primary column changes on the source sheet, it WILL update the vale on the target sheet.

Jeff Reisman

@Trevor Smit

Data Mesh can definitely help with this. I've only recently started using it myself, but it doesn't take long to figure out. I use it to create rows in a supplemental sheet when a new product row is created in my main sheet. I have something like 11 columns which mesh to the supplemental sheet, and then I've added a dozen or so other columns that other business areas can update. Any change made in the main sheet to a column which is meshed with the supplemental sheet updates the corresponding column in the supplemental sheet.

Trevor Smit

Thank you Jeff. Will I also be able to keep the primary columns in sync with Data Mesh? If one of the unique ID's change in the master sheet, can it update the same ID in the primary columns of the other sheets?

Jeff Reisman

https://community.smartsheet.com/discussion/comment/328089#Comment_328089

I just tested this. I added a new row with a value in my Material column (that's the primary.) After a couple minutes it showed up in my supplemental sheet. Then I updated the value on the main sheet. After a few minutes, Data Mesh added a new row with the revised Material value. It did not update the Material column for the existing row. Data Mesh's log shows it classified that row as "Unmatched." (I haven't run into this scenario with my setup, because this is a new implementation of an older process, and because changing the Material name violates the rules I gave to our Product Managers!)

In the Data Mesh config, on screen 4 "Define View Options", I have the Data Mapping Format set to "Copy and Add Data". You might try setting this to "Create Cell Links" to see if it updates that Primary column value.

Ideally though, since that is the Primary Column, that value shouldn't be changing anyway. If it's going to be changing frequently, I would recommend setting up a hidden AutoNumber column, and use that column as the value the sheets will use to match for the data mesh. Then when your primary column changes on the source sheet, it WILL update the vale on the target sheet.

Trevor Smit

Thank you for the feedback Jeff. We unfortunately have a preliminary ID which then changes to a permanent ID when certain steps have taken place. I think your suggestion for a hidden AutoNumber column might be the solution in conjunction with Data Mesh. I will give it a go, thank you!

Jeff Reisman

https://community.smartsheet.com/discussion/comment/328152#Comment_328152

Happy to help. I don't have anything set up to test this myself right now, so please let me know if it works!