Let us proceed to the second step.
Up to this point you entered the cost of consumed materials to the Services document manually during its creation.
Now let us define the cost of materials using average values: for each material its total cost is divided by the number of materials available in order to get an average cost of one material unit.
To perform this calculation, you need additional data that you do not have for now.
For each material type in the tabular section you need:
- Material cost, which is stored in the CostOfMaterials register
- Total number of material units in all warehouses, which is stored in the BalanceOfMaterials register
So you need to modify your query to get this data from the database.
You need the query to retrieve the following fields for each material in the document (fig. 14.9).
Fig. 14.9. Query field descriptions
The first four fields can be retrieved (in fact, they are already retrieved) from the tabular section of the document itself, while the latter two fields should be retrieved from other database tables:
- Cost is retrieved from the CostOfMaterials register
- The total balance in all warehouses is retrieved from the BalanceOfMaterials register (fig. 14.10)
Fig. 14.10. Query field and table descriptions
This means that the query should include two left joins of the document table with other tables: the left join with the AccumulationRegister.CostOfMaterials.Balance table, and the left join with the AccumulationRegister.BalanceOfMaterials.Balance table.
Now it seems that everything is ready to generate the query.
But note one important aspect: in the suggested arrangement virtual tables return costs and balances for all the materials. And you only need this data for the materials available in the document.
This might not be noticeable in a small database because the number of unique materials in a catalog is almost equal to the number of unique materials in a document.
But imagine a real-life database. Suppose the MaterialsAndServices catalog includes 15 000 items. And a document only includes 5 items. The virtual table works heavily to calculate the cost (or balance) for all the 15 000 items, and when this table is connected with the document table using the left join, only 5 rows that correspond to the materials specified in the document are used. The remaining 14 995 rows are simply discarded so it is a waste of efforts to calculate them.
In real life such wastage is not acceptable and this query is far from optimal. So you should add a filter condition to all the virtual tables that you use in order to select only the materials contained in the tabular section of the document. In this scenario cost and balance are only calculated for the required materials instead of all the materials.
This gives you the following query pattern (fig. 14.11).
Fig. 14.11. Query pattern
Note that both virtual tables use the list of materials from the document tabular section (the data is calculated only for this list).
Besides, not all the data is retrieved from the document tables: instead, only the data related to a specific document is retrieved. In order to avoid generating this list three times (once for the document and again for each virtual table), you can have it generated in advance for later use in query conditions.
Temporary tables will help here.
A temporary table is a script object that is created and populated with data by a developer. Then queries can use this data when needed. For example, they can use it to apply some complex condition, which is the case in our scenario.
This gives you the following query pattern (fig. 14.12).
Fig. 14.12. Query pattern
Let us proceed to implementing the automatic cost calculation.
Next page: In Designer mode