This function allows, from a formula, to obtain a text string using the data of one or several documents of any Docuo category. It is not necessary that the category indicated is related to the one that contains the formula.
The categoryAgg function is only available in Docuo Enterprise.
For example, if we have a category of countries (PAI – Country) and we want to list them from any formula, we must use this function:
Where:
Category : is the code, the name or the code – name of the category from which we want to extract the data:
Fields : is the text string between quotes with the labels of the fields that we want to extract from each document. They can be combined with fixed text as we see in the example:
Separator : is a text string between quotes that will separate the data of each document. You can include \n for line breaks or \t for tabs. For example:
Order : is an ordering text string. Indicates on which field or fields we want to order the results. The syntax must include the name of one or more fields separated by commas. Behind each of them we can specify the ASC or DESC clause to indicate the order. For example:
Condition : This is an optional parameter with a T-SQL condition text string of the same type used in dropdown lists. It allows us to filter the results of the aggregate. For example, to filter Countries in Europe or Asia:
Top: is an optional parameter with a number indicating the maximum number of records to return.
Grouping: is an optional parameter that indicates the field by which you want to group. When using groupings it is possible to indicate the aggregate functions:
- COUNT(*) to return the number of elements
- SUM(field) to get the sum of numeric and currency fields
- AVG(field) to get the sum of numeric and currency fields
- MAX(field) to get the maximum value of the field
- MIN(field) to obtain the minimum value of the field
It is important to know that when using groupers, only fields that are already included in the Fields parameter can be used in the Order parameter.
Finally, the final formula (without groupings) would be as follows:
We would obtain a text string with the specified data of the countries that are from Asia or Europe, up to a maximum of 7, sorted by population in descending order and separated by a line break:
Name: India , Continent: Asia, Population: 1,398,496,000
Name: Indonesia , Continent: Asia, Population: 269,856,000
Name: Pakistan, Continent: Asia, Inhabitants: 222,017,000
Name: Bangladesh , Continent: Asia, Population: 179,904,000
Name: Russia, Continent: Europe, Inhabitants: 146,710,000
Name: Japan, Continent: Asia, Inhabitants: 126,127,000
A variant with groupings of the same formula would be:
<@PAI:Data:Continent>"
)
The results of number of countries per continent with sum of inhabitants would be:
No. of countries: 54,
Inhabitants: 1,440,353,360
Continent: Americas,
No. of countries: 35,
Inhabitants: 1,046,571,635
Continent: Asia,
No. of countries: 48,
Inhabitants: 4.753.500.631
Continent: Europe,
No. of countries: 44,
Inhabitants: 747.089.798
Continent: Oceania,
No. of countries: 14,
Inhabitants: 74.915.610