I recently had a requirement to customize a SharePoint list using PowerApps that include 50+ fields defined as Currency fields in SharePoint (300+ fields in total – must be some kind of record 🤣).
As PowerApps for now at least doesn’t enable input mask fields such as currencies I searched the web looking for how others had implemented various workarounds for this requirement. I came across numerous suggestions on the PowerApps community web site as well as various blogs, with noteworthy examples such as:
- PowerApps – Working with Currency Values (YouTube)
- PowerApps – 3 different ways to implement currency input mask
- How do I format a field to look like currency
Let’s look at each of these techniques described and some of their pros and cons.
On the surface, the technique described by April on this YouTube video seemingly was by far the most simplistic method. However unfornately when I tested this by customizing a list form PowerApps Currency fields are no longer treated as Text format types in PowerApps and when added to a form they are now instead defined as TextFormat.Number in the Format property of the value field in the Data Card.
Accordingly if the form is in View mode and the currency value is SharePoint is now stored as a numeric field type the value will not display correctly on the PowerApps list view form. Attempting to actual edit the list item introduces further complexities even if you found a workaround to display the stored value in a currency format when the form is in View mode.
The 3 different techniques described by Pavel on his blog are certainly interesting and quite intuitive however as he states on his blog, the focus of the blog was to focus on proving the different concepts rather than on a polished app. As such the blog does not delve into additional complexities that one has to consider when editing items and how the currency fields would be accomodated based on the illustrative techniques.
For me at least what immediately stands out is that for all 3 techiques described the field types in SharePoint are defined as Text columns and not Currency field types. This raises numerous concerns wrt how one would prevent users from capturing values containing non-currency characters and equally how calculated values based on those currency fields would be implemented. For example, Unit Price (“currency”) times Quantity (“Number”) equals Line Item Total.
On the PowerApps cummunity forum, a number of community member posted similar messages on the forum and I found this particular perhaps the pick of the lot in terms os simpliscity and perhaps covering the bulk of the users requirements insofar as displaying currency values on their PowerApps form and advodace as the preferred technique of the one detailed on this including, includivance of a further alterative I implemently recenctly which achieves simply outputs and objectives on the PowerApps form for currency fields.
So why a further alternate workaround technique?
Well as noted earlier, personally I found the simplistic approach shared on the PorwerApps community the simplist and most refined of all the workaround techniquies proposed thus far. Naturally, the question then begs what are the cons of this technique, and if so, can it be tweaked to further enrich and or simply the implementation of this techinique.
- Each Currency field on a data card requires two text box controls (or one text box control and one lable control) to implement a solution such as this one. One to render the amount as a Currency when the form is in View mode, and one to render the amount as a number when the mode is in Edit mode in order to ensure only numeric characters are saved in the Currency column in SharePoint for corrosponding list item.
- For each control on the form, the control properties need to be in sync such that the user experiece is as close as possible to each other to make the interaction experience fluent. This means ensuring property values such as the X, Y, Width and possibly other common properties such a Color, Fill, Size etc. equally kept in sync.
How might an enchanced technique look?
Glad you asked! As mentioned in opening the blog, the requirement presented to me included over 50 fields that were deemed currency fields. When your solution approaches these type of volumes of currency fields to display at these levels and above, the thought of duplicating each currency field value text box to have a corrosponding label field with matching property values in order to display those fields as Currency fields in View mode, you start reflecting more on what altertive techniques could be possible.
As it turns out you can. I added a single Currency field data card to PowerApps SharePoint list form and updated only the Default value on the Data Card Value Text Input control to reflect:
Text( Value(Parent.Default); "[$-en-US]R ###,##0 ") Text( Value(Parent.Default), "[$-en-US]R ###,##0 ")
[Which ever works for you with commas and simi columns – for some reason Microsoft think South Africa use commas as decimal point characters. For the record we don’t.]
On the same said Data Card Value Text Input I set the Format property to the following values.
If( !( Parent.DisplayMode = DisplayMode.Edit ); TextFormat.Text; TextFormat.Number ) If( !( Parent.DisplayMode = DisplayMode.Edit ), TextFormat.Text, TextFormat.Number )
[Again commas or simi columns – whichever works for you]
That’s it – a pretty simple workaround in my opion. Unfortunately as with pretty much all the workaround techniques this essenstially only displays Currency format in Form View mode and not in Edit. No doubt this is equally the a possible high complixity enrichment and accordinglingly a plausible resason the product group have to overcome before they can introduce native Currency support within the app.
Hopefully this nonetheless helps out some other users in the community as fresh ideas are always welcome!