Sometimes you need to use a query to obtain data for several dates at once from a periodic information register. A typical example is working with exchange rates. Let's consider an algorithm for solving this problem using an example.
Formulation of the problem
A document “Sales of Goods and Services” has been created in the database, in the header of which there is the attribute “Currency”. The request requires for each document to obtain the current exchange rate from the header on the date of the document. Currency rates are stored in the periodic information register “Currency Rates”.
A direct solution to this problem could be a query in a loop: obtaining all documents with their dates and currencies and, in the selection, accessing a virtual slice table last register"Currency rates". But because a request in a loop 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.
SELECT Sales of Goods and Services. Link, Sales of Goods and Services. Currency, MAXIMUM (Currency Rates. Period) AS Period PLACE IN TPeriods Settings Rates FROM Document. Sales of Goods and Services HOW Sales of Goods and Services LEFT CONNECTION Register of Information. Currency Rates AS Currency Rates ON Sales of Goods servants.Currency = Currency Rates.Currency And Sales of GoodsServices.Date >= Currency Rates.Period GROUP Software Sales of Goods and Services. Link, Sales of Goods and Services. Currency; //////////////////////////////////////////////// ///////////////////////////// SELECT VTPeriodsSetting Rates.Link, VTPeriodsSetting Rates.Currency, RatesCurrency.Rate FROM VTPeriodsSetting Rates AS VTPeriodsSetting Rates LEFT CONNECTION RegisterInformation. Currency Rates AS Currency Rates ON VTPeriodsRate Settings.Period = Currency Rates.Period AND VTPeriodsRate Settings.Currency = Currency Rates.Currency
Procedure for request:
- Obtaining the period for setting the exchange rate for each document. Documents are connected to PHYSICAL table"Currency rates". Here you should pay attention to the connection conditions. The currencies must be equal, and the document date >= 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 exchange rates for the currency of the document, established no later than the date of the document.
The final step will be to group the rows to obtain the maximum rate period. As a result, for each document the required period for setting the exchange rate for the desired currency will be obtained (the maximum date for setting the exchange rate, but not more than the date of the document). The result is placed in the temporary table VTPeriodsSettingRates. - Getting a course. The temporary table VTPeriodsSetting Rates is connected to the PHYSICAL table “Currency Rates”. The connection occurs according to the Document Currency and the rate setting period defined in the second temporary table.
IN test configuration we have periodic register information "PriceNomenclature" 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 snapshot of the latest records for products and their prices with the condition that old price 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 | Register of Information. PricesNomenclatures. Slice of the Latest HOW PricesNomenclatureSlice of the Latest|WHERE | PricesNomenclatureSliceLatest. Old price< = 50 " ;
Pay attention to the condition in the "WHERE" section. This is what it's all about 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 PricesNomenclatureSliceLatest. Period, PricesNomenclatureSliceLatest. Product, PricesNomenclatureSliceLatest. Price, PricesNomenclatureSliceLatest. Old price FROM Register of Information. 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.”
/
Implementation of data processing
Resolving totals for periodic information registers
Scope of application: managed application, mobile app, a common 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 Slice of the First And Slice of the Last);
- while the remaining conditions for virtual tables Slice of the 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 PricesNomenclatures. 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, which store only the last values (for cutting the last ones) and the first values (for cutting the first ones).
1.2. In addition, you should consider alternative options to revise requests to the register 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 Slice of the First And Slice of the Last most often (always) subqueries and joins are used (calls “through a dot” to fields 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.
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.
Periodicity 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 bind the register to a document and change entries in the register in manual mode 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 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 click on the “magnifying glass” the desired type forms. 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 for storage 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 make a selection existing records. If you have any questions, use the comments in the article, I will try to quickly answer your question.
: Slice of the 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 not difficult to guess that for the first slice the principle of operation is the same, except that the first record after the cut date is selected.
Loading...
