Sample queries for hierarchical catalog operations

This section describes the solutions for typical tasks related to hierarchical catalogs.

Getting hierarchical catalog items that are subordinate to a specified folder

The IN HIERARCHY clause of the query language is intended for getting subordinate items of a hierarchical catalog. Example:

SELECT
    Items.Code,
    Items.Description AS Description,
    Items.PurchasePrice 
FROM
    Catalog.Items AS Items
WHERE
    Items.Ref IN HIERARCHY(&Folder)

This example gets all the items of the Items catalog that belong to the &Folder folder, including the folder itself, its subordinate folders, and items in these subordinate folders.

If you want to get only items and folders subordinate to a specific folder, you can get them by applying a condition to the Parent field. Example:

SELECT
    Items.Code,
    Items.Description AS Description,
    Items.PurchasePrice 
FROM
    Catalog.Items AS Items
WHERE
    Items.Parent = &Folder

This query selects items and folders that are subordinate to the folder with &Folder reference.

Checking availability of subordinate items for a catalog item

To check whether a catalog item has subordinate items, you can use the following query:

SELECT TOP 1
    Items.Ref
FROM
    Catalog.Items AS Items
WHERE
    Items.Parent = &Parent

In this example the reference to the item that should be checked for the availability of subordinate items is recorded to the Parent query parameter. After the query execution you have to check whether the result is empty. Subordinate records are available if the result is not empty. Otherwise, they are not available. Example:

If Query.Execute().IsEmpty() Then
    Message("No records");
Else 
    Message("Records are available");
EndIf;

Getting all parents of an item

The query language does not offer special means for getting all parents of an item. You can implement this using hierarchical totals, but getting hierarchical totals is optimized to build the totals of a large number of records and is not particularly efficient for getting the parents of a single item. To get all the parent records of an item in a more efficient manner, we recommend going through its parents in small batches in a loop.

Example:

CurrentItemsItem = ItemsItem;

Query = New Query("SELECT 
                      | Items.Parent, 
                      | Items.Parent.Parent, 
                      | Items.Parent.Parent.Parent, 
                      | Items.Parent.Parent.Parent.Parent, 
                      | Items.Parent.Parent.Parent.Parent.Parent 
                      |FROM 
                      | Catalog.Items AS Items 
                      |
                      |WHERE 
                      | Items.Ref = &CurrentItemsItem";

While True Do
    Query.SetParameter("CurrentItemsItem", CurrentItemsItem); 
    Result = Query.Execute(); 
    If Result.IsEmpty() Then 
        Break; 
    EndIf; 
    Selection = Result.Choose(); 
    Selection.Next(); 
    For ColumnNumber = 0 To Result.Columns.Count() - 1 Do 
        CurrentItemsItem = Selection[ColumnNumber]; 
        If CurrentItemsItem = Catalogs.Items.EmptyRef() Then 
            Break; 
        Else 
            Message(CurrentItemsItem); 
        EndIf; 
    EndDo; 

    If CurrentItemsItem = Catalogs.Items.EmptyRef() Then 
        Break; 
    EndIf;
EndDo;

In this example all parents for the reference stored in the ItemsItem variable are displayed in the message window. 5 parents are selected in each loop step.

If the number of levels in a catalog is limited and is not large, you can get all the parents in a single query without using a loop.

Displaying a hierarchical catalog in a report

To display a hierarchical catalog in a report while preserving the hierarchy, use the following query format:

SELECT
    Items.Code,
    Items.Description AS Description,
    Items.PurchasePrice 
FROM
    Catalog.Items AS Items 
ORDER BY
    Description HIERARCHY

This query selects all the records from a catalog and sorts them by hierarchy. The result is sorted by name with the hierarchy being taken into account.

For the catalog folders to be above the items, replace the ORDER BY clause with the following in this query:

ORDER BY
    Items.IsFolder HIERARCHY,
    Description

The result will be still sorted by hierarchy, but the folders will be located above the items.

It is also possible to replace the ORDER BY clause with the AUTOORDER clause. Then the result will be sorted according to the catalog settings, i.e. if the catalog settings specify that folders should be above items, they will be above.

You can also get the hierarchical structure of a catalog using totals.

Example:

SELECT
    Items.Code, 
    Items.Description AS Description, 
    Items.PurchasePrice 
FROM Catalog.Items AS Items
WHERE 
    (Items.IsFolder = FALSE)
ORDER BY Description
TOTALS BY Items.Ref ONLY HIERARCHY

Getting totals by hierarchy

In order to get the totals by hierarchy, in the TOTALS BY clause add the HIERARCHY keyword after specifying the field used for calculating totals. Here is an example "Item turnovers" report where totals are retrieved by hierarchy:

SELECT
    ItemAccountingTurnovers.Items AS Items,
    ItemAccountingTurnovers.Items.Presentation,
    ItemAccountingTurnovers.QuantityTurnover AS QuantityTurnover
FROM
    AccumulationRegister.ItemAccounting.Turnovers AS ItemAccountingTurnovers
TOTALS SUM(QuantityTurnover) BY
    Items HIERARCHY

This query calculates the totals not only for each item but also for the folders that contain the items.

If you do not need totals by items and only need totals by folders, use the ONLY HIERARCHY clause in the totals. Example:

SELECT
    ItemAccountingTurnovers.Items AS Items,
    ItemAccountingTurnovers.Items.Presentation,
    ItemAccountingTurnovers.QuantityTurnover AS QuantityTurnover
FROM
    AccumulationRegister.ItemAccounting.Turnovers AS ItemAccountingTurnovers
TOTALS SUM(QuantityTurnover) BY
    Items ONLY HIERARCHY

The query returns the totals for the item folders only.