Creating Calculated Fields in Your JavaScript Data Grid

When displaying information inside a data grid, there may be times when you want to demonstrate values calculated based on values in your database but not stored in your database. With Wijmo's 2020v3 release, we've added a new property to Wijmo's CollectionView that will allow you to do just that: calculatedFields. Calculated fields are new columns created by the CollectionView and placed inside FlexGrid that use custom expressions to populate their cells.

In this blog, we'll outline the steps to implement calculatedFields and demonstrate the power of the custom expressions you can implement in JavaScript.

Wijmo offers the fastest, most flexible JavaScript DataGrid with features including sorting, grouping, searching, Excel-like filtering, DataMaps, custom CellTemplates, sparklines, rich editing, ExcelPDF export, validation, DetailsRows, and more. You can download the entire Wijmo component library here.

How to Import the Required Modules

The first thing that we'll need to do is import the required modules into our app.js file:

import ‘@grapecity/wijmo.styles/wijmo-core.css’;
import ‘./styles.css’;  
import { FlexGrid } from ‘@grapecity/wijmo.grid’;  
import { getCalculatedView } from ‘./data’;

We import the CSS files that we'll be using to style our grid, the FlexGrid module. We can create our data grid and getCalculatedView, which will return the data that will be rendered inside the grid.

Creating the CollectionView

Next, we'll need to create our getCalculatedView method inside of our data.js file to return our CollectionView and the calculatedFields:

export function getCalculatedView() {  
  return new CollectionView(getData(), {  
    calculatedFields: { ... }  
  });  
}

Inside of the CollectionView we call getData() to retrieve our data set. In this sample, getData() returns a set of data defined in-code; in most cases, this will be a call to a database to retrieve your data:

export function getData() {  
  return [  
    { product: ‘Banana’, brand: ‘Chiquita’, unitPrice: 45.95, qty: 12, discount: .08 },  
    { product: ‘Apple’, brand: ‘Granndy’, unitPrice: 65.95, qty: 23, discount: 0.02 },  
    ...,  
  ];  
}

Implementing Custom Expressions Inside of calculatedFields

The last thing that we'llwe'll need to do before assigning our CollectionView to the FlexGrid is to create our custom expressions that will be used to populate the new columns. The calculatedFields'calculatedFields' custom expressions support both function-based expressions and string-based expressions; in this sample, we'll be using function-based expressions to calculate the values of our calculatedFields.

We have both a Brand and a Product for each data item in our data, but we may want to show these two values in a single column. So, we'll create a field that combines those two strings:

fullName: ($) => [$.brand, $.product].join(‘ ‘),

Here, we assign a function to the fullName field and pass "\$" as an argument. In calculatedFields'calculatedFields' custom expressions, the "$" value represents the current data item. Inside the function, we take the current data item's brand and product values and use a join() statement to combine them with space between them.

We can also use fields that we previously created inside of new fields. We’ll create a new field, allCaps, that uses the fullName field and the toUpperCase() method to capitalize the value of fullName:

allCaps: ($) => $.fullName.toUpperCase(),

Finally, we'll create two more fields inside of calculatedFields: totalPrice and tax. The totalPrice field will use the unit price, quantity, and discount of the current data item to calculate the total price of the order, and the tax field will use the totalPrice field to calculate the tax paid on the order:

totalPrice: ($) => ($.unitPrice * $.qty) * (1 - $.discount),  
tax: ($) => $.totalPrice * 0.12

You can see the code of the completed calculatedFields object below:

calculatedFields: {  
  fullName: ($) => [$.brand, $.product].join(‘ ‘),  
  allCaps: ($) => $.fullName.toUpperCase(),  
  totalPrice: ($) => ($.unitPrice * $.qty) * (1 - $.discount),  
  tax: ($) => $.totalPrice * 0.12  
}

Assigning the CollectionView to FlexGrid

Finally, we'll assign the CollectionView to the FlexGrid in our app.js file and define the columns that will be displayed:

new FlexGrid(‘#theGrid’, {  
  alternatingRowStep: 0,  
  showMarquee: true,  
  selectionMode: ‘MultiRange’,  
  autoGenerateColumns: false,  
  columns: [  
    // regular data fields  
    { binding: ‘product’, header: ‘Product’ },  
    { binding: ‘brand’, header: ‘Brand’ },  
    { binding: ‘unitPrice’, header: ‘Unit Price’, format: ‘c’ },  
    { binding: ‘qty’, header: ‘Quantity’, format: ‘n0’ },  
    { binding: ‘discount’, header: ‘Discount’, format: ‘p0’ },  
    // calculated fields  
    { binding: ‘fullName’, header: ‘Full Name’, cssClass: ‘calculated’ },  
    { binding: ‘allCaps’ header: ‘UpperCase’ cssClass: ‘calculated’ },  
    { binding: ‘totalPrice’, header: ‘Total Price’, format: ‘c’ cssClass: ‘calculated’ },  
    { binding: ‘tax’, header: ‘Tax Amount’, format: ‘c’, cssClass: ‘calculated’ },  
  ],  
  itemsSource: getCalculatedView()  
});

As you can see, we can now use the calculatedFields property objects as bindings for columns in the grid. We'll also assign those columns some unique styling so that they stand out from the data source:

.calculated {  
  background-color: azure;  
}

And that it! We have a set of columns that use data from our data source and another set of columns that holds the data created through our custom expressions.

You can check out this sample live here.