1. Home
  2. Docs
  3. Data Source Expressions
  4. Data extraction

Data extraction

Quick links:

The functions described in the current section extract data from Registers and Catalogs.

Functions of Extraction should be located after the Registers / Catalogs name or after filters in expression (see Fig. 1).

Figure 1. Function of Extraction

GroupBy

Group records by the specified fields. Numeric fields are summed, non-numeric fields are grouped.

Format:

GroupBy( fieldName1 as alias1, fieldName2 as alias2,…, fieldNameN as aliasN)

Parameters:

fieldName – data source field name

alias – You can set field aliases through “as”. For example, if you specify Partner as Supplier, then the Supplier column will be in the resulting table.

Data type returned:

table

Description:

The GroupBy function can be used in an expression several times. For example, you extracted data, performed some calculations, and performed grouping again.

Example:

Money | GroupBy (CompanyAccount, Amount) – calculate account’s balances



Select

Select records by the specified fields. If no fields are specified, then all Register’s / Catalog’s fields will be selected.

Format:

Select ( fieldName1 as alias1, fieldName2 as alias2,…, fieldNameN as aliasN)

Parameters:

fieldName – data source field name

alias – You can set field aliases through “as”.

Data type returned:

table

Example:

Catalog.Partners | Select () – selects all records from Catalog Partners


Last

Selects the most recent unique records. The key to uniqueness is the non-numeric fields specified in the function. The timeline is determined by the standard field of the Period Register.

Format:

Last ( fieldName1 as alias1, fieldName2 as alias2,…, fieldNameN as aliasN)

Parameters:

fieldName – data source field name

alias – You can set field aliases through “as”.

Data type returned:

table

Example:

TaxRates | Period ( , Now) | Last (TaxType, Amount) – selects the current tax rate


First

Selects the first unique records in chronology. The key to uniqueness is the non-numeric fields specified in the function. The timeline is determined by the standard field of the Period Register.

Format:

First ( fieldName1 as alias1, fieldName2 as alias2,…, fieldNameN as aliasN)

Parameters:

fieldName – data source field name

alias – You can set field aliases through “as”.

Data type returned:

table

Example:

Sales | Period ( ThisMonth ) | First ( customer ) – finds first customer this month.


Turnover

Calculates the turnover for the specified fields. Records with the value of the standard field Direction = 1 (income) are summed with plus, records with the value of the standard field Direction = 2 (expenses) are summed with a minus.

Format:

Turnover ( fieldName1 as alias1, fieldName2 as alias2,…, fieldNameN as aliasN)

Parameters:

fieldName – data source field name

alias – You can set field aliases through “as”.

Data type returned:

table

Description:

For example, if $ 100 was credited to the account, and then $ 50 was debited, then the turnover calculated by the Turnover function will be $ 150.

Example:

Money | Period ( ThisMonth ) | Account ( main ) | Turnover ( Amount ) – calculates the turnover on the main account in the current month.