Questions on DataMesh
Dear All:
Good morning. We are VERY close to getting the approval from my top management for DataMesh - this is something I have been hoping to get for us since 2019. We were able to help 4 departments set up and become power users of Smartsheet last year. DataMesh will allow easier and faster inter-departmental data share.
There are a list of 14 questions that I would like to get answers to to seal the deal. Our account manager cannot justify the cost and make resources avaiable to get those questions answered for me at this time. Any input/insight you are able to share is greatly appreciated🙏:
- Does DataMesh allow folders for organization purposes?
- Does DataMesh have any limits on how many Configs one can set up?
- Can another licensed user set up a DataMesh Config that uses my Source as their Target Sheet, my Target Sheet as their Source and that process cancels what I set up to run?
- Can a DataMesh Config be duplicated, activated and deactivated?
- After a DataMesh Config is set, can additional Target Sheets be added as long as there are 5 or less?
- My trials show that when a Data Field in a Target Sheet is locked, data cannot be mapped/added, is that accurate?
- DataMesh allows the mapping of 10 fields, does the “Lookup Values” Field have to be one of those fields? Or can it be a particular value from a dropdown menu in a field?
- If more fields need to be mapped, is adding another Config the only solution?
- In my trials, if the Lookup Values are removed from the Target Sheet, it breaks the Config, running the config again won't add what was removed from the Target Sheet, please confirm if that is accurate.
- If the Lookup Values are deleted from the Target Sheet, is View Cell History in the Target Sheet the best way to retrieve and recover? Last year, the Product Team was working on a Sheet Change Report for Sheet owners, is that available now? If not, does DataMesh provide such a report?
- In my trials, a DataMesh Config can be transferred but not shared, is that true?
- Can reports or Excel files be downloaded for the following:
• Affected: 20
• Unaffected: 157
• Duplicates: 20
• Unmatched:
13. Can a deleted DataMesh Config be recovered?
14. Where are the deleted DataMesh Configs?
Thank you in advance.
Megan
Best Answers
-
- No, there is no archive or recover feature that I've seen.
- Possibly, but it allows you to view up to 100 per page, so it would be a high limit.
- Datamesh is setup and managed per user. You can reference any sheets you have access to from viewer to admin rights. Another user's datamesh configuration wouldn't stop yours from running.
- Yes, they can be either manually run, run on a schedule. If you have it set to run manually, that's the same as "deactivated" as it won't run autonomously.
- Yes, you can select 1-5 target sheets per configuration.
- I just tried this and I can select lock columns to be mapped.
- You can map more than 10 fields, I map over to 78 in some configurations. The lookup value needs to be a data field/column.
- No, there is a button to add more fields to map.
- There are three settings you can use for this. "Copy and Add" will re-add data back into the target sheet as long as it exists in the source.
- If I understand this correctly, the same answer from question 9 is applicable here. You could also look into DataShuttle as a solution.
- This is correct.
- No, but it can be copy/pasted.
These answers are based on my usage with Datamesh, and are accurate to the best of my knowledge...but I could be mistaken. Someone from Smartsheet would likely be able to confirm these and help with any other questions. :)
-
Datamesh will override the column being locked and update the column values.
I found this answer from a Smartsheet Employee back in 2022:
-
Hi @MMcLain
Creating a Report to filter your rows to only be the ones with "Pass" is exactly what I would have suggested. And yes! If you have multiple sheets to use as the source, combining them into a Report is the best way to do that.
-
Hi @MMcLain
I'm glad I've been able to help!
Overwrite Existing Data in Target Sheets:
This is if you want DataMesh to only add data to empty cells ("No"), or if you want it to continually update all mapped content if there are changes in those fields ("Yes").
If you have "No" selected, it will only add new data and won't update any cell that has content in your destination sheet.
If you only want 5 fields to update/change out of 10, then you should only map those 5 fields, leaving out the 5 that you want to stay static or manually updated.
Mapping Lookup Value
Mapping the Lookup Value doesn't make a different for the overwriting setting because the Lookup Value won't be overwritten (it can't be - the lookup value is what determines the change, so if the lookup value has changed in the source sheet, it simply doesn't have a match in the destination sheet). However it is important to map if you want new rows added to your destination sheet when the lookup value is new / unique. This would be for the "Copy and Add Data" setting.
Duplicates in Source Sheet
The assumption here is that rows with a duplicate lookup value will only ever be duplicates in the Source file, with no duplicates in the destination.
If there are multiple rows with the same lookup value in the Source sheet, choosing "Pick 1st Match" will only take one of those rows (the first one it finds, usually top-down on the sheet) as the "source of truth" and uses that row to update your destination sheet row with the same value.
If you select "Ignore Entries", then rows that have the same unique ID on the source will be completely skipped, meaning that no updates happen on the destination sheet, even if there's a matching value.
For example, if my rows are "Test" and then "Copy", "Copy", "Copy", only the "Test" value would look for a match in the second sheet and update the matching row. Even if there's a row with "Copy" in the second sheet, it will be skipped because the source has multiple. Does that make sense?
Data Mapping Format:
- Copy Data will copy data over if the unique Lookup value already exists on the second sheet. It will not create new rows if new lookup values appear in the first sheet.
- Copy and Add Data will add in new rows when you have new values that don't have match on the second sheet. It sounds like this is what you should be using!
- Create Cell Links: I agree that you don't necessarily need this, if your DataMesh is running consistently. There isn't a bulk way to remove cell-links, although you can manage them from the Reference Manager (right click on any cell)
Answers
-
- No, there is no archive or recover feature that I've seen.
- Possibly, but it allows you to view up to 100 per page, so it would be a high limit.
- Datamesh is setup and managed per user. You can reference any sheets you have access to from viewer to admin rights. Another user's datamesh configuration wouldn't stop yours from running.
- Yes, they can be either manually run, run on a schedule. If you have it set to run manually, that's the same as "deactivated" as it won't run autonomously.
- Yes, you can select 1-5 target sheets per configuration.
- I just tried this and I can select lock columns to be mapped.
- You can map more than 10 fields, I map over to 78 in some configurations. The lookup value needs to be a data field/column.
- No, there is a button to add more fields to map.
- There are three settings you can use for this. "Copy and Add" will re-add data back into the target sheet as long as it exists in the source.
- If I understand this correctly, the same answer from question 9 is applicable here. You could also look into DataShuttle as a solution.
- This is correct.
- No, but it can be copy/pasted.
These answers are based on my usage with Datamesh, and are accurate to the best of my knowledge...but I could be mistaken. Someone from Smartsheet would likely be able to confirm these and help with any other questions. :)
-
Thank you so much @CodyRussell!
Regarding Item 6: I am able to map the field but data won't be added to a locked column in the Target Sheets. I am the owner of both the Source and Target Sheets.
I can't thank you enough and please let me know if I can be of help in any way.
-
Datamesh will override the column being locked and update the column values.
I found this answer from a Smartsheet Employee back in 2022:
-
Amazing find! Thank you so much @CodyRussell!
-
@CodyRussell We got DataMesh! Thank you so much for your help!
@CodyRussell @Genevieve P. and everyone else that's in the Mesh World:
I have another question that I hope you have an answer to: I know I can match a column from a Sheet or a report to another Sheet, but can I pick and choose specific values in a column and only map the chosen ones (rows) to another Sheet? My experience so far has been no - I pretty much have to leveage a report (in which I can filter and keep only what I want) to map to another Sheet.
Please also confirm for me: is it true that I cannot map multiple Sheets to one Sheet unless data from the multiple sheets are compiled in a report?
Thank you again!
Megan
-
Please allow me to add an example:
Column A in Target Sheet has: Pass, Fail, Dropped out (as values)
I need to "copy and add data" to Source Sheets but only the rows that have "Pass" entered in Column A in Target Sheet.
-
Hi @MMcLain
Creating a Report to filter your rows to only be the ones with "Pass" is exactly what I would have suggested. And yes! If you have multiple sheets to use as the source, combining them into a Report is the best way to do that.
-
Thank you for confirming that for me, @Genevieve P. !
I have read the definitions of "Define View Options" many times, every time I seem to walk away with more ideas and questions.
Would you be so kind as to clarify somethings for me?
Overwrite Existing Data in Target Sheets:
** I usually choose "Yes" because I want mistakes to be corrected: I also add the Lookup Value as one of my data fields to be mapped. Do you recommend that the lookup value also be one of the mapped fields? I caught a few mistakes but also a lot of cleanup to do. I am still weighing the pros and cons of mapping the Lookup value.
** If I mapped 10 fields but only want 5 to be updated when there are changes, should I then select "No"?
Duplicates in Source Sheet:
** When I select "Pick 1st Match", DataMesh updates all rows (duplicates with the same status even when the status of the first row should be kept the same). I think a synchronized archiving system will take care of that. Otherwise, if I select "Ignore Entries", does that mean duplicates will need to be updated manually?
Data Mapping Format:
Copy Data: I am not able to fully understand this because my testing hasn't showed me the difference between "Copy Data" and "Copy and Add Data". Is this more for a one-time mapping and is not good for Sheets or reports that continue to have new data added?
Create Cell Links: I use this one when I only need to update statuses of mapped sheets. I have trouble deciding if "Copy Data" is better than "Create Cell Links" for the updates of a few fields. Removing mapped cells has to be done one cell at a time, is there another project offered by Smartsheet that can take care of link removal more efficiently?
Copy and Add Data: I use this for most of the mapping I did. The source sheet/report keeps growing with new entries. This one seems to do the updates (changes of old rows) and add new rows.
THANK YOU for making the archived answers available. It's been great fun and quite a learning journey reading others' exploration of DataMesh. Thank you again and have a great weekend!
-
Hi @MMcLain
I'm glad I've been able to help!
Overwrite Existing Data in Target Sheets:
This is if you want DataMesh to only add data to empty cells ("No"), or if you want it to continually update all mapped content if there are changes in those fields ("Yes").
If you have "No" selected, it will only add new data and won't update any cell that has content in your destination sheet.
If you only want 5 fields to update/change out of 10, then you should only map those 5 fields, leaving out the 5 that you want to stay static or manually updated.
Mapping Lookup Value
Mapping the Lookup Value doesn't make a different for the overwriting setting because the Lookup Value won't be overwritten (it can't be - the lookup value is what determines the change, so if the lookup value has changed in the source sheet, it simply doesn't have a match in the destination sheet). However it is important to map if you want new rows added to your destination sheet when the lookup value is new / unique. This would be for the "Copy and Add Data" setting.
Duplicates in Source Sheet
The assumption here is that rows with a duplicate lookup value will only ever be duplicates in the Source file, with no duplicates in the destination.
If there are multiple rows with the same lookup value in the Source sheet, choosing "Pick 1st Match" will only take one of those rows (the first one it finds, usually top-down on the sheet) as the "source of truth" and uses that row to update your destination sheet row with the same value.
If you select "Ignore Entries", then rows that have the same unique ID on the source will be completely skipped, meaning that no updates happen on the destination sheet, even if there's a matching value.
For example, if my rows are "Test" and then "Copy", "Copy", "Copy", only the "Test" value would look for a match in the second sheet and update the matching row. Even if there's a row with "Copy" in the second sheet, it will be skipped because the source has multiple. Does that make sense?
Data Mapping Format:
- Copy Data will copy data over if the unique Lookup value already exists on the second sheet. It will not create new rows if new lookup values appear in the first sheet.
- Copy and Add Data will add in new rows when you have new values that don't have match on the second sheet. It sounds like this is what you should be using!
- Create Cell Links: I agree that you don't necessarily need this, if your DataMesh is running consistently. There isn't a bulk way to remove cell-links, although you can manage them from the Reference Manager (right click on any cell)
-
