home Instagram

1c virtual table balances and turnover. Query Batch tab

I decided to make my contribution and describe those features of the language that were not discussed in the above articles. The article is aimed at beginner developers.

1. “IZ” design.

In order to obtain data from the database, it is not at all necessary to use the “FROM” construction.
Example: We need to select all information about banks from the banks directory.
Request:

SELECT Directory.Banks.*

Selects all fields from the Banks directory. And is similar to the request:

SELECT Banks.* FROM Directory.Banks AS Banks

2. Ordering data by reference field

When we need to organize query data by primitive types: "String", "Number", "Date", etc., then everything is solved by using the "ORDER BY" construct if you need to order the data by a reference field? The reference field is a link, a unique identifier, i.e. Roughly speaking, some arbitrary set of characters and ordinary ordering may produce a result that is not entirely expected. To order reference fields, the "AUTO ORDER" construction is used. To do this, you must first order the data directly by the reference type using the "ORDER BY" construct, and then the "AUTO ORDER" construct.

In this case, for documents the ordering will occur in the order "Date->Number", for reference books in the "Main View". If the ordering does not occur by reference fields, then using the "AUTO ORDER" construction is not recommended.

In some cases, the "AUTO ORDER" construct can slow down the selection process. Similarly, you can rewrite without auto-ordering for documents:

3.Obtaining a text representation of a reference type. "PRESENTATION" design.

When you need to display a field of a reference type, for example, the "Bank" field, which is a link to an element of the "Banks" directory, you need to understand that when displaying this field, a subquery to the "Banks" directory will be automatically executed to obtain a view of the directory. This will slow down the data output. In order to avoid this, you need to use the “PREPRESENTATION” construction in the request in order to immediately obtain a representation of the object and then display it for viewing.

In the data composition system, this mechanism is used by default, but when creating layouts in cells, you should specify the representation of the reference field, and, for example, place the link itself in the transcript.

4. Condition for sampling data according to a template.

For example, you need to get mobile phones of employees of the form (8 -123- 456-78-912). To do this, you need to set the following condition in the request:

SELECT Employee.Name, Employee.Phone AS Phone FROM Directory.Employees AS Employees WHERE Phone LIKE "_-___-___-__-__"

The "_" character is a service character and replaces any character.

5. Simultaneous use of totals and groupings.

Totals are often used in conjunction with groupings; in this case, aggregate functions may not be specified in the totals.

SELECT Provision of Services.Organization AS Organization, Provision of Services.Nomenclature AS Nomenclature, SUM(Provision of Services.Amount of Document) AS Sum of Document FROM Document.Provision of Services AS Provision of Services GROUP BY Provision of Services.Organization, Provision of Services.Nomenclature RESULTS BY GENERAL, Organization, Nomen klatura

In this case, the query will return almost the same as the following query:

SELECT Provision of Services.Organization AS Organization, Provision of Services.Nomenclature AS Nomenclature, Provision of Services.Amount of Document AS Amount of Document FROM Document.Provision of Services AS Provision of Services RESULTS AMOUNT (Amount of Document) BY GENERAL, Organization, Nomenclature

Only the first query will collapse records with the same nomenclature.

6. Dereferencing fields.

Referring to fields through a dot is called the reference field dereferencing operation. For example Payment.Organization.Administrative Unit. In this case, in the reference field "Organization" of the "Payment" document, it refers to another table "Organizations", in which the value of the "Administrative Unit" attribute will be obtained. It is important to understand that when accessing fields through a dot, the platform implicitly creates a subquery and joins these tables.

Request:

Can be represented as:

SELECT Payment.Link, Payment.Organization, Payment.Organization, Organizations. AdministrativeUnit FROM Document.Payment AS Payment LEFT JOIN Directory.Organizations AS Software Organizations Payment.Organization = Organizations.Link

When dereferencing reference fields of a composite type, the framework attempts to create implicit joins to all tables that are part of that field's type. In this case, the query will not be optimal. If it is clearly known what type of field it is, it is necessary to limit such fields by type with a construct EXPRESS().

For example, there is an accumulation register “Undistributed payments”, where several documents can act as a registrar. In this case, it is incorrect to obtain the values of the registrar details in this way:

SELECT UnallocatedPayments.Register.Date, ..... FROM RegisterAccumulation.UnallocatedPayments AS UnallocatedPayments

you should restrict the type of the composite field to logger:

SELECT EXPRESS(UnallocatedPayments.Register AS Document.Payment).Date, ..... FROM RegisterAccumulation.UnallocatedPayments AS UnallocatedPayments

7. Construction "WHERE"

With a left join of two tables, when you impose a “WHERE” condition on the right table, we will get a result similar to the result with an inner join of tables.

Example. It is necessary to select all Clients from the Clients Directory and for those clients who have a payment document with the value of the attribute "Organization" = &Organization, display the document "Payment", for those who do not, do not display it.

The result of the query will return records only for those clients who had payment by organization in the parameter, and will filter out other clients. Therefore, you must first receive all payments for “such and such” organization in a temporary table, and then connect it to the “Clients” directory using a left join.

SELECT Payment.Link AS Payment, Payment.Shareholder AS Client PLACE toPayments FROM Document.Payment AS Payment WHERE Payment.Branch = &Branch; //////////////////////////////////////////////// ///////////////////////////// SELECT Clients.Link AS Client, ISNULL(tPayment.Payment, "") AS Payment FROM Directory .Clients AS Clients LEFT CONNECTION topayments AS topayments SOFTWARE Clients.Link = topayments.Client

You can get around this condition in another way. It is necessary to impose a "WHERE" condition directly on the relationship between the two tables. Example:

SELECT Clients.Link, Payment.Link FROM Directory.US_Subscribers AS US_Subscribers LEFT CONNECTION Document.Payment AS Payment Software (Clients.Link = Payment.Client AND Payment.Client.Name LIKE "Sugar Packet") GROUP BY Clients.Link, Payment. Link

8. Joins with Nested and Virtual Tables

Nested Queries often necessary to retrieve data based on some condition. If you then use them in conjunction with other tables, this can critically slow down the execution of the query.

For example, we need to get the Balance Amount as of the current date for some clients.

SELECT UnallocatedPaymentsRemains.Customer, UnallocatedPaymentsRemains.AmountRemaining FROM (SELECT Clients.Link AS Link FROM Directory.Clients AS Clients WHERE Clients.Link IN(&Clients)) AS NestedQuery LEFT JOIN RegisterAccumulations.UnallocatedPayments.Balances AS UnallocatedPayments BY Nested Request.Link = UnallocatedPaymentsBalances.Customer

When executing such a query, the DBMS optimizer may make errors when choosing a plan, which will lead to suboptimal execution of the query. When joining two tables, the DBMS optimizer selects a table joining algorithm based on the number of records in both tables. If there is a nested query, it is extremely difficult to determine the number of records that the nested query will return. Therefore, you should always use temporary tables instead of nested queries. So let's rewrite the request.

SELECT Clients.Link AS Link PLACE tClients FROM Directory.Clients AS Clients WHERE
Clients.Link B (&Clients) ; //////////////////////////////////////////////// ///////////////////////////// SELECT tClients.Link, UnallocatedPaymentsRemains.AmountRemaining, FROM tClients AS tClients LEFT JOIN RegisterAccumulations.UnallocatedPayments.Balances (, Client IN (SELECT tClients.Link FROM tClients)) AS UnallocatedPaymentsBalances tClients.Link = UnallocatedPaymentsBalances.Clients

In this case, the optimizer will be able to determine how many records the temporary table tClients uses and will be able to select the optimal algorithm for joining tables.

Virtual tables , allow you to obtain practically ready-made data for most applied tasks. (Slice of the First, Slice of the Last, Remains, Turnovers, Remains and Turnovers) The key word here is virtual. These tables are not physical, but are compiled by the system on the fly, i.e. When receiving data from virtual tables, the system collects data from the final register tables, assembles, groups and issues it to the user.

Those. When connecting to a virtual table, a connection is made to a subquery. In this case, the DBMS optimizer may also choose a non-optimal connection plan. If the query is not generated quickly enough and the query uses joins in virtual tables, then it is recommended to move the access to the virtual tables to a temporary table, and then make a join between two temporary tables. Let's rewrite the previous request.

SELECT Clients.Link AS Link PLACE tClients FROM Directory.Clients AS Clients INDEX BY Link WHERE
Clients.Link B (&Clients) ; //////////////////////////////////////////////// ///////////////////////////// SELECT UnallocatedPayments.AmountBalance, UnallocatedPayments.Client AS Client PLACE balances FROM RegisterAccumulations.UnallocatedPayments.Balances(, Client B (SELECT tClients. Link FROM tClients)) AS UnallocatedPaymentsBalances; //////////////////////////////////////////////// ///////////////////////////// SELECT tClients.Link, toRemainders.AmountRemaining AS AmountRemaining FROM tClients AS tClients LEFT JOIN toRemainders AS Remainders BY tClients.Link = tRemainings.Client

9.Checking the result of the request.

The result of the query may be empty; to check for empty values, use the following construct:

ResRequest = Request.Execute(); If resQuery.Empty() Then Return; endIf;

Method Empty() should be used before methods Choose() or Unload(), since retrieving the collection takes time.

It is not a revelation to anyone that it is extremely undesirable to use queries in a loop. This can critically affect the operating time of a particular function. It is highly desirable to receive all the data in the request and then process the data in a loop. But sometimes there are cases when it becomes impossible to move the request outside the loop. In this case, for optimization, you can move the creation of the query outside the loop, and in the loop, substitute the necessary parameters and execute the query.

This will save the system from syntax checking the request in a loop.

11. Construction "HAVING".

A design that is quite rare in requests. Allows you to impose conditions on the values of aggregate functions (SUM, MINIMUM, AVERAGE, etc.). For example, you need to select only those clients whose payment amount in September was more than 13,000 rubles. If you use the “WHERE” condition, you will first have to create a temporary table or a nested query, group records there by payment amount and then apply the condition. The “HAVING” construction will help avoid this.

SELECT Payment.Customer, AMOUNT(Payment.Amount) AS Amount FROM Document.Payment AS Payment WHERE MONTH(Payment.Date) = 9 GROUP BY Payment.Customer HAVING AMOUNT(Payment.Amount) > 13000

In the constructor, to do this, just go to the “Conditions” tab, add a new condition and check the “Custom” checkbox. Then just write Amount(Payment.Amount) > 13000

12. NULL value

I will not describe here the principles of three-valued logic in the database; there are many articles on this topic. Just briefly about how NULL may affect the result of the query. The value NULL is not actually a value, and the fact that the value is undefined is unknown. Therefore, any operation with NULL returns NULL, be it addition, subtraction, division or comparison. A NULL value cannot be compared to a NULL value because we don't know what to compare. Those. both of these comparisons are: NULL = NULL, NULL<>NULL is not True or False, it is unknown.

Let's look at an example.

For those clients who do not have payments, we need to display the “Sign” field with the value “No payments”. Moreover, we know for sure that we have such clients. And in order to reflect the essence of what I wrote above, let’s do it this way.

SELECT "No payments" AS Attribute, NULL AS Document PLACE topayments; //////////////////////////////////////////////// //////////////////////////// SELECT Clients.Link AS Client, Payment.Link HOW Payment PUT tClientPayment FROM Directory.Clients AS Clients LEFT CONNECTION Document.Payment AS Payment Software Clients.Link = Payment.Shareholder; //////////////////////////////////////////////// ///////////////////////////// SELECT tClientPayment.Client FROM tClientPayment AS tClientPayment INTERNAL JOIN tPayment AS tTopay BY tClientPayment.Payment = tPayment. Document

Pay attention to the second temporary table tClientPayment. With the left join I select all clients and all payments for these clients. For those clients who do not have payments, the “Payment” field will be NULL. Following the logic, in the first temporary table “tPayments” I designated 2 fields, one of them NULL, the second line “Does not have payments”. In the third table, I connect the tables “tClientPayment” and “tPayment” using the fields “Payment” and “Document” with an internal join. We know that in the first table the “Document” field is NULL, and in the second table, those who do not have payments in the “Payment” field are also NULL. What will such a connection return to us? But it won't return anything. Because the comparison NULL = NULL does not evaluate to True.

In order for the request to return the expected result, let’s rewrite it:

SELECT "No payments" AS Attribute, VALUE(Document.Payment.EmptyLink) AS Document PLACE toPayments; //////////////////////////////////////////////// ///////////////////////////// SELECT Clients.Link AS Client, ISNULL(Payment.Link, VALUE(Document.Payment.EmptyLink )) HOW Payment PUT tClientPayment FROM Directory.Clients AS Clients LEFT CONNECTION Document.Payment AS Payment BY Clients.Link = Payment.Shareholder; //////////////////////////////////////////////// ///////////////////////////// SELECT tClientPayment.Client FROM tClientPayment AS tClientPayment INTERNAL JOIN tPayment AS tTopay BY tClientPayment.Payment = tPayment. Document

Now, in the second temporary table, we have indicated that if the “Payment” field is NULL, then this field = an empty link to the payment document. In the First table we also replaced NULL with an empty reference. Now the connection involves non-NULL fields and the request will return the expected result.

All requests contained in the article reflect the situations that I would like to consider and nothing more. ABOUT They may not be delusional or suboptimal, the main thing is that they reflect the essence of the example.

13. An undocumented feature of the "CHOICE WHEN...THEN...END" design.

In the case when it is necessary to describe the “Conditions” construction in the request, we use the standard syntax:

SELECT SELECTION WHEN Users.Name = "Vasya Pupkin" THEN "Our favorite employee" ELSE "We don't know this" END AS Field1 FROM Directory.Users AS Users

But what if, for example, we need to get the name of the month in a request? Writing a huge construction in a request is ugly and time-consuming, so this form of writing above can help us out:

SELECT MONTH(US_CalculationConsumption_ScheduleTurnover.CalculationPeriod) WHEN 1 THEN "January" WHEN 2 THEN "February" WHEN 3 THEN "March" WHEN 4 THEN "April" WHEN 5 THEN "May" WHEN 6 THEN "June" WHEN 7 THEN "July" K WHEN 8 THEN "August" WHEN 9 THEN "September" WHEN 10 THEN "October" WHEN 11 THEN "November" WHEN 12 THEN "December" END AS A Month

Now the design looks less cumbersome and is easy to understand.

14. Batch query execution.

In order not to multiply requests, you can create one large request, split it into packages and work with it.
For example, I need to get the following fields from the "Users" directory: "Date of Birth" and the available roles for each user. upload this to different tabular parts on the form. Of course, you can do this in one request, then you will have to iterate through the records or collapse them, or you can do this:

SELECT Users.Link AS Full Name, Users.Date of Birth, Users.Role PUT vtUsers FROM Directory.Users AS Users; //////////////////////////////////////////////// ///////////////////////////// SELECT tueUsers.Full name, tueUsers.Date of Birth FROM tueUsers AS tueUsers GROUP BY tueUsers.full name, tueUsers. Date of Birth; //////////////////////////////////////////////// //////////////////////////// SELECT wUsers.Full Name, wUsers.Role FROM wUsers AS wUsers GROUP BY wUsers.Full Name, wUsers. Date of Birth

tPackage = Request.ExecutePackage();

TP_BirthDate = tPackage.Upload();
TP_Roles = tPackage.Unload();

As we can see, the query can be executed in a batch and the result can be processed as an array. In some cases it is very convenient.

15. Conditions in a batch request

For example, we have a batch request, where first we get the fields: “Name, Date of Birth, Code” from the “Users” directory and want to get records with conditions for these fields from the “Individuals” directory.

SELECT Users.Individual.Name AS Name, Users.Individual.Date of Birth AS Date of Birth, Users.Individual.Code AS Code PLACE vtUsers FROM Directory.Users AS Users; //////////////////////////////////////////////// ///////////////////////////// SELECT Individuals. Link AS Individual FROM Directory. Individuals AS Individuals

You can impose conditions like this:

WHERE Individuals.Code IN (SELECT vtUsers.Code FROM vtUsers) AND Individuals.Name IN (SELECT vtUsers.Code FROM vtUsers) AND Individuals.BirthDate IN (SELECT vtUsers.DateBirth FROM tvUsers)

And you can do it like this:

WHERE (Individuals.Code, Individuals.Name, Individuals.Date of Birth) IN (SELECT tueUsers.Code, tueUsers.Name, tueUsers.Date of Birth FROM tueUsers)

Moreover, it is necessary to maintain order.

16. Calling the query builder for “condition” in a batch request

When it is necessary to impose a condition, as in the example above, you can forget how this or that field is called in the virtual table.
For example, you need to impose a condition on the "Date of Birth" field, and in the virtual table this field is called "Debtor's Date of Birth", and if you forget the name, you will have to exit editing the condition without saving and look at the name of the field. In order to avoid this, you can use the following technique.

It is necessary to put brackets after Construction “B” and leave an empty space (space) between the brackets, select this space and call the query constructor. The designer will have access to all tables of the batch query. The technique works both on virtual register tables and on the “Conditions” tab. In the latter case, you need to check the "P (arbitrary condition)" box and enter the editing mode "F4".

The queries were often made up on the fly and they simply serve to illustrate the “techniques” that I was considering.

I wanted to look at the use of indexes in queries, but this is a very broad topic. I’ll put it in a separate article, or add it here later.

upd1. Points 11,12
upd2. Points 13,14,15,16

Used Books:
Query language "1C:Enterprise 8" - E.Yu. Khrustaleva
Professional development in the 1C:Enterprise 8 system."

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.

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

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.

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.

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.

Let's call the dialog for entering parameters of the PriceSliceLast virtual table and indicate that the period will be passed in the ReportDate parameter. To do this, select this table in the Tables list and click the Virtual Table Options button. Then select the following fields from the tables:

SprNomenclature. Parent,

PricesSlice of Latest.Price.

Left table join

- On the bookmark Connections: in the Link condition field, that the value of the Nomenclature dimension of the information register must be equal to the reference to the Nomenclature directory element. And also uncheck the All checkbox for the register table and check it for the lookup table, thereby setting the type of connection as a left connection for the lookup table:

Rice. 13.15. Relationship between tables in a query

- On the bookmark Conditions let's set the condition for selecting elements from the Nomenclature directory - the selected elements must correspond to the type of nomenclature passed in the Nomenclature Type request parameter:

Rice. 13.16. Conditions for selecting elements

- On the bookmark Unions/Aliases: specify the alias of the field Parent = Service Group, and the field Link = Service. - Click OK–

After this, you need to edit the data layout scheme, to do this on the tab Resources, click on the button add and select a resource - Price

- On the bookmark Options set the value of the Nomenclature Type parameter - Enumeration.Nomenclature Types.Service. In addition, we will remove the availability restriction for the ReportDate parameter. In the Type of this parameter field, set the composition of the date - Date. For the Period parameter, on the contrary, we set an availability restriction:

Rice. 13.17. Layout Scheme Options

Settings

- Let's go to the bookmark Settings: Let's create a grouping based on the Service Group field, specifying the grouping type Hierarchy.

The following hierarchy types exist for report groupings: Without hierarchy - only non-hierarchical records are displayed in the grouping. Hierarchy - both non-hierarchical and hierarchical records are displayed in the grouping. Hierarchy only - only hierarchical (parent) records are displayed in the grouping. Inside this group we will create another one, without specifying the group field. On the sub-tab Selected fields: specify the output fields Service and Price:

Rice. 13.18. Report structure and fields

On the sub-tab Other settings we will perform the following steps:

Rice. 13.19. Settings for displaying general totals for the "Service Group" grouping

Rice. 13.20. Setting up and displaying results for a global report

Finally, let’s include the Report Date parameter in the user settings and set its Editing Mode to Quick Access. Let's close the data composition scheme designer and in the window for editing the List of Services object, go to the Subsystems tab. In the list of configuration subsystems, note the Service Provision and Accounting subsystems.

In 1C: Enterprise mode

Let's launch 1C:Enterprise in debug mode and first of all open the periodic register Prices. Then we will test the report.

Using this report as an example, we studied how the data composition system obtains the latest values from the periodic information register and how groupings according to the directory hierarchy are displayed.

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 upload of the 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 for tabular parts will greatly help save time, since:
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 latest ones, but the last record 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
It means that a base will be obtained from this dimension in the future and serves for additional indexing of 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.