When it comes to SharePoint Calculated Column Optimization, working with SharePoint frequently presents obstacles, particularly regarding the formatting of numbers in calculated columns. This guide aims to provide a solution to the problem where numbers don’t format correctly in SharePoint calculated columns, especially when integrated with Power Apps. If you’ve noticed your numbers appearing with trailing figures or not displaying as intended, read on.
Understanding SharePoint Calculated Column Optimization Challenges
It’s a recognized issue within the SharePoint environment that numeric calculated columns sometimes fail to cooperate. Specifically, when working with Power Apps, it might not accurately recognize the data type for these columns, leading to formatting challenges. Fortunately, there’s a way to counteract this.
An instance of this issue can be observed at the following link, but it’s important to note that many app builders face this challenge:
For reference purposes, visit the link below. However, do note that for a comprehensive solution, continue reading this blog.
Steps for Effective SharePoint Column Optimization in Power Apps
Let’s illustrate the issue with a practical example:
Demonstration
Consider a SharePoint list containing property details. Among other columns, there’s an acquisition price column and another calculated column that transposes this price into US dollars by multiplying it by an exchange rate. When defining this calculated column, it’s specified to display results up to 2 decimal places.
Sample Power App Creation
When we initiate an auto-generated app and include our calculated field in a detail form, the issue becomes evident. Instead of displaying a numeric icon, an ‘abc’ icon emerges next to our calculated ‘AquisitionPriceUSD’ field. Furthermore, when the app is played and we navigate to the record details screen, the value of our calculated column astonishingly displays up to 9 decimal places!
Resolution
To rectify this, we need to transform the string value back into a numeric form. This is accomplished using the Value function. Subsequently, to limit the number to a defined set of decimal places (like 2), the round function can be utilized as demonstrated below:
Round(
Value(ThisItem.AquisitionPriceUSD),
2
)
For detailed formatting, like including thousand separators, the value is first turned into a number and then reverted to a string format. This is executed by invoking the Text function and providing a custom format string:
Text(
Value(ThisItem.AquisitionPriceUSD),
"[$-en-US]#,#.00"
)
Conclusion
Working with Power Apps and SharePoint often necessitates dealing with unexpected formatting problems, especially with numeric calculated columns. However, with the right functions and understanding, these issues can be navigated smoothly. Should you encounter any challenges or need assistance with other technical matters, don’t hesitate to contact us. Our team is always ready to assist and guide you to the best solutions.
