Dynamic currency

[Julien Hoffmann]

Hello support,

My data contains price and currency for various objects. 

In my Data Grid, the price column should be formatted as a price (C2) using the price's currency.

For example, if my data is { price: 10.20, price_currency: 'USD' }

Then the column should show : Price: $10.20

Of course this can be easily done using { format: 'C2', currency: 'USD' }

Can you help me  create a column definition where the currency code comes from the datarow itself ?

Thanks in advance

[Rajapandi Ravi]

Hi Julien, 

Greetings from Syncfusion support 

Before we start providing solution on your query, we need some information for our clarification. Please share the below details that will be helpful for us to provide better solution. 

1)         Please confirm you like to set the currency type for Price column based on the price_currency column value in the datasource. 

             For example: if your data is [{ price: 10.20, price_currency: 'USD' }, {price: 20.30, price_currency: EUR }] 

             Please confirm you like to set currency format as USD for first row and set currency format as EUR for the second row based on price_currency value in the datasource. 

2)        If the above one does not meet your requirement, please share your exact requirement with detailed description.  

3)        Please explain your requirement scenario with pictorial representation. 

Regards, 

Rajapandi R 

[Julien Hoffmann]

Hello Rajapandi,

Thanks for your reply.

I have data which has for every row: price, quantity, currency, and product reference.

My goal is to display product ref, price (using currency), quantity (editable), total (USD), and a footer aggregation of total (USD).

1. yes, this is exactly it. I want the currency to be taken from the data source itself. There is a property price and a property currency in each data row. So indeed [{ price: 10.20, price_currency: 'USD' }, {price: 20.30, price_currency: EUR }]  will display $10.20 and €20.30. For this problem, I figured out I could use a value-accessor:
   
   

   <e-column
     field="price"
     type="number"
     header-text="Price"
     :allow-editing="false"
     :value-accessor="currencyFormatter"
   />
   ...
   currencyFormatter (field, data, column) {
     return Number(data.price).toLocaleString(undefined, { style: 'currency', currency: `${data.currency}` })
   }

   
   
   This works well, but uncovered another issue: when I edit the "quantity" value, the price gets undefined in the currencyFormatter function.

2. My second issue is about row totals. The displayed grid should show : product ref, price/currency, quantity and row total as a computed column - total is always USD. Thanks to this forum and a previous related question, I figured out using the following works well
   
   

   <e-column
     field="Total"
     type="number"
     header-text="Total Price"
     :allow-editing="false"
     :value-accessor="totalPrice"
     :format="{format:'C2', currency: 'USD'}"
   ></e-column>
   
   ...totalPrice (field, data) {
     return data.price * data.quantity
   }
   

   But of course, I need to apply a currency rate to transform the price * quantity from its original currency into USD. Here comes the problem. I have an exchange rate API which works ok and returns exchange rates from input currency to output currency. The value-accessor becomes:
   
   

       totalPrice (field, data) {
         this.$axios.get(`/rates?from=${data.currency}&to=USD`)
           .then((response) => {
             if (response.data) {
               const rate = Number(response.data)
               return data.price * data.quantity * rate
             }
           })
       }
   

   
   
   The return value is correct (reading in google dev console) but nothing is displayed in the grid (empty string). I suspect the issue comes from the asynchronous nature of the value-accessor. 
3. My last problem is that the computed row totals should be aggregated into a grid total in the footer. For this I use a footer template and a custom field.
   
   

   <e-aggregates>
   <e-aggregate>
     <e-columns>
   <e-column column-name="Total" type="Custom" :custom-aggregate="customAggregateFn" :footer-template="footTemplate" :format="{format:'C2', currency: 'USD' }"></e-column>
     </e-columns>
   </e-aggregate>
   </e-aggregates>
   
   ...
   customAggregateFn (data) {
         var sum = 0
         const grid = document.getElementsByClassName('e-grid')[0].ej2_instances[0]
         grid.dataSource.filter((e) => {
           sum = sum + e.priceexw * e.quantity
           return sum
         })
         return sum
       }
   
   
   Here too, I need the exchange rate to be applied. But I admit I gave up at this point.

All in all, my problems are:

1. Display a price using a currency from the same data row, still being able to edit the associated quantity
2. Calculate a total per row using data coming from an asynchronous call
3. Get an aggregate total of the computed column

Thanks in advance for any help

Julien

[Rajapandi Ravi]

Hi Julien, 

Before we start providing solution on your query, we need some information for our clarification. Please share the below details that will be helpful for us to provide better solution. 

1)        In your query you said that “Calculate a total per row using data coming from an asynchronous call”. We understand that you are like to multiply the Price and Quantity column value  

            and displayed the result into the Total column. Since the price and quantity value was already present in the Grid, please share the details about why you are performing the axios call. 

2)        Please share your datasource structure, we would like to see what column fields you are maintaining in your datasource. 

Regards, 

Rajapandi R 

[Julien Hoffmann]

Hi there,

Thanks for the follow up.
Here is a small data set:

[
{ product_ref: t-shirt, price: 15.00, currency: USD, quantity: 2 },
{ product_ref: shoes, price: 25.00, currency: EUR, quantity: 4 },
{ product_ref: blouse, price: 4530.00, currency: YEN, quantity: 1 }
]

And this is the expected result: 

The total column shows total in USD.

Total = price(currency) * quantity * rate(usd)

The exchange rates are obtained through an axios query to an API.

The only column which is editable is the quantity.

Thanks for any help,

Julien

[Rajapandi Ravi]

Hi Julien, 

Thanks for the update 

Based on your requirement we have prepared a sample and we suggest you use the below way to achieve your requirement. 

In this below sample, to display the Price column with respective rowdata currency we have used the queryCellInfo event instead of ValueAccessor. Please refer the below code example for more information. 

queryCellInfo: function (args) { //queryCellInfo event 

      if (args.column.field === "price") { 

        args.cell.innerText = args.data.price.toLocaleString("en-US", {  

          style: "currency",  

          currency: `${args.data.currency}`, //set the currency format based on the respective datasource 

        }); 

      } 

    } 

Since we have the value of Quantity and Price column in the dataSource, based on that we have performed the (price * quantity) in QueryCellInfo event and displayed the result to the Total column. When the row turns into edited state, in actionBegin event we have to set the calculated result value in the Total column it will be displayed in the row edited form. Please refer the below code example for more information. 

queryCellInfo: function (args) { //queryCellInfo event 

      if (args.column.field === "Total") { 

        args.cell.innerText = "$" + args.data.price * args.data.Quantity; 

      } 

    } 

actionBegin: function (args) { //actionBegin event 

      if (args.requestType === "beginEdit") { 

        args.rowData.Total = +args.row.lastElementChild.innerText.replace(/^\D+/g, ""); 

      } 

    } 

To apply the sum aggregates for the Total column, we suggest you use the CustomAggregate feature of Grid. Since the Total column was not exist in your datasource we suggest you call the refresh() method of aggregate in actionComplete event. It will helps to refresh the aggregate with current edited value. Please refer the below code example for more information. 

<template> 

  <div id="app"> 

    <ejs-grid 

      :dataSource="data" 

      :allowFiltering="true" 

      :filterSettings="filterSettings" 

      :allowPaging="true" 

      :pageSettings="pageSettings" 

      :editSettings="editSettings" 

      :queryCellInfo="queryCellInfo" 

      :actionBegin="actionBegin" 

      :actionComplete="actionComplete" 

    > 

      <e-columns> 

       .  .  .  .  .  .  .  .  .  .  .  . 

       .  .  .  .  .  .  .  .  .  .  .  . 

        <e-column 

          field="Total" 

          :allowEditing="false" 

          type="number" 

          headerText="Total" 

        ></e-column> 

      </e-columns> 

      <e-aggregates> 

        <e-aggregate> 

          <e-columns> 

            <e-column 

              field="Total" 

              type="Custom" 

              :customAggregate="customAggregateFn" 

              :footerTemplate="footerTemp" 

            ></e-column> 

          </e-columns> 

        </e-aggregate> 

      </e-aggregates> 

    </ejs-grid> 

  </div> 

</template> 

<script> 

import Vue from "vue"; 

import { 

  GridPlugin, 

  Page, 

  Edit, 

  Aggregate, 

  Filter, 

} from "@syncfusion/ej2-vue-grids"; 

Vue.use(GridPlugin); 

export default { 

  data() { 

    return { 

      data: [ 

        { id: 1, price: 10.2, Quantity: 10, currency: "USD" }, 

        { id: 2, price: 20.3, Quantity: 20, currency: "EUR" }, 

        { id: 3, price: 12.23, Quantity: 2, currency: "USD" }, 

      ], 

      footerTemp: function () { 

        return { 

          template: Vue.component("footerTemplate", { 

            template: `<span>$ {{data.Custom}}</span>`, 

            data() { 

              return { data: {} }; 

            }, 

          }), 

        }; 

      }, 

    }; 

  }, 

  methods: { 

    actionComplete: function (args) { //actionComplete event of Grid 

      if (args.requestType === "save") { 

        var grid = document.getElementsByClassName("e-grid")[0] 

          .ej2_instances[0]; 

        grid.aggregateModule.refresh(); 

      } 

    }, 

    customAggregateFn: function (data) { 

      var sum = 0; 

      var grid = document.getElementsByClassName("e-grid")[0].ej2_instances[0]; 

      var val = grid.dataSource.filter((e) => { 

        let new_val = e.price * e.Quantity; 

        sum = sum + new_val; 

      }); 

      return sum; 

    }, 

  }, 

  provide: { 

    grid: [Filter, Page, Edit, Aggregate], 

  }, 

}; 

</script> 

<style> 

@import "https://cdn.syncfusion.com/ej2/material.css"; 

</style> 

For your both queries we have prepared a sample and achieved your requirement. Please refer the below sample for more information. 

Sample: https://codesandbox.io/s/vue-forked-vddfe?file=/src/App.vue 

Regards, 

Rajapandi R 

[Julien Hoffmann]

Hello  Rajapandi,

Thank you very much for this.

I still have a small requirement, is to have prices changed into USD when they are in another currency.

In your example, the id 2 is in EUR, the total should be converted into USD.

In the queryCellInfo, that would mean this change: price * quantity * rate

queryCellInfo: function (args) {
      if (args.column.field === "price") {
        args.cell.innerText = args.data.price.toLocaleString("en-US", {
          style: "currency",
          currency: `${args.data.currency}`,
        });
      }
      if (args.column.field === "Total") {
var rate_eur_usd = getExchangeRateFromAPIUsingAxios()

        args.cell.innerText = "$" + args.data.price * args.data.Quantity * rate_eur_usd;
      }
    },

I get the exchange rates on the fly using an external api like in the above. When I create such a solution, the Total column values are empty (not null, not zero, just empty). 

Can you help again ? To get fake rate from a real api, you can try GETing a number from here : https://www.random.org/integers/?num=1&min=1&max=10&col=1&base=10&format=plain&rnd=new

Thanks in advance,

Julien

[Rajapandi Ravi]

Hi Julien, 

Thanks for the update 

Currently, we are checking your query with the given details “that would mean this change: price * quantity * rate”. We will update you the details on 21^st Jan 2022. Until then we appreciate your patience. 

Regards, 

Rajapandi R 

[Rajapandi Ravi]

Hi Julien, 

By default, all our EJ2 Grid events are synchronized, we can be able to achieve your requirement only if you are having the rate value in your datasource. So, we cannot be able to achieve your requirement by using this asynchronous process. 

If you like to perform the calculation with rate column value, we suggest you get all rate column values from your service in the Grid initial rendering and maintain the fetched rate column value in one global variable array and then map the rate value from the array and perform your calculation. 

Regards, 

Rajapandi R 

[Julien Hoffmann]

Thank you very much !

[Rajapandi Ravi]

Hi Julien, 

Most Welcome. 

Regards, 

Rajapandi R 

[Julien Hoffmann]

Hello there,

While this solution works great, I now must export results in Excel, but the "Total" column doesn't get exported and I get an error.

Can you help again ?

Thank you.

Julien

Btw, I think I found a bug in

@syncfusion\ej2-grids\src\grid\actions\excel-export.js (v 18.4.41) line 719

it should read

 if (!isNullOrUndefined(column) && !isNullOrUndefined(style.type)) {
            style.type = column.type.toLowerCase();
        }




[Rajapandi Ravi]

Hi Julien, 

Thanks for the update 

We have checked your reported problem and we could see that you are using a syncfusion package version 18.4.41, we have prepared a sample with the same version and tried to reproduce your reported problem, but it was unsuccessful. Since you are using an older package version in your application; it may cause some problems in the Grid. So, we suggest you use the latest syncfusion package version. 

We have checked your shared information and we found that the “Total” column was not exist in your datasource. By default, the excel export shows the data only exist in the Grid datasource. We cannot export the Grid column which was not exist in our Grid datasource. This was the default behavior. 

If you want to export the customized value to the excel sheet, you can achieve this by using excelQueryCellInfo event. Please refer the below code example and sample for more information. 

excelQueryCellInfo: function (args) { 

      if (args.column.field === "Total") { 

        //you can perform your action here 

        args.value = args.data.price * args.data.Quantity; 

      } 

    } 

Sample: https://codesandbox.io/s/vue-forked-ooqklb?file=/src/App.vue 

API: https://ej2.syncfusion.com/vue/documentation/api/grid/#excelquerycellinfo 

Regards, 

Rajapandi R