Server Functionality

AdapTable is a client-side tool and designed to run on the client - either directly in the browser or in related environments like Electron, OpenFin, Finsemble, Glue42 etc.

Out of the box all the functionality in AdapTable will run purely in the client.

However there are frequent cases where our users want to perform some actions on the server. These include:

  • Server Validation - sometimes its preferable that proposed data edits are externally validated before being 'allowed'

  • Bespoke JavaScript Functions- often users will want to do external lookups (e.g. for Entitlements)

  • Searching and Filtering - perhaps the most common request, where users want to perform searches on the server but using AdapTable's rich 'Expression' / Querying capabilities

  • Getting Column Values From Server - many Modules (e.g. Column Filters) require a list of distinct column values and these might want to be sourced from a server

  • AG Grid Server Row Model - getting new data each time the user scrolls or 'pages'

What AdapTable can do for each of these use cases is detailed below in turn.

Server Validation

AdapTable provides a powerful Alert function which allows for the creation of Prevent Edit Rules that run each time a propsosed edit is made and disallow those which break a rule.

But sometimes users require more sophisticated rules that run on the Server that contain complicated calculated or external lookups.

To facilitate this AdapTable offers Server Validation functionality that works as follows:

In the Edit Options section of Adaptable Options developers supply a function that will run each time a cell is edited.

The function receives a DataChangedInfo object which provides the old and new values, the column, and also the node in which the cell lives.

important

This function will only be called AFTER Pevent Cell Alerts validation has successfully completed.

Validation Result object

The function returns a Promise containing a ValidationResult. This contains 2 properties:

New Value

NewValue is the value to use in the cell instead of the one proposed by the Cell Edit.

It can be one of 3 possibilities:

  • nothing - either the edit is valid - or you want to 'swallow' it and update the data through the API or an alternate mechanism
  • the old value - i.e. validation failed so we should return to the previous cell value
tip

You can retrieve the previous value through oldValue property of DataChangedInfo

  • a different value altogether - this is rare but sometimes used in advanced scenarios

Validation Message

The optional ValidationMessage property allows you to display a message to the User in addition to allowing / cancelling the Edit.

tip

Set displayServerValidationMessages to true in Edit Options to cause an Info Message to appear with any ValidationMessage sent by Server Validation

Server Validation Example

In this (slightly contrived) example the logic is that for the 'Amount' column any edit > 100 will return 100, any edit < 20 will return 20 and if edit is 50 its rejected.

note

Any edits that don't break those rules - or which are not for the 'amount' column - we ignore (so they will be processed normally)

adaptableOptions.editOptions = {
validateOnServer: (dataChangedInfo: DataChangedInfo) => {
return new Promise((resolve, reject) => {
setTimeout(() => resolve(getServerEditResponse(dataChangedInfo)), 2000);
});
},
};
function getServerEditResponse(dataChangedInfo: DataChangedInfo): ValidationResult {
if (dataChangedInfo.ColumnId == 'amount') {
if (dataChangedInfo.NewValue == 50) {
return {
NewValue: dataChangedInfo.OldValue,
ValidationMessage: 'Cannot set amount to 50',
};
} else if (dataChangedInfo.NewValue > 100) {
return {
NewValue: 100,
ValidationMessage: 'Amount cannot be greater than 100',
};
} else if (dataChangedInfo.NewValue < 20) {
return {
NewValue: 20,
ValidationMessage: 'Amount cannot be less than 20',
};
}
}
return {};
}

Server Searching and Filtering

By default the Client Side Row Model performas all filtering and searching in AdapTable on the client.

tip

See below for options when using Server Row Model or Infinite Scrolling.

However you can choose to run some or all filtering and searching on the Server instead.

caution

Modern browsers are very powerful and AdapTable is very fast and performant so only run server searching if you have more than 100,000 records that you need filtering.

This is done via the ServerSearchOptions property in the SearchOptions section of Adaptable Options.

The property is an array that can take any combination of these 3 values:

  • Query - runs the Query Module on the server.

  • Filter - enables a Filter to be run on the server

  • Sort - allows a sort to take place on the server (presumably to return a new Dataset)

SearchChanged Event

Whenever the search criteria in AdapTable change (e.g. a new Query has been run, or a Column Filter has been applied) the SearchChanged event is fired.

The event includes a SearchChangedInfo property which contains these important properties:

PropertyDescription
adaptableSearchStateCurrent searches and filters in the Grid
adaptableSortStateCurrent sort state in the Grid
searchChangedTriggerWhat action caused event to fire: DataSource, CurrentQuery, Filter, DataChange or Sort
important

AdapTable will not perform the client side action if a server search option is set, e.g. if its set to Query then AdapTable will do nothing when the Query is run (other than fire the SearchChangedEvent).

JSON Translation of Predicates

All the Filter and Predicate objects in the Search State are JSON.

warning

This means that in order to perform searching and filtering on the server this JSON will need to be 'translated' into something that the particular server can understand.

Obviously each server is different so AdapTable cannot provide an out of the box implemenation, but we do work with a number of partners who have performed this for clients and we have a Grid Gurus consultancy service that can advise you in a bespoke manner.

tip

If the SearchChangedTrigger is Data Source then you will not need to perform any JSON translation (though you will still, of course, need to provide AdapTable with the new dataset).

Managing Queries

The AdapTable Query Module uses a (boolean) Query to evaluate which rows should be returned.

This is an extremely powerful object which has a large number of built-in Expression functions that are evaluated by AdapTableQL and which operates, by default, on all columns in the grid.

However the Query (which is just a string) will need to be parsed and evaluated on the Server.

AdapTable helps in this effort in 2 ways:

1. Providing an AST

The SearchChangedInfo object provided by the SearchChanged Event includes an AdaptableSearchState property.

This itself contains a queryAST property which is the AST that AdapTableQL builds for the current Query and is provided as a utility to help those evaluating the Query on their server.

note

The AST for a given query can be retrieved by using the getASTForQuery method in the QueryAPI

2. Reducing Queries Scope

There are 2 ways to reduce the scope of a Query to enable server evaluation in more limited form:

  • Reducing Expression Functions

    The moduleExpressionFunctions property of Query Language Options allows developers to set (for each Module if required) which of the shipped Adaptable Expression Functions are available to be used in a Query (as well as add custom ones).

    warning

    If manipulating Expression Functions make sure to import from AdaptableExpressionFunctions from same location as Adaptable

    import Adaptable, { AdaptableExpressionFunctions } from '../../../../agGrid';
    ....
    const adaptableOptions: AdaptableOptions = {
    ...
    // Remove 3 unsupported functions, so unavailable in Editor or valid in Query
    queryLanguageOptions: {
    moduleExpressionFunctions: {
    ...omit(AdaptableExpressionFunctions, ['COALESCE', 'DIFF_YEARS', 'IS_BLANK']),
    },
    },
    }
    tip

    It is advisable intially to limit the Expression functions used on the server, and add more gradually

  • Limiting Queryable Columns

    By default all Columns in AdapTable can be used in a Query.

    To specify explicitly which columns are queryable you can list them in the queryableColumns property of Query Language Options.

    note

    If the property is left unset all Columns will be queryable, and if an empty array is provided, none will

    const adaptableOptions: AdaptableOptions = {
    ...
    // Remove 3 unsupported functions, so unavailable in Editor or valid in Query
    queryLanguageOptions: {
    queryableColumns: ['country', 'price', 'notional', 'ccy', 'bid', 'ask'],
    },
    }

Returning Search Results

Once the AdapTable JSON has been parsed into a query format that matches the particular server setup and the search has been run, then the data needs to be returned to AdapTable and displayed accordingly.

There are a number of different Adaptable Api methods you can use but the most common is perhaps setGridData in GridApi.

note

Once the data is provided, AdapTable will automatically apply and sorting and styles etc as required

Auto Applying

Related to Server Searching is turning off Auto Filtering.

By default, each Column Filter is applied as soon as it is created, but if peforming searching on the server it might be preferred to apply all the Column Filters simultaneously, by setting autoApplyFilter to false in Filter Options:

filterOptions = {
autoApplyFilter: false
};

Getting Column Values From Server

There are many places where AdapTable requires a list of distinct column values e.g. when opening a Column Filter list or when creating an Expression.

By default AdapTable will loop through values in the grid for that column retrieiving distinct items.

However there is an option to get the values for a column remotely.

To do this set the permittedValuesItems property in User Interface Options.

userInterfaceOptions = {
permittedValues: [
{
scope: {
ColumnIds: ['status'],
},
values: (column: AdaptableColumn) => {
return myServer.getValuesForColumn(column);
},
},
],
}

Read more about Permitted Column Values

AG Grid Server Row Model

AG Grid provides a Server Side Row Model which "allows applications to work with very large datasets by delegating grid operations such as grouping, sorting and pivoting to the server. The data is then lazy loaded from the server in blocks as the user browses through the data."

In this mode - or when using the similar Infinite Row Model - AG Grid will not perform any searching or filtering; instead it will hand it off to the server to perform.

important

With both those Models it is the develepor's responsibility to perform the actual searching on the server, including converting AdapTable's Filters and Queries to something relevant to your tech stack

See this demo on our Demo Site which provides an example how you can perform filtering and sorting on the server. Scroll to the bottom of the page to see all the code required to make it work.

Getting Search Config

When using the Server or Infinite Row Model, do not subscribe to the SearchChanged Event as it will not fire.

Instead, you need to fetch the current search (getAdaptableSearchState()) and sort (getAdaptableSortState()) state in AdapTable from the Config Api and pass it to the getRows function.

tip

To reduce the potential complexity of the Queries, the properties available to help you to Manage Queries detailed above can be used in Server and Infinite Row Models

FAQ

Can we swallow the Server Validation and return nothing?

Yes you can. You have the option in Server Validation of returning:

  • the original value - this will indicate that validation has succeeded

  • a replacement value - this new value will be used instead

  • nothing - the edit will persist in the Grid and presumably the user will update the DataSource in other ways.

More Information