A feature of obtaining a slice of the latest entries in the information register. Feature of obtaining a slice of the latest records in the information register Get a slice of the latest from the information register

Information registers 1C it is a structured dataset with dimensions and resources. Designed to store periodic information.

Periodicity

Information is stored in terms of measurements and period. Register information, you can set the frequency:

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

Periodicity 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 record was made.

Periodicity in information registers is needed for information that changes over time, for example: exchange rates, item prices, item discounts and margins, etc.

Registrars

If you write to the information register using a document, you need to set the recording mode: “Submission to the registrar” and select the document that will be used to write to the register. Then the "Registrar" field will appear in the register, where information will be stored, which document the entry was made with. Also, the registrar can be used as a period, for this, specify in the "Periodicality" - "By registrar" field. Submission to the registrar is done when it is required to firmly bind the register to the document and change the entries in the register in manual mode becomes unavailable.

There may be several documents that will be in the role of registrars. In order to add a registrar, you need to go to the properties of the required 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 that the registrar makes from the document. To do this, you need to go to the document you are interested in, click: Go - Document movements through the registrar.

In the registry properties, do not forget to add the rights, 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 set the rights to the selected role in the list of rights.

Uniqueness of records

The uniqueness of the record depends on the period and measurements. For example, if you want to write a record with the same measurements to the “Item prices” register on the same day, then you will not succeed and the program will cause an error, since the frequency of the register is within a day.

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

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

Forms

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

For editing / creating, use the entry form, if the entry is subordinate to the registrar, then the field will not be available and the form will not 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" at desired type forms. Next, a window will open where you can configure the fields of the future form (location, names and prescribe the functionality).

Dimensions, resources and props

The dimensions are intended to form the uniqueness of the record, they can then be used to select and cut by a specific dimension. The combination of measurements forms the key of the record. Better not create a large number of measurements so that the table does not grow and does not slow down in the process of working with it.

Dimensions have a checkbox "Leading", if it is set, then 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 the item that participates in the record, then the record 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.

Requisites, in most cases, are intended for storage additional information, they do not contribute to the uniqueness of the record. For example, information such as the author, comment, etc. can be entered in the details.

You can perform the following actions with the information register:

• Deleting an entry in the information register 1C

Peculiarities

- The uniqueness of records by a set of dimensions: each record in the information register is a new value of the resource.

- Records of the information register can be either periodic or not.

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

- It is possible to make a cut of the first and last records on the desired date. This is implemented by virtual tables: SliceFirst and SliceLast. To use these tables, you can use both selection and query (in the query designer, you will take away these virtual tables and you can query them). These tables will be available if the information register is periodic.

The register "Nomenclature prices" is a periodic register of information, entries are made according to the registrar.

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

The register is subordinated to the document "Setting prices for items". So, the entry in the register comes from this document. Movements on a specific document can be viewed from the form of the document "Setting item prices".

The register is designed to store information on the price of the item, with the dimensions "Price type", "Nomenclature" and "Characteristic of the item". The leading dimensions are all three fields of the dimension, it will be possible to make a selection on it during the selection.

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

: SliceFirst and SliceRecent Consider working with these virtual tables using 1C. Much more commonly used SliceRecent, so let's start with it.

The last slice allows you to get the last record of the information register on the specified date in the context of measurements. For the last (first) slice table, it is possible to specify two parameters separated by commas in brackets. The first parameter contains the date on which the cut is made (if the parameter is not specified, the cut is made on the current date). The second parameter is a condition in the 1C query language and allows you to set various filters. As a rule, measurements are used in these selections. All this sounds rather vague, so an example is indispensable.
So let's say we have a periodic register of information Price in which prices are stored in the context of goods and suppliers. The frequency of the register is a 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 query

SELECT PriceSliceLast.Period AS Period, PriceSliceLast.Item AS Product, PriceSliceLast.Supplier AS Supplier, PriceSliceLast.Sum AS Sum FROM DataRegister.Price.SliceLast AS PriceSliceLast

Since the parameters are not specified, the cut 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 entry with the maximum date was taken, and the date of the entry is less than or equal to the current date.

Let's say we need to do the same, 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 last slice table in the query as follows

FROM DataRegister.Price.SliceLast(&SliceDate,) AS PriceSliceLast

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

And finally, let's imagine that we need to get a slice of the latest on the same date, provided that we have the goods Pencil, and the supplier Stationery. To do this, specify the second parameter in the request

FROM DataRegister.Price.SliceLast(&SliceDate, Item = &Item AND Supplier = &Supplier) AS PriceSliceLast

As a result, we get only one record

In order not to get confused in all these brackets and commas, it is better to use the query builder. I will show on the example of the last request.

After selecting in the query constructor a table with a slice of the latter, you must click on the button Virtual Table Options and in the opened window write

It is not hard to guess that for the first slice, the principle is the same, except that the first record after the slice date is selected.

Sometimes it is required by a request to obtain data for several dates at once from the periodic information register. A typical example is working with exchange rates. Consider the algorithm for solving this problem using an example.

Formulation of the problem

The document “Sale of Goods and Services” has been created in the database, in the header of which there is the “Currency” attribute. The request is required for each document to get the current exchange rate from the header on the date of the document. Storage of exchange rates is carried out in the periodic register of information "Currency Rates".
A head-on solution to this problem could be a query in a loop: getting all documents with their dates and currencies, and in the selection, accessing the virtual table of the slice of the latest “Currency Rates” register. But since a request in a cycle is “bad”, let's try to implement the task with one request.

Solution

To solve the problem, we will use the fact that the tables in the query can be connected not only for equality of fields.

Choose the implementation of the services. Dear, implementation of services, maximum (headlut. Period) How to place VTereodiyAstniyads from the document. Realization services as the implementation of services to the left -wing union of registrations. Counsilers as a time for the implementation. Software Realization of Goods of Services. Link, Realization of Goods of Services. Currency; /////////////////////////////////////////////////// //////////////////////// SELECT VTPeriodsSetting Rates.Reference, VTPeriodsSetting Rates.Currency, RatesCurrency.Rate FROM VTPeriodsSetting Rates AS VTPeriodsSetting Rates LEFT JOIN Data register. Currencies Rates AS Currencies Rates ON TUPeriodsRateSettings.Period = Currencies Rates.Period And TuePeriodsCurrencySettings.Currency = Currencies.Currency

Order of actions in the request:

1. Getting for each document of the period of setting the exchange rate. Documents are connected to the PHYSICAL table "Currency Rates". Here you should pay attention to the connection conditions. Currencies must be equal, and the date of the document >= the period of the information register.
   As a result of such a connection for each document, a set of rows will be obtained that satisfy the condition: all records of rates for the currency of the document, set no later than the date of the document.
   The final step will be the grouping of rows to obtain the maximum rate period. As a result, for each document, the required exchange rate setting period for the required currency will be obtained (the maximum date for setting the exchange rate, but no more than the date of the document). The result is placed in the temporary table VTPeriodsSettingRate.
2. Getting a course. Temporary table VT PeriodsSetting Rates is connected to the PHYSICAL table "Currency Rates". The connection is made according to the Document Currency and the rate setting period defined in the second time table.

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

The figure also shows the structure of the register's metadata. As we can see, the register contains the "Product" dimension with the type of reference to the "Products" lookup, as well as the "Price" numeric resource and the "OldPrice" attribute.

Let's say in the 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 must say right away that we will consider the correct and incorrect options. Let's start with the last one. This mistake is often made by novice programmers. And so, the following query was written for the report:

Request = New Request; Request. Text = " SELECT | | | | | FROM | RegisterInformation. PricesNomenclature. Slice Recent AS Prices Items Slice Recent| WHERE | PricesItemsSliceLast. Old price< = 50 " ;

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

Therefore, in our example, the query does not return a result. First, it receives a slice of the latter, and after that it sets a condition on the "OldPrice" attribute. This is how it looks on the diagram:

To correctly solve the problem, the condition for the "OldPrice" attribute must be transferred to the conditions of the virtual table. This is what the correct request text would look like:

Request = New Request; Request. Text = " SELECT PricesItemsSliceLast. Period, PricesItemsSliceLast. Product, PricesItemsSliceLast. Price, PricesItemsSliceLast. Old price FROM RegisterInformation. PricesNomenclature. SliceLast(, OldPrice< = 50 ) AS PricesItemsSliceLatest"

Now the request will receive the correct data, since the slice of the latest prices will receive already 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: "using a virtual table, be sure to set the filter parameters directly in the virtual table, otherwise the query will receive unnecessary data, which will then be set filters."

/
Implementation of data processing

Resolution of Totals for Periodic Information Registers

Application area: managed application, mobile app, common application.

1.1. For periodic information registers, we recommend that you enable totals if all of the following conditions are met:

• a large amount 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 for frequency queries to slices of the last at the current time and / or to slices of the first to obtain up-to-date data (i.e. when the period is not specified in the parameters of virtual tables SliceFirst and SliceRecent);
• while the remaining conditions for virtual tables SliceFirst and SliceRecent are set only for measurement values ​​(and separators that are in the Independently and jointly);
• only dimensions (and delimiters that are in mode) are used in register data access restrictions. 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 get current item prices:

SELECT Nomenclature. Article AS Article, PricesNomenclature. Price AS Price, . . . FROM Handbook. Nomenclature AS Nomenclature LEFT JOIN Information register. PricesNomenclature. SliceLast(, PriceType = &TypePrices) AS PricesNomenclatures Software Price Nomenclature. nomenclature = nomenclature. Link . . .

then, subject to all other conditions listed above, setting the property Allow totals: slice recent 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 a slice of the last) and the first values ​​(for a slice of the first).

1.2. In addition, alternatives should be considered to revise registry requests so that these conditions are met.

For example, if in some cases the data in the register PricesNomenclatures are recorded with a future date, and when selecting goods for this register, a request is always made for the current date (the date is explicitly set in the virtual table parameter SliceRecent), then the totals will not speed up the execution of such queries. Since the totals are built only for the first and last entries of the register.

However, if, when opening the goods selection form, we analyze whether there are registrars with a future date, and if there are none, we execute another request to cut the latter without setting the date, then such a request will work faster.

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

• most often (always) to the virtual tables of the slice of the first / last periodic register of information, requests are made for a specific period (for example, for the date of the document).
• in conditions for virtual tables SliceFirst and SliceRecent most often (always) subqueries and joins are used (dotted calls to fields of related tables). For example, in this case:

3. It is not required to provide a separate mechanism for recalculating totals in the configuration, since the updating of the totals tables is performed automatically each time a set of records is written to the register.