Nested queries in the constructor. Query constructors 1c query constructor conditions
So, let's start with something simple: create a New Processing in the configurator, name it Query Console or Query Builder, as you like.
We can immediately add a table in “Data” for future Parameters that we need, so that in our Console we run not the most primitive request, but with parameters and links, for example, for our work we will create a request to periodic registers, but here without Parameter instructions=&Date nowhere.
To create our Parameters table, on the “Data” tab in its “Tabular part” we will add a new table, let’s call it Query Parameters, here we will add the columns of this table: 1) ParameterName, type string = 25 characters; ParameterValue, here is a composite data type, see Fig:
Therefore, as shown in the picture, we select a composite type for the Parameter Value column: in the type menu that opens, check the “Composite type” checkbox, select number, string (specify 20 characters), date, Boolean, and check the bottom checkbox – AnyLink – it means what follows, when specifying the Parameters of our request, we can refer to any object of our configuration, for example, directories or documents.
Now we need to create the form of our future Query Console. In processing, let’s go to the “Forms” tab and add a new one. We enter this form and here there is already an unlimited field for creativity - you can arrange the two details you just created and a plate with parameters as you like! To do this, you can use standard form elements like a Group or a Page with pages (if you prefer flipping pages.
The main thing here is one thing: after dragging the “TextValues” attribute into the left field of editing the form, be sure to set “View”=Text Document Field in its properties. See Fig:
In the properties of the “Query Table” attribute, you can optionally specify “Display Grid” and “Display Headers”.
Next, in the right window for editing the form, go to the “Commands” tab and add a new button, when clicked, our Console will perform a certain action. Let’s create a “Query Designer” Button, if you wish, you can add an icon to the button, the main thing is to place the button itself in the left window for editing the form - so that we can see it. Then in the right window for editing the form, right-click on our button and select properties – in the properties, click on the “Action” item, a modal window will appear asking where exactly our program code will be executed, which we will assign to the button – select “On the client”.
The Form Module will open with a ready-made empty procedure “Procedure Query Constructor (Command)”. Inside this procedure we will describe the call to the standard 1c8 Query Builder. It is very easy: Constructor = New Request Constructor; But there are pitfalls here - the Query Constructor built into the platform works in user mode ONLY under a thick client! Therefore, we will insert the condition of the preprocessor instruction #If, but here you decide for yourself, based on your platform, or you have ordinary forms, then select “ FatClientRegularApp” or you have a platform based on managed forms, then “ ThickClientManagedApplication“.see fig.
Now it remains to add to this procedure a condition for recording the request text, which the Query Builder will generate for us in our “Request Text” form details:
If Constructor.OpenModal()=True Then Object.RequestText=Constructor.Text; endIf;
But we can manually change something in the request text (in user mode - in the “Request Text” attribute window) so that our changes get into the Query Constructor when it is called again - we will add a simple condition here:
If not EmptyString(Object.QueryText) Then Constructor.Text = Object.QueryText; endIf;
That's it, we've connected the Query Constructor built into the 1c8 platform, let's look at our work. To do this, launch 1C:Enterprise in thick client mode using one of the following methods: 1) main menu of the Configurator – Debugging – Start Debugging – Thick Client; 2) or if you have these keys on the control panel in the configurator - just press the button with a yellow circle with a thick dot, see figure:
The user mode of 1cEnterprise8 starts, we find our processing, launch it, click on our “Query Designer” button and see how the designer built into the platform opens. see fig.
So, we have the Query Constructor running, we can start putting together our future query in it, but we are interested in seeing how the query we created will work out! And to do this, we need to create another button in the configurator when editing the form of our console, let's call it “Run Query”. In the properties of the “Run Query” button, click on “Action”, a menu appears again in which we are asked where our program code will be processed, in this case we select “Both on the client and on the server”, again we find ourselves in the Forms Module.
In the Execute Query() procedure, which we have on the client, we will write a condition if the user has not entered the query text, but asks to execute it:
If EmptyString(Object.QueryText) Then report("Enter the query text!"); endIf;
The system has already automatically generated a link to the Execute RequestOnServer() procedure; – that’s good, let’s go to this procedure, which is executed on the server, and write here the code for executing our entered request.
There are options here: You can write all the expressions related to building queries yourself, i.e. manually, but there is an even simpler option - inside the procedure, right-click and in the drop-down menu select “Query builder with processing of results, see figure”:
If you clicked on the “Query builder with result processing” item, a modal window “Query text not found. Create a new one?” will appear, click yes. The built-in query designer will open, in which on its first tab “Result processing” - select the first item “ Bypassing the result.” That’s it, we don’t need anything else from this constructor, click on the “Ok” button - a modal window will appear “No fields are selected in the request, click “Ok”.
After this, the following ready-made template will appear inside our ExecuteRequestOnServer() procedure:
Let's move on to the expression constructed by the constructor:
Request.Text = "";
Request.Text = Object.RequestText;
That’s how simple it is, our “Execute Request” button on the processing form is already practically functional, so far it can only process simple requests without parameters, but the main thing is that it works! All that remains is to visually display the results of our request in the “Value Table” attribute on the processing form. Let me remind you that our attribute “Value Table” is of the “Tabular Document” type, because otherwise we will not see our results in user mode. The display of tabular data to the user is always done either by a Tabular Document or a Layout. I would really like it to be possible to display data through a table of values - since it is very easy to use and familiar, but, unfortunately, a table of values is only a tool that a developer needs , you cannot display data on the screen using it...
Let's take a closer look at what a Spreadsheet Document is - it's like an Excel sheet - you can get to a record in a specific cell only using Table Cells, here we call them an area, but we ourselves can select the range of this area into one specific Cell:
So, we figured out what a spreadsheet document is and determined for ourselves that we will need to define the data from our query in a specific cell of this spreadsheet document. But let’s think: what is the “Query Result” that the designer so quickly generated for us? Open the help - The result of the query is a table that has the appropriate properties! see fig.
And if we now write after the expression Query Result = Query.Execute(); (created by the constructor), here is such a simple cycle for Collections:
For each ColumnName From Query Result.Columns Loop report(ColumnName.Name); EndCycle;
After this cycle, note down for now all the expressions built automatically by the constructor. And run 1C:Enterprise8 under the thick client. Create any simple query (You can use the Query Builder - it already works for us) and click on the “Run Query” button:
You will see at the bottom of the message window that the Query Result table stores the names of the fields that we just selected by creating a simple query.
Now let’s display these names of the fields of our suffering in a Spreadsheet document:
For each ColumnName From Query Result.Columns Loop Cell=Object.QueryTable.Area(1,QueryResult.Columns.Index(ColumnName)+1); Cell.Text=ColumnName.Name; EndCycle;
To display the details of the query data, let’s parse the expressions created automatically by the designer and insert into the “SelectionDetailedRecords” sorting loop of the query itself exactly the same loop that we used to display the column names, only now we need to transfer not the data from the “Query Result” table into the Cell text. and the data of the Selection itself, let’s see in the help how you can access the Detailed Selection field of the request:
SelectionDetailRecords = QueryResult.Select(); While SelectionDetailedRecords.Next() Loop //in the first line we already have the names of the table columns written down, so we load the data below the first lineDocRowNumber=Object.QueryTable.TableHeight+1; For each ColumnName From Query Result.Columns Cycle Cell=Object.QueryTable.Area(DocRowNumber,QueryResult.Columns.Index(ColumnName)+1); Cell.Text = SelectionDetailedRecords[ColumnName.Name]; EndCycle; EndCycle;
That’s all, we can check, load the enterprise under a thick client, enter a simple request without parameters, click on the “Run Query” button, see figure:
Hurray, everything works!!!
It is very convenient when, when opening/closing our Query Console, our query text, which we worked with before closing the console, is again written in the “Query Text” field. To do this, you just need to enable the form property = Autosave, see fig:
That's it, our console is working. So that we can write more complex queries with parameters specified in them, we need to create another “Find Parameters” button, as well as the code for the “Run Query” button - the code for the “Find Parameters” button will be executed on the client and on the server. Next, in the server procedure, we launch the request in the same way with the text passed into it from the “Request Text” window, using the expression “Request.FindParameters()” we find the passed parameters and simply enter them in a loop into the tabular part of the “Request Parameters” form. Don’t forget to then transfer them from the completed table of parameters to the “Run Query” procedure.
You can also add a couple of buttons to our Console that will clear the Parameters window and the Request Text window in user mode.
Our Query Console is ready to use, I wish you successful creative solutions using such a simple and powerful tool as the Query Console!
This processing is written on the 1c8.3 platform (managed forms) and runs under a thick client. It can also be written on the 1c8.2 platform, both under regular forms and under managed ones.
The download contains a sample of the Query Console we just created.
///////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
Query Console upgrade:
1) Now our homemade Query Console with a built-in Query Builder will run under any client: under a thick client of regular and managed forms and under a thin and web client.
p.s. The form and appearance of the built-in Query Builder is different - depending on which client we launched our Console under. (I personally am more familiar and convenient with the form of the Query Builder under a thick client)
&On the Client Procedure Query Constructor (Command) //calling the standard Query Constructor is only possible under a thick client #If ThickClientManagedApplication or ThickClientNormalApplication Then Constructor=New Query Constructor; If not EmptyString(Object.QueryText) Then Constructor.Text = Object.QueryText; endIf; If Constructor.OpenModal()=True Then Object.RequestText=Constructor.Text; endIf; // #Else // Report("Calling the Query Builder is only possible under a thick client"); // Return; //# EndIf #Else Report("You are running Query Builder under a thin client - it differs slightly in its form and performance speed!"); Constructor = New QueryConstructor(); If not EmptyString(Object.QueryText) Then Constructor.Text = Object.QueryText; endIf; Constructor Alert = New AlertDescription("RunAfterClosingConstructor", ThisForm); Constructor.Show(Constructor Alert); # EndIf End of Procedure &On the Client Procedure ExecuteAfterClosingConstructor(Result, ConstructorParameters) Export //Result=text, if Constructor was closed using the ok button Object.RequestText = AbbreviatedLP(Result); //works!!! End of Procedure
2) Added the ability to our simple Query Console to enter complex queries with a Temporary table passed to the parameters!!! The mechanism turned out to be very simple and elegant - without using XML code, as is done in professional consoles.
You can see the code itself and the procedures for the transfer mechanism to the parameters of Temporary tables in the second attached file. How I started developing my own version of Temporary tables in the parameters can be found at this link https://forum.infostart.ru/forum9/topic183700/
Now how to use the Console for a complex query when a temporary table is passed to its parameters. For example, you can take the code of this request;
SELECT ExternalData.Product, ExternalData.Quantity PLACE ExternalData FROM &ExternalData AS ExternalData; //////////////////////////////////////////////// ///////////////////////////// SELECT ExternalData.Product, ExternalData.Quantity, ISNULL(RemainingProductRemaining.QuantityRemaining, 0) AS Field1, ISNULL(Remaining ProductsRemainings.QuantityRemaining, 0) - ExternalData.Quantity AS Remaining FROM ExternalData AS ExternalData LEFT JOIN Register Accumulations.Remaining Products.Remainings(&Date, Product IN (SELECT ExternalData.Product FROM ExternalData AS ExternalData)) AS Remaining ProductsRemainings Software ExternalData.Product = RemainingProductRemaining. Product
Based on the example and similarity of the above query code, you can create your own complex query, taking into account your data objects.
So, in the query designer we created the above query, closing the Constructor - the query text will go into our console field “Query Text”, click on the “Find Parameters” button, we see that in the Parameters table a line has appeared = “External Data”, Value type = “Value Table” ,see fig.
In this table of Parameters - enter the Date parameter, for example, today's date, then click to try to edit our temporary table parameter “External Data”, click in the field with “Value Table” on three dots - a selection of types will appear, click Row, our mechanism turns us the page on the form, where we need to manually enter this very temporary table.
Note here that in this case, on the “TimeTables” page at the bottom in the “Temporary table name in parameters” field, the name of our temporary table will appear (it is copied from the Parameters table).
So far, on the “Time Tables” page we see only one empty table - this is the table of Types of our future temporary table. Using the “Add” button, we will add the name of the details and type of the future table. Be careful - the name and type must match what we specified in the request for &ExternalData:
Now we press the “Update Temporary Table” button - and we will have a second table here - we will directly fill it with temporary table data through the “Add” button.
That's it, we can double-check ourselves once again whether we have entered the primitive data of the query parameters in the parameter table on the 1st page of processing, and click the “Run Query” button - everything is calculated and selected accordingly with the limitation of the data passed in the parameter of our temporary table
p.s. If you made a mistake when typing the name of the details and their types (in the first table) - just close the Console and open it again - the temporary data table will be erased - and the Types table can again be edited and a new data table can be created again.
/////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
That's all, we can create a very powerful working tool with our own hands, in addition, our console is still very fast compared to professional ones - and this is a very big plus for developers! And, of course, now our console works under any client! Good luck in your creative developments!!!
Let's look at how we can join these two tables by field Product code using
query constructor (code for creating temporary tables is given as an example
I won't. You can take it from the above link).
Open the designer window, go to the tab "Tables and fields", to section
"Tables" select both of our tables, and in the section "Fields" -
those fields from both tables that we want to see as a result of the query.
Go to the bookmark "Connections". Add a new line. In field Table 1
from the drop-down list select the table with products, and in the field Table 2 Table
with countries.
For Tables1 check the box All. For
Tables2 We do not check this box. This means that from Tables1
all records will be selected, and from Tables2 only those for which it is performed
connection condition, that is, with such a combination of flags we get
LEFT CONNECTION. Next you need to fill out Communication condition.
Here we select the table fields from the drop-down lists and the comparison sign also from the drop-down
list. 
As a result of this connection, we get the following request text:
SELECT VT_Product.Product Code, VT_Product.Name, VT_Country.Country FROM VT_Product AS VT_Product LEFT JOIN VT_Country AS VT_Country BY VT_Product.Product Code = VT_Country.Product Code
Now let's take a closer look at some points.
Let's try to swap the checkbox All.
It would seem that in the end it should work out RIGHT JOIN, but if we
Let's look at the query text generated by the constructor, we will see that the tables
swapped places, but the connection still remained left:
SELECT VT_Product.Product Code, VT_Product.Name, VT_Country.Country FROM VT_Country AS VT_Country LEFT JOIN VT_Product AS VT_Product BY VT_Product.Product Code = VT_Country.Product Code
Let's see what happens if we uncheck both boxes 
As a result, we get an internal connection.
SELECT VT_Product.Product Code, VT_Product.Name, VT_Country.Country FROM VT_Product AS VT_Product INTERNAL JOIN VT_Country AS VT_Country BY VT_Product.Product Code = VT_Country.Product Code
Finally, if both checkboxes are checked 
we get a full connection
SELECT VT_Product.Product Code, VT_Product.Name, VT_Country.Country FROM VT_Product AS VT_Product FULL CONNECTION VT_Country AS VT_Country BY VT_Product.Product Code = VT_Country.Product Code
You can specify multiple lines on a bookmark Connections. In this case, several
rows are connected by condition AND. Moreover, lines are not necessary
drive in again each time. They can be copied using the key F9.
And in the new line, change only the connection condition. This significantly speeds up development.
Also, in the link condition it is not necessary to use only the predefined fields from
drop-down lists. You can use an arbitrary expression that satisfies
1C query language. To do this, you need to check the box free
and enter the condition directly into the field, or open an auxiliary window in
where you can use templates with query language functions. 
Of course, in practice there are much more complex queries where
several tables with various connections between each other. But in the query builder
connections of any complexity can be reproduced.
The query designer consists of the following tabs:
1. “Tables and fields” - there are three hierarchical lists on the tab:
a. “Database” - lists all available objects to which a query can be made. Also button "Display change tables", with which you can access tables of changes to information security objects if they are registered for any exchange plan.
b. “Tables” - a list of selected tables to which the query will be executed. Also in this window you can delete a table, rename or replace a table, and add an internal query.
You can assign parameters for virtual tables by clicking on the “Virtual Tables Parameters” button:
It is recommended to actively use the parameters of virtual tables for selection by certain dimensions, since this increases the speed of query execution. You can use external variables in parameters, the names of which are preceded by the “&” sign.
c. “Fields” - a list of fields that are selected from tables. You can also add calculated fields; to do this, clicking the “Add” button opens the custom expression constructor:
On the left is a window with the fields available in the expression. On the right is a hint of the functions used. Below is a constructable arbitrary expression. You can use external parameters in expressions; they are denoted by the “&” sign, for example: &Period, &StartDate
You need to be careful, if a long and complex expression is typed in the window, which contains a small syntax error, then after clicking the “OK” button, the system will issue a warning and close the window. All typed code will be lost, so I recommend that if you are not sure of the correctness of the expression, then always save the contents to the clipboard (Ctrl-C) before closing the constructor.
2. “Relations” - on the tab, connections between tables are indicated.
The table indicates the tables to be linked, the relationship between the tables to be linked and the connection condition. If the connection condition is complex, then you can specify a certain calculated expression, and the custom field constructor will open.
3. “Grouping” - the tab indicates which fields are grouped and which are aggregated (summed up).
4. Tab “Conditions” - lists the conditions that are imposed on the request.
In conditions, you can also write complex expressions using the simple expression constructor and using external variables:
5. "Advanced"
Additional parameters imposed on the request
6. “Associations and pseudonyms”
On this tab you can assign aliases for fields, as well as manage queries that are connected through the “UNITE” or “UNITE ALL” constructs.
7. "Order"
In what order will the query results be displayed?
Attention! At the bottom of the bookmark you can see a checkmark "Auto-order"- in the current version of 1C 8.1 in the ACS, it is useless; moreover, when the checkbox is checked, when recording, the ACS gives an error, so you should not use it.
8. "Data Composition"
The tab in which service fields for the access control system are defined. It plays approximately the same role as the “Report Builder” tab in a regular report designer.
A. On the “Tables” tab - the tables used in the query are listed; you can indicate whether the table must be included in the query by checking the “Required” checkbox. Those. if no fields are included in the selection, then this table does not participate in the query at all. You can also specify parameters for tables.
In the process of setting up the access control system, we specify any selections, then all selection values will be substituted into the parameters of the virtual tables, which again will help us optimize and speed up the query.
b. On the “Fields” tab, the fields and their aliases are listed that will be added to the list of ACS fields.
c. “Conditions” - if selections are specified in the ACS settings, all selection values will be added as additional conditions; complex expressions can also be added to the conditions.
9. "Characteristics"
A bookmark that has no analogue in the usual output form constructor.
This tab allows you to expand the work of queries with characteristics. The table on the tab consists of several fields:
a. “Value type” - the type for which the characteristics will be selected. For example, if you specify “Directory Link.Nomenclature”, then all characteristics for the nomenclature will be selected in the query.
b. “Source” - the source for properties of characteristic types, can be a query or a table. In this field we can write a request to select only those properties that we need.
c. “List of characteristics” - a field in which the source for the properties of the characteristics is indicated. Most often this is a plan of characteristic types or a request. You also need to specify the fields that are responsible for the “Identifier”, “Name” and “Type” of the property.
d. “Source” is the next field in which we indicate the source of the characteristic values, which can also be either a table or a query.
e. “Characteristic value” is a table or query that receives characteristic values. For example, the information register “ObjectPropertyValues” can serve as a table of characteristics values. We must also indicate those fields from the table (or query) that are responsible for the “Object”, “Property” and “Value” of the characteristic.
After editing the request, the request text can be seen in the window under the list of fields. Below, with the “Autofill” checkbox, we can regulate the completion of additional parameters for the fields specified in the request. Please note that the composition of the fields is determined only in the request itself.
Information taken from the site
Today we'll talk about how to do nested queries using query builder. Let's move straight to an example.
Let’s say we have this simple register of information, where prices are stored by goods and suppliers:
We want to request to receive all products that have more than one supplier. This can be implemented using the following query:
SELECT Number of Suppliers.Product AS Product FROM (SELECT Price.Product AS Product, QUANTITY(DIFFERENT Price.Supplier) AS Suppliers FROM RegisterInformation.Price AS Price GROUP BY Price.Product) AS Number of Suppliers WHERE Number of Suppliers.Suppliers > 1
Forming a nested query in the constructor
Let's create the above request using the constructor.
To do this, in the command panel above the field Tables press the button Create a subquery:
After which a window will open with another instance of the query constructor: 
And in this new window we construct a nested query: 
Clicking the button Request In the lower left corner we can see the text of the subquery: 
After clicking the OK button in the auxiliary constructor, we get the following picture in the main window: 
Since the phrase NestedQuery not very convenient for perception, let's use the right mouse button to rename the table to Number of Suppliers, select a field from it Product and on the bookmark Conditions Let's write down the necessary condition: 
And after all these manipulations, we receive the desired request. If necessary, you can create queries with several levels of nesting.
How to make a nested query from a regular query in the constructor
Very often a situation arises when you start making a request in the constructor and at some point you realize that it must be nested. Of course, in our example there is no problem - you can simply delete everything and re-issue the request. But in practice, there are much more complex examples, for example, with several levels of nesting, when several hours were spent making the request. And in this case there is a fairly simple way out. You can use the query text editor built into the designer. Need to use a button Request get the request text (see picture above) and copy it to the clipboard. Next, create a new nested query and press the button again Request, paste the text from the buffer, click OK. Accordingly, we clear the old request at the top level. This way, if necessary, we can easily create multi-level nested queries on the fly.
The ability to write query text “manually” has never been “superfluous”, but it is more convenient to use a query designer.
Comment.
Unfortunately, in a managed application, you cannot use the query designer in the "Get to Know With Query" processing. You can use it in this processing by switching to normal mode, but we will not do this.
Let's create the “Request Constructor” processing and define it in the “Reports and Processing” subsystem.
Let's create a processing form and add in it the attribute of the "TabDoc" form of the "Tabular Document" type, as well as the "Run Query" command with the "Run Query" action. Next, drag them onto the form.
In the form module, for the command to work, we write a procedure:
&OnClient
Procedure Execute Query (Command)
ExecuteRequestServer();
End of Procedure
&On server
End of Procedure
We become inside the procedure called on the server and call “Query constructor with result processing” from the context menu (Figure 2.65).
Figure 2.65 Query constructor with result processing
Set the processing type to “Output to a spreadsheet document” and click either the “Next” button or the “Tables and Fields” tab.
On the “Tables and Fields” tab of the designer, you can view the currently existing tables in the system (the left part is entitled “Database”) (Figure 2.66).
Figure 2.67 Query constructor
The tables from which the query will obtain data are transferred to the “Tables” area; the fields required by the query are transferred to the “Fields” area. Let's do it as in Figure 2.68.
Fig 2.68 Query constructor
Here we have removed the default view field created - “PREPRESENTATION (Product ReceiptProducts.Nomenclature)”
At any time while working with the designer, you can view the resulting request text. To perform this task, you must click the “Request” button located in the lower left corner of the designer form (Figure 2.69).
You can manually edit the request text by clicking on the “Edit” button.
Practice. Click the “OK” button and check our processing in user mode. If you analyze the data received when executing the request, you can come across “repeats” of item items (if this does not work out for you, then you can copy and post any “Receipt of Goods” document).
We return to the ExecuteRequestServer() procedure in the “Query Constructor” processing and from the context menu again call “Query Constructor with result processing”.
If you want to “collapse” the result of a query, then for this purpose you can use the “Grouping” tab of the query designer.
When defining groupings, you must adhere to the following rule: all query selection fields are divided into fields by which grouping (convolution) is performed, fields of nested tables (fields that are summed relative to those by which grouping is carried out) and aggregate functions. Let's define groupings (Figure 2.70).
Figure 2.70 Query Builder
If the data received by the request must be selected according to some condition, then in this case it may be necessary to use the “Conditions” tab. Let's select Goods ReceiptGoods.Quantity=10 (Figure 2.71).
Figure 2.71 Query constructor condition.
Please note that if the condition is defined in the same way as in the figure, the request will not be executed.
There are two ways to correct the situation:
By redefining the condition by checking the “P...” flag;
By taking advantage of the opportunity to change the request text itself (obtained by clicking on the “Edit Request” button).
The manual change itself will consist in the fact that it is necessary to remove the “&” symbol before the number “10”. This symbol in the request text defines the request parameters, into which some values must be written later (but before executing the request). Click on the “Edit Query” button and edit (Figure 2.72).
Figure 2.73 Editing a Query
On the “Advanced” tab, you can check a number of flags (related to the “Select” keyword of the query language) and determine the composition of the tables intended for changing the query (Figure 2.74).
Figure 2.74 Additional query functions
On the “Joins/Aliases” tab, you can change the field names by setting “Aliases”, but we will not do this.
On the “Order” tab, you can determine the sorting order of records as a result of the query (Figure 2.75).
Figure 2.75 Record sort order
Pay attention to the "Auto-order" flag, it can be used to order by fields of a reference type.
When defining the “Totals” section, you should be prepared for the fact that “additional” total records will appear as a result of the query. Together with these records, the query result becomes hierarchical (Figure 2.76).
Figure 2.76.Results of the query constructor.
It is possible to specify several types of totals:
Elements (the query result selection contains grouping totals and detailed records);
Hierarchy (in the query result selection, in the general case, there are summary records by hierarchy, summary records by grouping, and detailed records);
Hierarchy only (in the query result selection, in general, there are summary records by hierarchy).
After clicking on the “Ok” button of the constructor, a “Layout” will be generated and the code for the ExecuteRequestServer() procedure will be written in the form module:
&On server
Procedure ExecuteRequestServer()
//((QUERY_CONSTRUCTOR_WITH_RESULT_PROCESSING
// This fragment is built by the constructor.
// When reusing the constructor, changes made manually will be lost!!!
Layout = Processing.QueryConstructor.GetLayout("Layout");
Request = New Request;
Request.Text =
| Receipt of Goods Goods. Nomenclature AS Nomenclature,
| SUM (Receipt of Goods Goods. Quantity) AS Quantity,
| SUM(Receipt of GoodsGoods.Amount) AS Amount
| Document.Receipt of Goods.Goods
| HOW TO RECEIVE PRODUCTS
| Receipt of Goods Goods. Quantity > 1
|GROUP BY
| Receipt of GoodsGoods.Nomenclature
|ORDER BY
| Quantity,
| Amount DECREASE
| SUM(Quantity),
| SUM(Sum)
| Nomenclature HIERARCHY";
Result = Query.Run();
HeaderArea = Layout.GetArea("Header");
AreaFooter = Layout.GetArea("Footer");
TableHeadArea = Layout.GetArea("TableHeader");
TableFooterArea = Layout.GetArea("TableFooter");
AreaNomenclatureHierarchy = Layout.GetArea("NomenclatureHierarchy");
AreaNomenclature = Layout.GetArea("Nomenclature");
TabDoc.Clear();
TabDoc.Output(AreaHeader);
TabDoc.Output(TableHeadArea);
TabDoc.StartAutoGroupingRows();
SelectionNomenclature = Result.Select(BypassQueryResult.ByGrouping);
While SelectionNomenclature.Next() Loop
If SelectionNomenclature.RecordType() = RequestRecordType.TotalByHierarchy Then
Area = AreaNomenclatureHierarchy;
Region = RegionNomenclature;
endIf;
Area.Parameters.Fill(SelectionNomenclature);
TabDoc.Output(Area, SelectionNomenclature.Level());
EndCycle;
TabDoc.FinishAutoGroupingRows();
TabDoc.Output(TableFooterArea);
TabDoc.Output(AreaFooter);
//))CONSTRUCTOR_QUERY_WITH_RESULT_PROCESSING
