ODataVirtualCollectionView Class

Calculated fields require proxies. To use them in IE11, you will need a polyfill such as this one: https://www.npmjs.com/package/proxy-polyfill.

Calculated fields can be useful when dealing with external data. For example, you could add a per-capita income field (gnp/pop) or a profit field (revenue-expenses).

Calculated fields are dynamic. If you change the fields used in the calculation, their values are updated automatically. They are also read-only. You may change the value of the properties used to calculate them, but you cannot directly edit the result.

Unlike FlexGrid cellTemplates, calculated fields can be used for sorting, filtering, and grouping. They can also be used with charts and any other Wijmo controls.

Calculated fields can be defined as functions that take a data item as an argument or as strings.

For example, if your data looked like this:

// regular data item
interface IDataItem {
      product: string,
      brand: string,
      unitPrice: number,
      qty: number,
      shipped: boolean
}
function getData(): IDataItem[] {
    return [
        {
            product: 'Banana',
            brand: 'Chiquita',
            unitPrice: 45.95,
            qty: 12,
            discount: .08,
            shipped: true
        }, ...
    ]
}

You could add function-based calculated fields this way:

// add calculated properties to IDataItem
interface ICalcDataItem extends IDataItem {
    fullName: string;
    allCaps: string;
    totalPrice: number,
    tax: number;
}

let cv = new CollectionView<ICalcDataItem>(getData(), {
    calculatedFields: {
        fullName: ($: ICalcDataItem) => [$.brand, $.product].join(' '),
        allCaps: ($: ICalcDataItem) => $.fullName.toUpperCase(),
        totalPrice: ($: ICalcDataItem) => ($.unitPrice * $.qty) * (1 - $.discount),
        tax: ($: ICalcDataItem) => $.totalPrice * 0.12
    }
});

Function-based calculated fields are usually a better choice than string-based calculated fields because:

1) They provide design-time error checking and command completion, 2) They run faster, and 3) They do not have any issues with content-security policy (CSP).

Alternatively, you could add string-based calculated fields:

let cv = new CollectionView<IDataItem>(getData(), {
  calculatedFields: {
    fullName: '[$.brand, $.product].join(" ")',
    allCaps: '$.fullNameStr.toUpperCase()',
    totalPrice: '($.unitPrice * $.qty) * (1 - $.discount)',
    tax: '$.totalPrice * 0.12'
});

String expressions may refer to the current item via the context variable '$', which contains the item's original and calculated values.

String-based calculated fields have advantages over function-based calculated fields that may be important in some scenarios:

1) They are slightly more concise, and 2) They can be stored as data and easily changed at run-time.

This property does not affect the filters property, which are always applied.

The object keys represent the field names and the values are DataType values that indicate how the data should be coerced.

For example, the code below creates an ODataCollectionView and specifies that 'Freight' values, which are stored as strings in the database, should be converted into numbers; and that three date fields should be converted into dates:

import { ODataCollectionView } from '@grapecity/wijmo.odata';
import { DataType } from '@grapecity/wijmo';
const url = 'http://services.odata.org/V4/Northwind/Northwind.svc/';
const orders = new ODataCollectionView(url, 'Orders', {
  dataTypes: {
    Freight: DataType.Number
    OrderDate: DataType.Date,
    RequiredDate: DataType.Date,
    ShippedDate: DataType.Date,
  }
});

This property is useful when the database contains data stored in formats that do not conform to common usage.

In most cases you don't have to provide information about the data types, because the inferDataTypes property handles the conversion of Date values automatically.

If you do provide explicit type information, the inferDataTypes property is not applied. Because of this, any data type information that is provided should be complete, including all fields of type Date.

The default value for this property is false, which causes any changes to the data to be immediately committed to the database.

If you set this property to true, it will automatically set the trackChanges property to true. After this, any changes to the data (including edits, additions, and removals) will be tracked but not committed to the database until you call the commitChanges method to commit the changes, or the cancelChanges method to discard all pending changes.

For example:

import { ODataCollectionView } from '@grapecity/wijmo.odata';

// create data source
let url = 'https://services.odata.org/...';
let view = new ODataCollectionView(url, 'Categories', {
    keys: [ 'ID' ]
});

// defer commits
view.deferCommits = true;

// handle commit/cancel changes buttons
let btnCommit = document.getElementById('btn-commit') as HTMLButtonElement,
    btnCancel = document.getElementById('btn-cancel') as HTMLButtonElement;
btnCommit.addEventListener('click', () => view.commitChanges());
btnCancel.addEventListener('click', () => view.cancelChanges());
view.hasPendingChangesChanged.addHandler((s, e) => {
   btnCommit.disabled = btnCancel.disabled = !view.hasPendingChanges;
});

This may be required to update data in some OData services.

For more details, please see http://docs.oasis-open.org/odata/odata-json-format/v4.0/cs01/odata-json-format-v4.0-cs01.html#_Toc365464687.

This property maps directly to OData's $expand option.

For example, the code below retrieves all the customers and their orders from the database. Each customer entity has an "Orders" field that contains an array of order objects:

import { ODataCollectionView } from '@grapecity/wijmo.odata';
const url = 'http://services.odata.org/V4/Northwind/Northwind.svc/';
const customersOrders = new ODataCollectionView(url, 'Customers', {
  expand: 'Orders'
});

If this property is set to null or to an empty array, all fields are retrieved.

For example, the code below creates an ODataCollectionView that gets only three fields from the 'Categories' table in the database:

import { ODataCollectionView } from '@grapecity/wijmo.odata';
const url = 'http://services.odata.org/V4/Northwind/Northwind.svc/';
const categories = new ODataCollectionView(url, 'Categories', {
  fields: ['CategoryID', 'CategoryName', 'Description']
});

The callback should return true if the item passed in as a parameter should be included in the view.

The default value for this property is null, which means the data is not filtered.

The filter definition syntax is described in the OData documentation.

For example, the code below causes the server to return records where the 'CompanyName' field starts with 'A' and ends with 'S':

view.filterDefinition = "startswith(CompanyName, 'A') and endswith(CompanyName, 'B')";

Filter definitions can be generated automatically. For example, the FlexGridFilter component detects whether its data source is an ODataCollectionView and automatically updates both the ODataCollectionView.filter and ODataCollectionView.filterDefinition properties.

Note that the ODataCollectionView.filterDefinition property is applied even if the ODataCollectionView.filterOnServer property is set to false. This allows you to apply server and client filters to the same collection, which can be useful in many scenarios.

For example, the code below uses the ODataCollectionView.filterDefinition property to filter on the server and the ODataCollectionView.filter property to further filter on the client. The collection will show items with names that start with 'C' and have unit prices greater than 20:

import { ODataCollectionView } from '@grapecity/wijmo.odata';
const url = 'http://services.odata.org/V4/Northwind/Northwind.svc/';
const data = new ODataCollectionView(url, 'Products', {
  oDataVersion: 4,
  filterDefinition: 'startswith(ProductName, \'C\')', // server filter
  filterOnServer: false, // client filter
  filter: function(product) {
    return product.UnitPrice > 20;
  },
});

To be included in the view, an item has to pass the predicate in the filter property as well as all predicates in the filters collection.

The method takes as parameters a data item, the property being validated, and a parsing parameter that describes whether the data has already been parsed and applied to the data item (parsing == false), or whether the user was trying to edit the value and entered a value that could not be parsed into the data type expected (parsing == true).

The method returns a string containing an error message, or null if no errors were detected.

For example,

view = new CollectionView(data, {
    getError: (item: any, prop: string, parsing: boolean) => {

        // parsing failed, show message
        if (parsing) {
            if (prop == 'date') {
                return 'Please enter a valid date in the format "MM/dd/yyyy"';
            } else if (prop == 'id') {
                return 'Please enter a positive number';
            }
        }

        // check that stored (parsed) data is valid
        if (prop == 'date' && item.date < minDate) {
            return 'Please enter a date after ' + Globalize.formatDate(minDate, 'd');
        } else if (prop == 'id' && item.id < 0) {
            return 'Please enter a positive number';
        }
    }
});

See also the deferCommits property and the commitChanges and cancelChanges methods.

This property is set to true by default, because the ODataCollectionView class uses JSON and that format does not support Date objects.

This property has no effect if specific type information is provided using the dataTypes property.

This property can be used to provide progress indicators.

If provided, the function must take two parameters (key and value), and must return the parsed value (which can be the same as the original value).

For details about reviver functions, please refer to the documentation for the JSON.parse method.

Key fields are required for update operations (add/remove/delete).

If the creator function is not supplied, the CollectionView will try to create an uninitialized item of the appropriate type.

If the creator function is supplied, it should be a function that takes no parameters and returns an initialized object of the proper type for the collection.

There are currently four versions of OData services, 1.0 through 4.0. Version 4.0 is used by the latest services, but there are many legacy services still in operation.

If you know what version of OData your service implements, set the oDataVersion property to the appropriate value (1 through 4) when creating the ODataCollectionView (see example below).

import { ODataCollectionView } from '@grapecity/wijmo.odata';
let url = 'https://services.odata.org/Northwind/Northwind.svc/';
let categories = new ODataCollectionView(url, 'Categories', {
  oDataVersion: 1.0, // legacy OData source
  fields: ['CategoryID', 'CategoryName', 'Description'],
  sortOnServer: false
});

If you do not know what version of OData your service implements (perhaps you are writing an OData explorer application), then do not specify the version. In this case, the ODataCollectionView will get this information from the server. This operation requires an extra request, but only once per service URL, so the overhead is small.

The default value for this property is true, which ensures the collection is always sorted, filtered, and grouped correctly after any edit operations.

Set it to false if you want updates to be deferred when items are edited. In this case, the collection will not be refreshed until the sorting, filtering, and grouping criteria change or until the refresh method is called (Excel behavior).

The most typical use for this property is in scenarios where authentication is required. For example:

import { ODataCollectionView } from '@grapecity/wijmo.odata';
const url = 'http://services.odata.org/V4/Northwind/Northwind.svc/';
const categories = new ODataCollectionView(serviceUrl, 'Categories', {
  fields: ['Category_ID', 'Category_Name'],
  requestHeaders: { Authorization: db.token }
});

If provided, the sort comparer function should take as parameters two values of any type, and should return -1, 0, or +1 to indicate whether the first value is smaller than, equal to, or greater than the second. If the sort comparer returns null, the standard built-in comparer is used.

This sortComparer property allows you to use custom comparison algorithms that in some cases result in sorting sequences that are more consistent with user's expectations than plain string comparisons.

For example, see Dave Koele's Alphanum algorithm. It breaks up strings into chunks composed of strings or numbers, then sorts number chunks in value order and string chunks in ASCII order. Dave calls the result a "natural sorting order".

The example below shows a typical use for the sortComparer property:

import { CollectionView, isString } from '@grapecity/wijmo';

// create a CollectionView with a custom sort comparer
const view = new CollectionView(data, {
    sortComparer: (a: any, b: any) => {
        return isString(a) && isString(b)
            ? alphanum(a, b) // use custom comparer for strings
            : null; // use default comparer for everything else
    }
});

The example below shows how you can use an Intl.Collator to control the sort order:

import { CollectionView, isString } from '@grapecity/wijmo';

// create a CollectionView that uses an Intl.Collator to sort
const collator = window.Intl ? new Intl.Collator() : null;
let view = new CollectionView(data, {
    sortComparer: (a, b) => {
        return isString(a) && isString(b) && collator
            ? collator.compare(a, b) // use collator for strings
            : null; // use default comparer for everything else
    }
});

If provided, the function should take as parameters a SortDescription, a data item, and a value to convert, and should return the converted value.

This property provides a way to customize sorting. For example, the FlexGrid control uses it to sort mapped columns by display value instead of by raw value.

For example, the code below causes a CollectionView to sort the 'country' property, which contains country code integers, using the corresponding country names:

const countries = 'US,Germany,UK,Japan,Italy,Greece'.split(',');
view.sortConverter = (sd: SortDescription, item: any, value: any) => {
    return sd.property === 'countryMapped'
        ? countries[value]; // convert country id into name
        : value;
}

The next example combines two values so when sorting by country, the view will break ties by city:

view.sortConverter: (sd: SortDescription, item: any, value: any) => {
    if (sd.property == 'country') {
        value = item.country + '\t' + item.city;
    }
    return value;
}

This property is set to SortNulls.Last by default, which causes null values to appear last on the sorted collection, regardless of sort direction. This is also the default behavior in Excel.

The default value for this property is false, so the CollectionView does not keep track of which data items have changed.

If you set this property to true, the CollectionView will keep track of changes to the data and will expose them through the itemsAdded, itemsRemoved, and itemsEdited collections.

Tracking changes is useful in situations where you need to update the server after the user has confirmed that the modifications are valid.

After committing or cancelling changes, use the clearChanges method to clear the itemsAdded, itemsRemoved, and itemsEdited collections.

The CollectionView only tracks changes made when the proper CollectionView methods are used (editItem/commitEdit, addNew/commitNew, and remove). Changes made directly to the data are not tracked.

Stable sorting algorithms maintain the relative order of records with equal keys. For example, consider a collection of objects with an "Amount" field. If you sort the collection by "Amount", a stable sort will keep the original order of records with the same Amount value.

The default value for this property is false, which causes the CollectionView to use JavaScript's built-in sort method, which is fast and usually stable.

Chrome provides stable sorting since version 70, and Firefox since version 3. As of ES2019, sort is required to be stable. In ECMAScript 1st edition through ES2018, it was allowed to be unstable.

Setting the useStableSort property to true ensures stable sorts on all browsers (even IE 11), but increases sort times by 30% to 50%.