In addition to primitive data types that can be found in any programming language, there are unique types in 1C. Each of them has its own properties, methods, functions, purpose and nuances of use in the system. One of these types is a dynamic list, which greatly simplifies many applied tasks. That is why developers should know and be able to handle this versatile tool.
Features of dynamic lists in 1C
purpose of this type is to display information from any database tables, regardless of its type. The mechanism was created on the basis of the SKD and has similar capabilities. But this does not mean that you will definitely have to write a query in the 1C language, although this possibility exists and you need to use it. You can simply specify the table, the information from which you are interested in, and 1C will independently generate a simple query.
To see how a dynamic list is formed and what data it shows, you need to open managed forms, where it is located, in the configurator: in the list of details via context menu open its properties and pay attention to the item "Arbitrary Request". If there is no checkbox, then the "Main table" parameter reflects the database table where the data is taken from. Otherwise, the dynamic list reflects custom query data, which can be seen by opening the list setup.
An arbitrary query schema is much more commonly used, as it provides a great opportunity to combine and display a wide variety of data. Most often, this mechanism is used to reflect stock balances, item prices, income, expenses, or purchases. You need to use it carefully, as the performance may drop with complex queries.
Another useful feature dynamic list opens when you click on the inscription "List settings". This menu allows, even when using a standard set of fields, to make information more accessible and understandable to end users. Whether it's an arbitrary request or not, you'll see a "Settings" tab where you can specify:
- Selection of a dynamic list;
- groupings;
- sorting;
- Decor.
The use of parameters makes dynamic lists versatile and flexible enough. You can also bind them to props on a managed form, and the data will change depending on the options selected by the user. The use of these mechanisms can be understood and appreciated by looking at examples of real-world problems.
As an example, consider the problem of reflecting the remnants of the nomenclature on a managed form. In real practice, such orders are quite common in various configurations, and the dynamic list is ideal as a tool. For this task, we will have to use an arbitrary query, dynamic list parameters and its settings.
For greater clarity, we will create a separate external processing and place a dynamic list on it. To implement our plans, there will be few tables with nomenclature, so we need to allow an arbitrary request. In it, we will describe the left connection of the reference book with the list of nomenclature and register of residues and set the reference book as the main table. Such a scheme will allow users, while working with a dynamic list, to add or change the nomenclature.
Since the CurrentDate parameter was used in our request, we need to set its value before using the processing. To do this, in the form module in the procedure "On CreationOnServer" by a standard command, assign the function "CurrentSessionDate" to it with a standard command. We also need to display a dynamic list on the control form and change the order of the fields for clarity. Drag the props "Nomenclature Remains" into the form elements (upper left) and use the blue arrows to change the order of the fields in the table on the form.
&At Server Procedure On CreationAt Server(Failure, StandardProcessing) Nomenclature Remains.Parameters.SetParameterValue("CurrentDate",CurrentSessionDate()) EndProcedure
Already at this stage, we can open our external processing in 1C and see that the dynamic list is working. We can look at the balances, create nomenclature and groups, and search. Often customers ask to add the ability to choose the date for which they will see the balance. In the case of a form with a dynamic list, this is provided by additional field and setting parameters with it.
We add the attribute “Remains Date” of the “Date” type and transfer it to the form elements. In the field events, we create an event "OnChange" and write the code for setting the "CurrentDate" parameter used in the dynamic query. So that when opening the form, the user immediately understands on what date he sees the balances, we will make small changes to the OnCreateOnServer procedure.
As a result, our Dynamic List form can display balances for any date.
We have covered only a small part of the capabilities of this toolkit, but this is already enough to understand the convenience of this type of dynamic list. A similar mechanism is used for a variety of tasks, but it is most often found in typical configurations V managed forms:
- selection;
- Lists.
To get a dynamic list and its request in typical managed forms, the developer needs to open the required form in the configurator. In the attributes section, find the attribute with the "DynamicList" data type (most often it is in bold). Its properties contain the text of the request, selections and other settings.
The 1C:Enterprise 8.2 platform can work with multiple records in a database table in a dynamic way, that is, it can read data in chunks. Earlier in the articles, we considered the mechanism of dynamic lists and methods for optimizing work with them.
Today we will solve a non-standard problem for dynamic lists. We will need to calculate the total for the document attribute "Amount" and display it in the footer of the list. Similarly, calculate the average value for the "Rating" field and also display it in the footer of the dynamic list. The calculation of the total fields must take into account the selection set by the user in the document list settings.
The whole difficulty lies in the fact that the dynamic list does not receive all the records at once, but receives them in portions. Accordingly, we cannot immediately get the total for all documents corresponding to the current selection. How to calculate the total?
Implementation
And so, let's move on to solving the problem. Let's start with changing the form, then we will describe the algorithm for obtaining the final values.
Form and interface
First, let's prepare a document form to display the final fields. To do this, let's add two string attributes of the form "Rating" and "Amount".
These details will contain the total values for the documents.
To display attribute values in the footer of a dynamic list, you must enable the corresponding option of the associated list form element (see the following screenshot).
Now you need to decide on which event the results will be updated in the footer of the list. For the convenience of development, let's add the "Update" command and the corresponding form element to the command panel. When this command is executed, the totals will be updated.
In the test configuration, which you can download from the link at the end of the article, I also added a totals update event when writing a document. This uses the form notification mechanism. We will not dwell on this in more detail.
Algorithm
The most problematic part remains - you need to get the values \u200b\u200bof the totals. Let's proceed as follows: we will form a database query to obtain the values of the final fields in accordance with the selection set in the dynamic list. It should be borne in mind that in the selection there may be a complex condition from the groups.
Note: Dynamic list settings (see screenshot above) are based on the mechanisms of the data composition system (ACS) . Accordingly, you can work with them in a similar way (programmatically adding, changing, reading, etc.).
The steps for generating a query to get the results are as follows:
1. Get the initial request for a dynamic list.
 |
As we can see, the request selects all the details of the document. For a little complication, I added my own field "LevelRating", formed by the "CHOICE" construct.
2. We form the text of the request conditions ("WHERE" section) and substitute it into the original request.
We need to add conditions to the received source text of the request in accordance with the configured selection of the dynamic list.
The procedure, depending on the type of the passed filter element (group or filter element), generates the appropriate text of the condition. All conditions in the group are framed by brackets, the members of the group are also framed by parentheses. The conditions between expressions depend on the parent group (the "AND" condition is placed between the top elements in the hierarchy).
If the element has a usage flag (property "Usage") then the element is processed. The generated text also depends on the comparison condition (Equal, not equal, in the list, etc.). The dependence of the generated text of the condition on the type of comparison can be seen in the following function.
Another interesting, in my opinion, function is "GetFieldTextOnView". It is needed in order to substitute fields into the query conditions that are formed by expressions of the query language. Above, I added the "LevelRating" field to the original request. If the user will use it in the selection, then the entire expression must be substituted into the query condition. This function gets the text of the field from the request by its representation. For such complex fields, it will return the entire text of the expression.
See the test configuration attached to the article for more details on the algorithm. Below is a screenshot of the selection settings and the query conditions generated for them.
The generated condition text is attached to the original dynamic list request. The query result is placed in a temporary table.
3. We place the first query in a temporary table and perform grouping by summary fields with the necessary aggregate functions.
Let me remind you that we need to get the average value for the "Rating" field and the total amount for the "Amount" field. We have already formed a query taking into account the selections, it remains to calculate the total values. This is done with the following query:
After executing the request, we process the result, return it to the client and write it to the form details that we created earlier. Ultimately, we got the display of the totals in the footer of the dynamic list (see the first screenshot for the article).
Solution optimality
In general, this approach can adversely affect performance. For example, if the program will calculate the total without the established selection in the dynamic list, then the number of selected records can be huge (for example, for several years). As a result, each update of the results can last tens (!!!) seconds. Therefore, it makes sense to calculate the totals only if a selection has been established. This condition put in a test configuration.
Plus, the calculation of the results, we can make a request only on the server side. Therefore, you need to seriously approach the request to the server, since the procedure for updating the totals can be performed very often. Imagine a register of KKM checks in a trade organization, where up to 5 checks can be entered per minute, and up to 300 checks per hour. Each time after the document is written, the totals update will be called. Therefore, it would be reasonable to reduce the transmitted traffic by using out-of-context procedures.
The following screenshot shows programming code calling an out-of-context server function that returns totals.
The first parameter is the selection of a dynamic list, the second is the structure of the type "FilterFieldNameFilterFieldValueType". Note that the first parameter in the function is received as a standalone value. I can’t say exactly why, but if you pass the selection as a link, the platform gives an error that the selection cannot be changed. The error could only be bypassed in this way.->
Note: the use of out-of-context procedures allows you to reduce the amount of transmitted traffic by several times, since form data is not transmitted to the server, unlike context server procedures (the "&AtServer" directive).
Conclusion
In conclusion, I will say that such an approach for calculating totals in a dynamic list is incorrect from the point of view of the development methodology. It would be correct to receive a total on registers. For example, in the journal of checks, again, we can get the amount of money in the current cash register either according to documents or according to the corresponding accumulation register.
Plus, the use of accumulation registers will allow you to calculate the totals without taking into account the selection, since the system works optimally with the already calculated totals for the past months.
However, the method described in the article takes place when solving problems.
Downloads:
Home Notes from the Looking Glass04/21/2014 Getting dynamic list data
Implemented in version 8.3.6.1977.
We have implemented the ability to easily and conveniently obtain data displayed using a dynamic list.
You may need the dynamic list data in order to print it in a non-standard, "specific" form. Or in order to perform certain actions with them. For example, send a letter to all counterparties that you have selected in the list according to some criteria.
In addition, there are a number of tasks in which the user, in addition to the list of elements, also wants to see the summary data associated with this list. For example, having selected the goods of a certain product group and a certain supplier, he immediately wants to see the total number of such goods in the database.
The dynamic list itself cannot provide you with this information. The purpose of a dynamic list is to provide quick view large amounts of data. Therefore, it reads the data in chunks required to display on one or two screens. And “knows nothing”, for example, about the total amount of data that he has to read.
In general, in order to get Additional information that the user wants, you need to query the database. Exactly the same as the one used in the dynamic list.
You could do it before. But it wasn't always easy. After all, in addition to the text of the original query, according to which the dynamic list works, you needed to know all the selections, sortings, and other parameters that the user interactively set in the table that displays the data.
Now this task is easily solved. The dynamic list table has two new methods:
- GetExecutableDataCompositionSchema();
- GetExecutableDataCompositionSettings().
Thus, you get the data composition scheme itself, and, most importantly, all its settings, thanks to which the user sees the list exactly as it is. All you have to do is programmatically lay out the layout and output it to a collection of values (for software processing) or in spreadsheet document(to display):
As a result, you will get a structure (or report) containing columns and rows that are displayed in a dynamic list table.
The important point is that the layout and settings that you get from the dynamic list table take into account, among other things, the visibility of the columns and the search applied. Since the settings are obtained separately, you can change the composition of the fields for your own purposes and get, for example, all the columns of the list, and not just those that are visible to the user.
When outputting to a spreadsheet document, there is another nice moment. In general, the appearance of the report will correspond to appearance dynamic list tables at the time the schema and settings are received. Including the conditional design of the table. You will need some additional actions only if you want to transfer the conditional design of the form to the report as well.
In the process of finalizing configurations, each 1C programmer encounters dynamic lists.
A dynamic list is an interface object used to display various lists of database objects or non-object data - register entries.
For example, a dynamic list is used to display a list of items:
To demonstrate the possibilities of a dynamic list, let's create an external processing, add the main form. Let's add a new attribute with the "Dynamic List" type to the form. Let's go to its properties and see what's there.
We are interested in the "Custom request" property. Enabling it will show us all the possibilities of a dynamic list. We will be able to write a query using almost all the features of the query language of the 1C: Enterprise system. Check the box and click the "Open" link:
By default, our list will display a list of items with a total balance for all warehouses. To implement such a list, add the following query:
As the main table, we will select "Directory. Nomenclature", this will allow us to work with the dynamic list, as with the list of nomenclature - add, change, mark for deletion elements of the directory. Also, setting the main table makes available the possibility of dynamic data reading - this means that the selection will be made in portions, as needed.
Next, we need to create form elements for our list:
If we try to run our processing in this form, we will get an error:
To eliminate it, you must set the value of the "Period" parameter. To do this, you can use the "SetParameterValue" method of the "Parameters" collection of the dynamic list. The method takes two parameters:
. "Parameter" - Type: String; DataCompositionParameter. The name of the parameter or data composition parameter whose value is to be set;
. "Value" - Type: Arbitrary. The value to be set.
Can be called in the "OnCreateOnServer" handler of the form:
Do you have a question, do you need the help of a consultant?
Let's give the user the ability to change the period for receiving balances. To do this, let's add the attribute and the "Date" form element associated with it:
In the "OnChange" handler of the "Date" form element, call the "SetParameterValue" method, passing the value of the associated attribute as the value. Similarly, we will change the procedure "On CreationAtServer" of the form. Since the method is available on the client, there is no need to call the server:
Now, when the date changes, the balances will be automatically updated:
Suppose users want to see either current balances or projected receipts. Let's consider one of the implementation options. Let's add a Boolean form attribute and an associated radio button:
When changing the value of the switch, we will change the text of the request. To do this, we will use the event handler "OnChange" of the form element "DisplayQuantityToReceipt". We need to change the "QueryText" property of the dynamic list depending on the value of the attribute. Since this property is not available on the client, a call to the server procedure is required:
The result of the changes made: 
Finally, the dream of any "seven-player" has come true. How often users of program 7.7 asked to make a normal selection of items. So that you can see the balances, prices, and set filters. I had to come up with various tricks, up to writing external components. In 1C 8.2, dynamic lists appeared. I propose to consider what it is and what they can give us in 1C 8.3.
Let's take as a basis some test configuration 1C: "Enterprise accounting 3.0". We will not make a selection now, just add another selection form in the "Nomenclature" reference book and make it temporarily the main one:
When created, the system will by default add a table field with the "Dynamic list" type to the form.
Let's go to its properties and see what's there.
First of all, we are interested in the "Arbitrary request" checkbox. It is he who will reveal to us all the advantages of a dynamic list. We will have the opportunity to write our own query, and with parameters. Check the box and click the "Open" link:
A window will open with a ready-made code for . So far, all the fields of the "Nomenclature" directory are simply listed there.
Get 267 1C video lessons for free:
As you can see, there is a call button "" and a checkbox that allows you to dynamically change the contents of the list. That is, when another user changes something in the directory, it will also change in our list. In addition, there is a Settings tab, but we will touch on that later.
Custom query in dynamic list
First, let's create the query we need with balances and prices. Like that:
Settings tab
And now the most delicious! Go to the "Settings" tab. And we immediately see that on the first tab we can make any selections for any field in the request:
Programmatically setting query parameters in a dynamic list 1C 8.3
Do not forget that we have two parameters in the request: "Period" and "Price type". We must pass them to the request, otherwise there will be an error.
Let's write these parameters in the form parameters, and add the following lines in the form module:
&OnServerProcedure OnCreateOnServer(Failure, StandardProcessing) List. Options. SetParameterValue("Period", Parameters. Date) ; List. Options. SetParameterValue("PriceType" , Parameters.PriceType) ; EndProcedure
Loading...