Currency Conversion and Formatting in PCF

[skoofy5]

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?

[skoofy5]

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/numberformattingi...

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.

[DianaBirkelbach]

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/format...

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

[NickDewitt]

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

[DianaBirkelbach]

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-...

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

https://docs.microsoft.com/en-us/powerapps/developer/component-framework/reference/usersettings?WT.m...

but I don't see a transactioncurrency there.

Kind regards,

Diana

[NickDewitt]

@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.

[NickDewitt]

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.

[skoofy5 · Accepted solution]

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.