Using the FILTER() function

The FILTER() function is used to fetch data from a data store and filter data from that data store. The data is fetched for the Datastore from different sources using a BINDAPI or a LOOKUP function. The FILTER function is ideally used with the list controls like TableGrid and Detail view.

Syntax

FILTER([datastore.columnname1, datastore.columnname2,…], Filter or Condition, Sort column,Order)

Let us take a simple example here to bind a DataStore to an API (MyData for this example).

Formula:
BINDAPI( [MyData.result.rows.name,MyData.result.rows.author_id])

Where the data is fetched to the DataStore (prod_datastore) as per the API which in this example had a condition to fetch data for names starting with ‘B’. Under properties set the All Rows property ON and Default Selected Item Index as Zero (0).

bindapi

Now let us add a table grid and fetch the rows from this Data Store. Now let us take a scenario where we want to find records with Author ID more than 6. We will now apply this filter condition only to the Data Store and not the API directly.

So, let us add a condition for the Table Grid control under Data > Custom Formula that will fetch all those records where the Author_id is more than 6. We will then sort the rows based on Name and in Descending order.

tablegrid preview

Formula:
FILTER([prod_datastore.result.rows.name, prod_datastore.result.rows.author_id] , prod_datastore.result.rows.author_id > 6,prod_datastore.result.rows.name, “DESC”)

Where

  • Prod_datastore.result.rows.name and Prod_datastore.result.rows.author_id is the name column to be fetched from the data store
  • Prod_datastore.result.rows.author_id > 6 is the filter / condition.
  • Prod_datastore.result.rows.name is the field to sort on in the order specified.
  • DESC specifies the order to display data in as descending order

Now let us take a preview to understand how the data is fetched and reflected to the table grid control.

tablegrid preview 1

Here we can see the two matching records as per the Filter applied and the rows ordered on Name column and in Descending order.