Datamesh - blanking mapped columns

Fabien Videotron

In Datamesh, when the lookup value in the target sheet is blanked out (delete) or even changed to a value that has no match in the source sheet, all the mapped columns in the target sheet remain with the previous value which makes the data not consistent.

Is there a was to blank out the mapped value if the lookup value is blanked out or if this value has no match in the source sheet ?

Tx

Fabien

Ella

Datamesh is one-directional, the data moves from Source to Target sheet. Are you saying you want to delete something from the Target and have it be deleted from the Source sheet as well?

Nathan Lloyd

Hi Fabien,

At the moment DataMesh will only move data in one direction (from the Source sheet to the Target sheet(s)). Deleting or removing a row / lookup value in either sheet will not cause the matching row to be removed in the other sheet. Please feel free to submit an Enhancement Request for this.

As a workaround suggestion, you might consider setting up a process for archiving rows in both sheets using the Automated Move Row functionality.

Hope that helps!

Best,

Nathan L.

Fabien Videotron

Hello,

My point is not about deleting or removing a row in either de Source or Target sheet but making sure that the data is consistent in the target sheet. The purpose of Datamesh is to have consistency between the source and target sheet.

Lets assume you have a source sheet with Employee info and that in the target sheet the lookup value is the employee_id and that you want to copy over some info from the source sheet (name, DOB etc..).

The first time you'll run the data mesh everything will be good. If there is a match the data is copied over, and if there is no match the mapped cells will be blank.

But let assume now that in the target sheet, you update the lookup value in an existing row with an other employee_id and that you do a typo (oh no !), then it doesn't match and the data will remain the same in that row creating unconsitent data. You end up with a valid employee name, DOB for a non valid employee_id.

I see Datamesh as a lookup tool like VLOOKUP so it should tell you NOMATCH in the mapped columns if it is a nomatch and if the lookup value is blank, then the mapped column should also be empty everytime it runs.

Thank you !

Fabien

Ella

Hi Fabien,

When you run it it gives you a summary of the matched and the unmatched rows. There is no way that it can empty columns if it is not able to find a match in the Target Source.

Manuel Macias

Hi Fabien,

A work around that works fairly well though not always perfectly depending your setup is to create a placeholder row in the source sheet with no values in any cell except for the 'Look Up Value' cell being referenced by DataMesh. There you insert a dash (-) or any other symbol or combination of letters you choose.

From there just make sure that the 'Look Up Value' cell in the TARGET sheet either has a real value such as an employee id in your case or a dash (-).

Now, all you have to do is make sure to select YES to 'Overwrite Existing Data in Target Sheet(s)' in the DataMesh configuration window and run it. DataMesh will match the dash values in the Source and Target sheets and overwrite any inconsistent data in the cells back to blank.

IMPORTANT: Make sure you never delete the placeholder row from the source sheet as this would make the entire setup useless.

Cheers!

Fabien M

Hi Manuel,

Thank you. This is exactly what I did with a entry at the bottom of the dropdown !

But unfortunately, even if the "Restrict to dropdown only" option is checked the user (Editor) can still blank the lookup value (by pressing delete) and it defeat the purpose. The option only prevents for entry not in the list but blank is allowed.

Regardes,

Kelly Drake

If you run the data mesh on a schedule it will replace/correct the accidental deletions.

As a best practice when I data mesh data from one sheet to another, I lock column so that no one except sheet Admins can create data errors like this.

Laura Carruthers

I have this issue too. A lookup value is deleted in the target sheet after a datamesh has been done. I tried adding a blank cell in the lookup value in the source sheet with a corresponding zero in the data field (which would work for my situation - to removed a number and make it zero), but this didn't work. Any workaround since this was posted 2 yrs ago?

SwitchFoot

There is a critical error and a significant developmental oversight. When the key column finds a match, it should enter the information related to that key value. If the value does not find a match, it should enter blank values, even if there was data filled in previously. What is the point of the setting "Overwrite Existing Data in Target Sheet(s)" if it leaves the wrong information tied to the wrong key value? This is basic competency. If a user enters the wrong key value and it finds a match, it will bring in that data. If the user changes that key value and the system does not find a match, it LEAVES the information tied to the first key value that was originally entered. This is a MASSIVE developmental oversight and renders this feature completely useless and unreliable in every way…