As you know, a dynamic list is convenient and useful for quickly displaying large lists. It is quite flexible, allows you to modify its query, and even create your own complex query. Thanks to this, you have the ability to display in the list different additional data associated with the master list data.
This versatility, however, has a downside. The desire to show “everything at once” may make your query much more complex. As a result, you'll get inefficient query plans and, subsequently, a decrease in performance when displaying and scrolling through the list.
In addition, there are a number of application tasks, whose algorithm of selection and formatting of the list data is quite complex. In these cases, there are not enough features for ad hoc queries and conditional formatting. We would like to have a more “flexible” and “precise” tool that would let us do “anything” and “only where needed”.
OnDataGetAtServer event
For this purpose, we have implemented a new handler OnDataGetAtServer(). It was added to the extension of the table that displays a dynamic list.This handler is called on the server after the dynamic list gets and formats for display the next portion of data.
An important feature is that this handler does not have access to the form context. You can only manipulate the collection of those rows of the list that were received from the database, already filtered and formatted (conditional appearance) according to the settings of the dynamic list.
You can change the data in these rows, or apply your own formatting to the cells. Available formatting features are BackColor, TextColor, Font, HorizontalAlign, Visible, Enabled, Show, Text and Format.
Optionally, you can also view and analyze the copy of the full dynamic list settings. However, you will not be able to change the settings.
The simplest use scenario
The easiest way to understand the purpose of this handler is to look at an example. In this example, a list of goods shows the inventory balances from the accumulation register. The quantities that are less than a certain value are highlighted in red.So far, this task was solved through the left join in the dynamic list query and conditional appearance of the list specified in Designer.
Now it is solved this way. The original query of the list is not made more complex - only the dummy field Balance is added to it.
The balance is calculated only for the retrieved rows by the OnDataGetAtServer() handler. Immediately, the small balance values are highlighted in red.
Here the GetKeys() method gets the keys (references) from the collection of the dynamic list rows, and these keys are added to the balance query. Then, during query result iteration, you get the desired rows by key, add the value to the Balance column, and set appearance for the field, if needed.
As a result, the user sees the following list:
What are the advantages of this option?
Firstly, the query list remains simple. Or it does not remain simple, but the number of joins in it decreases. This means that more than likely, a good plan will be created for it.
Secondly, the handler will calculate the balance only once, whereas the query list can be executed multiple times (depending on the settings of the list and scrolling scenario).
And, thirdly, a new handler is executed after the basic data are obtained from the database and the access rights check is passed. This means that in some scenarios, related data can be retrieved in the handler in the privileged mode, without authentication. This makes the list work faster.
Restrictions on modifiable and added fields
Due to the fact that you can change the values of fields in the list in the handler, you have to always take into account one peculiarity.If additional measures are not taken, the user can be very surprised by the results of grouping, sorting, or filtering (including search results) for those fields that you changed. These actions will be executed based on the source data received by the query, rather than based on the data that the user sees.
That is why, when you change the values of the fields, do not forget to exclude them from the groups, order, and selection. For this purpose, we implemented three new methods.
Besides, in some cases, it might make sense to visually highlight these columns in the table for users to see that these columns are "special", not like all the rest.
