Select and query specific records

updated 9 days ago

select ... where

select lets you access any record of any table within a database using a script. Enter the name of the table from which you want to pull records after the select command.

Example

Insert the following script in the options of an embedded table view under Formula to display all the records of the Customers (New) table.

select 'Customers (New)';

Result: All records of the Customers (new) table are displayed in an embedded table.

To avoid displaying all records of a table, limit the selection with select ... where. Then only the records are displayed which fulfill the condition after where.

Example

In the Customers table, a choice field Headquarter is available, in which the IDs of the choice values are assigned as

1 = Germany
2 = Austria
3 = Switzerland
4 = France
5 = Spain
6 = Italy

and you'd like to display only the DACH region, i.e., Germany, Austria, and Switzerland.

select 'Customers (new)' where 'Headquarter' = 1 or 'Headquarter' = 2 or 'Headquarter' = 3;

Result: In an embedded table the records of the table Customers (new) are displayed, which have their company headquarters in 1 ("Germany"), 2 ("Austria"), or 3 ("Switzerland").

In case you use special characters in a field or table name, e.g., white space, hyphen, underscore etc., put the name between 2 single quotes ('...').

Useful alternative to `... where`

You may further filter already selected records, i.e., an array of type [nid], by specifying the condition to filter by in brackets [...] after the array.

let customers := select 'Customers (New)';
customers['Headquarter'=1]

In contrast to select ... where, all records are selected first and then filtered. With select ... where only the records that meet the conditions are selected.