How to stop DataMesh breaking % Complete parent row roll-up calculations?

Sarah541
Sarah541
edited 09/20/24 in Add Ons and Integrations

I am trying to use DataMesh to populate my % Complete column using a helper column that provides % completion based on the Status column (because my PM doesn't want their employees to have to enter percentages, but rather statuses). I have the DataMesh config set to copy what's in the helper cell into % Complete. In the parent roll-up rows, even when the helper cell is completely empty (i.e. there is nothing to copy into the % Complete column), after DataMesh runs, the parent roll-up calculations for % Complete no longer show up. Does anyone know why this might be happening and how I can work around it?

Best Answer

  • Isaac A.
    Isaac A. Employee
    edited 09/23/24 Answer ✓

    Hi @Sarah541!

    I ran a test based on your description and a simple DataMesh configuration. Here's what I found:

    It seems that the behavior you're experiencing depends on the logic used in the formula of your helper column. For example, if your formula returns numeric values, like this:

    =IF(Status@row = "", 0, IF(Status@row = "Not Started", 0, IF(Status@row = "In Progress", 0.5, IF(Status@row = "Coming Soon", 0.75, IF(Status@row = "Complete", 1)))))

    The parent roll-up for % Complete should work without issues, and the percentages should display correctly in your parent rows.

     

    However, if your formula returns text values, such as:

    =IF(Status@row = "", "", IF(Status@row = "Not Started", "0%", IF(Status@row = "In Progress", "50%", IF(Status@row = "Coming Soon", "75%", IF(Status@row = "Complete", "100%")))))

    Then the values won't appear in the parent rows, and the roll-up will not function as expected.

     

    As a workaround, instead of using DataMesh you might want to consider using an automated workflow to update the % Complete column based on specific conditions. Here’s an example screenshot of how the workflow might be structured to help with this:

    I hope this helps! Let me know if you have any further questions.

    Cheers,

    Isaac.

    Join us at Smartsheet ENGAGE 2024🎉

    October 8 - 10, Seattle, WA | Register now

Answers

  • Isaac A.
    Isaac A. Employee
    edited 09/23/24 Answer ✓

    Hi @Sarah541!

    I ran a test based on your description and a simple DataMesh configuration. Here's what I found:

    It seems that the behavior you're experiencing depends on the logic used in the formula of your helper column. For example, if your formula returns numeric values, like this:

    =IF(Status@row = "", 0, IF(Status@row = "Not Started", 0, IF(Status@row = "In Progress", 0.5, IF(Status@row = "Coming Soon", 0.75, IF(Status@row = "Complete", 1)))))

    The parent roll-up for % Complete should work without issues, and the percentages should display correctly in your parent rows.

     

    However, if your formula returns text values, such as:

    =IF(Status@row = "", "", IF(Status@row = "Not Started", "0%", IF(Status@row = "In Progress", "50%", IF(Status@row = "Coming Soon", "75%", IF(Status@row = "Complete", "100%")))))

    Then the values won't appear in the parent rows, and the roll-up will not function as expected.

     

    As a workaround, instead of using DataMesh you might want to consider using an automated workflow to update the % Complete column based on specific conditions. Here’s an example screenshot of how the workflow might be structured to help with this:

    I hope this helps! Let me know if you have any further questions.

    Cheers,

    Isaac.

    Join us at Smartsheet ENGAGE 2024🎉

    October 8 - 10, Seattle, WA | Register now

  • Isaac, you are amazing. I was indeed using non-numeric values (e.g. "100%") in my formula so it wasn't a DataMesh issue - it was a formula issue. Thank you so much! The screenshots you shared aren't loading for me though - it wants me to log in to Okta to see them. I'd love to see what you were suggesting for the automation too. Thank you!

  • Isaac A.
    Isaac A. Employee

    @Sarah541 I’m so glad we figured out the issue! I just re-uploaded the screenshots, so please let me know if you're still having trouble accessing them.

    Cheers,

    Isaac.

    Join us at Smartsheet ENGAGE 2024🎉

    October 8 - 10, Seattle, WA | Register now

  • Thanks Isaac! The screenshots are showing now. I super appreciate your help!