"Balance with Partners" opens slowly: configuring filters, selecting a period, and checking the database

In the daily operations of a retail or wholesale store, it is important to have up-to-date information about settlements with business partners: how much customers owe for shipped goods, how much needs to be paid to suppliers for new deliveries, and how financial obligations are distributed across different store locations. For this purpose, Torgsoft provides a specialized «Balance with Partners» mode (Payment -> Balance with Partners menu), which consolidates all inventory and financial transactions of the business. However, as product ranges expand, the number of business partners increases, and several years of sales history accumulate, entrepreneurs contact technical support and sales specialists with questions: why does the «Balance with Partners» mode take so long to load, how can settlements be properly filtered by specific accounting centers or currencies, how can discrepancies in totals be avoided, and which maintenance operations can improve database performance?

Purpose of the Mode and Basic Concepts of the Goods and Cash Balance

The «Balance with Partners» mode is designed to organize and analyze the goods and cash balance (GCB) between a retail network and its business partners. The goods and cash balance reflects the relationship between the value of goods received or shipped and the money actually paid or received.

All business partners in the system are divided into main categories according to the type of partnership:

• Supplier — a business partner from whom goods are received.

• Customer — a business partner (retail, wholesale, or VIP customer) to whom goods are shipped.

• Supplier-Customer — a partner with whom the business conducts both purchases and sales.

For customers, the goods and cash balance is calculated exclusively in the national currency. For suppliers, accounting is maintained in the currencies used for incoming invoices and settlements. If there are no ongoing outstanding balances with a partner (for example, in ordinary retail sales to end customers at the checkout), no goods and cash balance accumulates for that partner.

Why the «Balance with Partners» Mode Loads Slowly

Imagine an accountant opening a thick paper ledger covering the last 10 years every time they enter the store and recalculating every receipt from the first day of business. This is essentially what happens in the software when a calculation is started without a date restriction.

The main factors affecting loading speed are:

1. No Date Restriction (Empty Start Date Field). If the left-hand field of the period filter is left blank, Torgsoft analyzes the entire history of inventory and financial documents from the day the database was created. After several years of operation, this may require processing hundreds of thousands or millions of records.

2. Large Number of Business Partners and Accounting Centers. Calculating totals for each partner across multiple cash registers, bank accounts, accounting centers, and currencies requires a series of resource-intensive queries to the database management system.

3. Concurrent Transactions During Peak Trading Hours. If a manager or accountant calculates the balance while sales are actively being processed at checkout counters, a large data retrieval query may temporarily lock database tables, resulting in delays while waiting for responses.

4. Unoptimized Indexes and Database Fragmentation. Over time, as documents are deleted, modified, or added, SQL indexes (special structures used to quickly locate database records) become less efficient, while database statistics become outdated, slowing down analytical operations.

Step-by-Step Guide to Configuring Filters for Faster Loading

To speed up the «Balance with Partners» mode, the following data filtering methods are recommended:

1. Select a Specific Analysis Period

Do not leave the period start date blank unless necessary. Specify a particular time range (for example, the current month or year). If outstanding balances are regularly reconciled, calculations for a limited period load several times faster.

2. Specify the Partnership Type and Currency

Always select a specific category in the Partnership Type filter: «Supplier», «Customer», or «Supplier-Customer». Calculating balances for all business partners simultaneously requires the most resources. Also select a specific Currency and Accounting Center / Company if accounting is maintained in multiple currencies or across several store locations.

3. Configure the «Refresh When Filters Change» Option

In the form settings, you can disable «Refresh When Filters Change». This prevents the resource-intensive calculation from restarting automatically whenever a user changes a parameter (for example, the currency or debt status). After selecting all the necessary parameters, start the calculation manually by clicking «Refresh».

4. Hide Inactive Business Partners

Over time, the database accumulates records of partners with whom the business no longer works. In the Settings -> Business Partner menu, mark outdated records as inactive. They will no longer be loaded into the «Balance with Partners» form, reducing the amount of data retrieved.

5. Filter by Outstanding Balance Status

To work with debtors more efficiently, use the Debt Filter:

• My Debtors — displays partners who owe money to the store.

• Who I Owe — displays business partners to whom the store owes money.

• Outstanding Balances — displays only partners whose final balance is not zero.

• No Outstanding Balances — displays partners with a zero balance.

Role-Based Restrictions and Employee Access Permissions

Accurate accounting and system performance also depend on access rights configured under Settings -> Parameters -> Role:

1. Restricting the Information Viewing Period. The «Restrict Information Viewing Period» setting allows you to specify how many months of documents a cashier or manager can access. This automatically prevents users from selecting excessively early dates in filters, protecting the system against accidentally recalculating the entire transaction history.

2. Restricting Access to Accounting Centers. The «Apply Accounting Center Access Restrictions to Balance with Partners» setting ensures that employees see settlements and documents only for store locations they are authorized to access. If this setting is disabled, calculations under a restricted role will cover the entire retail network.

Database Maintenance and Optimization

If the filters are configured correctly but the form still loads slowly, database maintenance is required.

Index Reorganization Maintenance Operations

The File -> Restore and Reorganize Indexes menu provides a routine maintenance procedure for updating statistics. It reorganizes data storage in tables and updates statistics used by the SQL query optimizer, speeding up document searches and calculations. This operation should be performed outside business hours, when checkout counters are closed.

Using the «Database Optimization» Form

Modern versions of Torgsoft provide the File -> Database Optimization tool. It analyzes resource-intensive queries and creates optimized indexes tailored to the company's specific data structure, potentially increasing the form's refresh speed several times over.

Deleting Statistics for Closed Periods in Large Databases

For businesses that have used Torgsoft for many years and have databases several gigabytes in size, the deletion of statistics for closed periods procedure can reduce historical data volume (an additional option available under Analysis -> Period).

How Historical Data Consolidation Works

1. Before deleting statistics, the required months must be closed under Analysis -> Period.

2. During the procedure, detailed original warehouse and financial documents for closed years are physically deleted.

3. On the last day of the closed period, the software creates summary records: opening debts for business partners in the respective currencies and incoming invoices reflecting the final inventory balances.

4. All accumulated customer purchase totals, their discounts, and current cash register balances are fully preserved.

After the statistics are deleted, calculations in the «Balance with Partners» mode run significantly faster because the software no longer needs to process millions of original documents from previous years.

Mandatory Verification of Balances Before Deletion

According to Torgsoft procedure, the following key control figures must be recorded before deleting statistics:

• The total quantity of goods across all warehouses.

• Outstanding debt amounts by currency in «Balance with Partners» for suppliers and customers.

• Cash register and bank account balances by currency.

• Total amounts used to calculate discounts and customer bonuses.

After the procedure is completed, these figures are compared with the recorded values to ensure the accuracy of financial accounting.

Answers to Frequently Asked Questions from Business Owners

The «Settlement Record» displays a chronological list of transactions for the selected period, including details of specific invoices and payments. Discrepancies can occur when a user's access to certain accounting centers is restricted, when a financial payment document is assigned to a non-balance-sheet category, or when goods are assigned to the system customer «CUSTOMER» instead of the actual partner's record during a sale.

In the «Balance with Partners» form, select a business partner and click «Partner Balance Breakdown». This window displays all incoming and outgoing invoices, their associated payments, the list of goods, and unidentified payments.

If an incoming invoice was created in a foreign currency but payment was made in hryvnias without specifying the foreign currency equivalent, or if the exchange rate in the system changed, the software displays settlements according to the currency selected in the filter. To display the information correctly, select the corresponding invoice currency in the filter.

The «Balance with Partners» mode includes a «Max. Debt Amount» column. It displays the credit limit set for the customer in the business partner record (Settings -> Business Partner -> Additional tab). This allows you to monitor the permitted level of accounts receivable directly in the balance form.