DataMesh Help!

HarryS
HarryS ✭✭
edited 06/14/22 in Add Ons and Integrations

I am currently running into a strange problem.

In Datamesh I have created the lookup value from the source sheet, and matched the identical value on the target sheet. I have mapped Data field 1 < Data field 1 on both sheets, (this is the column that I would like DM to review for changes).

I have played around with numerous settings in the DM Options screen, but not matter what I change, the new data from the source sheet cells, does not update the cells on the target sheet.

I.e. Source sheet, data field 1, cell 48 shows: Tomorrow

Target sheet, data field 1 cell 48 shows: Today.

After I run datamesh, the target sheet still shows: Today. No changes are made.

This is the last config that I tried and the data still does not transfer over.

Any ideas what I might be doing wrong?

Best Answer

Answers

  • Genevieve P.
    Genevieve P. Employee Admin
    edited 03/24/22

    Hi @HarryS

    Is it possible that DataMesh is not finding the Lookup value to be matching across your sheets? For example, if one column contains Text values (appearing on the Left side of the cell) but the other sheet has the same values seen as Numerical (appearing on the Right side of the cell), these won't be able to be matched across sheets.

    Could you post a screen capture of your Lookup Value columns from both sheets, but block out sensitive data?

    I would also recommend adjusting your "Duplicates in Source Sheet" configuration to select the 1st matching value. That way if you have two rows in your source sheet with the same Lookup ID, it will bring in the first matching information instead of skipping them completely.

    Cheers,

    Genevieve

  • HarryS
    HarryS ✭✭

    Absolutely! Screenshot below:

    I've tried switching to 1st matching and it still does not update the cell with new data.

    I've even tried clearing out the cell on the target sheet, and the datamesh does not add the new info into the corresponding cell. Example below are the Data field 1 and Data Field 2 values in the screen shot above. I have added what the fields look like after running DM:

    Source Sheet:

    Target sheet after DM:


  • Genevieve P.
    Genevieve P. Employee Admin

    Hi @HarryS

    Thank you for this! What about the "Catalogue Service" column that you're using as the Matching Value? It sounds like this is where Data Mesh is getting stuck - it can't find the matching rows across sheets so it doesn't update any cells in those rows.

    We can test this by using a formula, if that helps.

    Try using COUNTIF to see if Sheet 1 can find the value in this row in Sheet 2:

    =COUNTIF({Catalogue Service Column sheet 2}, [Catalogue Service]@row)

    If this Count returns 0, there's no matching row. If it returns 1, then we can rule this out as the cause for your workflow not updating.

  • HarryS
    HarryS ✭✭
    edited 03/24/22

    Ok I actually recreated my source spreadsheet and re-imported it. The datamesh seems to be updating now (Yay!), so there must have been something wonky about the original import. However, when the DM runs, it is creating duplicate entries for each change made to the target sheet, and the new data was not updated %100 .

    Source sheet:


    Target:


    My DM options:


  • HarryS
    HarryS ✭✭

    Ok I think I have solved the duplicate entries. Now on to why the datamesh did not make all changes on the target sheet. :)

  • HarryS
    HarryS ✭✭

    So it seems that DataMesh will only copy the data over if the cell is empty. Is that correct?

  • Genevieve P.
    Genevieve P. Employee Admin

    Hi @HarryS

    I'm glad you were able to resolve parts of it!

    The reason your current workflow is only copying data if the cell is blank is because "Overwrite Existing Data" is set to "No". You'll want to swap this to "Yes" so that it updates cell contents even if there's data in there.

  • HarryS
    HarryS ✭✭

    Thanks @Genevieve P. ! When I change the Overwrite option to 'yes', I am seeing something strange. Datamesh only copies over the data from the first line of the lookup value on the source sheet and overwrites every subsequent line on the target sheet with the same data that is shown in the lookup value on the source sheet.

    I.e.:

    On the source sheet, line 1 shows Day as the catalogue service and Sun as the catalog category. Each subsequent line has unique entries.

    On the target sheet the data from line 1 is showing correct, but then that same line gets duplicated for the next 100 lines, until the catalogue category changes, then the same thing happens again.

    It is almost as if DM is only taking into consideration the lookup value and category as data to be mapped, rather than mapping over the points of data that are different between the two sheets.

  • Genevieve P.
    Genevieve P. Employee Admin

    Hi @HarryS

    It sounds to me like you're wanting different data to map across sheets but the value you're using to match is the same ("Day" as the matching value for 100 rows that all have different data). In this instance you would need to choose a different column as the Lookup Value so that Data Mesh can accurately transfer information from one row in your source sheet to a different row in the destination sheet.

    The "Catalogue Service" column that has the value to match between the two sheets would need to have a unique value to match per-row, otherwise it populates duplicate values with the first matching row data. Does that help clarify?

  • HarryS
    HarryS ✭✭
    edited 03/24/22

    So the Lookup Value has to be a column with unique data points and no duplicate entries?

    I was under the impression that the lookup value should be the primary column on each sheet. In my situation, the Catalog Service is the primary column on each sheet, but there is repeating data within the columns. (There are are 21 catalog service classifications spread over the 1000 lines of data on the sheets).

  • Genevieve P.
    Genevieve P. Employee Admin

    Hi @HarryS

    Yes, exactly! The Lookup Value doesn't have to be the Primary column, it can be any column. This is how the workflow identifies the matching rows across sheets.

    It means you can have your two sheets sorted in a completely different order, but as long as "Row ID 1" matches "Row ID 1" on your destination sheet, Data Mesh will find it and populate the rest of your mapped fields.

    However if you have 100 rows that say "Row ID 1", there's no way for Data Mesh to know what order these original 100 rows should be in on your destination sheet. It matches based on the content of the cell, not based on the row order. In this instance you can either populate the second sheet with the first match or skip duplicates completely.

    An example would be if you have a sheet that contains full Names in one column, then that person's address, role, and department in other columns. As long as the Name is populated in both sheets, you can use this as your unique Lookup Value to bring in the address, role, and department into a second sheet, even if the Role and Department are the same for all 100 people and the order is mixed around - the Name is the only thing that needs to be unique to match across sheets.

    I hope this helps!

  • HarryS
    HarryS ✭✭

    Ok I see. Wow that throws a wrench into my plans. I need to figure out a way to create a column of unique entries on my sheet then.

  • Genevieve P.
    Genevieve P. Employee Admin
    Answer ✓

    It may be easier to run through this via screen share; based on this Community account it looks like your plan has access to booking Pro Desk sessions. These are 30-minute coaching sessions over screen share. There's a category specific for "Premium Apps" like DataMesh. You can book sessions from this page: https://www.smartsheet.com/pro-desk

    Cheers,

    Genevieve

  • HarryS
    HarryS ✭✭

    @Genevieve P. Thank you so much for your help!