Revolving register of accumulation “Sales. Questions on current and residual accumulation registers Create an accumulation register 1c

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

The register type is selected when creating it in the configurator

As the name implies, some are designed to receive balances for a certain date, and the second to receive turnover for a selected period. Depending on the type of accumulation register, the 1C:Enterprise platform generates a different set of virtual tables. In this article, we will consider working with virtual tables of accumulation registers. To do this, we will create a register for the accumulation of balances - GoodsRemains and the turnover accumulation register - Goods Turnovers.

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

Turnover register

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

Everything is clear with the standard table. Let's take a closer look at virtual reality.

Virtual Table Turnovers

This table allows you to get the turnover of resources in the context of measurements. In our case, we have two dimensions: Stock and Product. And one resource Quantity

Let our register have the following entries

Let's go back to the query builder and start by just selecting from the table Goods Turnovers. Turnovers all fields

Accordingly, the request will look like this:

CHOOSE GoodsTurnoversTurnovers.Warehouse, GoodsTurnoversTurnovers.Product, GoodsTurnoversTurnovers.QuantityTurnover FROM RegisterAccumulation.GoodsTurnovers.Turnovers(,) AS GoodsTurnoversTurnovers

The query result looks like this:

That is, we received turnover in the context of goods and warehouses for the entire time. Suppose that we are not interested in warehouses and we want to get turnover only in the context of goods.

To do this, exclude the dimension from the query Stock

CHOOSE GoodsTurnoversTurnovers.Product, GoodsTurnoversTurnovers.QuantityTurnover FROM RegisterAccumulation.GoodsTurnovers.Turnovers(,) AS GoodsTurnoversTurnovers

and as a result we will have only two lines

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

After that, our original query will take the following form

SELECT GoodsTurnoversTurnovers.Warehouse, GoodsTurnoversTurnovers.Product, GoodsTurnoversTurnovers.QuantityTurnover FROM Accumulation register.GoodsTurnovers.Turnovers(&Beginning of the Period, &End of the Period, Warehouse = &Warehouse) AS GoodsTurnoversTurnovers

As you can see, the difference is that parameters appeared in brackets after the name of the virtual table, which must be filled in before executing the query.

For those who are just starting to work with virtual tables, it is often tempting to set the selection in the usual way instead of using parameters:

FROM Accumulation Register.GoodsTurnovers.Turnovers(,) AS GoodsTurnoversTurnovers WHERE GoodsTurnoversTurnovers.Warehouse = &Warehouse

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

After that, we observe that a field appeared in the fields of the table Period.

Adding it to the selected fields, we get the following query text:

CHOOSE GoodsTurnoversTurnovers.Period, GoodsTurnoversTurnovers.Warehouse, GoodsTurnoversTurnovers.Product, GoodsTurnoversTurnovers.QuantityTurnover FROM Accumulation register.GoodsTurnovers.Turnovers(, Month,) AS GoodsTurnoversTurnovers

We execute the request:

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

Balance accumulation register

Just like with the reverse register, let's see in the query builder which virtual tables are available for the balance accumulation register

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

Virtual Table Turnovers

Although the register type is Remains, we can still get turnovers from it. Plus we have two additional resources here: Coming and Consumption

Let me remind you that when an entry is made in the register of balances, the type of movement of accumulation (income or expense) is indicated, while for the turnover register the type of movement is not indicated. Therefore, here we have an additional bonus in the form of an opportunity to receive not only the turnover for the period as a whole, but also income with expenses separately. But of course, if there is a turnover register in the metadata with a similar set of measurements, then it is better to use it to get turnovers. In general, working with this virtual table is similar to working with a virtual table Turnovers turnover register discussed above.

Virtual Table Balances

This table is used to obtain the remaining resources in terms of dimensions. In the table parameters, we can specify the date for which we receive the balances and set the filters:

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

We select all available fields and set the end of June as the date for receiving the balances. We will not establish selection by measurements. Then the request text will look like this:

SELECT GoodsRemainsRemains.Warehouse, GoodsRemainsRemains.Product, GoodsRemainsRemains.QuantityBalance FROM Accumulation Register.ProductsRemains.Remains(&RemainsDate,) AS GoodsRemainsRemains

And after executing it, we get the following result

Virtual table

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

The use of this table can be justified when it is necessary to simultaneously obtain both turnovers and balances at the beginning and end of the period in one report. In other cases, its use should not be abused.

the cost of the materials available in the organization, we will need to access the documents, view each of them, writing out the necessary data, and then summarize them, obtaining the necessary data. This approach is inconvenient - it is too slow both for our imaginary "manual" case and for automated accounting.

It would be more logical, in addition to the documents, to keep special tables in which, when receiving materials and writing them off, to enter brief information about this. If, say, on a daily basis, to sum up these tables and display the remaining materials, then, in order to say how much and what materials are available in the organization on a certain date, it is enough to refer to the corresponding column of the table. In the 1C:Enterprise system, such tables are accumulation registers. As the name implies, they are designed to reflect, accumulate, certain indicators. And the reflection in the registers of receipts and expenditures of materials is one of the typical examples of their use.

Our organization keeps records of materials in relation to the responsible persons who receive them and from whom these materials are written off when released into production. We need to store information about the quantity of materials and their cost, and also, when writing off to production, have information about which particular master these materials were transferred to. These considerations allow us to design a structure accumulation register which we will now create.

When planning the composition accumulation register we need to understand what kind of data we are going to store in it, and then "decompose" this data into measurements, resources and register details.

So, we need to store the following data:

• Nomenclature position
• Responsible person for this position
• Item quantity
• Item cost
• Information about the master to whom the materials were transferred for use.

Register measurements, or sections in which data is stored, allow us to answer questions about what kind of data is stored in the register. In our case, we need to know two main characteristics - this is what responsible person fixed one or the other nomenclature position. Obviously, the dimensions from our data list will be the nomenclature position and the responsible employee.

Register resources are always numeric values ​​characterizing the stored data. Numeric values ​​are the quantity and the amount, and they will be the resources of our register.

Register details play an auxiliary role, and, in our case, it would be logical to store information about the master who received the materials for work in the register requisite - in case we need to find out who exactly used these materials.

Another important question to decide when designing a register is whether this register will balance register or turnover register. We are interested in both information on the balance of materials and information on turnover, therefore, when setting up the register, you should specify the type of register - Remains. Register with a view Remains allows us to work with balances and turnovers

The structure proposed here accumulation register- this is just one of the options for how you can organize the storage of the described data. Such an accounting scheme can be implemented, say, using a pair of registers, one of which is used solely for the purpose of storing the total balance of materials - that is, the data that is needed for financial reports, the other - for storing data on responsibility centers. In any case, each specific accounting scheme may require its own register structure, and our example is just a demonstration of one of the possible options.

Having discussed the theoretical part of the issue, let's move on to practice. Let's create a new accumulation register, let's call it RemainsMaterials, parameter Register type leave it in the meaning Remains, rice. 6.12.

Rice. 6.12.

Turn on accumulation register into the subsystem Operational AccountingMaterials.

On the tab Data Let's create the following measurements, resources and attributes:

measurements:

Name: Nomenclature, Type: DirectoryReference.Nomenclature, Prohibition of blank values ​​– set.

Name: ResponsibleEmployee, Type: DirectoryReference.Employees, Prohibition of blank values ​​- set.

Resources

Name: Quantity, Type: number, length 10, precision 3

Name: Sum, Type: number, length 10, precision 2

Requisites:

Name: Materials Receiver, Type: DirectoryLink.Employees

Pay attention to the names of these details, their types, as well as the standard register details (Fig. 6.13.) - these data will be useful to us when working on the procedure document.

Let's exclude the general attribute from the composition of the register details Organization. Now it is not needed. To organize the storage of data in the register in the context of various organizations, we would need a new dimension - Organization, thanks to which we would be able to work with materials from various organizations.

Let's go to the tab Registrars object editing window and select documents as registrar documents - ReceiptMaterials and VacationMaterialsTo Master.

At this stage, the setting accumulation register finished, let's move on to the document settings. Let's start with the document ReceiptMaterials.

Open the object editing window for this document, go to the tab movements(Fig. 6.14.) and press the button

Rice. 6.14.

In the constructor, select the type of register movement - Coming, in field tabular part specify the tabular part of the document materials, click on the button Fill expressions. The automatic mechanism for establishing a correspondence between document and register data does not always work correctly (in the event that it cannot unambiguously determine the correspondence, or when the correspondence determined by it according to its logic differs from the desired one), so let's check the correctness of the established correspondences. As a result, the window Constructor register movements should look like the one shown in Fig. 6.15.

Rice. 6.15.

After pressing the OK button, the following procedure for processing the posting will be generated in the document object module (this is how it looks after removing the comments that the code was built by the motion designer):

Procedure HandlingPerforming(Failure, Mode) // register Remains of Materials Arrival of Movement.Residuals of Materials.Write = True; For Each TechStringMaterials From Materials Loop Movement = Movements.RemaindersMaterials.Add(); Movement.MovementType = MovementTypeAccumulation.Incoming; Movement.Period = Date; Movement.Nomenclature = TekStringMaterials.Nomenclature; Movement.ResponsibleEmployee = ResponsibleEmployee; Movement.Quantity = CurrentStringMaterials.Quantity; Movement.Amount = CurrentStringMaterials.Amount; EndCycle; EndProcedure

This procedure is declared in the object module, it is executed on

To reflect various business operations in programs based on 1C: Enterprise, users use documents. From them you can get all the necessary data, for example, on the balance of funds in the accounts of the company, the number of goods sold for a certain period of time.

In the process of work, there can be quite a large number of such documents. Here we can talk not about hundreds of documents, but about thousands and tens of thousands. Naturally, building reports and other data from such a large number of documents is very long, laborious and inefficient.

Just imagine that when registering the sale of any product, the accountant sits and waits for the program to check the previously entered documents and calculate whether there is enough balance in the warehouse to write off.

To solve such problems in 1C, there are special configuration objects - accumulation registers. When conducting documents, they make entries in them, and only then from these registers you can quickly get all the necessary data, including balances and turnovers. That is, accumulation registers not only store data, but also allow you to quickly process it.

As mentioned earlier, accumulation registers can allow you to quickly receive balances and turnovers. This functionality is directly related to the type of register. It can be changed on the "Basic" tab of the form for editing a specific register.

• Turnover accumulation registers allow you to get only turnover for a certain period of time. In this case, it is not possible to receive the rest. The accumulation register with the “Turnover” type creates only one virtual table in the system - turnover.
• Accumulation registers with the “Remainders” type enable the developer to receive both balances and turnovers. In this case, three virtual tables will be created: balances, turnovers, balances and turnovers. An example of such a register can be a register of balances of goods and materials in warehouses, cash flows on the settlement accounts of the organization.

note that, despite the universality of the balance register in obtaining both balances and turnovers, it also has its drawbacks. When receiving turnovers in the balance register, the performance is significantly reduced. In this regard, it is strongly not recommended to use the balance register in cases where you need only turnovers (for example, sales turnover). It is not necessary to set the register type to remainders “just in case”, because the registers are designed to speed up the program, and on the contrary, you will slow down this process.

Accumulation register data

measurements

Measurements of accumulation registers mean certain cuts in which you will be able to obtain information in the future. In our case, as measurements of the register "Product Release" are: organization, order, specification, division, and others.

At any time, we will be able to receive balances, for example, for the entire organization as a whole, for a specific order.

Dimension Properties

Each dimension has its own set of properties, which can significantly affect the operation of the register as a whole. Similar properties are also present in resources with attributes.

In the event that you plan to set the selection by any dimension quite often in the future. In our case, we will not use indexing for the organization.

You can also specify here whether the dimension is required to be filled in, whether full-text search should be used, and other settings.

Resources

Resources are numeric data that store some information. It is this that we will obtain in the future in various sections. For example, in the accumulation register “Product output”, only quantity will act as a resource. In this case, we will be able to get the number of products manufactured at a certain point in time, for example, for a certain division.

Another simple example. Let's assume that we carry out all operations on the cash register according to the accumulation register. The resources will be the amount of money that is either received (receipt) or issued (expenditure) from the cash register. The dimension can be, for example, a counterparty/reporting entity. As a result, we will be able to obtain data on the balances and turnovers of both the cash register as a whole and for individual counterparties.

Requisites

Unlike dimensions and resources, props are not used as often. They store the data that is needed for the informative completion of the register (note). Data will be used as props if it is neither a resource nor a dimension.

Standard details

In addition to the usual requisites that the developer can add on their own, there are also standard requisites. You cannot delete or add new ones to this list. You can go to the standard details by clicking the button of the same name on the "Data" tab.

Aggregates

Aggregates are available only in accumulation registers with the “Turnover” type. You can access them from the Data tab. Aggregates are used in databases with large amounts of data to speed up reporting. We will not consider this mechanism in detail, since this is a rather extensive topic.

Movements in accumulation registers

Any accumulation register can have one or more registrars. A document acts as a registrar, during which a corresponding entry is formed in the accumulation register.

You can view the list of accumulation registrars on the tab of the same name in the form of the register itself. In this example, movements in the “Product Release” register can be generated when posting four different documents.

In the documents themselves, which are registrars, the list of registers by which they make movements is located on the "Movements" tab. A motion constructor is also available here, which will help in creating such procedures (posting and undoing). Comparison of document and register data is performed by comparing data types.

All changes made to the database are stored in the appropriate tables. For 1C, these are tables of documents, document journals, directories and registers. The types of 1C registers, the features and subtleties of their use will be discussed in our article.

Formation of records in registers

One of the first questions related to registers is: why?

Why do you need to create separate tables, often duplicating existing records?

The answer here is quite simple. Of course, it is possible to make complex and time-consuming queries to tables of source documents by listing the selection conditions, checking them for deletion marks and conduction, but it is much easier and less laborious to create a certain slice of the recordset directly when saving the document and store it in a separate table by accessing to him as needed.

Thus, we found out that one of the ways to create a register entry is to write using a registrar (document). This option is present in all types of registers.

The process of generating register entries based on a document is commonly called posting a document. An unposted registrar document does not have register movements; it is, in fact, a draft or a blank.

The second option for generating a record is directly, without creating a registering document. You can create records in this way only in information registers, while in the properties of the register the attribute "Record mode" must have the appropriate value (Fig. 1).

Common to all registers

The internal structure of any register can be shown in Fig.2

Fig.2

Let's consider it in more detail:

• Dimensions – record properties that determine in which sections important information is stored;
• Resources - they contain information that needs to be systematized;
• Requisites - record fields that contain additional information;
• Forms is a property that contains graphical information about the appearance of a list, element, etc. and their internal modules;
• Layouts - printed forms of registers.

Information registers

Since we are talking about information registers above, let's talk about them.

This is probably the simplest and most understandable type of registers. A regular table containing columns and columns that store information.

The list of important properties of the information register is small (Fig. 3), let's talk about the main ones:

Fig.3

1. Periodicity, it indicates the extent to which the uniqueness of the record is controlled (within a minute, hour, day, year, in accordance with the selected value, there cannot be two records with the same measurements), it can also take the value "By registrar", but for this you must select the appropriate recording mode;
2. The recording mode is actually a choice of two values: "Independent" and "Subordinate to the registrar".
   1. It is important to understand that the choice of an independent mode does not mean that a record cannot be formed by a document, only selection by registrar and control of record uniqueness by it will be impossible;
3. Allow totals slice of the first and Allow totals slice of the last: (combine two items into one) - when the corresponding checkboxes are selected, a request to the information register can be made on additional tables (Slice of the first and Slice of the last), which contain the corresponding data sets, as one of The parameters of these tables are the date on which it is necessary to make a selection of data.

Accumulation registers

We saw the structure of one of them in Fig.2. The main property that strongly affects the appearance of the register, as well as its internal structure, is the "Type of the register" (Fig. 4)

Depending on the requirements for the stored information, it can take on the following values:

• Remains;
• Turnovers.

In the first case, the database will contain information not only about the movements of resources in the context of measurements, but also about the type of operation (receipt or expense). In addition, when creating a query, an additional table containing the totals will be available.

One of the main problems that novice developers face when using the Balances and BalancesAnd Turnovers tables in queries is that when a query receives balances for a certain date, the data in these tables may differ. And here there is one nuance: when specifying a certain value as the end date of the period, the platform takes data from the Balances table without including this value in the selection period.

If you need data that includes the end of the period, you can:

• Use the table Balances and Turnovers;
• Make a selection for a date 1 second greater than the given one (i.e. not on 12/31/16 23:59:59, but on 01/01/17 00:00:00);
• Use the Boundary method, which helps to configure the option to include a point in time in the period under consideration (use case: Boundary(EndDate,Including).

Accounting registers

Sufficiently specialized registers, in their design resemble accumulation registers. The main difference from other types of registers of the 1C platform is the presence of the Chart of Accounts parameter in the property structure (Fig. 5).

Fig.5

The chart of accounts is a separate metadata object that requires a separate discussion. Depending on the chart of accounts, modern typical 1C configurations contain 4 main accounting registers:

1. Budgeting;
2. International;
3. tax;
4. Self-supporting.

The second parameter, typical for accounting registers, is “Correspondence”.

Checking this box allows you to create double entries containing the credit account AccountKt and the debit account AccountDt and the analytics (subconto) corresponding to these accounts. If the checkbox is not checked, only one account will be entered in the register entries.

Calculation registers

These are probably the most difficult registers to understand. Meanwhile, in their essence, they are very much like accumulation registers of the "Turnover" type.

The defining difference of the calculation register from other registers is the presence in its properties of the parameter "Plan of the type of calculation". In addition, the calculation register, as well as the information register, is periodic.

In each calculation register, the possibility of linking the entry with the time schedule specified in the corresponding information register can be enabled. This allows code to retrieve working time data.

In addition to the dimensions, resources, and forms found in other ledger types, calculation registers can be given a Recalculation object to store information about records that are out of date and need to be revised.

Their main use in typical 1C configurations is to register and facilitate work with accruals for employees of the organization.

Hello dear readers, today I got the idea to tell you about 1С registers. Yes, I have not yet written about reference books, documents, but we will consider this later.

And now let's look at the purpose of registers, types of registers, depict how registers look like, talk about their design and data storage.

Purpose of registers

Registers are intended to increase the speed of reading certain (analytical) indicators on the activities of the enterprise. Indicators are stored in registers in certain sections, taking into account the frequency. Those. registers store time, for example, you can find out the balance in the warehouse of a certain product a month ago.

Register- a structure of a certain type, which allows you to receive data at the same speed, regardless of the size of the database, without them the output of the result will be slow.

Scheme of using registers in 1C:Enterprise
Documents => Registers => Reports

It can be seen from the diagram that registers are the basis for reports, and data is added to registers only when documents are posted.

Types of analytical indicators

Registers are designed to store various indicators:
SAVING INDICATORS: Remains and Turnovers. Accumulation registers are designed to account for them.

• Accumulation register(residual). Increases on receipt and decreases on sale. At each point in time, the register must remember the balance of this product.

Some indicators can grow only in one direction (turnover indicator).

• Accumulation register(reverse). For example, sales data (negative sales cannot be). If a product is returned, it will be registered with a negative sign.

STATE INDICATORS, a characteristic of an object, a register of information is intended.

• Information register, has absolutely any data type. This value is valid until the next state change.

There are other types of registers: Registers of accounting, Calculation.

Image registers

The indicators that are stored in the register are called resources. Analytical sections - measurements.
key register task– storage of indicators in the context of measurements and in terms of time

If we represent the register as a coordinate system, we get:
Axes - register dimensions
Separate axis for changing over time
Intersection Indicators - Resources

As an example, the figure shows the storage of goods in warehouses

The number of measurements in the register can be quite large. In this case, the register can be represented as a table, where each column corresponds to a dimension and there are also columns with resources.

Register: Remaining goods
Measurements: Commodity, Warehouse
Resources: Quantity, Cost

Register design

The excessive structure of the register leads to an increase in the volume of the database, the time it takes to write to the register increases (documents are processed longer), and the complexity of development increases. The structure of the register must be determined immediately, because its change in the future is a very complex process.

When determining measurements It is recommended to indicate the reference data type (for directories, documents, enumerations). Order measurements affects the speed of obtaining data from the register. Therefore, when designing a register, it is important to understand how often the user will receive residuals for each dimension. All measurements are in order.

Resources- numerical data that characterize some indicators (the resource of the information register can have any type of data). Resources are values ​​that are stored at the intersection of a dimension.

Props- some additional characteristic of the register, is not displayed when receiving the totals. Register details can be of any data type.

Storing data in registers

Registers can be represented by one or more tables. The composition of the tables depends on the properties of the register. There are no classes such as RegisterReference or RegisterObject to work with registers, because the register storage structure is different from object tables. The information is stored in the form of records. A register entry cannot be marked for deletion.

You can view the contents of the register in user mode using the main menu button "All functions".

This is the end of the little review. 1С registers if you have any suggestions, additions then write in the comments.

1C Programmer was with you, I will help with .

P.S. The girl is dancing