1C:Professional training course. Lesson 3-5

Lesson 3-5

Checking the balance using virtual table’s parameters

Earlier we have mentioned that the Platform allows you to get the accumulation register balance for any given moment in time. Does it mean that the Balance table stores balance records for all moments? Well, no, it doesn’t. Otherwise, it would be too much data to store and search in. But how does it work, though?

There are two physical tables that store an accumulation register’s data:

  • Main table: stores all posted recordsets in their original form (no aggregation).
  • Balance table: stores the balances aggregated by dimensions.

Let’s consider the example of these tables content for specific material and warehouse.


If the initial balance of this material was 0, the current balance will be: 0 + 20 - 5 - 1 - 10 + 20 - 10 - 1 = 13. This current balance can be obtained directly from the Balance table. There are no intermediate balances for every time moments, but we can calculate them using both tables.

For instance, the balance by the midnight of 2015-05-01 will be 13 + 1 + 10 - 20 = 4 (if we calculate it from current balance backward in time). Or (if we calculate it from initial balance forward) 0 + 20 - 5 - 1 - 10 = 4.

To speed up the calculation, you can set up the accumulation register to calculate and store the intermediate monthly balances as well. If there are monthly balances in the Balance table, the balance for a specific time will be calculated starting from the closest monthly balance available.

Now, imagine the query you need to use to get the balance for an arbitrary point in time. This would be something really big, complex and unreadable. The Platform simplifies the task by hiding this complexity behind the virtual table concept. To get the BalanceOfMaterials accumulation register’s balance for any given &DateTime you can just use this simple query: 

SELECT
	BalanceTable.Material,
	BalanceTable.Warehouse,
	BalanceTable.QuantityBalance
FROM
	AccumulationRegister.BalanceOfMaterials.Balance(&DateTime) AS BalanceTable

The “AccumulationRegister.BalanceOfMaterials.Balance(&DateTime)” is a virtual table that can be used in queries as if it was the real physical table that contained the ready-to-go balances for any given moment. Before sending this query to the DBMS, the Platform will rewrite the query in order to calculate the balance you requested.

You can look up for the complete list of all tables (both real and virtual) in the Query Builder (virtual ones are outlined in red):


Note that we passed the moment of time in a form of a parameter. Different virtual tables support different lists of parameters. When you add a virtual table in the Tables list of the Query Builder you can get the complete list of its parameters by pressing this button:


In the case of our accumulation register we have only two parameters available:

  • Period - the moment of time we need the balance for. If the parameter is omitted, you will get the current balance.
  • Condition - additional condition.

Please note, that you can use only some of the virtual table’s fields in the Condition parameter. To get the list of fields available, press the ellipsis button. This is what you will get:


As you can see, there are only two virtual table’s fields available for use in the Condition parameter. There is no QuantityBalance field, so we cannot use it in the Condition. The thing is that you cannot use calculated (i.e. virtual) fields as conditions in the virtual table parameters, because these conditions cannot be applied to the real physical tables behind the virtual table. Therefore, you can use dimensions (Material and Warehouse) - they are the real physical fields in the Balance table, but cannot use the QuantityBalance - there is no ready-to-use value physically stored in the table, it needs to be calculated first.

OK. Now, how do we actually use the virtual table to check the balance? Where do we place the conditions - into the parameters of the virtual table or in the WHERE clause?

Here is the main rule: all conditions that can be put in the virtual table’s parameters, have to be put there. Use virtual table conditions in a WHERE clause to filter a virtual table only if there is no way to use them in a virtual table’s parameters. Not following this rule can cause severe performance issues in some cases.

So, this is an example of a query that returns correct results but needs to be rewritten using the virtual table parameters: 

SELECT
	BalanceTable.Material,
	BalanceTable.Warehouse,
	BalanceTable.QuantityBalance
FROM
	AccumulationRegister.BalanceOfMaterials.Balance AS BalanceTable
WHERE
	BalanceTable.Material = &Material

And this is the fixed query returning the same results: 

SELECT
	BalanceTable.Material,
	BalanceTable.Warehouse,
	BalanceTable.QuantityBalance
FROM
	AccumulationRegister.BalanceOfMaterials.Balance(, Material = &Material) AS BalanceTable

Now we are ready to write the Balance checking query using virtual table parameters: 

SELECT
 BalanceTable.Material,
 BalanceTable.Warehouse,
 BalanceTable.QuantityBalance AS QuantityBalance
FROM
 AccumulationRegister.BalanceOfMaterials.Balance(
, (Material, Warehouse) IN (
SELECT
Document.Services.MaterialsAndServices.MaterialOrService,
  Document.Services.MaterialsAndServices.Warehouse
 FROM
  Document.Services.MaterialsAndServices
 WHERE
 Document.Services.MaterialsAndServices.Ref = &Ref AND 
 Document.Services.MaterialsAndServices.MaterialOrService.MaterialOrService = &Material)
) AS BalanceTable
WHERE
 BalanceTable.QuantityBalance < 0

Note that we use the same familiar (<exp1>, <exp2>,...) IN (SELECT <exp1>, <exp2>,...) syntax here - but this time not in the WHERE clause but the virtual table’s parameters.

Choosing the best checking balance query

In this Lesson we considered three different ways to check if there is negative balance in the accumulation register:

  • Using WHERE;
  • Using JOIN;
  • Using virtual table’s parameters.

Which one should you prefer? If you use a virtual table, you need to put inside its parameters all conditions that can be possibly put there. In first two queries we used the virtual table without parameters and checked all conditions in WHERE clause or JOIN’s ON section.

Therefore, the best choice here is the last option we considered. And here the final version of our Posting handler: 

    Procedure Posting(Cancel, Mode)
 
 // 1. Filling out BalanceOfMaterials recordset
 RegisterRecords.BalanceOfMaterials.Write = True;
 For Each CurRowMaterialsAndServices In MaterialsAndServices Do
  If CurRowMaterialsAndServices.MaterialOrService.MaterialOrService =
   Enums.MaterialOrService.Service Then Continue;
  EndIf;
  Record = RegisterRecords.BalanceOfMaterials.Add();
  Record.RecordType = AccumulationRecordType.Expense;
  Record.Period = Date;
  Record.Material = CurRowMaterialsAndServices.MaterialOrService;
  Record.Warehouse = CurRowMaterialsAndServices.Warehouse;
  Record.Quantity = CurRowMaterialsAndServices.Quantity;
 EndDo;
 
 //2. Writing down BalanceOfMaterials recordset
 RegisterRecords.BalanceOfMaterials.LockForUpdate = True;
 RegisterRecords.Write();
  
 //3. Checking if there any negative balance records
 Query = New Query;
 Query.Text = 
  "SELECT
  | BalanceTable.Material,
  | BalanceTable.Warehouse,
  | BalanceTable.QuantityBalance AS QuantityBalance
  |FROM
  | AccumulationRegister.BalanceOfMaterials.Balance(
  |   ,
  |   (Material, Warehouse) IN
  |    (SELECT
  |     Document.Services.MaterialsAndServices.MaterialOrService,
  |     Document.Services.MaterialsAndServices.Warehouse
  |    FROM
  |     Document.Services.MaterialsAndServices
  |    WHERE
  |     Document.Services.MaterialsAndServices.Ref = &Ref
  |     AND Document.Services.MaterialsAndServices.MaterialOrService.MaterialOrService = 
  |     &Material)) AS BalanceTable
  |WHERE
  | BalanceTable.QuantityBalance < 0";
  
 Query.SetParameter("Ref", ThisObject.Ref);
 Query.SetParameter("Material", Enums.MaterialOrService.Material);
  
 //4. Execute the query
 QueryResult = Query.Execute();
 SelectionDetailRecords = QueryResult.Select();
 
 //5. If the result is not empty, cancel the document's posting
 If SelectionDetailRecords.Count() = 0 Then
  Return;
 EndIf;
 Cancel = True;
 
 //6. Prepare the error message and display it.
 MessageText = 
  "There is not enough materials. See the list of deficits below:" +
  MessageText;
 While SelectionDetailRecords.Next() Do
  MessageText = 
   MessageText + Chars.CR + Chars.LF + 
   SelectionDetailRecords.Warehouse.Description + "\" + 
   SelectionDetailRecords.Material.Description + ": " +
   SelectionDetailRecords.QuantityBalance;
 EndDo;
 Message(MessageText);
 
EndProcedure


 Lesson 3-4 | Course description | Lesson 4-1

1C:Enterprise Developer's Community