Menu
homeBios

A feature of obtaining a snapshot of the latest entries in the information register. Receiving prices for the document date in a request; Slicing the latest does not work

: Cutting First And Slice of the Last Let's consider working with these virtual tables using 1C. Much more often used Slice of the Last, so let's start with it.

A slice of the latest allows you to obtain the last record of the information register for a specified date in the context of measurements. For the last (first) slice table, it is possible to specify two parameters in parentheses, separated by commas. The first parameter contains the date on which the slice is made (if the parameter is not specified, the slice is made on the current date). The second parameter is a condition in the 1C query language and allows you to set various selections. As a rule, measurements are used in these selections. All this sounds quite vague, so it’s impossible to do without an example.
So let us have a periodic register of information Price which stores prices by product and supplier. The frequency of the register is day.

The register contains the following entries

To begin with, we will get a slice of the latter without using parameters by executing the following request:

SELECT PriceSliceLast.Period AS Period, PriceSliceLast.Product AS Product, PriceSliceLast.Supplier AS Supplier, PriceSliceLast.Amount AS Amount FROM Register Information.Price.SliceLast AS PriceSliceLast

Since the parameters are not specified, the slice is performed on the current date - 02/01/2017. As a result, we get the following table

Here we see that the combination of dimensions Product + Supplier is unique, i.e. For each combination of register measurements, the record with the maximum date was taken, and the record date is less than or equal to the current date.

Let's say we need to do the same thing, but we want to get records with a date less than or equal to 01/15/2017. To do this, you need to change the row with the latest slice table in the request as follows

FROM RegisterInformation.Price.SliceLast(&CutDate,) AS PriceSliceLast

Before executing the request, of course, you need to pass a parameter to it &CutDate. Now the query result will look like this

And finally, imagine that we need to get a snapshot of the latest ones for the same date with the condition that we have the goods Pencil, and the supplier Stationery. To do this, specify the second parameter in the request

FROM RegisterInformation.Price.Last Cut(&CutDate, Product = &Product AND Supplier = &Supplier) AS PriceLast Cut

As a result, we get only one record

To avoid getting lost in all these parentheses and commas, it is better to use a query builder. I'll show you using the last request as an example.

After selecting a table with a slice of the latest ones in the query designer, you must click on the button Virtual Table Options and in the window that opens write

It is easy to guess that for the first slice the operating principle is the same, except that the first record after the cut date is selected.

Let's assume that we have received a task for which the customer wants to receive a report on the “Sales of goods and services” documents entered into the database, and for each document it is necessary to obtain the price from the information register “Item prices” on the date of the document. The report is written for the "Manufacturing Enterprise Management" configuration version 1.3.

The record from the document table is joined by a record from the virtual table "Nomenclature Prices. SliceLast" according to the corresponding conditions of the period, price type and item. The price type is obtained from the document details of the same name.

The problem has several ways to solve it. Let's consider two of them: a report on the access control system using two sets of data and obtaining all the necessary data in one request. Now, in order.

Using ACS

To implement such a report on the access control system, we will create two data sets in the data composition scheme. The first will receive a list of documents, the second will receive prices for document dates according to the selected item and price type. The connection between two sets of data is carried out by item, period (document date) and price type. From the first set it is necessary to transfer the parameters “Nomenclature”, “Price Type” and “Period” to the second set.

The first data set contains the following query:

The query selects the following fields from the document table and the “Products” tabular part: “Link”, “Date”, “Price Type”, “Nomenclature”. Now let's look at the second data set:

In the second set of data, you should pay attention to the parameters passed to the request. The list of selectable fields contains the parameters "Period, "Item" and "Price Type". The same parameters are used in the parameters of the virtual table "Nomenclature Prices. Slice of Latest". It is necessary to display these parameters in the list of selectable fields so that the values ​​of these parameters can be transferred from the first set to the second set. This is done using the connection settings between data sets in the “Data Set Connections” tab of the data layout diagram designer:

Setting up connections between data sources is carried out using the selection fields in the request. In the second data set, we added query parameters to the selection fields. When establishing relationships between sets, if you select a field in the "Parameter" column for the relationship, the value from the "Source Expression" column will be transferred to the "Destination Expression" and fill the selected parameter with the corresponding value.

Having configured the report structure and generated it, we will get the following result:

The report is working. Let's move on to considering options for obtaining the price for the document date in one request.

In one request

Let's create a new report with similar output fields and report settings. The only difference will be the data sources. This time, the report will contain a single data set in which all document products and their associated prices are selected in one query. The request text looks like this:

"SELECT | DocumentNomenclaturePeriod.Document, | DocumentNomenclaturePeriod.Document.Date AS Date, | DocumentNomenclaturePeriod.Document.PriceType AS TypePrice, | DocumentNomenclaturePeriod.Nomenclature, | PricesNomenclature.Price | FROM | (SELECT | Sales of GoodsServicesProducts. Link AS Document, | ovServicesProducts.Nomenclature AS Nomenclature, | MAXIMUM (PricesNomenclatures.Period) AS Period | Document.Sale of GoodsServices.Goods AS Sales of GoodsServicesProducts | LEFT CONNECTION Register of Information.PricesNomenclatures AS PricesNomenclatures | Clatura | And Sales of Goods and Services Goods. Link. Date >= Prices Nomenclatures. Period | And Sales of GoodsServicesProducts.Link.PriceType = PricesNomenclatures.TypePrices | | Software DocumentNomenclaturePeriod.Nomenclature = PricesNomenclature.Nomenclature | And DocumentNomenclaturePeriod.Document.PriceType = PricesNomenclature.PriceType | And DocumentNomenclaturePeriod.Period = PricesNomenclature.Period"

The principle of the request is to obtain the nearest date of price change for each used item in the tabular section of the document, and then attach data from the physical table “Item prices” by period, item and price type to the resulting query result. At first glance, the request may seem complicated, but in fact its syntax is quite simple.

The execution result is similar to the result of the previous report (see screenshot above).

Draw conclusions

Which option for completing the task is better? It all depends on the specific conditions. In some situations, a report from two data sets will be faster than retrieving all the data for a report in one query. Within the scope of this article, we will not be able to touch upon performance issues for the considered methods of solving the problem.

It is also worth remembering that creating reports based on document tables is not recommended by the development methodology from the 1C company, since it is believed that the information in the documents may not be reliable. All reports must be based on registers.

/
Implementation of data processing

Resolving totals for periodic information registers

Scope of application: managed application, mobile application, regular application.

1.1. For periodic information registers, it is recommended to allow totals if all of the following conditions are met:

  • a large volume of data is expected in the register (for example, it is justified for a register with item prices; but it does not make sense for a register with exchange rates);
  • the configuration provides frequency queries to slices of the latter at the current time and/or to slices of the former to obtain current data (i.e. when the period is not specified in the parameters of virtual tables Cutting First And Slice of the Last);
  • while the remaining conditions for virtual tables Cutting First And Slice of the Last are set only to measurement values ​​(and separators in the mode Independently and jointly);
  • register data access restrictions use only dimensions (and delimiters that are in mode Independently and jointly).

For a complete list of all conditions when queries use information register totals, seedocumentation for the 1C:Enterprise platform.

For example, if the configuration includes frequently executed queries to the register PricesNomenclatures to obtain current item prices:

SELECT Item. Article AS Article, PricesNomenclature. Price AS Price, . . . FROM Directory. Nomenclature AS Nomenclature LEFT CONNECTION Register of Information. PricesNomenclatures. SliceLast(, PriceView = &Type of Prices) HOW PricesNomenclatures Software PricesNomenclature. Nomenclature = Nomenclature. Link . . .

then, subject to all other conditions listed above, setting the property Allow totals: slice of recent ones will significantly speed up the execution of such queries, due to the fact that the selection will be performed directly from additional tables that store only the last values ​​(for cutting the last ones) and the first values ​​(for cutting the first ones).

1.2. In addition, alternative options should be considered to revise the registry queries so that these conditions are met.

For example, if in some cases the data in the register PricesNomenclatures are recorded on a future date, and when selecting goods to this register, a query is always executed for the current date (the date is explicitly specified in the virtual table parameter Slice of the Last), then the results will not speed up the execution of such queries. Since the totals are built only for the first and last records of the register.

However, if, when opening the product selection form, you analyze whether there are registrars with a future date, and if there are none, you run another query for a slice of the latter without setting the date, then such a query will work faster.

2. In all other cases, totals should not be allowed for periodic information registers. First of all, if

  • most often (always) queries are made to the virtual tables of the first/last periodic register of information for a specific period (for example, for the date of the document).
  • in conditions for virtual tables Cutting First And Slice of the Last Most often (always) subqueries and joins are used (calls “through a dot” to the fields of related tables). For example, in this case:

3. There is no need to provide a separate mechanism for recalculating totals in the configuration, since the updating of the totals tables is performed automatically every time a set of records is written to the register.

In the test configuration, we have a periodic information register “Nomenclature Prices” with the following initial data:

The figure also shows the structure of the registry metadata. As we can see, the register contains the “Product” dimension with the reference type “Products”, as well as the “Price” numeric resource and the “OldPrice” attribute.

Let's say in a report we need to get a slice of the latest records for products and their prices with the condition that the old price is less than or equal to 50.

Two request options

I’ll say right away that we will consider the correct and incorrect options. Let’s start with the latter. This is a mistake that newbie programmers often make. And so, the following query was written for the report:

Request = New Request; Request. Text = " SELECT | | | | | FROM | Information Register. PricesNomenclatures. Slice of the Latest HOW PricesNomenclatureSlice of the Latest|WHERE | PricesNomenclatureSliceLast. Old price< = 50 " ;

Pay attention to the condition in the "WHERE" section. This is the main mistake! This query will not return a single record, and here's why: when using virtual tables, in our case "Last Slice", data is first fetched from the database according to the conditions described in the virtual table, and then the actions described in the query text are performed (groupings, conditions in the "WHERE" section, sorting, etc.).

Therefore, in our example, the request does not return a result. First, he receives a slice of the latter, and only then sets the condition on the “Old Price” attribute. This is what it looks like in the diagram:

To solve the problem correctly, the condition for the “Old Price” attribute must be transferred to the conditions of the virtual table. This is what the correct request text will look like:

Request = New Request; Request. Text = " SELECT PricesNomenclatureSliceLast. Period, PricesNomenclatureSliceLast. Product, PricesNomenclatureSliceLast. Price, PricesNomenclatureSliceLast. Old price FROM Information Register. PricesNomenclatures. SliceLast(, OldPrice< = 50 ) HOW PRICESNomenclatureSliceLatest"

Now the request will receive the correct data, since a slice of the latest prices will be received taking into account the condition for the “OldPrice” attribute.

results

It should be understood that the above applies to all cases of using virtual tables in queries (for accumulation registers, accounting registers, tasks, etc.).

This also implies the main rule for using virtual tables: “when using a virtual table, be sure to set the selection parameters directly in the virtual table, otherwise the query will receive unnecessary data, which will then be used for selection.”

1C information registers it is a structured set of data with dimensions and resources. Designed to store periodic information.

Periodicity

Information is stored by dimension and period. You can set the frequency of the information register:

  • Non-periodic
  • by registrar
  • second
  • a week
  • month
  • quarter

Frequency is needed to select information from the register for a certain period of time. If you specify a frequency, entries in the register will be made with the period when the entry was made. Let’s say if you look at the “Item Prices” register, you can see the history of price changes, with what measurements and in what period of time the entry was made.

Frequency in information registers is needed for information that changes over time, for example: exchange rates, product prices, product discounts and markups, etc.

Registrars

If you make an entry into the information register using a document, you need to set the entry mode: “Submission to the registrar” and select the document with which the entry will be made in the register. Then the “Registrar” field will appear in the register, where information will be stored on what document the entry was made with. The recorder can also be used as a period; to do this, indicate in the “Frequency” field – “By recorder”. Subordination to the registrar is done when it is necessary to strictly link a register to a document and changing entries in the register manually becomes unavailable.

There may be several documents that will act as registrars. In order to add a registrar, you need to go to the properties of the desired information register, go to the “Registrars” tab and check the boxes next to the documents that will act as a registrar.

You can see the movements the recorder makes from the document. To do this, you need to go to the document you are interested in, click: Go – Document movements by the registrar.

Don’t forget to add rights in the registry properties; they can be assigned on the “Rights” tab. Then in the list of roles you need to select the role to which you want to add rights to the register and in the list of rights set the rights to for the selected role.

Uniqueness of records

The uniqueness of a record depends on the period and measurements. For example, if you want to write a record with the same measurements in the “Item Prices” register on the same day, then you will not be able to do this and the program will cause an error, since the periodicity of the register is within a day.

If the frequency is set by the registrar, it also participates in the uniqueness of the record.

For non-periodic and independent registers, uniqueness depends on the combination of dimensions.

Forms

To view records, use the list form, in it you can set the selection according to the fields you are interested in, view the history of records and change them through the record form. You can view register entries as follows: in the top menu, click the “Operations” - “Information Registers” button. In the window that opens, select the register you need. After this, a list form will open in the form of a table, where each entry is a unique entry.

To edit/create, use the record form; if the record is subordinate to the registrar, then the field will not be available and the form cannot be created.

You need to add forms in the configurator by going to the information register, in the “Forms” tab and clicking on the “magnifying glass” next to the desired type of form. Next, a window will open where you can configure the fields of the future form (location, names and specify functionality).


Dimensions, resources and details

Dimensions are intended to form the uniqueness of a record; in the future, you can select them and make a cut based on a specific dimension. The combination of measurements forms the record key. It is better not to create a large number of dimensions so that the table does not grow and does not slow down while working with it.

Dimensions have a “Leading” checkbox; if it is checked, the record will be stored in the database as long as this dimension exists. Several leading measurements can be made. For example, in the information register “Item prices”, the leading dimension is the item; if you delete an item that is included in the record, then the entry in the information register for this item will be automatically deleted.

Resources are designed to store summary information: quantity, price, etc. In the future, we will receive resources for a certain period of time (if the register is periodic), according to measurements.

Details, in most cases, are intended to store additional information; they do not take part in the uniqueness of the record. For example, you can enter information such as author, comment, etc. into the details.

You can perform the following actions with the information register:

  • Deleting an entry in the 1C information register

Peculiarities

— Uniqueness of records based on a set of dimensions: each record in the information register is a new resource value.

— Entries in the information register can be either periodic or not.

— The information register can be dependent and independent of the registrar.

— It is possible to make a cross-section of the first and last records for the desired date. This is implemented by virtual tables: “Slice of the First” and “Slice of the Last”. To use these tables, you can use both selection and query (in the query designer you will select these virtual tables and you can make a query on them). These tables will be available if the information register is periodic.

The “Item Prices” register is a periodic register of information, entries are made according to the registrar.

The image shows that the frequency is set to within a day. This means that the price can be changed once a day based on measurements unique during the day.

The register is subordinated to the document “Setting item prices”. This means that the entry into the register comes from this document. Movements on a specific document can be viewed from the document form “Setting item prices”.

The register is designed to store information on the price of an item, with the dimensions “Price type”, “Item” and “Item characteristics”. The leading dimension is all three dimension fields; it will be possible to make selections based on it when sampling.

Conclusion: After reading the article, you will be able to create a 1C information register, add dimensions and resources, configure editing and list forms. Create a record and select existing records. If you have any questions, use the comments in the article, I will try to quickly answer your question.