Programmatically check an element in the 1C hierarchy. The "in hierarchy" operator in a query. Using Logical OR in Conditions

This section shows examples of solving typical problems when working with hierarchical directories.

Obtaining elements of a hierarchical directory that are subordinate to a given group

To obtain subordinate elements of a hierarchical directory, the query language provides the IN HIERARCHY construct. Example of use IN HIERARCHY:

CHOOSE
Nomenclature.Code,
Nomenclature.PurchasePrice
FROM

In this example, all records of the Nomenclature directory located in the &Group group will be obtained, including itself, its subordinate groups and elements belonging to subordinate groups.

If we are only interested in elements and groups located directly in a given group, then we can obtain such elements by setting a condition on the Parent field. Example:

CHOOSE
Nomenclature.Code,
Nomenclature.Name AS Name,
Nomenclature.PurchasePrice
FROM
Directory.Nomenclature AS Nomenclature

WHERE
Nomenclature.Parent = &Group

This query will select groups and elements subordinate to the group with the &Group link.

Checking the presence of subordinate elements of a directory element

To check the presence of subordinate records of a directory element, you can use a query similar to the one presented:

In this example, the reference to the element for which you want to check for children is written to the Parent query parameter. After executing such a query, you need to check the result for emptiness. If the result is not empty, then there are subordinate records. Otherwise - no. Example:

If Request.Execute().Empty() Then
Report("No entries");
Otherwise
Report("Records available");
endIf;

Getting all parents of an element

The query language does not provide any special means for retrieving all parents of an element. You can use hierarchical totals to complete the task, but obtaining hierarchical totals is optimized for building totals for a large number of records, and is not entirely effective for obtaining the parents of a single element. To more efficiently retrieve all parent records of an element, it is recommended to loop through its parents in small portions. Example:

CurrentItemItem = ItemItem;

While the Truth Cycle
Request.SetParameter("CurrentItemItem", CurrentItemItem);
Result = Query.Run();
If Result.Empty() Then
Abort;
endIf;
Selection = Result.Select();
Selection.Next();
For ColumnNumber = 0 By Result.Columns.Quantity() - 1 Loop
CurrentItemItem = Selection[ColumnNumber];
Abort;
Otherwise
Report(CurrentItemItem);
endIf;
EndCycle;

If CurrentItemItem = Directories.Nomenclature.EmptyLink() Then
Abort;
endIf;
EndCycle;

In this example, all parents for the link recorded in the ElementNomenclature variable are displayed in the service message window. In the cycle, 5 link parents are selected.

If the number of levels in the directory is limited and small, then it is possible to obtain all parents with one request without a loop.

Displaying a hierarchical directory in a report

To display a hierarchical directory in a report while preserving the hierarchy, you must use a query similar to the following:

CHOOSE
Nomenclature.Code,
Nomenclature.Name AS Name,
Nomenclature.PurchasePrice
FROM
Directory.Nomenclature AS Nomenclature
SORT BY
Name HIERARCHY

This query selects all records from the directory and arranges them in a hierarchy. The result will be ordered by name, taking into account the hierarchy.

In order for directory groups to be placed above the elements, it is necessary to replace the ORDER BY clause in this request with the following:

SORT BY
Nomenclature.This is Group HIERARCHY,
Name

The result will still be ordered hierarchically, but the groups will appear above the elements.

It is also possible to replace the ORDER BY offer with the AUTO ORDER option. In this case, the result will be ordered in accordance with the settings of the directory, i.e. if the directory states that groups should be located above the elements, then they will be located above.

It is also possible to obtain the hierarchical structure of the directory using the results.

CHOOSE
Nomenclature.Code,
Nomenclature.Name AS Name,
Nomenclature.PurchasePrice

FROM Directory.Nomenclature AS Nomenclature

WHERE
(Nomenclature.ThisGroup = FALSE)

ORDER BY Name

Getting totals by hierarchy

To obtain totals by hierarchy in a query, you must specify the keyword HIERARCHY in the SOFTWARE TOTAL clause after specifying the field by which the totals will be calculated. An example of a report "Item turnover" with obtaining totals by hierarchy:

CHOOSE

FROM

Nomenclature HIERARCHY

As a result of this request, totals will be calculated not only for each item, but also for the groups to which this or that item belongs.

In the case where we do not need totals for elements, but only need totals for groups, we need to use the HIERARCHY ONLY construction in the totals. Example:

CHOOSE
Accounting for NomenclatureTurnover.Nomenclature AS Nomenclature,
Accounting for NomenclatureTurnover.Nomenclature.Presentation,
Accounting for NomenclatureTurnover.QuantityTurnover AS QuantityTurnover
FROM
Accumulation Register.Nomenclature Accounting.Turnover HOW Nomenclature AccountingTurnover
RESULTS AMOUNT (QuantityTurnover) PO
Nomenclature HIERARCHY ONLY

The result of this query will be total records only for item groups.

Attention! This is an introductory version of the lesson, the materials of which may be incomplete.

Query language 1C 8.3 for beginner programmers: operators BETWEEN and IN

Logical operator BETWEEN

Operator BETWEEN allows you to check is the value included? expression specified to the left of it into the range specified to the right ( along with range boundaries, that is, inclusive).

So instead of

If, on the contrary, it is necessary to select all food, the calorie content of which is not included in the range, then the following form of negation is suitable (a particle has appeared NOT):

Operator BETWEEN Can be applied to more than just numeric ranges. It also works well with dates:

Logical operator B

Checking for a match with one of the listed

Operator IN allows you to check does the value match the expression indicated to the left of it, with one of the values described on the right.

So instead of

you can write something more concise

And the result will be the same:

If, on the contrary, it is necessary to select all food, the color of which does not coincide with any value from the list, then the following form of negation is suitable (a particle has appeared NOT):

Checking if a value matches one of the query results

Suppose we need to select from the database only those colors that are present in the description of the food. Thus, the selected list should not contain, for example, the color black, since there is no black food in our database. You are reading a trial version of the lesson, full lessons are available.

One way to do this is to use the logical operator IN, to the right of which there will be a subquery selecting color names from all directory entries Food:

SELECT Name FROM Directory. Colors WHERE Name IN ( SELECT Color. Name FROM Directory. Food )

As an aside, I’ll mention that from an internal request it is possible to access the fields of an external request.

Again, for this form of the operator IN, particle usage is also available NOT in front of him.

Checking hierarchy membership for directories

For directories, checking can also be carried out for hierarchy membership.

First, let's look at an example of a hierarchical directory. Open the "Cities" directory in our database:

Please note that its elements are different from other directories ( Food, Colors, Flavors) presence of yellow folders. This directory groups.

Groups differ from ordinary elements in that they can include other groups and elements. Just like folders contain other folders and files.

To view the contents of a group, double-click on it:

To go up a level, double-click on the group again:

Thus, a hierarchical directory can contain both ordinary elements (for example, Rio de Janeiro, Salvador) and groups (for example, Brazil, India). You are reading a trial version of the lesson, full lessons are available.

Each element (whether a group or an ordinary element) can have a parent. For example, the parent of the element Rio de Janeiro is the group Brazil:

And this is true because Rio de Janeiro is part of the Brazil group in the directory hierarchy:

Now let's write a query that will request the selected country group and all the city elements included in it.

Please note that in the request text there is an ampersand (&) before the name GroupCountry. Names with an ampersand are automatically recognized by the system as parameters whose value must be set before the query is executed.

After we paste this query into the console and click the Run button to update, we will be able to set this parameter:

Select the "Russia" group as its value (Select button):

If we now click the "Run" button, the result of the query will be as follows:

The result of the request included the group itself (Russia) and all the elements that are part of it (Perm, Krasnoyarsk and Voronezh).

If you select “Brazil” instead of Russia, the result will be like this:

Thus the result of the operator IN THE HIERARCHY will be TRUE if the value of the expression on the left is a reference to a directory element and is included in the set of values on the right (Brazil) or hierarchically belongs to some group contained in this set (Sao Paulo, Rio de Janeiro, Salvador).

The set of values to be checked for matching can also include the result of a query. In this case, to the right of the operator IN you must specify a description of the request:

For the operator IN THE HIERARCHY particle usage is also available NOT in front of him.

Take the test

Start test

1. The logical operator BETWEEN checks the value

2. Logical expression 1 BETWEEN 1 AND 1

3. Logical expression 1 NOT BETWEEN 2 AND 2

4. Logical operator B checks

5. Logical operator B allows you to check

1C directories are a specialized metadata tree object that serves to store static reference information. For example, in typical configurations you can see the following views: , Nomenclature, Employees, Fixed Assets, etc. Information in directories, as a rule, does not change often. Directories are subsequently used in almost all accounting objects as an accounting section or reference information.

Below we will look at setting up and designing a directory from the configurator using the “Nomenclature” directory as an example.

Basic Tab

The “Basic” tab specifies the name, synonym, object representation, and description of purpose.

“Directory Hierarchy” tab

Here the hierarchy of the directory is established.

Hierarchy in 1C 8.3 is of two types - “ groups and elements" And " elements". It differs in that in the first case, only a folder (group) can be a parent (folder), and in the second case, an element can also be a parent.

“Place groups on top” - the flag is responsible for displaying groups in list form.

Also in the settings you can limit the number of groups in the directory hierarchy using the appropriate setting.

Owners Tab

A directory can be subordinated to another directory. From the point of view of configuring 1C 8.3, this means that the “Owner” attribute becomes mandatory for the subordinate element. An example of such a connection between directories in standard configurations “Nomenclature - Units of Measurement”, “Counterparties - Contractors' Agreements”.

The directory owner can also be the following metadata objects: , .

Data Tab

Get 267 video lessons on 1C for free:

The most important tab from a programmer's point of view. It contains the directory details.

The directory has a set of standard details that are not edited by the 1C 8.2 programmer; a list of them can be seen by clicking the “Standard Details” button:

I will dwell on each in more detail:

This group— an attribute with a Boolean type, indicating whether it is a group or an element. Available only in the hierarchical directory. Note, the value of this attribute cannot be changed in 1C: Enterprise mode.
Code— props, type number or string (usually a string). A number assigned automatically by the system. Typically calculated as (previous code + 1). I recommend using the string type, because sorting numeric values does not work as expected. Can be used as a directory presentation in a list and in input fields. Typically used to search for an element when entering a string. If you need to remove the Code field, enter zero in the line length.
Name— mandatory details, string type. The maximum line length is 150 characters. Can be used as a directory presentation in a list and in input fields. Typically used to search for an element when entering a string. If you need to remove the Name field, enter zero in the line length.
Parent— an attribute of the DirectoryLink type.<ИмяТекущегоСправочника>. Available only in the hierarchical directory. Points to the superior parent in the hierarchy. If the Element or Group is at the root of the directory, the value Directory is specified.<ИмяТекущегоСправочника>.EmptyLink.
Owner— link to the owner element of the current directory element (group). Available only in the subordinate 1C directory.
FlagDeletion— props with type Boolean. Responsible for displaying the “deletion mark” in the system. An element marked for deletion is considered unusable, but old document movements may remain on it.
Link— field of string type. This attribute stores a unique object identifier - GUID. What we see in the system in a visual display called “link” is just a representation of the object. Cannot be changed.
Predefined— boolean type, displays whether the element is predefined, more on that later. Cannot be changed.

The “Data” tab also indicates the representation of the directory in the system; before version 8.2.16, the representation could only be Code or Name. In recent versions of the platform (starting from 8.3), the view can be described independently in the manager module using the “ViewReceivingProcessing” handler.

Numbering tab

Here you can specify the settings of the directory regarding numbering. It is recommended to use autonumbering. Uniqueness control is a flag that helps, if necessary, to make the code unique. If, with the flag set, you try to write a directory element with a non-unique code, in 1C you will receive the message “The directory code has become non-unique.”

Code series - determines how to number the directory; you can enter the numbering of the directory by owner. For example, the counterparty “Horns and Hooves” will have its own numbering of contracts - “1, 2, 3”, etc.

Forms Tab

The forms for the directory are described here. If the configuration is launched in both normal and managed modes, then there will be two tabs with forms by default: “main” and “advanced” - different for the normal and managed applications.

This page has an important feature of the directory - ““. This is a very convenient function of 1C 8, which allows you, when filling out data in the input field, not to go into the directory, but to type its name, code, etc. and select the desired element from the drop-down list. It looks like this:

Other Tab

On the tab you can get quick access to the main modules of the directory - the object module and the manager module.

You can also define a list of predefined directory elements on the page. These are items that cannot be deleted in Enterprise Mode. Predefined elements can be accessed directly in the configurator by name, for example: Directories.Nomenclature.Service.

This tab also determines the blocking mode - automatic or controlled. Use of full-text search, as well as reference information about the directory, available in 1C: Enterprise mode.

In this article we want to discuss everything with you 1C query language functions, and query language constructs. What is the difference between function and design? The function is called with parentheses and possible parameters in them, and the construct is written without parentheses. Undoubtedly all structures and functions of the 1C query language make the data acquisition process flexible and multifunctional. These functions and constructs apply to query fields, and some also apply to conditions.

1C Query Language Functions

Because a clear description 1C query language functions is much less common than descriptions of structures, we decided to start looking at functions. Now let's look at each one separately, describing its purpose, syntax and example of use, so:

1. Function DATE TIME- this function creates a constant field with the "Date" type.

Syntax: DATE TIME(<Год>,<Месяц>,<День>,<Час>,<Минута>,<Секунда>)

Usage example:

2. DATE DIFFERENCE function- returns the difference between two dates in one of the dimensions (year, month, day, hour, minute, second). The measurement is passed as a parameter.

Syntax: DIFFERENCEDATE(<Дата1>, <Дата2>, <Тип>)

Usage example:

Query.Text = "SELECT | DIFFERENCEDATE(DATETIME(2015, 4, 17), DATETIME(2015, 2, 1), DAY) | AS Qty.Days";

3. Function VALUE- sets a constant field with a predefined record from the database; you can also get an empty link of any type.

Syntax: VALUE(<Имя>)

Usage example:

Request.Text = "SELECT //predefined element | VALUE(Directory.Currencies.Dollar) AS Dollar, //empty link | VALUE(Document.Receipt of Goods and Services.EmptyLink) AS Receipt, //transfer value | VALUE(Transfer. Legal Individual. Individual) AS Individual, //predefined account VALUE(Chart of Accounts. Self-Accounting.Materials) AS Account_10" ;

4. SELECT function- we have before us an analogue of the IF construction, which is used in the code, only this one is used in 1C queries.

Syntax: CHOICE WHEN<Выражение>THEN<Выражение>OTHERWISE<Выражение>END

Usage example:

Request.Text = //if the amount is more than 7500, then there should be a discount of 300 rubles, //so if the condition is triggered then the function //returns Sum - 300 //otherwise the request will return simply Sum "SELECT | SELECT | WHEN TCReceipts.Amount > 7500 | THEN TCReceipts.Amount - 300 | ELSE TCReceipts.Amount | END AS AmountWithDiscount | FROM |

5. EXPRESS function- allows you to express a constant field with a specific type.

Syntax: EXPRESS(FieldName AS TypeName)

Usage example:

Is there another option for using the EXPRESS function in fields of mixed types, where do they occur? The simplest example is the “Registrar” for any register. So why might we need to qualify the type in the registrar? Let's consider the situation when we select the "Number" field from the registrar, from which table will the number be selected? The correct answer of all! Therefore, for our query to work quickly, we should specify an explicit type using the EXPRESS function

Usage example:

Query.Text = "SELECT | EXPRESS(Nomenclature.Comment AS Line(300)) AS Comment, | EXPRESS(Nomenclature.Sum AS Number(15,2)) AS Sum |FROM | Directory.Nomenclature AS Nomenclature";

6. ISNULL function(alternative spelling ISNULL) - if the field is of type NULL, then it is replaced with the second parameter of the function.

Syntax: ISNULL(<Поле>, <ПодставляемоеЗначение>)

Usage example:

Also note that it is advisable to ALWAYS replace the NULL type with some value, because comparison with type NULL always returns FALSE even if you compare NULL with NULL. Most often, NULL values are formed as a result of joining tables (all types of joins except internal ones).

Query.Text = //Select the entire item and its balances //if there is no balance in some item, then there will be a field //NULL which will be replaced with the value 0 "SELECT | No. Link, | ISNULL(ProductsInStockRemains.InStockRemaining, 0) AS Remaining | FROM | Directory.Nomenclature AS No. | LEFT CONNECTION RegisterAccumulations.GoodsInWarehouses.Remainings AS GoodsInWarehousesRemainings | PO (GoodsInWarehousesRemainings.Nomenclature = No.Link)";

7. REPRESENTATION function- allows you to get a representation of the request field.

Syntax: PERFORMANCE(<НаименованиеПоля>)

Usage example:

Query.Text = "SELECT | REPRESENTATION(FreeRemainingRemains.Nomenclature) AS Nomenclature, | REPRESENTATION(FreeRemainingRemaining.Warehouse) AS Warehouse, | FreeRemainingRemaining.InStockRemaining |FROM |Accumulation Register.FreeRemaining.Remaining AS FreeRemainingRemaining";

Constructs in the 1C query language

We discussed with you above 1C query language functions, now it's time to consider constructs in the 1C query language, they are no less important and useful, let’s get started.

1. Construction LINK- is a logical operator for checking a reference type. Most often encountered when checking a field of a complex type against a specific type. Syntax: LINK<Имя таблицы>

Usage example:

Request.Text = //if the recorder value type is document Receipt, //then the query will return "Receipt of goods", otherwise "Sales of goods" "SELECT | SELECT | WHEN Remainings.Registrar LINK Document.Receipt of Goods and Services | THEN ""Receipt"" | ELSE ""Consumption"" | END AS Type of Movement | FROM | Register of Accumulation. Remaining Products in Warehouses AS Remaining" ;

2. Design BETWEEN- this operator checks whether the value is within the specified range.

Syntax: BETWEEN<Выражение>AND<Выражение>

Usage example:

3. Construction B and B HIERARCHY- check whether the value is in the transferred list (arrays, tables of values, etc. can be transferred as a list). The IN HIERARCHY operator allows you to view the hierarchy (an example of using the Chart of Accounts).

Syntax: IN(<СписокЗначений>), IN HIERARCHY(<СписокЗначений>)

Usage example:

4. Design SIMILAR- This function allows us to compare a string with a string pattern.

Syntax: LIKE "<ТекстШаблона>"

Row pattern options:

% - a sequence containing any number of arbitrary characters.

One arbitrary character.

[...] - any single character or sequence of characters listed inside square brackets. The enumeration can specify ranges, for example a-z, meaning an arbitrary character included in the range, including the ends of the range.

[^...] - any single character or sequence of characters listed inside square brackets except those listed after the negation sign.

Usage example:

5. Design ALLOWED- this operator allows you to select only those records from the database for which the caller has read permission. These rights are configured at the record level (RLS).

Syntax: ALLOWED is written after the keyword SELECT

Usage example:

Request.Text = "SELECT ALLOWED | Counterparties. Link | FROM | Directory. Counterparties AS Counterparties";

6. Design VARIOUS- allows you to select records in which there are no duplicate records.

Syntax: VARIOUS is written after the keyword SELECT

Usage example:

Request.Text = //selects records to which the reader has rights "SELECT VARIOUS | Counterparties.Name |FROM | Directory. Counterparties AS Counterparties" ;

Also, the VARIOUS construction can be used with the ALLOWED operator and other operators.

Usage example:

Request.Text = //selects various records to which the reader has rights "SELECT ALLOWED VARIOUS | Counterparties.Name |FROM | Directory. Counterparties AS Counterparties";

7. Design FIRST- selects the number of records specified in the parameter from the query result.

Syntax: FIRST<число>

Usage example:

Request.Text = //select the first 4 CCD numbers from the directory "SELECT FIRST 4 | CCD Numbers. Link | FROM | Directory. CCD Numbers AS CCD Numbers";

8. Design FOR CHANGE- allows you to lock a table, works only in transactions (relevant only for automatic locks).

Syntax: FOR CHANGE<НаименованиеТаблицы>

Usage example:

9. Design ORDER BY- organizes data by a specific field. If the field is a link, then when setting the flag AUTO ORDER Sorting will occur by link representation; if the flag is turned off, then links are sorted by the seniority of the link address in memory.

Syntax: SORT BY<НаименованиеПоля>AUTO ORDER

Usage example:

10. Design GROUP BY- used to group query strings by specific fields. Numeric fields must be used with any aggregate function.

Syntax: GROUP BY<НаименованиеПоля1>, .... , <НаименованиеПоляN>

Usage example:

11. Design HAVING- allows you to apply an aggregate function to a data selection condition, similar to the WHERE construction.

Syntax: HAVING<агрегатная функция с условием>

Usage example:

12. Construction INDEX BY- used for indexing the query field. A query with indexing takes longer to complete, but speeds up searching through indexed fields. Can only be used in virtual tables.

Syntax: INDEX BY<Поле1, ... , ПолеN>

Usage example:

13. Design WHERE- allows you to impose a condition on any selection fields. The result will include only records that satisfy the condition.

Syntax: WHERE<Условие1 ОператорЛогСоединения УсловиеN>

Usage example:

Query.Text = //all records with CompensationRemaining are selected<>0 and //AmountForCalcCompRemaining > 100 "SELECT | CompensationRPORemains.Counterparty, |CompensationRPORemains.Child, | CompensationRPORemains.CompensationRemaining, | CompensationRPORemains.AmountForCalcCompRemains |Place DataTz |FROM | Accumulation Register.CompensationRP.Remains AS CompensationRPORemains |WHERE |CompensationRPORemaining.CompensationRemaining<>0 | And CompensationRPORemains.AmountForCalcCompRemaining> 100" ;

14. Design RESULTS... GENERAL- used to calculate totals; the design specifies the fields by which totals will be calculated and aggregate functions applied to the total fields. When using totals for each field following the TOTAL construction, data is grouped. There is an optional GENERAL construct; its use also provides additional grouping. You will see an example of the request result below.

Syntax: RESULTS<АгрегатнаяФункция1, ... , АгрегатнаяФункцияN>BY<ОБЩИЕ> <Поле1, ... , ПолеN>

Usage example:

The figure outlines the groupings that were formed during the execution of the request, the top one refers to the GENERAL section, and the second to the Counterparty AgreementAgreement Type field.