I recently had a requirement to customize a SharePoint list using Power Apps that included 50+ fields defined as Currency fields in SharePoint (300+ fields in total – must be some kind of record ?).
As Power Apps 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 Power Apps 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 unfortunately when I tested this by customizing a list form PowerApps Currency fields are no longer treated as Text format types in Power Apps 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 Power Apps list view form. Attempting to 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 accommodated based on the illustrative techniques.
For me at least what immediately stands out is that for all 3 techniques 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.
I personally found the simplistic approach shared on the Power Apps community the simplistic and most refined of all the workaround techniques 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 technique.
- Each Currency field on a data card requires two text box controls (or one text box control and one label 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 corresponding list item.
- For each control on the form, the control properties need to be in sync such that the user experience 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 corresponding label field with matching property values in order to display those fields as Currency fields in View mode, you start reflecting more on what alterative techniques could be possible.
As it turns out you can. I added a single Currency field data card to Power Apps 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 semi 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 )
That’s it – a pretty simple workaround in my opinion. Unfortunately as with pretty much all the workaround techniques this essentially only displays Currency format in Form View mode and not in Edit. No doubt this is equally the a possible high complexity enrichment and accordingly a plausible reason 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!