Totaling buffer table pattern
Views (4)
Jalmaraz
669
Like
Share
ReportSome days ago, a workmate asked me best way to use a buffer table for totaling. I though I already published this, but not. This is a very old pattern suggestion I made years ago (notice that is developed in C/AL) and it should be shared (IMO). Not an official pattern, only a suggestion.
Totaling buffer table
Context
This pattern explains the use of temporary tables for grouping and totaling. This is widely used in Navision, with the “Inventory buffer”, “VAT Amount” tables and more.
Problem
When we need to group and total data, in reporting, calculations, posting or exporting and writing data in tables, files or external databases.
Force 1. Create too much indexes in tables.
Overall in older versions using totaling and grouping forces us to create new indexes, modifying standard design, increasing database size and updating time.
Force 2. Increase cyclomatic complexity in process (and increase process time too) and coupled code.
Making manual grouping, our code become more complex, imperative (instead declarative), because we have to control the group breaking with too many variables and flow control instructions.
Also, it could be interesting to decouple data process and the posterior use of data (print then, save a file or database). This way our code became more flexible. If our code make data calculation and output together all changes will be very difficult.
Solution
When a complex grouping and totaling must be implemented, better than extract it directly, we must:
- Create a table buffer.
- Use this table in the process.
- Later, extract data from buffer with REPEAT.
Usage
Example report 790 “Calculate Inventory”.
Step 1. Create table named “<Subject> buffer”
The primary key must have all the fields we want to group by. No matter with the key size, is a temporary table.
The other “no key” fields are the quantities or amounts for totals.
Example: Table 307 “Inventory buffer”.
Step 2. Use the buffer table in process.
Declare the buffer table as Temporary=Yes.
When we want to group you must follow this sequence:
- Set the primary key fields.
- Check with FIND if exists. If not exists, do INSERT. Else increase totals and MODIFY.
Example report 790:
Step 1 Variable temporary declaration:
Step2. Set primary key fields:
Step 3. Check with FIND and if not INSERT, else modify:
Insert or update:
Improvements
I think we have to see buffer tables as objects, with its properties and methods. So when I add a new buffer table I extend the actual NAV Pattern adding this functions in the table:
Local UpdateBuffer and global function UpdateFromItemLedgerEntry. This global function must be used from called object and make simple the process.
If I want to group and total with this buffer with a new record (sales lines by example) only add this new global function in the buffer table:
*This post is locked for comments