Unanswered

Currency Conversion and Formatting in PCF

(1) Share

Report

Posted on by skoofy5

482

Hi - I'm trying to create a dataset PCF that formats currency by the logged in users' preference or by the base formatting. I also need to allow for some aggregating, meaning I cannot simply use the formatted value. Does anyone know how I can achieve this in a supported way? How do I retrieve the logged in users currency id?

Categories:

Power Apps Pro Dev & ISV

I have the same question (0)

All responses (7)

Answers (1)

skoofy5 482 on at

Like (1)

Report

I should say that it's particularly currency conversion and formatting that I'm trying to handle. The base of a currency is always available, but how do I tie this to a currency without an id? How do I also then retrieve the users currency to retrieve the exchange rate?

So the methods that are documented are:
https://docs.microsoft.com/en-us/powerapps/developer/component-framework/reference/numberformattinginfo

There's also information available through the context:
userSettings.numberFormattingInfo
client.numberFormattingInfo
formatting._formattingData.numberFormatInfo

I would have hoped that I at least had the id for the user's currency to retrieve the exchange rate if it wasn't just simply supplied.

Was this reply helpful? Yes No
Diana Birkelbach 3,072 Most Valuable Professional on at

Like (0)

Report

Hi @skoofy5 ,

I think you have a few issues.

1. To format the data you can use the formatting (the link below is for currency, but you have functions for all kind of data types): https://docs.microsoft.com/en-us/powerapps/developer/component-framework/reference/formatting/formatcurrency?WT.mc_id=BA-MVP-5004107

2. For each record you have the transactioncurrencyid field, which you can define for your dataset using property-set, so you know you have it provided in the dataset even if it's not defined in the view.

3. Currency sum is always a problem. Which currency is the right one? As you say, you can use the base for each record, and make the sum. Then you can use a request to "transactioncurrency" table (maybe linked to usersettings based on the field transactioncurrencyid), and get the rate and the symbol. Using the formatting from (1) you can show that value.

Hope this helps!

Kind regards,

Diana

Was this reply helpful? Yes No
Ndewitt 136 on at

Like (0)

Report

I think you are looking for getGlobalContext.userSettings.transactionCurrency to get the current user's currency.

Was this reply helpful? Yes No
Diana Birkelbach 3,072 Most Valuable Professional on at

Like (0)

Report

Hi @NickDewitt ,

The Xrm (and globalContext) shouldn't be used inside a PCF. Sometimes it works, but it's explictly specified in the sdk not to do it : https://docs.microsoft.com/en-us/powerapps/developer/component-framework/reference/?WT.mc_id=BA-MVP-5004107

There are some userSettings properties for PCF, as @skoofy5 mentioned:

https://docs.microsoft.com/en-us/powerapps/developer/component-framework/reference/usersettings?WT.mc_id=BA-MVP-5004107

but I don't see a transactioncurrency there.

Kind regards,

Diana

Was this reply helpful? Yes No
Ndewitt 136 on at

Like (0)

Report

@DianaBirkelbach ah yes good point, I wasn't thinking of the Xrm object but more by importing the ClientGlobalContext.js.aspx like you would for a standalone HTML resource, but it looks like they are deprecating that too.

There is a currencySymbol property on the PCF UserSettings.numberFormattingInfo object, it looks like that could be used with the Formatting.formatCurrency function but I do wonder how that is going to work for different currencies that use that same symbol.

Was this reply helpful? Yes No
Ndewitt 136 on at

Like (0)

Report
You should be able to get the information you need by querying the usersettingscollection table and filtering for the current user by using the PCF context WebApi to query the data directly:

// get the user settings for this user const results = await context.webAPI.retrieveMultipleRecords("usersettingscollection", `$select=_transactioncurrencyid_value&$filter=systemuserid eq ${context.userSettings.userId}`); // there should only be one row as we filtered for systemuserid const currencyId = results.entities[0]._transactioncurrencyid_value; // get the details of the currency const currency = await context.webAPI.retrieveRecord("transactioncurrency",currencyId,"$select=currencyname,currencysymbol,isocurrencycode,exchangerate") returns : { "currencyname": "US Dollar", "currencysymbol": "$", "isocurrencycode": "USD", "exchangerate": 1.4400000000, }

That should get you what you are looking for without using something that is due to be deprecated. The exchange rate seems to be auto calculated from the base currency aswell if you want to use that.

Was this reply helpful? Yes No
Verified answer

skoofy5 482 on at

Like (1)

Report
Thanks for the comments guys. Pretty much confirmed what I've already ended up doing earlier today as the only path. I thought I'd let everyone know here if they're after a solution.

Below is the query to return both the user's currency preferences, if available, and the base currency. For this particular scenario I'm not interested in the records' currencies as they could potentially vary from one record to the other and so cannot be aggregated, but as Diana pointed out you can grab the currency related to a record if you wish.
<fetch> <entity name="usersettings" > <attribute name="systemuserid" /> <filter> <condition attribute="systemuserid" operator="eq-userid" /> </filter> <link-entity name="transactioncurrency" from="transactioncurrencyid" to="transactioncurrencyid" link-type="outer" alias="usercurrency" > <attribute name="currencysymbol" /> <attribute name="currencyprecision" /> <attribute name="isocurrencycode" /> <attribute name="exchangerate" /> <attribute name="currencyname" /> </link-entity> <link-entity name="systemuser" from="systemuserid" to="systemuserid" link-type="outer" alias="user" > <attribute name="fullname" /> <link-entity name="organization" from="organizationid" to="organizationid" link-type="outer" alias="userorg" > <attribute name="basecurrencyprecision" /> <attribute name="basecurrencysymbol" /> <attribute name="name" /> <attribute name="basecurrencyid" /> </link-entity> </link-entity> </entity> </fetch>

So what I have from this is:

Base
Symbol
Precision
User
Symbol
Precision
Exchange Rate
You can then add the "base" (all currency fields have a calculated base field added) columns to the view, or use addColumn to ensure the field is in the dataset:
context.parameters.dataset.addColumn([currencyfield + '_base']);

Then I get the unformatted value of the column to do whatever aggregation/maths I need using the base amount, or by taking the value of the base field times by the user's exchange rate I retrieved earlier. In this case I want to be able to switch between the base and users currency.

Finally I pass out the formatted value.
context.formatting.formatCurrency(100, 2, $)

I was really hoping that somewhere in the dataset info this would have been available already considering what else is already there.

Was this reply helpful? Yes No