It wasn’t really my intention to start blogging about currency fields and functionality in Power Apps. If you have been following along, last week I posted about how currency fields work in CDS.
If you read my posts primarily for content around Power Apps Portals, then I suggest you check out CRM Tip of the Day, where George Doubinski dug into the portals source code and discovered a treasure trove of Liquid filters that help display currency information on a portal.
Onward to this week’s post.
Mass Edit Currency Fields
I was working on a project for client where we are replacing an Excel sheet with a model-driven Power App to track grants for student employment placements.
My client was providing some feedback about the form layouts, fields, option sets, etc. and also asked if it would be possible to “unlock” a particular money field on the form for bulk editing.
Now most of you would have likely responded knowing full well that it is a limitation to bulk edit currency fields in Dynamics 365 (including model-driven Power Apps).
Or if you were like me, you had forgotten this tidbit of info (pub trivia or exam question writers, take note!). I went to investigate, did a bit of Googling, and discovered that you cannot bulk edit currency fields.
This actually makes sense because if you recall from my last post, a currency field is actually a combination of a base currency field, a currency lookup and an exchange rate.
The issue itself is actually an outstanding request on the Ideas site. You could do it in Dynamics CRM 3.0, you should be able to do it now!
Attempting to Mass Edit a Currency Field
The bulk edit feature is very powerful (and a bit dangerous). You select a group of records from a standard model-driven view (or a Dynamics 365 view) and choose the Edit button.
You will be presented with a version of the form that will show all the unhidden sections, tabs and fields. However, certain fields, such as money based fields are read-only and not editable.
In order to mass edit the records, you will need to open up each one individually, update, and move to the next. Another method is to export to Excel and re-import, but this could be tedious.
If only there were a tool that could take some parameters and run against a set of records…
Power Automate to the Rescue!
What would have once been a challenging issue to resolve, now becomes very easy using Power Automate.
For our solution to mimic the bulk edit, we first want to select records and then kick off the process to update.
For this we need the CDS “When a record is selected” trigger. This only exists in the “classic” Common Data Service connector, but NOT the Common Data Service (current environment) connector. The no-code Queen, MVP Sara Lagerquist has an excellent article on the various CDS connectors and when to use each.
We still start to build out our Power Automate flow. We will want to prompt our end user and ask them to what value we want to set our money field. There is no currency user input type but there is a number field which will work for our purposes. Note that we can’t add spaces to our prompts.
Now that we have our trigger and prompt, we can start the update step. Note that we could potentially add multiple prompts to update more than one field.
We will add an Update Record step, for the entity for which are going to update, along with the ID (from our “when record is selected” step) and will put in our prompt value (in our example “Update_Cost”) into the money field we want to update.
The following is the complete flow. This is just two steps and can be put together in a manner of minutes.
Testing the flow
Once we save our flow and refresh our model-driven app, we can select the records we want to update and then choose our flow from the Flow drop-down (if it doesn’t show up, refresh your browser)
Our users will now enter the value to update the particular currency field in the selected records.
After a few brief seconds, the records will be updated (a refresh of the form will be required)
Even if Power Automate is not yet at 100% parity of Dynamics 365 workflows, it can do a lot that workflows or dialogs could not. For example, the ability to allow users to enter in parameters at the start of a flow is something that was not possible with regular Dynamics 365 workflows. The ability to run against multiple records is something that wasn’t possible by the now-deprecated Dialogs. The ability to not be able to bulk edit money fields (since CRM 3.0) is now possible using Power Automate.
Nick Doelman is a Microsoft Business Applications MVP, a Microsoft Certified Trainer and one of the hosts of Refresh the Cache: Power Apps Portals Podcast and the host of the new MVPreneur video series and podcasts. Follow Nick on twitter @ReadyXRM