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).
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.