How to stop DataMesh breaking % Complete parent row roll-up calculations?
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
-
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
-
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!
-
@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!
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63K Get Help
- 380 Global Discussions
- 212 Industry Talk
- 442 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 305 Events
- 34 Webinars
- 7.3K Forum Archives