Skip to content
  • Try Smartsheet

    • Add Ons and Integrations

    Add Ons and Integrations

    Ask questions about Control Center, Dynamic View, DataMesh, Pivot App, Calendar App, or WorkApps. Discuss connecting Smartsheet to your other systems with integrations such as Bridge, Data Shuttle, the Jira connector, and the Salesforce connector.

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

    ✭✭✭
    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?

    Tags:
    · Share on FacebookShare on Twitter

    Sign in
    Try Smartsheet

    Best Answer

    • 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.

       

      Change % Complete Mesh.PNG
      Change % Complete numeric.PNG

      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.

       

      Change % Complete Mesh2.PNG
      Change % Complete text.PNG

      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:

      Change % Complete WF.PNG

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

      Cheers,

      Isaac.

      Need more information? 👀 |Help and Learning Center
      こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 |Global Discussions

      · Share on FacebookShare on Twitter

    Answers

    • 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.

       

      Change % Complete Mesh.PNG
      Change % Complete numeric.PNG

      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.

       

      Change % Complete Mesh2.PNG
      Change % Complete text.PNG

      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:

      Change % Complete WF.PNG

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

      Cheers,

      Isaac.

      Need more information? 👀 |Help and Learning Center
      こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 |Global Discussions

      · Share on FacebookShare on Twitter
    • ✭✭✭

      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!

      · Share on FacebookShare on Twitter
    • 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.

      Need more information? 👀 |Help and Learning Center
      こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 |Global Discussions

      · Share on FacebookShare on Twitter
    • ✭✭✭

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

      · Share on FacebookShare on Twitter

    Sign in
    Try Smartsheet

    Quick Links

    Categories

    Trending Posts

    Help shape the future of Smartsheet.
    Share your ideas and feature requests.

     Watch video