When organizing samples in real problems, in the vast majority of cases, data selection is organized in accordance with certain criteria.

In the case when the selection is made from a real table, no difficulties arise. The data is processed absolutely trivially:

In the case when the source in the query is a virtual table, the situation becomes somewhat more complicated.

The query language allows you to impose a condition on a selection from virtual tables in two ways: in the WHERE clause and using virtual table parameters. Both methods will lead to the same result (with the exception of some specific cases), but, nevertheless, they are far from equivalent.

We already know that virtual tables are called virtual because they are not actually in the database. They are formed only at the moment when a request is made to them. Despite this, it is convenient for us (that is, those who write the query) to consider virtual tables as real ones. What will happen in the 1C Enterprise 8 system when the query we compiled still accesses the virtual table?

In the first step, the system will build a virtual table. In the second step, records will be selected from the resulting table that satisfy the condition specified in the WHERE clause:
It is clearly seen that the final sample will not include all records from the virtual table (and, therefore, from the database), but only those that satisfy the given condition. And the remaining records will simply be excluded from the result.

Thus, the system will do not just useless work, but double useless work! First, resources will be spent on building a virtual table based on unnecessary data (in the figure they are marked as “data areas A and B”), and then work will be done to filter this data from the final result.

Is it possible to immediately, at the stage of constructing a virtual table, stop using unnecessary data? It turns out that it is possible. This is exactly what the virtual table parameters are designed for:

By parameterizing a virtual table, we immediately limit the amount of data that will be processed by the query.

What is the difference between the values ​​of the virtual table parameter "Addition Method"?
When the Addition Method is set to "movements", then only those periods in which there were movements will be returned. When "Movements and Period Boundaries" is set, then 2 records will be added to the above movements: movements at the beginning and end of the period specified in the VT parameters. The “Registrar” field will be empty for these 2 records.

Information taken from the site

The query language in 1C 8 is a simplified analogue of the well-known “structured programming language” (as it is more often called, SQL). But in 1C it is used only for reading data; an object data model is used to change data.

Another interesting difference is the Russian syntax. Although in fact you can use English-language constructions.

Example request:

CHOOSE
Banks.Name,
Banks.CorrAccount
FROM
Directory.Banks HOW Banks

This request will allow us to see information about the name and correspondent account of all banks existing in the database.

The query language is the simplest and effective method obtaining information. As can be seen from the example above, in the query language you need to use metadata names (this is a list of system objects that make up the configuration, i.e. directories, documents, registers, etc.).

Description of query language constructs

Query structure

To obtain data, it is enough to use the “SELECT” and “FROM” constructions. The simplest request looks like this:

SELECT * FROM Directories.Nomenclature

Where “*” means selecting all fields of the table, and Directories.Nomenclature – the name of the table in the database.

Let's look at a more complex and general example:

CHOOSE
<ИмяПоля1>HOW<ПредставлениеПоля1>,
Sum(<ИмяПоля2>) HOW<ПредставлениеПоля2>
FROM
<ИмяТаблицы1>HOW<ПредставлениеТаблицы1>
<ТипСоединения>COMPOUND<ИмяТаблицы2>HOW<ПредставлениеТаблицы2>
BY<УсловиеСоединениеТаблиц>

WHERE
<УсловиеОтбораДанных>

GROUP BY
<ИмяПоля1>

SORT BY
<ИмяПоля1>

RESULTS
<ИмяПоля2>
BY
<ИмяПоля1>

IN this request we select the data of the fields “FieldName1” and “FieldName1” from the tables “TableName1” and “TableName”, assign synonyms to the fields using the “HOW” operator, and connect them using a certain condition “TableConnectionCondition”.

From the received data, we select only data that meets the condition from “WHERE” “Data Selection Condition”. Next, we group the request by the field “Field Name1”, while summing “Field Name2”. We create totals for the field “Field Name1” and the final field “Field Name2”.

The last step is to sort the request using the ORDER BY construct.

General designs

Let's look at the general structures of the 1C 8.2 query language.

FIRSTn

By using of this operator you can get n number of first records. The order of the records is determined by the order in the query.

SELECT FIRST 100
Banks.Name,
Banks. Code AS BIC
FROM
Directory.Banks HOW Banks
SORT BY
Banks.Name

The request will receive the first 100 entries of the “Banks” directory, sorted alphabetically.

ALLOWED

This design is relevant for working with the mechanism. The essence of the mechanism is to restrict reading (and other actions) to users for specific records in a database table, and not the table as a whole.

If a user tries to use a query to read records that are inaccessible to him, he will receive an error message. To avoid this, you should use the “ALLOWED” construction, i.e. the request will read only records that are allowed to it.

SELECT ALLOWED
Repository of Additional Information. Link
FROM
Directory.Repository of Additional Information

VARIOUS

Using “DIFFERENT” will prevent duplicate lines from entering the 1C query result. Duplication means that all request fields match.

SELECT FIRST 100
Banks.Name,
Banks. Code AS BIC
FROM
Directory.Banks HOW Banks

EmptyTable

This construction is used very rarely to combine queries. When joining, you may need to specify an empty nested table in one of the tables. The “EmptyTable” operator is just right for this.

Example from 1C 8 help:

SELECT Link.Number, EMPTY TABLE.(No., Item, Quantity) AS Composition
FROM Document.Expense Invoice
COMBINE EVERYTHING
SELECT Link.Number, Contents.(LineNumber, Product, Quantity)
FROM Document.Invoice Document.Invoice.Composition.*

ISNULL

A very useful feature that allows you to avoid many mistakes. YesNULL() allows you to replace the NULL value with the desired one. Very often used in checking for the presence of a value in joined tables, for example:

CHOOSE
Nomenclature Ref. Link,
IsNULL(Item Remaining.QuantityRemaining,0) AS QuantityRemaining
FROM

Can be used in other ways. For example, if for each row it is not known in which table the value exists:

ISNULL(InvoiceReceived.Date, InvoiceIssued.Date)

HOW is an operator that allows us to assign a name (synonym) to a table or field. We saw an example of use above.

These constructions are very similar - they allow you to get a string representation of the desired value. The only difference is that REPRESENTATION converts any values ​​to string type, and REPRESENTATIONLINKS are reference only. REFERENCE REPRESENTATION is recommended to be used in data composition system queries for optimization, unless, of course, the reference data field is planned to be used in selections.

CHOOSE
View(Link), //string, for example “Advance report No. 123 dated 10/10/2015
View(DeletionMark) AS DeleteMarkText, //string, “Yes” or “No”
ViewReferences(DeletionMark) AS DeleteMarkBoolean //boolean, True or False
FROM
Document.Advance Report

EXPRESS

Express allows you to convert field values ​​to the right type data. You can convert a value to either a primitive type or a reference type.

Express for a reference type is used to restrict the requested data types in fields of a complex type, often used to optimize system performance. Example:

EXPRESS(TableCost.Subconto1 AS Directory.Cost Items).Type of ActivityForTaxAccountingCosts

For primitive types, this function is often used to limit the number of characters in fields of unlimited length (such fields cannot be compared with). To avoid the error " Invalid parameters in comparison operation. You can't compare fields
unlimited length and fields of incompatible types", you need to express such fields as follows:

EXPRESS(Comment AS Line(150))

DIFFERENCEDATE

Get 267 video lessons on 1C for free:

An example of using IS NULL in a 1C request:

CHOOSE FROM
Ref
LEFT CONNECTION RegisterAccumulations.ProductsInWarehouses.Remaining AS Product Remaining
Software NomenclatureRef.Link = Sold GoodsCommitteesRemains.Nomenclature
WHERE NOT Remaining Goods. QuantityRemaining IS NULL

The data type in a query can be determined as follows: using the TYPE() and VALUETYPE() functions, or using logical operator LINK. The two functions are similar.

Predefined values

In addition to using passed parameters in queries in the 1C query language, you can use predefined values ​​or . For example, transfers, predefined directories, charts of accounts, and so on. For this, the “Value()” construct is used.

Usage example:

WHERE Nomenclature.Type of Nomenclature = Value(Directory.Types of Nomenclature.Product)

WHERE Counterparties.Type of Contact Information = Value(Enumeration.Types of Contact Information.Phone)

WHERE Account Balances.Accounting Account = Value(Chart of Accounts.Profit.ProfitsLoss)

Connections

There are 4 types of connections: LEFT, RIGHT, COMPLETE, INTERNAL.

LEFT and RIGHT CONNECTION

Joins are used to link two tables based on a specific condition. Feature when LEFT JOIN is that we take the first specified table in its entirety and conditionally bind the second table. The fields of the second table that could not be bound by condition are filled with the value NULL.

For example:

It will return the entire table of Counterparties and fill in the “Bank” field only in those places where the condition “Counterparties.Name = Banks.Name” will be met. If the condition is not met, the Bank field will be set to NULL.

RIGHT JOIN in 1C language absolutely similar LEFT connection, with the exception of one difference - in RIGHT OF CONNECTION The “main” table is the second, not the first.

FULL CONNECTION

FULL CONNECTION differs from left and right in that it displays all records from two tables and connects only those that it can connect by condition.

For example:

FROM

FULL CONNECTION
Directory.Banks HOW Banks

BY

The query language will return both tables completely only if the condition to join the records is met. Unlike a left/right join, it is possible for NULL to appear in two fields.

INNER JOIN

INNER JOIN differs from the full one in that it displays only those records that could be connected according to a given condition.

For example:

FROM
Directory. Counterparties AS Clients

INNER JOIN
Directory.Banks HOW Banks

BY
Clients.Name = Banks.Name

This query will return only rows in which the bank and counterparty have the same name.

Associations

The JOIN and JOIN ALL constructs combine two results into one. Those. the result of performing two will be “merged” into one, common one.

That is, the system works exactly the same as regular ones, only for a temporary table.

How to use INDEX BY

However, there is one point to consider. Building an index on a temporary table also takes time to complete. Therefore, it is advisable to use the “ ” construction only if it is known for sure that there will be more than 1-2 records in the temporary table. Otherwise, the effect may be the opposite - the performance of indexed fields does not compensate for the time it takes to build the index.

CHOOSE
Currency rates Latest cross-section. Currency AS Currency,
Currency rates Latest cross-section.
PUT Currency Rates
FROM
Information Register.Currency Rates.Last Slice(&Period,) AS Currency RatesLast Slice
INDEX BY
Currency
;
CHOOSE
PricesNomenclature.Nomenclature,
PricesNomenclatures.Price,
PricesNomenclatures.Currency,
Currency rates.Rate
FROM
Information Register.Nomenclature Prices.Last Slice(&Period,
Nomenclature B (&Nomenclature) AND PriceType = &PriceType) AS PriceNomenclature
LEFT JOIN Currency Rates AS Currency Rates
Software PricesNomenclatures.Currency = Currency Rates.Currency

Grouping

The 1C query language allows you to use special aggregate functions when grouping query results. Grouping can also be used without aggregate functions to “eliminate” duplicates.

The following functions exist:

Amount, Quantity, Number of different, Maximum, Minimum, Average.

Example #1:

CHOOSE
Sales of Goods and Services Goods. Nomenclature,
SUM(Sales of GoodsServicesGoods.Quantity) AS Quantity,
SUM(Sales of GoodsServicesGoods.Amount) AS Amount
FROM

GROUP BY
Sales of Goods and Services Goods. Nomenclature

The query receives all lines with goods and summarizes them by quantity and amounts by item.

Example No. 2

CHOOSE
Banks.Code,
QUANTITY(DIFFERENT Banks.Link) AS Number Of Duplicates
FROM
Directory.Banks HOW Banks
GROUP BY
Banks.Code

This example will display a list of BICs in the “Banks” directory and show how many duplicates exist for each of them.

Results

Results - a way to obtain data from a system with hierarchical structure. Aggregate functions can be used for summary fields, just as for groupings.

One of the most popular ways to use results in practice is batch write-off of goods.

CHOOSE




FROM
Document. Sales of Goods and Services. Goods HOW to Sale of Goods and Services Goods
SORT BY

RESULTS
SUM(Quantity),
SUM(Sum)
BY
Nomenclature

The result of the query will be the following hierarchical:

General results

If you need to get totals for all “totals”, use the “GENERAL” operator.

CHOOSE
Sales of Goods and Services Goods. Nomenclature AS Nomenclature,
Sales of Goods and Services Goods. Link AS Document,
Sales of Goods and Services Goods. Quantity AS Quantity,
Sales of Goods and Services Goods. Amount AS Amount
FROM
Document. Sales of Goods and Services. Goods HOW to Sale of Goods and Services Goods
SORT BY
Sales of Goods and Services Goods. Link. Date
RESULTS
SUM(Quantity),
SUM(Sum)
BY
ARE COMMON,
Nomenclature

As a result of executing the request, we get the following result:

In which 1 level of grouping is the aggregation of all necessary fields.

Arranging

The ORDER BY operator is used to sort the result of a query.

Sorting for primitive types (string, number, boolean) follows the usual rules. For reference type fields, sorting occurs by the internal representation of the link (the unique identifier), rather than by code or by reference representation.

CHOOSE

FROM
Directory.Nomenclature AS Nomenclature
SORT BY
Name

The request will display a list of names in the nomenclature directory, sorted alphabetically.

Auto-order

The result of a query without sorting is a chaotically presented set of rows. 1C platform developers do not guarantee that rows will be output in the same sequence when executing identical queries.

If you need to display table records in a constant order, you must use the Auto-Order construct.

CHOOSE
Nomenclature.Name AS Name
FROM
Directory.Nomenclature AS Nomenclature
AUTO ORDER

Virtual tables

Virtual tables in 1C are a unique feature of the 1C query language that is not found in other similar syntaxes. Virtual table – quick way obtaining profile information from registers.

Each register type has its own set of virtual tables, which may differ depending on the register settings.

• cut of the first;
• cut of the latter.

• leftovers;
• revolutions;
• balances and turnover.

• movements from subconto;
• revolutions;
• speed Dt Kt;
• leftovers;
• balances and turnover
• subconto.

• base;
• graph data;
• actual period of validity.

For the solution developer, the data is taken from one (virtual) table, but in fact the 1C platform takes from many tables, transforming them into the required form.

CHOOSE
Products in Warehouses Remains and Turnover. Nomenclature,
ProductsInWarehousesRemainingAndTurnover.QuantityInitialRemaining,
ProductsInWarehousesRemainsAndTurnover.QuantityTurnover,
GoodsInWarehousesRemainsAndTurnover.QuantityIncoming,
GoodsInWarehousesRemainsAndTurnover.QuantityConsumption,
ProductsInWarehousesRemainingsAndTurnover.QuantityFinalRemaining
FROM
RegisterAccumulations.GoodsInWarehouses.RemainsAndTurnover AS GoodsInWarehousesRemainsAndTurnover

This query allows you to quickly retrieve a large amount of data.

Virtual Table Options

A very important aspect of working with virtual tables is the use of parameters. Virtual table parameters are specialized parameters for selection and configuration.

For such tables, it is considered incorrect to use selection in the “WHERE” construction. In addition to the fact that the query becomes suboptimal, it is possible to receive incorrect data.

An example of using these parameters:

Register of Accumulations. Goods in Warehouses. Balances and Turnovers (& Beginning of the Period, & End of the Period, Month, Movements and Borders of the Period, Nomenclature = & Required Nomenclature)

Algorithm for virtual tables

For example, the most used virtual table of the “Remains” type stores data from two physical tables – balances and movements.

When using a virtual table, the system performs the following manipulations:

1. We get the closest calculated value in terms of date and measurements in the totals table.
2. We “add” the amount from the movement table to the amount from the totals table.

Such simple actions can significantly improve the performance of the system as a whole.

Using the Query Builder

Query Builder– a tool built into the 1C Enterprise system that greatly facilitates the development of database queries.

The query builder has a fairly simple, intuitive interface. Nevertheless, let's look at using the query constructor in more detail.

The query text constructor is launched from the context menu (right mouse button) in the desired place in the program code.

Description of the 1C request constructor

Let's look at each tab of the designer in more detail. The exception is the Builder tab, which is a topic for another discussion.

Tables and Fields tab

This tab specifies the data source and fields that need to be displayed in the report. In essence, the constructions SELECT.. FROM are described here.

The source can be a physical database table, a virtual register table, temporary tables, nested queries, etc.

In the context menu of virtual tables, you can set virtual table parameters:

Connections tab

The tab is used to describe connections of several tables and creates constructions with the word CONNECTION.

Grouping tab

On this tab, the system allows you to group and summarize the required fields of the table result. Describes the use of the constructions GROUP BY, SUM, MINIMUM, AVERAGE, MAXIMUM, QUANTITY, NUMBER OF DIFFERENT.

Conditions tab

Responsible for everything that comes in the request text after the WHERE construction, i.e. for all the conditions imposed on the received data.

Advanced tab

Tab Additionally replete with all sorts of parameters that are very important. Let's look at each of the properties.

Grouping Selecting records:

• First N– a parameter that returns only N records to the query (the FIRST operator)
• No duplicates– ensures the uniqueness of the received records (DIFFERENT operator)
• Allowed– allows you to select only those records that the system allows you to select taking into account (ALLOWED construction)

Grouping Request type determines what type of request will be: data retrieval, creation of a temporary table, or destruction of a temporary table.

Below there is a flag Lock received data for later modification. It allows you to enable the ability to set data locking, which ensures the safety of data from the moment it is read until it is changed (relevant only for Automatic mode interlocks, design TO CHANGE).

Joins/Aliases Tab

On this tab of the query designer, you can set the ability to join different tables and aliases (the HOW construct). The tables are indicated on the left side. If you set the flags opposite the table, the UNITE construction will be used, otherwise - UNITE ALL (differences between the two methods). On the right side, the correspondence of fields in different tables is indicated; if the correspondence is not specified, the query will return NULL.

Order tab

This specifies the order in which the values ​​are sorted (ORDER BY) - descending (DESC) or ascending (ASC).

There is also an interesting flag - Auto-order(in the request - AUTO ORDERING). By default, the 1C system displays data in a “chaotic” order. If you set this flag, the system will sort data by internal data.

Query Batch tab

On the query designer tab, you can create new ones, and also use it as a navigation. In the request text, packets are separated by the symbol “;” (comma).

“Query” button in the query designer

In the lower left corner of the request designer there is a Request button, with which you can view the request text at any time:

In this window, you can make adjustments to the request and execute it.

Using the Query Console

The Query Console is a simple and convenient way to debug complex queries and quickly obtain information. In this article, I will try to describe how to use the Query Console and provide a link to download the Query Console.

Let's take a closer look at this tool.

Download 1C query console

First of all, to start working with the query console, you need to download it from somewhere. Treatments are usually divided into two types - controlled forms and regular (or sometimes called 8.1 and 8.2/8.3).

I tried to combine these two types in one treatment - in desired mode operation, the desired form opens (in managed mode, the console only works in thick mode).

Description of the 1C query console

Let's start looking at the query console with a description of the main processing panel:

In the query console header, you can see the execution time of the last query with millisecond accuracy, this allows you to compare different designs in terms of performance.

The first group of buttons in the command bar is responsible for saving current queries to an external file. This is very convenient; you can always return to writing a complex request. Or, for example, store a list of typical examples of certain designs.

On the left, in the “Request” field, you can create new requests and save them in a tree structure. The second group of buttons is responsible for managing the list of requests. Using it you can create, copy, delete, move a request.

• Executerequest– simple execution and results
• Execute package– allows you to view all intermediate queries in a batch of queries
• Viewing temporary tables– allows you to see the results that temporary queries return on a table

Request parameters:

Allows you to set the current parameters for the request.

In the query parameters window, the following is interesting:

• Button Get from request automatically finds all parameters in the request for the convenience of the developer.
• Flag Common parameters for all requests– when installed, its processing does not clear the parameters when moving from request to request in the general list of requests.

Set a parameter with a list of values It’s very simple, just when choosing a parameter value, click on the clear value button (cross), the system will prompt you to select the data type, where you need to select “Value List”:

Also in the top panel there is a button for calling up the query console settings:

Here you can specify parameters for autosaving queries and query execution parameters.

The request text is entered into the console request field. This can be done by simply typing a query test or by calling a special tool - the query designer.

The 1C 8 query designer is called from the context menu (right mouse button) when you click on the input field:

Also in this menu there are such useful features, like clearing or adding line breaks (“|”) to the request, or getting the request code in this convenient form:

Request = New Request;
Request.Text = ”
|SELECT
| Currencies.Link
|FROM
| Directory.Currencies AS Currencies”;
RequestResult = Request.Execute();

The lower field of the query console displays the query result field, which is why this processing was created:

Also, the query console, in addition to the list, can display data in the form of a tree - for queries containing totals.

Query optimization

One of the most important points in increasing the productivity of 1C enterprise 8.3 is optimizationrequests. This point is also very important when passing the certification. Below we will talk about typical reasons not optimal operation of queries and optimization methods.

Selections in a virtual table using the WHERE construct

It is necessary to apply filters to the virtual table details only through the VT parameters. Under no circumstances should you use the WHERE construct for selection in a virtual table; this is a serious mistake from an optimization point of view. In the case of selection using WHERE, in fact, the system will receive ALL records and only then select the necessary ones.

RIGHT:

CHOOSE

FROM
Register of Accumulations. Mutual settlements with Participants of Organizations. Balances (
,
Organization = &Organization
AND Individual = &Individual) HOW Mutual settlements with Participants of Organizations Balances

WRONG:

CHOOSE
Mutual settlements with Participants of Organizations Balances. Amount Balance
FROM
Register of Accumulations. Mutual settlements with Participants of Organizations. Balances (,) HOW Mutual settlements with Participants of Organizations Balances
WHERE
Mutual settlements with Participants of Organizations Balances. Organization = & Organization
AND Mutual settlements with Participants of Organizations Balances. Individual = &Individual

Getting the value of a field of a complex type using a dot

When receiving data of a complex type in a query through a dot, the system connects with a left join exactly as many tables as there are types possible in the field of the complex type.

For example, it is highly undesirable for optimization to access the register record field – registrar. The registrar has a composite data type, among which are all possible document types that can write data to the register.

WRONG:

CHOOSE
Record Set.Recorder.Date,
RecordSet.Quantity
FROM
RegisterAccumulations.ProductsOrganizations AS SetRecords

That is, in fact, such a query will access not one table, but 22 database tables (this register has 21 registrar types).

RIGHT:

CHOOSE
CHOICE
WHEN ProductsOrg.Registrar LINK Document.Sales of Products and Services
THEN EXPRESS(ProductsOrganization.Registrar AS Document.Sales of GoodsServices).Date
WHEN GoodsOrg.Registrar LINK Document.Receipt of GoodsServices
THEN EXPRESS(GoodsOrg.Registrar AS Document.Receipt of GoodsServices).Date
END AS DATE,
ProductsOrg.Quantity
FROM
RegisterAccumulations.ProductsOrganizations AS ProductsOrganization

Or the second option is to add such information to the details, for example, in our case, adding a date.

RIGHT:

CHOOSE
ProductsOrganizations.Date,
ProductsOrganizations.Quantity
FROM
Register of Accumulations. Goods of Organizations AS Goods of Organizations

Subqueries in a join condition

For optimization, it is unacceptable to use subqueries in join conditions; this significantly slows down the query. It is advisable to use VT in such cases. To connect, you need to use only metadata and VT objects, having previously indexed them by connection fields.

WRONG:

CHOOSE …

LEFT JOIN (
SELECT FROM RegisterInformation.Limits
WHERE …
GROUP BY...
) BY …

RIGHT:

CHOOSE …
PUT Limits
FROM Information Register.Limits
WHERE …
GROUP BY...
INDEX BY...;

CHOOSE …
FROM Document. Sales of Goods and Services
LEFT JOIN Limits
BY …;

Joining Records with Virtual Tables

There are situations when, when connecting a virtual table to others, the system does not work optimally. In this case, to optimize the performance of the query, you can try placing the virtual table in a temporary table, not forgetting to index the joined fields in the temporary table query. This is due to the fact that VTs are often contained in several physical DBMS tables; as a result, a subquery is compiled to select them, and the problem turns out to be similar to the previous point.

Using selections based on non-indexed fields

One of the most common mistakes when writing queries is using conditions on non-indexed fields, this contradicts query optimization rules. The DBMS cannot execute a query optimally if the query includes selection on non-indexable fields. If you take a temporary table, you also need to index the connection fields.

There must be a suitable index for each condition. A suitable index is one that satisfies the following requirements:

1. The index contains all the fields listed in the condition.
2. These fields are at the very beginning of the index.
3. These selections are consecutive, that is, values ​​that are not involved in the query condition are not “wedged” between them.

If the DBMS does not select the correct indexes, the entire table will be scanned - this will have a very negative impact on performance and can lead to prolonged blocking of the entire set of records.

Using logical OR in conditions

That's all, this article covered the basic aspects of query optimization that every 1C expert should know.

A very useful free video course on query development and optimization, I strongly recommend for beginners and more!

If my publication is useful to you, do not forget to give it a plus :-)

Here is a rubricator for all tasks in the collection(a page containing links to forum threads for each task)
http://chistov.spb.ru/forum/16-969-1

Well, now my developments and notes that I created during the preparation process.
I will try to repeat as little as possible with the two mentioned above last publications.

So let's get started:

If you take it remotely, you should have two objects on your desktop at the end of the exam:

1. Final unloading information base(dt file)
2. Explanatory note

There should be nothing else, no intermediate copies, etc.

Be sure to write an explanatory note!
In the case of a vaguely formulated task, be sure to write there that you have chosen exactly such and such a solution option.
Also, in key places in the code, it is better to leave brief comments, without fanaticism, but where the examiner may have questions, it is better to write.

But you will be told about this in the instructions that you will be given to read before the exam.
It's just better to know in advance)

Using the ampersand character in queries.

Sometimes it’s faster to type from an additional keyboard than to switch the layout back and forth, saving time
& = Alt+38

*************************************************************************************************
Using TimePoint() in Queries

In queries to accumulation and accounting registers, it is necessary to use not the document date as a virtual table (period) parameter, but the Moment parameter, which is defined in the code as follows:

Moment = ?(Passing Mode = Document Posting Mode. Operational, Undefined, Moment of Time());

*************************************************************************************************
When generating document movements by register, at the very beginning of the posting processing procedure, it is necessary to clear the movements of the current document by register.

The code is:

Movement.RegisterName.Write = True; Movements.RegisterName.Clear();

It is possible that during the process it will be necessary to analyze records from this register.
So, so that when analyzing the current records (old ones, before the document was changed) they are definitely not included in the sample, you can add one more line to the above two lines:

Movement.RegisterName.Write();

Or, when analyzing records, explicitly indicate a boundary that does not include the point in time of the current document.

But everywhere I simply indicated the construction of these three lines:

Movement.RegisterName.Write = True; Movements.RegisterName.Clear(); Movement.RegisterName.Write();

*************************************************************************************************
There are two ways to block data, the choice between them depends on the method used - old or new:

1) Regular controlled blocking, old method of document processing (Data Blocking object)

Set if balances are first checked and then written off.
In the case when we need to have some information from the register to form a movement.

Example:

In the document - quantity, in the register - quantity and amount (cost)
So, we know the quantity of goods from the document - how much we write off, but the cost - not.
We can only find it out from the register, but in order to ensure that no one changes the register between the moment of receiving the balances and the moment of recording the movements, we need to lock the register even before reading the balances.
So, in this case, the Data Locking object is used. And when creating it, it is more correct to indicate by what dimensions we are blocking the register (for example, in our case - only by the item specified in the document) - so that there are no unnecessary locks and another user can sell another item.


1. Set a lock using the Data Lock object
2. Read the rest
3. We check the possibility of write-off
4. We create movements, for example, write off goods
5. After posting the document, the blocking is automatically removed (the blocking is valid as part of the posting transaction and is removed automatically by the system). That is, there is no need to specially unlock the object.

2) New methodology for processing documents (using the LockForChange property = True)

It is used if we do not need information from the registers to form movements, and we can check whether we have gone into the negative when writing off if, after recording, we look at the balances in the register and see that there are negative ones. In this case, we will understand that we have written off too much and will cancel the write-off operation.

Example:
Let's consider the operation of selling a product.
In the document - quantity, in the register - only quantity
So, we know the quantity of goods from the document.
We form movements with the quantity specified in the document and record them. Next, we read the register, look at the balances, and analyze whether there are any negative ones. If there is, display an error and set Refusal = True.

That is, the sequence is like this:
1. To move through the register, set the BlockForChange property = True
2. We create movements - write off the goods
3. Record the movements
4. Read the register and make sure there are no negative balances. If there is, then they wrote off the excess, if not, then everything is fine.

So, in this case, there is no need to indicate by which dimensions we need to block the register.
We simply set the BlockForChange property to True before recording our movements, form the movements and record.
The system itself will block the register at the time of recording according to the measurements that are needed, having analyzed what we have recorded.
Once completed, the blocking will be removed.

This option (the second) is simpler, it’s called the “new methodology for processing documents” and 1C recommends using it if possible and deducts points if the first option is used, but in some cases it simply cannot be applied and the first option with the Data Locking object is used (see. above example).

I also note that regardless of the chosen method, the movements must be cleaned before working with them (see previous advice)

*************************************************************************************************
Data blocking (blocking method No. 1 from the above description)

Controlled locking is required where data is read and movements are made based on this data
The fastest way to get the managed locking code is to enter “Data Locking”, call the Syntax Assistant and simply copy the example code from there. Then simply change it to the name of your register and dimensions.

It looks something like this:

Lock = NewDataLock; Locking Element = Locking.Add("Accumulation Register.GoodsInWarehouses"); LockElement.Mode = DataLockMode.Exclusive; BlockingElement.DataSource = PM; Locking Element.UseFromDataSource("Nomenclature", "Nomenclature"); Lock.Lock();

*************************************************************************************************
It is better to call the tabular part of the documents simply “TC”

There is only one tabular part in 99% of documents. Such a unified name tabular parts It will help you save a lot of time because:
1) Very short - write quickly
2) The same for all documents, you don’t have to remember what it’s called when writing code

*************************************************************************************************
The query result should be checked for emptiness before fetching or uploading to the technical specification.

In general, I used sampling in all tasks.

Sampling is more optimal for the system in terms of performance, since it is “sharpened” only for reading data (unlike TK).

But in any case, before the Select() method, it is better to check the query result for emptiness, this will further reduce the load on the system.

Result = Query.Run(); If Not Result.Empty() Then Select = Result.Select(TravelQueryResult.ByGrouping); ... EndIf;

And in case we need to get only one value from the request
(for example, only the write-off method in accordance with the accounting policy established for this year):

Result = Query.Run(); If Not Result.Empty() Then Select = Result.Select(); Selection.Next(); Cost Write-off Method = Sample.Cost Write-Off Method; endIf;

*************************************************************************************************
Document "Operation" for an accounting task

It is necessary to create an Operation document for accounting tasks.

We disable posting for it altogether (in the properties “Posting = Deny”), indicate that it makes movements in the accounting register, and drag the movements onto the form.

*************************************************************************************************
Prompt processing of documents:

Must be included:
In operational and accounting. accounting for documents must be enabled (except for the “Operation” document, see below).

Must be turned off:
in calculation tasks it does not make sense for a payroll document.

For the document "Operation", posting should be disabled altogether (in the document properties "Posting = Prohibit"),
since he writes simply writes data directly to the register when writing.

*************************************************************************************************
Condition in a request of the form "Either the specified nomenclature or any, if not specified"

In queries, the following task is encountered: for example, you need to select documents with a specified nomenclature or all documents if the nomenclature is not specified.
It is solved by the following condition in the request itself:

Nomenclature = &Nomenclature OR &Nomenclature = Value(Directory.Nomenclature.EmptyLink)

But it would be more optimal and correct to transform this condition (thanks yukon):

Request.Text = Request.Text + "WHERE Nomenclature = &Nomenclature";

endIf;

With the advent of the query object model in 8.3.5, it will be possible to add a condition more securely:

If ValueFilled(Nomenclature) Then
Query1.Selection.Add("Item = &Nomenclature");
Request.SetParameter("Nomenclature", Nomenclature);
endIf;

*************************************************************************************************
Joining tables in queries:

The number of total records does not depend on whether the field of the joined table is displayed, it depends only on the configured relationships.
That is, the field of the attached table may not be displayed.

If you want to attach a table without any conditions, then on the conditions tab simply write the condition “TRUE”.
In this case, the table will be joined exactly.

*************************************************************************************************
Using the plan of characteristics types (PVC):

1. Use as a mechanism for describing the characteristics of objects.

1.1. We create PVC. These will be Types of Characteristics (for example, color, size, max. speed, etc.). In the settings, select all possible types of characteristic values ​​and, if necessary, create the object from point 1.2 and also indicate it in the settings.

1.2. For additional values ​​of PVC, we create a subordinate directory AdditionalValues ​​of Characteristics (or simply Values ​​of Characteristics).
It will store characteristics if they are not in existing directories. We may not create it if all the characteristics we need are in existing directories, or these values ​​can be represented by elementary data types. In the PVC settings we indicate that this directory will be used for additional purposes. characteristics values.

1.3. We create an information register, which actually connects three objects:
- The object to which we connect the characteristics mechanism
- TypeCharacteristics (PVC type)
- Characteristics value (type - characteristic, this is a new type that appeared in the system after the creation of PVC
and describing all possible data types that a characteristic value can take).
In the information register, we indicate that the Characteristic Type is the owner for the Characteristic Value (link to the selection parameter), as well as the type connection for the Characteristic Value, again from the Characteristic Type.

Another feature is that for each created type of characteristic, you can specify the type of characteristic value, if you do not need all possible types to describe the value of this characteristic.

2. Using PVC to create a sub-conto mechanism for the accounting register .

2.1. We create PVC TypesSubconto.

2.2. We create a subordinate directory ValuesSubConto (as with characteristics, it will contain subconto values ​​if there are no such in other directories).

2.3. Communication is made using a chart of accounts.

*************************************************************************************************
Accounting register resources:

Amount - balance sheet,
Quantity - off-balance sheet and associated with the accounting characteristic Quantitative

*************************************************************************************************
Virtual accounting register tables:

Turnover: turnover of a single account
TurnoverDtKt: turnover between any two accounts, that is, all the same transactions for the period.

*************************************************************************************************
Currency accounting on accounting registers - how to implement:

We create an accounting attribute “currency” in the chart of accounts.
In the accounting register, we additionally create:
- Currency dimension (prohibition of blank values, off-balance sheet, accounting attribute - currency)
- resource CurrencyAmount (off-balance sheet, accounting attribute - currency, it will store the amount in currency, that is, $100 for example)
All.

Thus the register structure is:

Measurements:
- Currency
Resources
- Quantity
- Amount (amount in rubles)
- CurrencyAmount (amount in currency)

Thus, currency accounting is only a refinement of conventional accounting in the Republic of Belarus; it does not change the essence of, for example, the resource Amount
(there, as usual, the amount is in rubles, regardless of whether the account is in foreign currency or not).
And if the Currency accounting feature is turned off for the account, then this is the usual structure of the Republic of Belarus (resources - only quantity and amount).

*************************************************************************************************
When setting the parameters of a virtual table to obtain a slice of the latter, we impose conditions on dimensions, and not on resources.

Otherwise we will get not a slice of the latter, but last entry with the specified resource value - it may not be the last in the set of dimensions

*************************************************************************************************
The meaning of the resource and details in the calculation register

In calculation registers, creating a resource makes it possible to receive it when calculating the base using this register.
And even in proportion to the given period, the resource value will be recalculated (if the base period does not coincide with the periodicity of the register).

And the value of the attribute is available only in the real table of the calculation register; it is not available in virtual tables.

*************************************************************************************************
Checkbox "Basic" in the properties of the calculation register dimension
Means that this measurement will be used to obtain a base in the future and is used for additional indexing values ​​for this field.

*************************************************************************************************
Breakdown of the vacation validity period by month when recording sets of register entries,
if vacation is specified in the document in one line for several months at once in one line:

StartDate of CurrentMonth = Start of Month(TexLineMainAccruals.ActionPeriodStart); CurrentMonthEndDate = EndMonth(TexLineMainAccruals.ActionPeriodStart); CurrentMonth = Date; WhileDateStartCurrentMonth<= НачалоМесяца(ТекСтрокаОсновныеНачисления.ПериодДействияКонец) Цикл Движение = Движения.ОсновныеНачисления.Добавить(); Движение.Сторно = Ложь; Движение.ВидРасчета = ТекСтрокаОсновныеНачисления.ВидРасчета; Движение.ПериодДействияНачало = Макс(ДатаНачалаТекМесяца, ТекСтрокаОсновныеНачисления.ПериодДействияНачало); Движение.ПериодДействияКонец = КонецДня(Мин(ДатаОкончанияТекМесяца, ТекСтрокаОсновныеНачисления.ПериодДействияКонец)); Движение.ПериодРегистрации = Дата; Движение.Сотрудник = ТекСтрокаОсновныеНачисления.Сотрудник; Движение.Подразделение = ТекСтрокаОсновныеНачисления.Подразделение; Движение.Сумма = 0; Движение.КоличествоДней = 0; Движение.График = ТекСтрокаОсновныеНачисления.График; Движение.Параметр = ТекСтрокаОсновныеНачисления.Параметр; Движение.БазовыйПериодНачало = НачалоМесяца(ДобавитьМесяц(Дата, -3)); Движение.БазовыйПериодКонец = КонецДня(КонецМесяца(ДобавитьМесяц(Дата, -1))); ДатаНачалаТекМесяца = НачалоМесяца(ДобавитьМесяц(ДатаНачалаТекМесяца, 1)); ДатаОкончанияТекМесяца = КонецМесяца(ДатаНачалаТекМесяца); КонецЦикла; КонецЕсли;

*************************************************************************************************
Building a Gantt Chart:

We place an element of the “Gantt Chart” type on the form, call it DG, then create the “Generate” command and write the following in the form module:

&OnClient Procedure Generate(Command) GenerateOnServer(); End of Procedure &On the Server Procedure GenerateOn Server() DG.Clear(); DG.Update = False; Request = New Request("SELECT |BasicAccrualsActualActionPeriod.Employee, |BasicAccrualsActualActionPeriod.CalculationType, |BasicAccrualsActualActionPeriod.ActionPeriodStart AS ActionPeriodStart, |BasicAccrualsActualActionPeriod.Period ActionsEnd AS PeriodActionsEnd |FROM |Calculation Register.BasicAccruals.ActualPeriodActions AS BasicAccrualsActualPeriodActions |WHERE |BasicAccrualsActualPeriodActions.PeriodActions BETWEEN &StartDate AND &EndDate "); Query.SetParameter("StartDate", Period.StartDate); Request.SetParameter("EndDate", Period.EndDate); Select = Query.Run().Select(); While Selection.Next() Loop Point = DG.SetPoint(Selection.Employee); Series = DG.SetSeries(Selection.CalculationType); Value = DG.GetValue(Point, Series); Interval = Value.Add(); Interval.Start = Sample.PeriodActionStart; Interval.End = Sample.ActionPeriodEnd; EndCycle; DG.Update = True; End of Procedure

Actually, only the code in the loop is important to us here, the rest of the things are auxiliary, I just gave the entire implementation of this subtask.
In the request, it is important for us that there is an employee, type of payment, start date and end date of the period.
The code is actually very simple, easy to remember, don't be alarmed if it seems cumbersome

*************************************************************************************************
Processing “reversal” entries in calculation tasks:

In the transaction processing procedure (object module), we form all movements, and then if there are records in other periods, we get them like this
(the system generates them automatically - helps us):

Addition Records = Movements.MainAccruals.GetAddition(); // No need to record movements to get the addition

For Each Tech Line From Record Additions Cycle
Record = Movements.MainAccruals.Add();
FillPropertyValues(Record, TechString);
Record.RegistrationPeriod = TechString.RegistrationPeriodReversal;
Record.ActionPeriodStart = TechString.ActionPeriodStartReverse;
Record.ActionPeriodEnd = TechString.ActionPeriodEndReversal;
End of the Cycle

And when calculating records, insert checks:

If TechMotion.Reversal Then
CurrentMovement.Sum = - CurrentMovement.Amount;
endIf;

*************************************************************************************************
How to determine what is included in the main accruals and what is included in additional accruals in calculation tasks.

But this is not always 100% clear; there are also more complicated cases, although there are quite a few of them
(for example, a bonus that depends on the number of working days in a month - this is HE).

Basic charges:
If the type of calculation is dependent on the schedule (meaning a register of information with calendar dates), then it refers to the main charges.

Example OH:
- Salary
- Something that is calculated from the number of working days (and for this you need to use a schedule): either in the validity period (like salary) or in the base period

Additional charges:
What is considered either from the accrued amount, or the time WORKED (and not the norm!), or does not depend at all - this is additional. accruals.

That is: accruals for the calculation of which the time standard is used (maybe also a fact) is OH, and for which actual data or nothing at all is needed is DN.

Or in other words:

If the VR uses a time standard, then the validity period must be included for the VR.

*************************************************************************************************
Add the ability to open the built-in help section "Working with reference books" in the list form of the "Nomenclature" directory.

Run the command on the form:

&OnClient
Procedure Help(Command)
OpenHelp("v8help://1cv8/EnterprWorkingWithCatalogs");
End of Procedure

We define the section line as follows:
Go to the help information of the configuration object (in the configurator), write a word, select it, go to the Elements/Link menu and select the desired section of 1C Help, after which the link is inserted automatically. It looks complicated, but in practice it’s easy.

*************************************************************************************************
Implementation of interaction between forms, for example, selection:

1. From the current form, open the desired one using the “OpenForm()” method, passing the structure with parameters as the second parameter (if necessary). The third parameter can pass a link to this form - ThisForm.

2. In the opened form, in the “When CreatedOnServer()” handler, we can catch the parameters passed in step 1 through “Parameters.[ParameterName]”. The form that initiated the opening of this form will be accessible through the “Owner” identifier (if it was, of course, specified in step 1).

And most importantly, export functions of the owner form will be available. That is, we can call the export function of the source form and pass something there as a parameter to process the selection. And this function will already fill in what is needed in the original form. There is only one caveat - you cannot pass a table of values ​​between client procedures, but we can place it in temporary storage and simply pass the VX address, and then extract it from the VX.

*************************************************************************************************
Lifecycle of Form Parameters

All parameters transferred to the form at the time of its opening are visible only in the “When CreateOnServer” procedure.
Once created, all parameters are destroyed and are no longer available on the form.
The exception is for parameters that are declared in the form editor with the “Key Parameter” attribute.
They determine the uniqueness of the form.
This parameter will exist as long as the form itself exists.

*************************************************************************************************
Using the Taxi interface

During development, you can set the usual managed interface 8.2 in the configuration properties - this makes everything noticeably more compact and familiar.
This is especially true if you rent remotely - the screen resolution is very small, and it’s impossible to do anything with the “taxi” interface.
Just don’t forget to put “Taxi” again when you’re done!Otherwise, the examiner will deduct points!

*************************************************************************************************

PS: E There are separate standard subtasks that are used in all tasks, and it is these that you need to be able to solve (for example, writing off by batches, using PVC (well, this is really rare) and others). And in all tasks they are simply repeated (somewhere there are some subtasks, somewhere else, just in different combinations). Moreover, they have long promised to release a new collection (if they haven’t already), in which there should be much more problems, that is, there is no point in memorizing solutions to individual problems, it makes sense to learn how to solve individual standard subtasks, then you will solve any problem.

PSS: Colleagues, if anyone has any other useful information on preparing for the exam and passing it, please write in the comments and we will add to the article.

Accumulation registers in the 1C:Enterprise system are divided into two types: accumulation registers leftovers and accumulation registers rpm.

The register type is selected when creating it in the configurator

As the name suggests, some are intended to obtain balances as of a certain date, and the second are intended to obtain turnover for a selected period. Depending on the type of accumulation register, the 1C:Enterprise platform generates a different set of virtual tables. In this article we will look at working with virtual tables of accumulation registers. To do this, we will create a register for accumulating balances - ProductsRemains and the revolution accumulation register - ProductsTurnover.

Now let's look at what virtual tables the platform provides for each of these registers.

Revolution accumulation register

For clarity, let’s open and see which tables are available for the register ProductsTurnover. This is the table of the register itself - ProductsTurnover, which exists physically in the database, and one virtual table - ProductsTurnover.Turnover

With the standard table everything is clear. Let's take a closer look at the virtual one.

Virtual table Turnover

This table allows you to obtain resource turnover in terms of dimensions. In our case we have two dimensions: Stock And Product. And one resource - Quantity

Let our register have the following entries

Let's go back to the query designer and start by simply selecting from the table ProductsTurnover.Turnover all fields

Accordingly, the request will look like this:

SELECT ProductsTurnoverTurnover.Warehouse, ProductsTurnoverTurnover.Product, ProductsTurnoverTurnover.QuantityTurnover FROM RegisterAccumulation.ProductsTurnover.Turnover(,) AS ProductsTurnoverTurnover

The result of the query looks like this:

That is, we received turnover in terms of goods and warehouses for the entire time. Let's assume that we are not interested in warehouses and we want to get turnover only in terms of goods.

To do this, we exclude the dimension from the request Stock

SELECT ProductsTurnoverTurnover.Product, ProductTurnoverTurnover.QuantityTurnover FROM RegisterAccumulation.ProductsTurnover.Turnover(,) AS ProductsTurnoverTurnover

and as a result we will only have two lines left

But as a rule, there is no need to obtain turnover for the entire existence of the register. Basically, they are needed for a specific period: month, quarter, year, etc. Plus, selections by dimensions (Product, Warehouse) are usually needed. This is achieved using virtual table parameters. It is convenient to fill in parameters from the constructor. By button Virtual Table Options A dialog box opens in which you can enter everything we need:

After this, our original request will take the following form

SELECT GoodsTurnoverTurnover.Warehouse, GoodsTurnoverTurnover.Product, GoodsTurnoverTurnover.QuantityTurnover FROM RegisterAccumulation.GoodsTurnover.Turnover(&Beginning of Period, &End of Period, Warehouse = &Warehouse) AS GoodsTurnoverTurnover

As we can see, the difference is that in parentheses after the name of the virtual table there are parameters that must be filled in before executing the query.

Those who are just starting to work with virtual tables are often tempted to set selection in the usual way instead of using parameters:

FROM RegisterAccumulations.ProductsTurnover.Turnover(,) HOW ProductsTurnoverTurnover WHERE ProductsTurnoverTurnover.Warehouse = &Warehouse

When filling out the parameters we missed Periodicity. Let's open the list and choose from the mass of possible options Month. We will remove all other parameters so as not to get confused.

After this, we observe that a field appears in the table fields Period.

By adding it to the selected fields, we get the following request text:

SELECT ProductsTurnoverTurnover.Period, ProductsTurnoverTurnover.Warehouse, ProductsTurnoverTurnover.Product, ProductsTurnoverTurnover.QuantityTurnover FROM RegisterAccumulation.ProductsTurnover.Turnover(, Month,) AS ProductsTurnoverTurnover

We execute the request:

Thus, within the selected time interval, we can divide the revolutions into smaller intervals in accordance with the selected frequency.

Balance accumulation register

Just like with the reverse register, let’s look in the query designer which virtual tables are available for the balance accumulation register

As you can see, three virtual tables are available for the balance accumulation register: Revolutions, Leftovers, Remains and Turnovers. Let's consider each of them separately.

Virtual table Turnover

Despite the fact that the register type is Leftovers, we can nevertheless receive turnover from it. Plus we have two additional resources here: Coming And Consumption

Let me remind you that when an entry is made in the balance register, the type of accumulation movement (income or expense) is indicated, whereas for the turnover register the type of movement is not indicated. Therefore, here we have an additional bonus in the form of the opportunity to receive not only the overall turnover for the period, but also income and expenses separately. But of course, if the metadata contains a reverse register with a similar set of measurements, then it is better to use it to obtain turnover. In general, working with this virtual table is similar to working with a virtual table Revolutions the negotiable register discussed above.

Virtual table Balances

This table is used to obtain resource balances by dimension. In the table parameters we can specify the date for which we receive balances and set selections:

Let's look at a small example. We have the following register entries:

Let's select all available fields and set the end of June as the date for receiving balances. We will not select based on measurements. Then the request text will look like this:

SELECT ProductsRemainingsRemainings.Warehouse, ProductsRemainingsRemainings.Product, ProductsRemainingsRemainings.QuantityRemaining FROM RegisterAccumulations.ProductsRemainings.Remainings(&DateRemainings,) AS ProductsRemainingRemainings

And after executing it we get this result

Virtual table Balances and Turnovers

This table combines the two previously discussed ones and allows you to obtain turnover for the selected period of time, as well as balances at the beginning and end of the period. You can also set selection.

The use of this table can be justified when you need to simultaneously obtain both turnover and balances at the beginning and end of the period in one report. In other cases, you should not abuse its use.