Sample queries for hierarchical catalog operations
This section describes the solutions of 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.
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.
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.