The brand new accumulated balance is an exceptionally useful feature for users who would like to define and run balances for the specified fields. The balance calculations are automatically updated, thus no user intervention is required to get the actual state of the values.

 

This tutorial displays how to create an accumulated balance sheet from the scratch.

 

To begin with, let us create the database that we can run for our little imaginary electronics shop.
The basic database tables list for such undertaking implies the availability of the following tables:
“Customers” and “Employees” – the tables that keep records of our customers and employees, as well as “Products” table that lists the electronic items we sell.
These auxiliary tables do not take part in our calculations.

 

  

 

 
 
The rest two tables named “In” and “Out” are the main tables that we use to debit incoming goods and to issue invoices to our customers.
The “In” table keeps the daily records of the electronics supplied to our storage.

 

 
Whereas the ‘Out’ table maintains the list of the invoices issued to our customers by the employees.

 

 

In such scenario, the supply of electronic devices to our shop and their subsequent selling are the two processes that run separately.  This means we can have difficulties with the calculation of products in stock and cannot easily define the list of currently available devices. This is the case when the accumulated balance feature comes in handy.

The next steps will describe the process of adding the accumulated balance feature to our database in order to keep track of the items available in stock.

1. Add a new accumulated balance by tapping on the ‘Plus’ icon in the upper right corner of the instrument panel.

 

 

2. Specify the accumulated balance name and tap ‘OK’ to confirm.

 

 

3. To configure the accumulated balance, tap on the ‘Open configurator’ option.

 

 

4. Then, tap ‘Open structure editor’ to run it.

 

 

5. The accumulated balance structure consists of two field types.The detail fields that specify the fields we would like to run the balance for and the aggregation fields that display the data itself.

 

 

6. Set the detail field name to ‘Product’ and specify the field type. In most cases, the ‘Auto’ type will be the reasonable choice.

 

 

In case you made a mistake or would like to modify/delete the existing detail field simply tap on the dotted icon to the right of the field and select either ‘Edit’ or ‘Delete’ option. The same is true for the aggregation fields.

 

 

 

7. Set the aggregation field name to ‘Availability’ and tap ‘OK’ to confirm.

 

 

This is how your accumulated balance structure should look like. 

 

 

After the balance structure is created, we have to configure the settings by running the ‘Configurator’.

8. Tap on the ‘Plus’ button to add the data source to our balance.

 

 

 

9. As data source we should specify the ‘In.Items’ nested table that contains the actual number of devices supplied to our storage for the entire period.

 

 

10. Now, set the ‘Product’ field from the “In.Items”nested table as the detail field value.

 

 

11. Then specify the “Quantity” field as the aggregation field value.

 

 

The active green ‘plus’ sign to the left of the data source signals that the data from this source will be added to our balance. 

The next step is to add the ‘Out’ table data to our balance that will be subtracted from the ‘In’ table data.

12. Tap on the ‘Plus’ icon once again to add the ‘Out’ table data source.

 

 

13. Set the ‘Out.Items list’ table that lists the devices invoiced by the employees as the data source for ‘Out’ table. Then specify the product and quantity fields accordingly.

 

 

The active red ‘minus’ sign to the left of the ‘Out.Items list’ data source signals that the data from this source will be subtracted from our balance. 

Eventually, the configured balance should look like this.

 

 

 

14. Finally, tap on the check mark on the instrument panel to exit the configurator and save the accumulated balance.

 

 

The figure below displays the newly created ‘Stock’ balance that lists the devices from our storage and counts the number of devices left in stock. As seen, some of the devices are out of stock, which is proved by the ‘0’ value in the ‘Availability’ column. In some cases this information may look quite redundant and can be excluded by using the appropriate filter.

 

 

15. To configure the filter tap on the ‘Filter’ icon in the instrument panel.

 

 

16. Set the filter parameters as specified below and tap ‘OK’ to confirm the changes.

 

 

As a result, our balance lists only those devices that are currently available in stock.

 

 

The sample database can be downloaded by the following link: