How to automatically remove "unmatched" datamesh results from the Target sheet?

Edwina Sampler-Wright

Hi Smartsheet SMEs! In DataMesh, selecting the "Copy and Add Data" option will keep the Source and Target sheet in sync --- automatically creates new lookup values if needed. Have you devised a solution that automatically removes/deletes unmatched data --- rows in the target sheet that no longer reconciles to the Source sheet?

Thanks in advance for knowledge sharing!

Edwina Sampler-Wright, PMP

Genevieve P.

Hi @Edwina Sampler-Wright

Data Mesh currently cannot delete out unmatched data in the target sheet, as you've found.

The way I would quickly identify rows in the target sheet that no longer have a match would be to set up a cross-sheet formula to check a box if the value is found on both sheets. Then you could set up conditional formatting to highlight rows in the target sheet that have this box checked.

Ex:

=IF(COUNTIF({Column with unique value}, [Unique Value]@row) >= 1, 0, 1)

Let me know if you'd like to see screen captures of what I'm describing and I'm happy to clarify further!

Cheers,

Genevieve

Edwina Sampler-Wright

Hi @Genevieve P , thanks so much for the suggestion. This a great workaround. Using a Checkbox column property, I amended the formula as follows --- IF(COUNTIF({Project Number Range}, [Project Number]@row) = True, True, False) and voilà!

Thanks again for your help.

Edwina -

Genevieve P.

Hi @Edwina Sampler-Wright

Data Mesh currently cannot delete out unmatched data in the target sheet, as you've found.

The way I would quickly identify rows in the target sheet that no longer have a match would be to set up a cross-sheet formula to check a box if the value is found on both sheets. Then you could set up conditional formatting to highlight rows in the target sheet that have this box checked.

Ex:

=IF(COUNTIF({Column with unique value}, [Unique Value]@row) >= 1, 0, 1)

Let me know if you'd like to see screen captures of what I'm describing and I'm happy to clarify further!

Cheers,

Genevieve

Edwina Sampler-Wright

Hi @Genevieve P , thanks so much for the suggestion. This a great workaround. Using a Checkbox column property, I amended the formula as follows --- IF(COUNTIF({Project Number Range}, [Project Number]@row) = True, True, False) and voilà!

Thanks again for your help.

Edwina -

Samuel Mueller

@Genevieve P. Is this feature to delete records on the roadmap for datamesh? having to create cross sheet references and lookup formulas manually to know when a source row has been deleted is a huge gap in datamesh functionality.

Sara H

@Genevieve P. Not sure if this process has changed since 2021, but if it hasn't, is the Column with unique value the cross-sheet reference?

Genevieve P.

Hi @Sarah H

The column with a unique value is any column on your sheet that identifies your row as a unique row. For example, a Name or a row ID. In the example above, it was a Project Number. That way the formula could look to see if the number exists in both sheets or not.

Cheers,

Genevieve