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.
Power Apps – Working with Currency Values
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.
Power Apps – 3 different ways to implement currency input mask
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.
How do I format a field to look like currency
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!
6 thoughts on “Displaying numeric fields as Currency in PowerApps”
This method will display the currency in view mode not in the Edit mode. How can I display it in Edit mode?
Unfortunately you can’t.
Having said that not even when using MS Excel when editing the value of a cell formated as a currency, the currency does not display in “Edit” mode.
Thanks for the article. Do you know of a way to format chart items as currency?
No unfortunately not. I haven’t tried though however by the sounds of it I at least doubt that it is possible.
Thanks for a great article and the tip about the decimal place is default to a comma for RSA. I seem to be having an issue when I first published my little app a few days ago people were able to enter in decimal points but it appears that when running on IOS the phone wants to use a comma not a period and then the app will not allow the user to enter a decimal value.
I am very new to this development so please bear with any stupid mistakes i am making.
I have tried the formula that you have posted here and it works apart from the issue with IOS
Sorry for the delayed response – I’m sure you’ve probably solved this already.
If not, my first thoughts are that this would almost certainly be related to the Language a Region settings configured on the iPhone(s), more specifically the Region being set to South Africa. I checked my own iPhone now, with the Region set to South Africa, and for that region Apple have decided that a comma is used for the decimal place character.
Should you change the region to for example United Kingdom the decimal character changes to a period, however unfortunately to too does the currency change to a £ character. Unlike Regional Settings on Windows these 2 characters seemingly cannot be independently set on iOS devices.
I’m relatively certain that Power Apps, like most apps, will display numerical values and dates in any given app in an format determined by the users’ device regional settings, with the exception being where a date value format can be specified in an expression in Power Apps.
I understand your frustration however I am not very hopeful there is anything you can do about it.
Comments are closed.