VAR Function

Summary

The VAR function in AdaptableQL enables developers to supply bespoke, custom values
These can then be referenced, and evaluated, in other Expressions

The VAR Expression Function allows developers to provide custom values which can be used in Expressions.

The return value of the VAR can be either

a 'hard-coded' value (string, number, boolean or Date)
a function that returns a value

The VAR values are provided at design-time via the customQueryVariables property in Expression Options.

customQueryVariables

Default: null

Record<string, [return_value]>

Custom variables to be used in AdapTableQL

Property which allow values to be attached to variables so that a single value can easily be expressed multiple times within a query, or quickly changed to affect the results of a query.

Variables are evaluated synchronously with each expression evaluation.

The definition of the property is:

customQueryVariables?: Record<
  string,
  | string | number | boolean | Date 
  | ((context: CustomQueryVariableContext) => string | number | boolean | Date)

As can be seen you can provide a Record of string (key) and either value or function that returns a value.

So, for example, a VAR which returns the VAT rate can be provided in 2 ways.

Either as a hard-coded value:

// Provide VAT as a hard-coded value
expressionOptions:{
  customQueryVariables: {
    VAT: 1.2,
  } 
};

Or using a function which receives a CustomQueryVariableContext object and returns a value.

The CustomQueryVariableContext contains the custom args required by the VAR.

// Provide VAT using a function (in the real world this will call server logic)
expressionOptions:{
  customQueryVariables: {
    VAT: (context: CustomQueryVariableContext) => {
      if (context.args) {
        const country: any = context?.args[0];
        switch (country) {
          case 'US':
            return 1;
          case 'Japan':
            return 1.1;
          case 'Germany':
            return 1.19;
          case 'UK':
            return 1.2;
        }
      }
      return 1;
    },
  },
};

AdapTableQL: VAR Expression Function

Fork Fork

This Example demonstrates how to use the VAR Expression Function
In Expression Options we define 3 VAR Expressions (all of which are then used in Adaptable Objects):
- EMISSION_DATE - has a hard-coded value of 1 Jan 2014 - and then used in a Format Column Condition
- VAT - a function that returns a number based on the Country param it receives - used to create the Full Price Calculated Column
- BEST_SELLER - has a mock server returned value of "Corolla" - and then used in a Format Column Condition and a Data Change Alert
We have also created a Named Query called 'Good Performance' which uses the first 2 VAR Expressions

Expand to see how the VAR Expression Functions are Used

The VAR Expression Functions are defined in Adaptable Options:

expressionOptions: {
 customQueryVariables: {
   BEST_SELLER: () => {
    return 'Corolla'; // in reality would fetch from server
   },
   EMISSION_DATE: new Date(2014, 0, 1),
   VAT: (context: CustomQueryVariableContext) => {
     if (context.args) {
       const country: any = context?.args[0];
       switch (country) {
         case 'US':
           return 1;
         case 'Japan':
           return 1.1;
         case 'Germany':
           return 1.19;
       }
     }
     return 1;
   },
 },
},

and they are referenced in Initial Adaptable State:

Alert: {
   AlertDefinitions: [
     {
       Name: 'alert-best-seller',
       Scope: {
         All: true,
       },
       Rule: {
         BooleanExpression: '[model] = VAR("BEST_SELLER")',
       },
       MessageType: 'Warning',
       MessageText: 'Updating the current Best Seller!',
       AlertProperties: {
         DisplayNotification: true,
       },
     },
   ],
NamedQuery: {
   NamedQueries: [
     {
       Name: 'Good Performance',
       BooleanExpression:
         'VAR("VAT",[country]) > 1 AND DIFF_YEARS(VAR("EMISSION_DATE") , [productionDate])  < 1',
     },
   ],
 },
 CalculatedColumn: {
   CalculatedColumns: [
     {
       ColumnId: 'full_price',
       FriendlyName: 'Full Price',
       Query: {
         ScalarExpression: "[price] * VAR('VAT',[country])",
       },
       CalculatedColumnSettings: {
         DataType: 'number',
         Resizable: true,
       },
     },
   ],
 },
 FormatColumn: {
   FormatColumns: [
     {
       Name: 'style-emissions',
       Style: {
         BackColor: '#b3e5fc',
         FontStyle: 'Italic',
       },
       Scope: {
         All: true,
       },
       Rule: {
         BooleanExpression: 'VAR("EMISSION_DATE") > [productionDate]',
       },
     },
    {
       Name: 'style-best-seller',
       Style: {
         FontSize: 'Large',
         FontWeight: 'Bold',
         FontStyle: 'Italic',
       },
       Scope: {
         ColumnIds: ['model'],
       },
       Rule: {
         BooleanExpression: 'VAR("BEST_SELLER") = [model]',
       },
      },
    ],
 },

Try It Out

Change the Price for "Corolla" and note the Alert that fires
Select and run the "Good Performance" Query from the Grid Filter Toolbar and see both VAR Expressions be used

import {
  AdaptableOptions,
  CustomQueryVariableContext,
} from '@adaptabletools/adaptable';
import {Car} from './rowData';

export const adaptableOptions: AdaptableOptions<Car> = {
  primaryKey: 'model',
  adaptableId: 'VAR Expression Functions',
  expressionOptions: {
    customQueryVariables: {
      BEST_SELLER: () => {
        return 'Corolla'; // would normally fetch from server
      },
      EMISSION_DATE: new Date(2014, 0, 1),
      VAT: (context: CustomQueryVariableContext) => {
        if (context.args) {
          const country: any = context?.args[0];
          switch (country) {
            case 'US':
              return 1;
            case 'Japan':
              return 1.1;
            case 'Germany':
              return 1.19;
          }
        }
        return 1;
      },
    },
  },
  initialState: {
    Dashboard: {
      ModuleButtons: ['FormatColumn', 'SettingsPanel'],
      PinnedToolbars: ['GridFilter'],
    },
    Theme: {CurrentTheme: 'dark'},
    NamedQuery: {
      NamedQueries: [
        {
          Name: 'Good Performance',
          BooleanExpression:
            'VAR("VAT",[country]) > 1 AND DIFF_YEARS(VAR("EMISSION_DATE") , [productionDate])  < 1',
        },
      ],
    },
    Alert: {
      AlertDefinitions: [
        {
          Name: 'alert-best-seller',
          Scope: {
            All: true,
          },
          Rule: {
            BooleanExpression: '[model] = VAR("BEST_SELLER")',
          },
          MessageType: 'Warning',
          MessageText: 'Updating the current Best Seller!',
          AlertProperties: {
            DisplayNotification: true,
          },
        },
      ],
    },
    CalculatedColumn: {
      CalculatedColumns: [
        {
          ColumnId: 'full_price',
          FriendlyName: 'Full Price',
          Query: {
            ScalarExpression: "[price] * VAR('VAT',[country])",
          },
          CalculatedColumnSettings: {
            DataType: 'number',
            Resizable: true,
          },
        },
      ],
    },
    FormatColumn: {
      FormatColumns: [
        {
          Name: 'style-emissions',
          Style: {
            BackColor: '#b3e5fc',
            ForeColor: 'Black',
          },
          Scope: {
            All: true,
          },
          Rule: {
            BooleanExpression: 'VAR("EMISSION_DATE") > [productionDate]',
          },
        },
        {
          Name: 'style-best-seller',
          Style: {
            FontSize: 'Large',
            FontWeight: 'Bold',
            FontStyle: 'Italic',
          },
          Scope: {
            ColumnIds: ['model'],
          },
          Rule: {
            BooleanExpression: 'VAR("BEST_SELLER") = [model]',
          },
        },
        {
          Name: 'formatColumn-price-full_price',
          Scope: {
            ColumnIds: ['price', 'full_price'],
          },
          DisplayFormat: {
            Formatter: 'NumberFormatter',
            Options: {
              Prefix: '$',
              FractionDigits: 0,
            },
          },
        },
        {
          Name: 'formatColumn-milesToGallon',
          Scope: {
            ColumnIds: ['milesToGallon'],
          },
          DisplayFormat: {
            Formatter: 'NumberFormatter',
            Options: {
              FractionDigits: 2,
            },
          },
        },
        {
          Name: 'formatColumn-productionDate',
          Scope: {
            ColumnIds: ['productionDate'],
          },
          DisplayFormat: {
            Formatter: 'DateFormatter',
            Options: {
              Pattern: 'yyyy/MM/dd',
            },
          },
        },
      ],
    },

    Layout: {
      CurrentLayout: 'Standard Layout',
      Layouts: [
        {
          Name: 'Standard Layout',
          TableColumns: [
            'make',
            'model',
            'price',
            'full_price',
            'productionDate',
            'available',
            'milesToGallon',
            'country',
          ],
          AutoSizeColumns: true,
        },
      ],
    },
  },
};

PreviousQUERY Function NextIF & CASE Logic Functions

Framework: