The serial number in the request is 1c. View and ViewLink functions

Let's look at the rest now.

Functions for working with strings in 1C queries

There are few functions and operators for working with string data in 1C queries.

Firstly, strings in queries can be added. To do this, use the “+” operator:

Request. Text = "SELECT
" "Line: " " + Source.Name
;

Secondly, you can select part of the line. To do this, use the function SUBSTRUCTION. The function is similar to the built-in 1C language. It has three parameters:

Source string.
The number of the character with which the selected line should begin.
Characters.

Request. Text= "CHOOSE
SUBSTRING(""Line: " ", 4, 3) AS Result";

// Result: oka Function

ISNULL

NULL is a special data type on the 1C:Enterprise platform. It is also the only possible value of this type. NULL can appear in queries in several cases: when connecting query sources, if a corresponding value was not found in one of the tables; when accessing the details of a non-existent object; if NULL was specified in the list of query fields (for example, when combining selection results from several tables), etc. Because NULL is neither null, nor the empty string, nor even a value Undefined, it is often useful to replace it with some more useful data type. This is what the function is designed for.

ISNULL.

It has two parameters:
The value being checked.

Request. Text= "CHOOSE
The value to replace the first parameter with if it turns out to be NULL.; ISNULL(Source.Remainder, 0) AS Remainder"
// If the result of the request is the field remainder = NULL,

// then it will be replaced by 0, and you can perform mathematical operations with it Functions PERFORMANCE And

INTRODUCTIONLINKS Functions These functions are designed to obtain string representations of various values. That is, they convert references, numbers, booleans, etc. into plain text. The difference between them is that the function And converts any data types to text (string), and the function

Request. Text= "CHOOSE
- only links, and returns the remaining values as is, not converted.
REPRESENTATION(TRUE) AS Boolean,
REPRESENTATION (4) AS A Number,
REPRESENTATION (Source.Link) AS Link,;
REPRESENTATION(DATETIME(2016,10,07)) AS Date"
// Boolean = "Yes", Number = "4", Link = "Document Cash receipt order No.... from..."

Request. Text= "CHOOSE
// Date="07.10.2016 0:00:00"
REPRESENTATIONREFERENCE(TRUE) AS Boolean,
PRESENTINGLINK(Source.Link) AS Link,
REPRESENTATIONREFERENCE(DATETIME(2016,10,07)) AS Date";
// Boolean = TRUE, Number = 4, Link = "Document Cash receipt order No.... from..."
// Date=07.10.2016 0:00:00

// then it will be replaced by 0, and you can perform mathematical operations with it TYPE PERFORMANCE TYPE VALUES

// Result: oka TYPE returns the 1C:Enterprise platform data type.

Request. Text= "CHOOSE
TYPE (Number)
TYPE (String),
TYPE (Document. Expenditure Cash Order)";

// Result: oka TYPE VALUES returns the type of the value passed to it.

Request. Text= "CHOOSE
VALUES TYPE (5) AS Number,
TYPE (""Line" ") AS String,
TYPE (Source.Link) AS Reference
From the Directory.Source AS Source";
//Number=Number, String=String, Directory = DirectoryLink.Source

These functions are convenient to use, for example, when you need to find out whether a field received in a request is a value of some type. For example, let’s get the contact information of counterparties from the ContactInformation information register (contacts of not only counterparties, but also organizations, individuals, etc. are stored there):

Request. Text= "CHOOSE

FROM

WHERE
VALUES TYPE(ContactInformation.Object) = TYPE(Directory.Counterparties)";

// Result: oka MEANING

// Result: oka Meaning allows you to use 1C configuration objects directly in a request, without using .

Let's add one more condition to the previous example. You only need to get the phone numbers of your counterparties.

Request. Text= "CHOOSE
ContactInformation.Introduction
FROM
Register of Information. Contact Information HOW Contact Information
WHERE
VALUES TYPE(ContactInformation.Object) = TYPE(Directory.Counterparties)
AND ContactInfo.Type = VALUE(Enum.ContactInfoTypes.Phone)";

It should be noted that this function can only be used with predefined values, i.e. with values that can be accessed directly from the configurator. That is, the function MEANING cannot be used with directory elements created by users, but can work with enumerations, with predefined directory elements, with values EmptyLink.

Operator LINK

Operator LINK is designed to check the values returned by a request to see if they belong to a specific reference type. The same task can be accomplished using functions TYPE PERFORMANCE TYPE VALUES(which have a wider scope and were discussed above).

For example, the task of selecting contact information for counterparties could be solved this way:

Request. Text= "CHOOSE
ContactInformation.Introduction
FROM
Register of Information. Contact Information HOW Contact Information
WHERE
ContactInformation.Object LINK Directory.Counterparties";

Operator EXPRESS

Operator EXPRESS used in 1C queries in two cases:

when you need to change the characteristics of a primitive type;
when you need to turn a field with a compound data type into a field with a single type.

Primitive data types include: number, string, date, boolean. Some of these data types have additional characteristics. Type Number has length and precision, type Line - length or unlimited.

Operator EXPRESS allows you to change not the data type, but additional characteristics. For example, he can turn a string with unlimited length into a string with limited length. This can be useful if you need to group query results by such a field. You can't group by fields with an unlimited length, so we convert it to a string with a length of 200 characters.

Request. Text= "CHOOSE
QUANTITY (DIFFERENT Arrival of Goods and Services. Link) AS Link
FROM
Document. Receipt of Goods and Services HOW Receipt of Goods and Services
GROUP BY
EXPRESS(Receipt of Goods and Services. Comment AS ROW (200))";

In some cases, queries to fields with a composite data type may not be processed optimally by the 1C platform. This results in longer query times, so it can be useful to convert a compound type to a single type in advance.

Request. Text= "CHOOSE
EXPRESS(Movement of GoodsTurnover.Order AS Document.Customer Order).Date AS Order Date,
Movement of GoodsTurnover.Nomenclature
FROM
RegisterAccumulations.Movement of Goods.Turnover AS Movement of GoodsTurnover
WHERE
Movement of GoodsTurnover.Order LINK Document.Client Order";

Operators CHOICE PERFORMANCE IS NULL

Operator CHOICE similar to operator IF in the built-in 1C language, but has somewhat reduced functionality.

Let's say we want to receive contact information from the ContactInformation information register and, at the same time, indicate in a separate request field whether it belongs to a counterparty or an individual.

Request. Text= "CHOOSE
ContactInformation.Introduction,
CHOICE
WHEN VALUES TYPE(ContactInformation.Object) = TYPE(Directory.Counterparties)
THEN " Counterparty "
ELSE CHOICE
WHEN VALUES TYPE(ContactInformation.Object) = TYPE(Directory.Individuals)
THEN " Individual"
ELSE "Someone else" "
END
END AS OWNER
FROM
Register of Information. Contact Information AS Contact Information";

As can be seen from the example, in the design CHOICE there is always a condition after the word WHEN; value applied if the condition is true after the word THEN and the value applied if the condition is not met, after the word OTHERWISE. All three design elements CHOICE are mandatory. Omit element OTHERWISE, the same way as when using the operator IF in the built-in 1C language, it is impossible. Also from the operator CHOICE there is no analogue of the design ELSEIF, but you can invest one CHOICE in another, as was done in our example.

Operator IS NULL used in design CHOICE to compare a query field with type NULL.

Request. Text= "CHOOSE
CHOICE
WHEN THE VALUE IS NULL THEN 0
ELSE Meaning
END";

In addition, the operator IS NULL can be used in query conditions, such as in a sentence WHERE.

In the test configuration in the document log, it is necessary to implement line numbering in a dynamic list. The journal includes three documents: “Application”, “Incoming order” and “Outgoing order”. Initially, the document log list form looks like this:

In principle, the problem would be easily solved if we received a list of documents in the report. But here things are different.

Reports are not a problem!

By creating a report on a data composition system with a list of documents, the problem would be solved very simply. You would just need to add a special field “Sequence number” to the report fields.

As a result, the user will see a similar report generation result:

But what if you need to get the numbering directly in the request? For example, this may be needed to obtain the sequence number of an entry in a dynamic list.

Numbering in the request

Let's change the query for the dynamic document log list as follows:

Document Journal. Type) AS T

Note: the example shows only the principle of numbering lines directly in the request. If you noticed, in the screenshot above the documents in the list, sorted by line number, “go” in type order (first incoming orders, then outgoing orders, etc.). This happens because when using a link to connect, we cannot guarantee that the GUIDs of the links will be unique. Also, you cannot compare links of different types of documents - this will lead to incorrect results. You can use a point in time or other fields that determine the specific location of the document from the general list of the magazine.

Of course, using such an approach, we would complicate the query to the database, plus, some features of the dynamic list that are important for the user’s work would become unavailable (dynamic reading of data, the main table, etc.), but the example was created only to demonstrate the ability to number lines in queries. Using this principle to solve other problems, for example for printing forms, such problems will not arise.

Please be aware that using a document table or document history table repeatedly can have a negative impact on performance. The optimal solution would be to create a temporary table of all selected documents and then work with it. Again, it all depends on the specific task!

Keywords: numbering query lines, FIRST, numbering lines in the builder, number

Sometimes there is a need to add a column with line numbers to the query, for example, when you need to display line numbers in a report, and you are using the report builder. Line numbering is also useful when it is necessary to display a limited number of data lines for certain groupings. Something like the FIRST 5 documents for each counterparty.

So, there is code that selects items, sorted by name:
CHOOSE
Nomenclature.Link
FROM

SORT BY
Nomenclature.Name
original request

You need to add a column here with the serial numbers of the item (within the framework of this sorting).
CHOOSE

FROM
Directory.Nomenclature AS Nomenclature
Software Nomenclature.Name >= Nomenclature_1.Name // Sorting condition for numbering

SORT BY
Nomenclature.Name
query with line numbers

We connect the table to itself in a rather clever way. Using the right table, we count the number of items with a name less than the current one on the left, using grouping.

A similar technique can be used if you need to specify row numbering taking into account grouping by some field.
CHOOSE
Nomenclature.Link AS Nomenclature,
Nomenclature.Parent AS Parent,
QUANTITY(Nomenclature_1.Link) AS Number
FROM
Directory.Nomenclature AS Nomenclature
INNER JOIN Directory.Nomenclature AS Nomenclature_1
Software Nomenclature.Name >= Nomenclature_1.Name
And Nomenclature.Parent = Nomenclature_1.Parent

Document Journal software. Link > = DocumentJournalDocumentJournal. Link /
Nomenclature.Parent,
Nomenclature.Link
query with line numbers within a grouping

This is the same request, it just has a grouping added to it by the “Parent” field, the numbering is different for each parent.

Performance:
If we talk about performance, then queries of this kind are naturally resource-intensive (mathematically false on the order of N^2). For most everyday tasks their use is quite acceptable.
By the way, the second query is executed somewhat faster than the first, this is due to the smaller size of the auxiliary tables.

Real usage example:
Now a little about why all this is being done, that is, the benefit that can be derived from the numbering of query strings.

For example, we are faced with the task of obtaining for each counterparty the last five documents of receipt of goods and services:
CHOOSE
Receipt of Goods and Services. Counterparty AS Counterparty,
Receipt of Goods and Services. Link AS Link,
QUANTITY(Receipt of Goods and Services_1.Link) AS Number
FROM
Document. Receipt of Goods and Services HOW Receipt of Goods and Services
INTERNAL JOIN Document. Receipt of Goods and Services AS Receipt of Goods and Services_1
PO Receipt of Goods and Services. Date<= ПоступлениеТоваровУслуг_1.Дата
And Receipt of Goods and Services. Counterparty = Receipt of Goods and Services_1. Counterparty

Document Journal software. Link > = DocumentJournalDocumentJournal. Link /
Receipt of Goods and Services. Link,
Receipt of Goods and Services. Counterparty

HAVING
QUANTITY(Receipt of Goods and Services_1.Link)<= 5

SORT BY
Counterparty,
Number
a request with line numbers if there are duplicates of the field of interest (the one relative to which the counter is being built)
CHOOSE
QUANTITY(Individuals_1.Name) AS Number,
Individuals. Link AS FULL NAME
FROM
Directory.Individuals AS Individuals
INTERNAL JOIN Directory.Individuals AS Individuals_1
Software (Individuals.Name + Individuals.Code >= Individuals_1.Name + Individuals_1.Code)

SORT BY
Number
Actually, the idea is this: use a “composite field” to obtain an increasing total (in this case, one field may contain duplicates, but the second in this example does not.)
Born in NEKRON's head