Let's solve the following problem. It is necessary that when creating or changing a Service Provision document and when adding it to tabular part any nomenclature of services, at the same time the current price obtained from the information register and corresponding to the date of the document would be immediately inserted.

To do this you need:

write a RetailPrice function that will return the current price;

call the function when a new item is added to the document.

Since such a function may be needed in the future, we will write it in a “publicly accessible” place - in a common module.

1. Create a function Retail price in the General branch – General modules with the name Working with Directories. Set flag Server call(so that export procedures and functions of this module can be called from the client) (Fig. 71a).

Rice. 71a. Common Module Properties

2. Place in module next text.

Function RetailPrice(CurrentDate,ServiceNomenclatureElement)Export

//Create an auxiliary object Selection

Selection = New Structure("Service Nomenclature",Service Nomenclature Element);

//Get current registry resource values

ResourceValues=InformationRegisters.Prices.GetLast(CurrentDate, Selection);

ReturnResourceValue.Price;

EndFunction

Let's explain the function.

To get the retail price, you need to pass two parameters to the function:

CurrentDate– a parameter of the Date type, defines the point on the time axis at which we are interested in the value of the retail price.

This structure contains Selection according to register measurements. With its help, the required dimension of the Nomenclature of Services register is determined, which is equal to the reference to the directory element passed to the function.

Structure key name ("H Nomenclature of Services") must match the name of the register dimension specified in the configurator, and the value of the structure element ( Element of Nomenclature of Services) specifies the value selected for this dimension.

The second line contains a call to the information register manager Prices ( Registers of Information. Prices) and method execution RunLast(), which returns the value of the resources itself last entry register corresponding to the date passed to the function ( CurrentDate) and register measurement values Selection.

Method GetLatest returns a structure containing resource values, which is stored in a variable ResourceValues.

In the next line we get the required retail price, indicating the name of the desired register resource separated by a dot ( ValueResources.Price) and return it when the function is executed.

That. this function can be called at some point in time.

Need to open Document form Execution of Services(it was created earlier). IN Form module necessary add Procedure When Changing.

To do this, double-click on the form element List of Items and Services (Fig. 71b).

Rice. 71b. Elements of the Service Performance document

In the properties window in the event When it changes create an event handler with the following text:

&OnClient

Procedure List of Nomenclatures Service When Changing (Element)

//Get the next row of the table section

TabularPart Row = Elements.List of Nomenclature.CurrentData;

//Set price

TabularPart Row.Price = Working with Directories.RetailPrice(Object.Date, TabularPartLine.Service);

End of Procedure

Let's explain the function.

The first line is already known: first we get the current line of the tabular part of the document, because we will need it later and save it in a variable RowTabularPart.

Then we call the function Retail price() from a common module Working with Directories.

The first parameter to the function is passing the date of the document for which you need to get the price. We get the document date from the main details of the form - Object.Date.

The function returns the last price value, and this value is assigned to the Price field in the current row of the tabular part of the document ( RowTabularPart.Price).

Please note that the procedure List of NomenclaturesServiceWhenChanged() starts working in the form module on the client side, because This is the form's interactive event handler. When creating a template for this procedure, the platform will automatically place a compilation directive before the procedure description &OnClient.

Then we call the function Retail price(). Because this function will not be found on the client side, then the execution will be transferred to the general module Working with Directories, which runs on the server. After the function completes program code will continue to execute on the client.

Let's check the above steps and launch the application in 1C: Enterprise mode. Let's work with the developed configuration.

ENTERING DATA INTO THE DB

1. Enter at least 8 documents Provision of services into the application.

Task 1. Creating a drop-down list

1. Open the file Ex03_1.xlsx.

2. On a sheet Price There is a table of goods with prices.

3. Give names to the ranges with the product name ( Product) and price table ( Table) (picture 1). Range names are specified for their further use in formulas and to make working with ranges easier. The range name can be set through the cell name field or select the command in the context menu Assign a name(of course, in one case or another, a range of cells must be previously selected, as shown in Figure 1).

Picture 1

4. On the sales sheet in the column Name of product create a dropdown list and fill it with data.

To create a drop-down list, you need to select the cells (you can have several at once) in which you want to get a drop-down list and select on the tab Data button Data checking (Figure 2).

Figure 2

On the first tab Options from the dropdown list Data type select an option List and enter in the line Source= and the name of the range with the required data (i.e. =Product) (Figure 3). Click OK.

The result of creating a drop-down list is shown in Figure 4.

If the set of values ​​in the source may change, it is better to insert or remove data in the middle of the list.

Figure 3

Figure 4

Introducing the MATCH and INDEX functions

The MATCH and INDEX functions are mainly used to automatically substitute data into a table from a given range.

Function syntax

MATCH(lookup_value, array, match_type)

Array is a block consisting of one column or one row.

Match_type is the number -1, 0 or 1.

If match_type is equal to 1, then the function SEARCH finds highest value, which is less than lookup_value or equal to it. Viewed array must be ordered in ascending order.

If match_type equals 0, That MATCH function finds the first value that is exactly equal to the argument search_value. Viewed array may be disordered.

If match_type is equal to -1, then the function SEARCH finds the smallest value that is greater than search_value, or equal to it. View_array must be ordered in descending order.

If match_type is omitted, it is assumed to be equal to 1 .

Function SEARCH returns the position of the searched value in the array, not the value itself.

Function INDEX has two forms. We will consider only one.

INDEX(table; row_number; column_number)

This function selects from a rectangular block (table) an element specified by row and column numbers, and these numbers are counted from the top left element of the block.

Let's look at how these functions work using specific examples.

Task 2. Introduction to the MATCH function

Using the MATCH() function, determine the position of the product with the name “Yogurt” in the range Product. For this:

1. in e-book Ex03_1.xlsx go to sheet Price and place the sheet cursor in any free cell;

2. enter the formula =MATCH("Yogurt";Product;0) into this cell, i.e. We indicate using this function that we need to find the element “Yogurt” ( text value in functions is always indicated in quotes) in the range Product, matching type 0 – exact match;

3. Press Enter. As a result, you should get the number 7 (Figure 5).

Figure 5

Indeed, if you look at the range Product, then the position of the yogurt corresponds to this figure. It is worth remembering that the position of the value in the array, i.e. in a given range, counted from the beginning of the range. It turns out that if we add at the beginning of the table with data new line, then the value “Yogurt” will move to line number 8, but the range Product starts with the value “Milk”, so from the beginning of the range the position of yogurt will in any case correspond to the number 7 (Figure 6).

Figure 6

Please note that the value for the MATCH() function can be specified either the value itself or the name of the cell in which this value is located. For example, the entry =MATCH(A7;Product;0) will provide the same result as when using the entry =MATCH("Yogurt";Product;0).

Task 3. Introduction to the INDEX function

Let us recall that the INDEX() function selects from a rectangular block (table) an element specified by the row and column numbers, and these numbers are counted from the top left element of the block.

Use the INDEX() function to find in a range Product element located in the fifth position. For this:

1. in an e-book Ex03_1.xlsx on a sheet Price place the sheet cursor in any free cell;

2. Enter the following function in this cell =INDEX(Product;5). Press Enter. The result is presented in Figure 7.

Figure 7

Note that we only specified the range values ​​and row number for the INDEX function. We did not indicate the column number. This parameter is optional if we are looking for a value in a table (range) consisting of one column. If we need to find a value in a table (range) consisting of several columns, then in the formula, for a more accurate search for the value, it is necessary to indicate the column number.

In the new cell, enter the formula =INDEX(Table;8;2). As a result, you will find the cost of ham (Figure 8).

Figure 8

Automatic data substitution

When working with a drop-down list, you can automate data entry in the table. For example, there is a price list containing the names of goods and their prices. You can organize the selection of the product name from the list and automatic substitution of the product price in the final table.

Automatic substitution of data from a lookup table is based on the combined use of the INDEX and MATCH functions.

Task 4. Combining the MATCH and INDEX functions

In e-book Ex03_1.xlsx on a sheet Sales in a collumn Price, using MS Excel functions, provide automatic substitution of the cost of goods from the sheet Price, depending on the value selected from the drop-down list on the sheet Sales.

1. Place the worksheet cursor in cell C2 and enter the following formula into it:

2. Press Enter. As a result, you will receive in cell C2 the cost of the product whose name is indicated in the adjacent column (Figure 9). When you select another product name from the drop-down list, the price value will change accordingly.

Figure 9

3. Using the autocomplete marker, duplicate this formula to the end of the table (Figure 10).

Figure 10

Let's decipher this formula. This formula starts with the MATCH function, which allows us to find the position of the value from cell B2 on the worksheet Sales in the range Product from sight Price, and precision type 0. For example, if you use the list of products, which is presented in Figure 10, then the SEARCH function looks for the position of bread, the name of which is indicated in cell B2 from the column Name of product in the range Product on a sheet Price. This position will be equal to 3. Therefore, in the computer memory the formula we entered is

=INDEX(Table, MATCH(B2, Product, 0), 2)

will take the form

=INDEX(Table,3,2)

Parameter “3” is the result of the SEARCH function.

Next, the INDEX function comes into play, which will search in the range Table on a sheet Price the value located at the intersection of the third row and the second column. (Please be reminded that the range Table consists of two columns). This value will be the cost of the product, in this case bread = 27.

Task 5. Design of the final table

On a sheet Sales in a collumn Quantity enter arbitrary values. Calculate the values ​​in a column Total.

Task 6. Independent task

Open the file Frames.xls . It is required to automate changes in salaries on a sheet Personnel .

For example, it is necessary to transfer some of the managers and transfer senior managers or consultants to loaders. You need to perform this operation for four employees. Along with the position, the salary should also change.

Create a new sheet and name it State .

Copy to sheet State columns Job title And Salary.

Remove duplicate employees. For this:

1. Select the position and salary columns.

2. On the tab Data click the button Remove duplicates .

3. Remove the check mark from the column Salary.

4. Click the button Ok.

In order to be able to change positions, on the sheet Personnel In the position column, create a drop-down list. In this case, you will also need to change the official salary; to do this, we will automate this process so that when you change your position, Excel itself changes the salary. To do this, we use the functions: SEARCH and INDEX.

Guidelines

1. Select on the sheet State range of positions. Give the selected range a name job title.

2. Select on the sheet State salary range. Give the selected range a name salary.

3. On a sheet Employees in column Salary enter the formula that would look for on the worksheet State in the range job title position corresponding to the employee’s position, and then from the range salary sheet State inserted a salary corresponding to the position found.

4. After entering the formula, make sure that the resulting salary actually corresponds to the salary of the employee’s position from the sheet State.

5. Change the salary of Ch. accountant on sheet State. Check whether the corresponding salary has changed on the sheet Employees.

Our task is the following. We now store the price of an item in a separate information register. When we create or change a Service Provision document and add any item to the tabular part, we want the current price of this item, obtained from the information register and corresponding to the date of the document, to be immediately inserted into the document.

To do this we need to do two things.

First, write a certain function that will return the current price of the item, and then call this function at the moment when the item is added to the document, and substitute the price of the item that this function will return into the document.

Since we will most likely need such a “service” not only in this document, but also in other documents that contain nomenclature in the tabular part, we will place the function in some “publicly accessible” place - in a common module.

In Configurator mode

Function returning the price of an item

First, we will create the RetailPrice() function, which will return us the current retail price of the item, and place it in the general configuration module.

Let's open the configurator, in the General General modules branch, add a new configuration object Module and call it WorkWithDirectories.

We see that the module has the Server checkbox selected by default. This means that instances of this module will only be compiled on the server side.

Let's check the Call server checkbox so that export procedures and functions of this module can be called from the client (Fig. 9.9).

Let's place the following text in it (Listing 9.1).

Let's explain this function.

To get the retail price, we will pass two parameters to the function:

CurrentDate is a Date type parameter that defines the point on the time axis at which we are interested in the retail price value.

In the body of the function, we first create a Selection helper object.

This is a structure containing selection by register dimensions. With its help, we determine that we will be interested in register entries in which the dimension of the Nomenclature register is equal to the reference to the directory element passed to the function.

The name of the structure key ("Nomenclature") must match the name of the register dimension specified in the configurator, and the value of the structure element (Nomenclature Element) specifies the value selected for this dimension.

In the second line, we contact the manager of the Price information register (Information Registers.Prices) and execute the GetLast() method, which returns us the resource values ​​of the latest register record corresponding to the date passed to the function (CurrentDate) and the values ​​of the register dimensions (Selection).

The GetLast method returns a structure containing resource values, which is stored in the ResourceValues ​​variable. Generally speaking, a register can have multiple resources. There is only one resource in our register, but a structure containing a single element will still be returned.

Therefore, in the next line we get the retail price we are looking for by simply specifying the name of the register resource we need through a dot (ResourceValues.Price) and return it when the function is executed.

Now this function needs to be called at some point in the document's execution.

Calling a function when selecting an item and filling in the price in the document

So, the task that faces us is the following. When editing the Service Provision document, we need to ensure that the Price field is automatically filled in after the user selects a service. Moreover, the price of the service should be determined based on the date of the document being created.

Let's find the Service Provision document in the configurator and open its form Document Form.

Double-click on the form element List of ItemsNomenclature or right-click to open the properties palette for it (Properties context menu item). Scrolling to the end of the list, we find the OnChange event, which occurs after a field value has changed.

Click the open button B with the magnifying glass icon in the input field.

The system will create a template for the handler procedure for this event in our form module and open the Form Editor Module tab.

Let's add the following text to it (Listing 9.2).

Let's comment on the contents of the handler.

The first line of the handler is already familiar to you from the procedures List of Items Quantity on Change and List of Items Price on Change. First, we get the current row of the tabular part of the document, since we will need it later, and save it in the TabularPart Row variable.

Then we call our RetailPrice() function from the general module Working With Directories.

As the first parameter, we pass to this function the date of the document for which we need to get the price. We get the document date from the main form attribute - Object.Date.

The function returns the last price value, and we assign this value to the Price field in the current row of the tabular part of the document (TabularPartRow.Price).

Note that the Item List NomenclatureOnChange() procedure itself starts working in the form module on the client side, since it is a handler for the form's interactive event. When creating a template for this procedure, the platform automatically placed the &OnClient compilation directive before the description of the procedure.

Then the RetailPrice() function is called. Since this function will not be found on the client side, execution will be transferred to the common module Working with Directories, which runs on the server. After the function completes, the program code will continue to execute on the client.

Why was such a “trick” used in this case? Why was it necessary to transfer code execution to the server?

The fact is that any work with the database (reading data, writing) is possible only on the server. In this case, we needed to read the latest data from the information register for some item.

Such actions can only be performed on the server, and if you look in the syntax assistant for the description of the GetLast() method of the information register, you will notice that this method is only available on the server, in the thick client and in the external connection.

Thick client and external connection are client applications previous version platforms that exist for compatibility with legacy application solutions.

We are developing a completely new application solution that works in thin client or in the web client. Therefore, in our case, to obtain any data from the database, we need to transfer code execution to the server, receive the necessary data there and return this data to the client. That's what we did.

In 1C:Enterprise mode

Let's check how our document works now.

Let's launch 1C:Enterprise in debug mode and open the Prices information register.

For the Philips transistor, let's add a new price with a different number (Fig. 9.10).

Now let’s open the document Provision of Service No. 1. As you remember, with this document we just “used up” one such transistor.

Let's leave the date of the document unchanged and repeat the selection of the transistor in the Nomenclature column of the tabular part of the document. The transistor price will be automatically set to 07/01/2009. This is the latest price value on the document date (Fig. 9.11).

Now let's change the date of the document to 07/13/2009 and repeat the selection of the transistor again. A new price value will be set, the latest for this date (Fig. 9.12).

Thus, the price of the service that is current at the time the document was created appears in the document.

Control questions

1. What is the purpose of the Information Register configuration object?

2. What features does the Information Register configuration object have?

3. What are the main differences between the information register and the accumulation register.

4. Which fields determine the uniqueness key of the accumulation register.

5. What is a periodic register of information and what is an independent register of information.

6. How to create a periodic register of information.

7. What is the leading dimension of a register.

8. How to obtain the resource values ​​of the most recent registry entries using the built-in language.

/
Accounting for Ukraine /
Working with documents, reference books and reports

How to set the main organization for substitution in documents

This section describes how to set up a primary organization for automatic substitution in new documents.

Installation from the directory

In the directory of organizations (menu "Enterprise" - "Organizations") the main organization is highlighted in bold. This means that when creating new documents it will be inserted automatically.

To make another organization the main one, you need to select this organization in the list and click on the button Set main in the command panel.

Connect Code Preoix Name l w 00000001 to Dobro 000000001 DB Do5ro5ut 00000006 DU Do st eka and installation 00000005 NF National S0000001 nsho Our organization" width="757" height="216" border="0">

NOTE
The primary organization is saved in the user settings. For each user, the main organization is indicated individually.

Selection of documents by main organization

In the user settings (menu "Service" - "User Settings") you can set the mode for automatic selection of documents for the main organization. This means that all journals and document lists will only show documents from the primary organization.

The selection of documents in journals is controlled by the flag Accounting for all organizations. When it is installed, the journals display documents for all organizations. When the flag is cleared, only documents for the main organization are displayed in the journals.

If necessary, the main organization can be changed directly in the user settings in the "Basic values ​​for substitution in documents and directories" group.

Very often, while working in the 1C program, you need to quickly enter several documents or add new objects to directories (type of item, organization, warehouse, cash register, etc.). At the same time, not everyone has an understanding of how to automate the entry of basic values, so that when creating them, the required values ​​are immediately substituted into the fields. I will tell you how to do this in this article.
The 1C program has mechanisms that allow you to quickly fill in the values ​​of details in documents and reference books. Based on their action, they can be divided into the following types:

• Single value substitution
• Substituting a value by selection
• Filling out statistics details
• Substitution of previously entered details
• Substituting values ​​using a pattern
• Entering a new document by copying

Substitution of a single value for which the user has rights.

Let's say your organization has three warehouses, they are entered into the database. At the same time, the manager is allowed to sell goods only from one. When creating a new document, this warehouse will be inserted automatically. This works based on the user's set permissions. That is, by turning off rights to objects that the user does not work with, you can automate data entry.

Substituting a value based on the selection specified in the list form.

If the list is set to select by some detail (cash register, bank account, warehouse), then this detail will be automatically filled in in the new document. For example, you set selection by cash register in the form of a list of cash register documents. When you issue a new payment document, information about the selected cash register will be automatically taken from the list. And since an organization is specified for the cash register, information about the organization will be automatically filled in. You can use selections in any list of documents.

Filling in statistics details.

The 1C program keeps statistics on the entry of basic values ​​for each user. If you entered the same organization, division, warehouse, business transaction, etc. twice when creating a new document, the program will analyze the last entered values. Thus, the statistical filling algorithm automatically “adjusts” to each user.
If the user violates this chain in a new document, the program will understand this and will begin to substitute new values ​​after a while, but in the first such document the values ​​will not be substituted.
Filling out previously entered details when selecting the value of another detail is possible if you select, for example, a trade agreement or contract where all the main fields are filled in (warehouse, payment schedule, prices, etc.). When specifying an agreement or contract in a document, the details will be filled in according to the following rules:

all the data that was specified in the agreement or contract will be filled in;

if any value (for example, division) was not specified in the agreement or contract, then this value will be filled in according to statistics. If, when entering a new document, the values ​​were filled in in accordance with statistics, then they will be refilled if they are defined in the agreement or contract.

Substitution of values ​​using a template.

Please note, when a new type of item is created, you can specify default values ​​that will be substituted into new objects. When registering a new item for this type of item, these values ​​will be entered automatically. Another example would be the automatic substitution of information about a department into a document if the user drawing up the document has information about the department defined in the user’s card.

Entering a new document by copying.

Everything is simple here. If the user has already entered a similar document, then it can be copied and then the changed data can be corrected. Check all the details, paying special attention to details such as the date.

Here are five main ways to make it easier to enter similar documents and reference books. Be sure to use them in your daily work

How to quickly enter the value of details in 1C?