Data Mesh questions - sort and deletion
Hi all
I have 2 questions please.
- When you complete a data mesh to add a new row - it adds it to the target sheet but to the bottom of the sheet. Is there a way you can sort the rows on your source sheet and it will sort in your target sheet? - If not then is there an automation that I can set per target sheet to sort the data?
- Also with data mesh when you delete a row from the source sheet can it automatically delete the row in your target sheets?
Thank you for your support and assistnace.
Best Answer
-
1 . DataMesh adds new rows to the bottom of the sheet, as you've found. If you need your sheet constantly sorted, I would recommend building a Report off of the destination sheet to bring in all the rows from this second sheet in the order you'd like!
A Report will automatically Sort based on your Report criteria as new rows are added to the source sheet. (See: Build a row report & Sort Your Report Results with Report Builder)
2 . DataMesh currently does not delete rows from the Destination sheet, no. The row in the destination will no longer be updated as there is no match in the Source sheet, and the Run Config will confirm that you have rows in the destination that don't match your Source:
What I would personally do in this instance is add a helper column in my destination sheet that does a quick check into your Source sheet. For example:
=COUNTIF({Unique Column Source Sheet}, [Unique Column]@row)
Then if this formula returns 0 (the current row does not have a matching value in the source sheet), you can set up a couple of automations based on this helper column... for example Conditional Formatting, or exclude those rows from your Report, or a daily automation to move those rows to an Archive sheet.
Cheers!
Genevieve
Answers
-
1 . DataMesh adds new rows to the bottom of the sheet, as you've found. If you need your sheet constantly sorted, I would recommend building a Report off of the destination sheet to bring in all the rows from this second sheet in the order you'd like!
A Report will automatically Sort based on your Report criteria as new rows are added to the source sheet. (See: Build a row report & Sort Your Report Results with Report Builder)
2 . DataMesh currently does not delete rows from the Destination sheet, no. The row in the destination will no longer be updated as there is no match in the Source sheet, and the Run Config will confirm that you have rows in the destination that don't match your Source:
What I would personally do in this instance is add a helper column in my destination sheet that does a quick check into your Source sheet. For example:
=COUNTIF({Unique Column Source Sheet}, [Unique Column]@row)
Then if this formula returns 0 (the current row does not have a matching value in the source sheet), you can set up a couple of automations based on this helper column... for example Conditional Formatting, or exclude those rows from your Report, or a daily automation to move those rows to an Archive sheet.
Cheers!
Genevieve
-
Thank you very much for the feedback and options.
-
The formula idea you had above....
Would that work for a report as the source going to a sheet.
I need a report to goto a sheet. And when the report updates I need the sheet to update. I have it working with data mesh but if a row is deleted from the report it does not remove from the target sheet.
What would the formula look like in the helper column for report to sheet
Thanks
-
Hi @Jon Barto
Formulas only work across sheets; there currently isn't a way for a formula to look into a Report and return a result. This means that no, the formula idea won't work as it cannot look into your Report to see if the row has been removed.
However you could build a formula that looks into the underlying sheet(s) that the Report is built off of, using the same Filter criteria in your Report in the formula so it returns "yes" or "no" depending on if the row matches or not. Does that make sense?
-
I used the following work-around for this.
- Created a primary datamesh (Datamesh 1) to copy and add data from a source report to a target sheet.
- Created a second datamesh (Datamesh 2) to create a cell link between one checkbox column on my target sheet and its respective source sheet. This column is not inlcuded in Datamesh 1.
- Created an automation on target sheet that removes rows when the checkbox is deselected. The same checkbox filters data into the source report.
- Created a helper sheet to collect "deleted" rows.
Basically when the checkbox for the row is engaged, it enters the report and datamesh adds it to the target sheet. When the checkbox is deselected on the underlying source sheet (formula driven), it is filtered out of the report, so can no longer be updated by Datamesh 1. But the cell-link remains intact, so the checkbox is deselected on the Target sheet. This triggers a Smartsheet automation that moves the row to a helper sheet.
