The first thing you have to do is deleting the Cost attribute of the Services document because you do not need it anymore.
- Open the editor of the Services document configuration object, click the Data tab, expand the list of document tabular section attributes, click Cost, and click the Delete current item button in the command bar (fig. 14.13).
Fig. 14.13. Deleting a tabular section attribute
You also have to delete the corresponding field from the MaterialsAndServices table located in the document form. - For the Services document, open the DocumentForm form and then, in the list of form elements, click MaterialsAndServicesCost, and click the Delete current item button in the command bar (fig. 14.14).
Fig. 14.14. Deleting a tabular section field
Then let us deal with the query. First, let us create a temporary table using the query that you have created earlier. - Open the Services document module.
- In the Posting() procedure, before the query generation, add the lines that create a temporary tables manager and specify that the query uses the manager (listing 14.17).
Listing 14.17. Using a temporary tables manager
RegisterRecords.BalanceOfMaterials.Write = True; RegisterRecords.CostOfMaterials.Write = True; RegisterRecords.Sales.Write = True; // Creating temporary tables manager TTManager = New TempTablesManager; Query = New Query; // Specifying the temporary tables manager used by the query Query.TempTablesManager = TTManager; Query.Text = "SELECT | ServicesMaterialsAndServices.MaterialOrService,
Let us modify the query so that it creates a temporary table to be stored in the TTManager temporary tables manager. - Delete the query line shown in listing 14.18.
This is required to make the query suitable for opening with the query wizard (as you do not have the Cost field anymore).
Listing 14.18. Line to remove
| MAX(ServicesMaterialsAndServices.Cost) AS Cost
- Delete the comma in the end of the previous line (listing 14.19).
Listing 14.19. Line to modify
| SUM(ServicesMaterialsAndServices.Total) AS TotalInDocument
- Right-click anywhere inside the query (for example, on the SELECT word) and click Query Builder.
This opens the query text in the form of the query wizard (fig. 14.15).
Fig. 14.15. Query wizard
Let us store the query result to a temporary table. - Click the More tab and then click Create temporary table.
- In the Temporary table name field, enter DocumentMaterialsAndServices (fig. 14.16).
Fig. 14.16. Creating a temporary table - Click OK and review the text generated by the query wizard (listing 14.20).
Listing 14.20. Query text
"SELECT | ServicesMaterialsAndServices.MaterialOrService, | ServicesMaterialsAndServices.MaterialOrService.MaterialServiceType AS MaterialServiceType, | SUM(ServicesMaterialsAndServices.Quantity) AS QuantityInDocument, | SUM(ServicesMaterialsAndServices.Total) AS TotalInDocument |INTO DocumentMaterialsAndServices |FROM | Document.Services.MaterialsAndServices AS ServicesMaterialsAndServices |WHERE | ServicesMaterialsAndServices.Ref = &Ref |GROUP BY | ServicesMaterialsAndServices.MaterialOrService, | ServicesMaterialsAndServices.MaterialOrSevice.MaterialServiceType";
There is only one new line here (listing 14.21).
Listing 14.21. Creating a temporary table
|INTO DocumentMaterialsAndServices
This line means that the query result is stored to the DocumentMaterialsAndServices temporary table.
If you specify the same temporary tables manager (TTManager) for another query, you will be able to access the data of this temporary table in that query.
So you have completed a part of the task: created a query that stores the document tabular section data to a temporary table (fig. 14.17).
Fig. 14.17. Creating the first query
Let us proceed to designing the second query. - Put the cursor into the line following the clause QueryResult = Query.Execute(); (this is where the temporary table is created) and create a template for your future query (listing 14.22).
Listing 14.22. Creating the second query
Query2 = New Query; Query2.TempTablesManager = TTManager; Query2.Text = "";
You have created a new Query object and specified the same temporary tables manager for this query. Now the query can access the temporary table that you created earlier. - Right-click the area between the quotation marks and click Query Builder.
- Confirm that you want to create a new query.
Since you are going to select data from your temporary table, let us add the temporary table description to the query. - Above the Tables list, click the Create a temporary table description button (fig. 14.18).
Fig. 14.18. Creating a temporary table description - In the window that is opened, in the Table name field, enter DocumentMaterialsAndServices.
- Add the following field descriptions:
- MaterialOrService. Type: CatalogRef.MaterialsAndServices
- MaterialServiceType. Type: EnumRef.MaterialServiceTypes
- QuantityInDocument. Type: Number, length: 15, precision: 3
- TotalInDocument. Type: Number, length: 15, precision: 2
Fig. 14.19. Creating a temporary table description - Click OK.
- Select all the fields from this table (fig. 14.20) and click the Query button in the bottom left corner of the query wizard.
Fig. 14.20. Selected temporary table fields
The query text should look as shown in listing 14.23.
Listing 14.23. Text of the second query
SELECT DocumentMaterialsAndServices.MaterialOrService, DocumentMaterialsAndServices.MaterialServiceType, DocumentMaterialsAndServices.QuantityIndocument, DocumentMaterialsAndServices.TotalInDocument FROM DocumentMaterialsAndServices AS DocumentMaterialsAndServices
So you have created the first part of the second query, which selects data from the temporary table (fig. 14.21).
Fig. 14.21. Creating the second query
Now you need left joins to connect this query part with the balance tables.
Let us begin with the cost of materials. - Add the AccumulationRegister.CostOfMaterials.Balance virtual table to the list of query tables. Select the CostBalance field from this table.
Let us define the relation between the tables. - Click the Links tab and specify that all the records are selected from the temporary table and the MaterialOrService field of the temporary table matches the Material field of the balances table (fig. 14.22).
Fig. 14.22. Relation between the tables
You also need to filter the virtual table data, so that only materials available in the temporary table are selected. - Click the Tables and fields tab, click the CostOfMaterialsBalance table, and click the Virtual table parameters button above the list of tables.
- In the Condition field, enter the line shown in listing 14.24.
Listing 14.24. Virtual table condition
Material IN (SELECT DocumentMaterialsAndServices.MaterialOrService FROM DocumentMaterialsAndServices)
This means that material should be in the list of materials and services selected from the temporary table.
Learn more! Be careful in how you use virtual query tables. Be particularly mindful of using virtual table parameters as much as possible.
For example, in this scenario you could avoid using the Condition parameter and filter the field selection in the query itself, by using the BY condition with materials and services from the document equal to materials from the balance table. Technically it gives exactly the same result, but not the same performance.
In the scenario that you implemented the number or records retrieved from the virtual table is equal to the number of unique materials and services in the document being posted. And if you set no conditions, the virtual table provides you with the records for all the materials that are available in the accumulation register, and you have to filter that large list of records in the query to get the several records that you need.
Clearly, the second option would take longer to complete, and the execution time for such a query would depend less on the amount of data contained in the document (the actual amount of data to be processed), but rather on the size of the accumulation register.
Aside from the fact that this option affects the configuration performance, it may happen that the two approaches produce different results. For example, this might happen when you use the SliceLast virtual table of an information register. For more information, see Using filters in queries with virtual tables. - Click the Query button and review the text generated by the query wizard (listing 14.25).
Listing 14.25. Query text
SELECT DocumentMaterialsAndServices.MaterialOrService, DocumentMaterialsAndServices.MaterialServiceType, DocumentMaterialsAndServices.QuantityIndocument, DocumentMaterialsAndServices.TotalInDocument, CostOfMaterialsBalance.CostBalance FROM DocumentMaterialsAndServices AS DocumentMaterialsAndServices LEFT JOIN AccumulationRegister.CostOfMaterials.Balance( , Material IN ( SELECT DocumentMaterialsAndServices.MaterialOrService FROM DocumentMaterialsAndServices)) AS CostOfMaterialsBalance ON DocumentMaterialsAndServices.MaterialOrService = CostOfMaterialsBalance.Material
So you added the material cost to the selection fields (fig. 14.23).
Fig. 14.23. Creating the second query - Add the Balances virtual table of the BalanceOfMaterials register to the list of query tables.
- Select the QuantityBalance field from this table.
Let us define the relation between the tables. - Click the Links tab and specify that all the records are selected from the temporary table and the MaterialOrService field of the temporary table matches the Material field of the balances table (fig. 14.24).
Fig. 14.24. Relation between the tables
Let us specify the parameters for the BalanceOfMaterialsBalance virtual table. - In the Condition field, enter the line shown in listing 14.26.
Listing 14.26. Virtual table condition
Material IN (SELECT DocumentMaterialsAndServices.MaterialOrService FROM DocumentMaterialsAndServices)
This gives you the following query text (listing 14.27).
Listing 14.27. Query text
SELECT DocumentMaterialsAndServices.MaterialOrService, DocumentMaterialsAndServices.MaterialServiceType, DocumentMaterialsAndServices.QuantityIndocument, DocumentMaterialsAndServices.TotalInDocument, CostOfMaterialsBalance.CostBalance, BalanceOfMaterialsBalance.QuantityBalance FROM DocumentMaterialsAndServices AS DocumentMaterialsAndServices LEFT JOIN AccumulationRegister.CostOfMaterials.Balance( , Material IN ( SELECT DocumentMaterialsAndServices.MaterialOrService FROM DocumentMaterialsAndServices)) AS CostOfMaterialsBalance ON DocumentMaterialsAndServices.MaterialOrService = CostOfMaterialsBalance.Material LEFT JOIN AccumulationRegister.BalanceOfMaterials.Balance( , Material IN ( SELECT DocumentMaterialsAndServices.MaterialOrService FROM DocumentMaterialsAndServices)) AS BalanceOfMaterialsBalance ON DocumentMaterialsAndServices.MaterialOrService = BalanceOfMaterialsBalance.Material
So you added the material balance in all the warehouses to the selection fields (fig. 14.25).
Fig. 14.25. Creating the second query - Click the Unions/Aliases tab, set the CostBalance alias to Cost, and set the QuantityBalance alias to Quantity (fig. 14.26).
Fig. 14.26. Field aliases
The query text generated by the wizard is shown in listing 14.28.
Listing 14.28. Query text
SELECT DocumentMaterialsAndServices.MaterialOrService, DocumentMaterialsAndServices.MaterialServiceType, DocumentMaterialsAndServices.QuantityInDocument, DocumentMaterialsAndServices.TotalInDocument, CostOfMaterialsBalance.CostBalance AS Cost, BalanceOfMaterialsBalance.QuantityBalance AS Quantity FROM DocumentMaterialsAndServices AS DocumentMaterialsAndServices LEFT JOIN AccumulationRegister.CostOfMaterials.Balance( , Material IN ( SELECT DocumentMaterialsAndServices.MaterialOrService FROM DocumentMaterialsAndServices)) AS CostOfMaterialsBalance ON DocumentMaterialsAndServices.MaterialOrService = CostOfMaterialsBalance.Material LEFT JOIN AccumulationRegister.BalanceOfMaterials.Balance( , Material IN ( SELECT DocumentMaterialsAndServices.MaterialOrService FROM DocumentMaterialsAndServices)) AS BalanceOfMaterialsBalance ON DocumentMaterialsAndServices.MaterialOrService = BalanceOfMaterialsBalance.Material
The final thing to do with the query is specifying how to handle the situation where a material is available in the catalog but without any balance or cost data. For example, this can happen when a material has already been added to the catalog but has not been delivered to the company yet.
In this situation left joins with virtual tables return nothing. In the query language it means that the cost and quantity fields store NULL values.
For future convenience, let us eliminate such values right in the query.
Let us apply the ISNULL() function to the Cost and Quantity fields. If a field value is NULL, the function returns 0. Otherwise, it returns the field value. - Click the Tables and fields tab, click the CostOfMaterialsBalance.CostBalance field, and click the Change current item button (fig. 14.27).
Fig. 14.27. Changing a field value in a query - In the window that is opened, edit the field value as shown in listing 14.29 (fig. 14.28).
Listing 14.29. Expression for calculating a field in a query
ISNULL(CostOfMaterialsBalance.CostBalance, 0)
Fig. 14.28. Changing a field value in a query - Set a similar expression for calculating the BalanceOfMaterialsBalance.QuantityBalance field and click OK.
The query text is added to the module (listing 14.30).
Listing 14.30. Query text
Query2.Text = "SELECT | DocumentMaterialsAndServices.MaterialOrService, | DocumentMaterialsAndServices.MaterialServiceType, | DocumentMaterialsAndServices.QuantityInDocument, | DocumentMaterialsAndServices.TotalInDocument, | ISNULL(CostOfMaterialsBalance.CostBalance, 0) AS Cost, | ISNULL(BalanceOfMaterialsBalance.QuantityBalance, 0) AS Quantity |FROM | DocumentMaterialsAndServices AS DocumentMaterialsAndServices | LEFT JOIN AccumulationRegister.CostOfMaterials.Balance( , Material IN ( | SELECT | DocumentMaterialsAndServices.MaterialOrService | FROM | DocumentMaterialsAndServices)) AS CostOfMaterialsBalance | ON DocumentMaterialsAndServices.MaterialOrService = CostOfMaterialsBalance.Material | LEFT JOIN AccumulationRegister.BalanceOfMaterials.Balance( , Material IN ( | SELECT | DocumentMaterialsAndServices.MaterialOrService | FROM | DocumentMaterialsAndServices)) AS BalanceOfMaterialsBalance | ON DocumentMaterialsAndServices.MaterialOrService = BalanceOfMaterialsBalance.Material";
The only thing you need to do is add the query execution statement after the query text (listing 14.31).
Listing 14.31. Posting() procedure (fragment)
... Query2 = New Query; Query2.TempTablesManager = TTManager; Query2.Text = "SELECT | DocumentMaterialsAndServices.MaterialOrService, ... | ON DocumentMaterialsAndServices.MaterialOrService = BalanceOfMaterialsBalance.Material"; QueryResult = Query2.Execute(); SelectionDetailRecords = QueryResult.Select(); ...
Now let us deal with writing register records.
You can use the script lines that you wrote earlier. The only thing you need to change is how the cost is obtained. You used to take it directly from the document but now you need to calculate it based on the value retrieved in the query.
The material cost is calculated by dividing the total cost retrieved in the query (Cost) by the total quantity of material units available in all warehouses (Quantity).
But we have already mentioned that the Quantity field might contain zero and one cannot divide by zero. So right after the beginning of the loop that iterates through the query result add the script that calculates the cost for the current material (listing 14.32).
Listing 14.32. Posting() procedure (fragment)
While SelectionDetailRecords.Next() Do If SelectionDetailRecords.Quantity = 0 Then MaterialCost = 0; Else MaterialCost = SelectionDetailRecords.Cost / SelectionDetailRecords.Quantity; EndIf; If SelectionDetailRecords.MaterialServiceType = Enums.MaterialServiceTypes.Material Then ...
- Replace the cost calculation expressions in the script fragment that writes CostOfMaterials and Sales register records (listing 14.33).
Listing 14.33. Posting() procedure (fragment)
While SelectionDetailRecords.Next() Do If SelectionDetailRecords.Quantity = 0 Then MaterialCost = 0; Else MaterialCost = SelectionDetailRecords.Cost / SelectionDetailRecords.Quantity; EndIf; If SelectionDetailRecords.MaterialServiceType = Enums.MaterialServiceTypes.Material Then // register BalanceOfMaterials Expense Record = RegisterRecords.BalanceOfMaterials.Add(); Record.RecordType = AccumulationRecordType.Expense; Record.Period = Date; Record.Material = SelectionDetailRecords.MaterialOrService; Record.Warehouse = Warehouse; Record.Quantity = SelectionDetailRecords.QuantityInDocument; // register CostOfMaterials Expense Record = RegisterRecords.CostOfMaterials.Add(); Record.RecordType = AccumulationRecordType.Expense; Record.Period = Date; Record.Material = SelectionDetailRecords.MaterialOrService; Record.Cost = SelectionDetailRecords.QuantityInDocument * MaterialCost; EndIf; // register Sales Record = RegisterRecords.Sales.Add(); Record.Period = Date; Record.MaterialOrService = SelectionDetailRecords.MaterialOrService; Record.Customer = Customer; Record.Technician = Technician; Record.Quantity = SelectionDetailRecords.QuantityInDocument; Record.Revenue = SelectionDetailRecords.TotalInDocument; Record.Cost = SelectionDetailRecords.QuantityInDocument * MaterialCost; EndDo;
Now everything seems to be fine but there is still one important issue.
The first posting of a document gives the correct result but the second posting makes it incorrect because at the time of the second posting the database contains the register records written during the first posting.
So when the cost and balance of materials are read from the database, the retrieved values are based on the register records written by this document earlier. And this is absolutely wrong.
In order to read database data in the posting event handler without reading the register records written by this document earlier, you have to write empty record sets to the registers before reading data from them.
In this example you have to write empty record sets to the CostOfMaterials and BalanceOfMaterials accumulation registers. - Add two lines before the execution of the second query, as shown in listing 14.33a.
Listing 14.33a. Posting() procedure (fragment)
... | ON DocumentMaterialsAndServices.MaterialOrService = BalanceOfMaterialsBalance.Material"; // Writing empty record sets to read balances without the data added by this document RegisterRecords.CostOfMaterials.Write(); RegisterRecords.BalanceOfMaterials.Write(); QueryResult = Query2.Execute();