Options for 'Transactions' and 'Balances' tables

Discussion created by Stu412 on Aug 18, 2016

Hi there


I'm toying with a pet project at the moment whereby sales or purchase transactions are stored on the table 'Transactions'.  The sales and purchase types can be assigned against any code up to 100, for any month, customer, supplier etc.


The issue I know I'll probably have later on is speed of reporting once the 'Transactions' table gets long.  I know there is massive capacity for transactions to be worked on in volume on FM, but the issue I fear and have had problems with historically is the field where it's Calculation>>>Summary type.   I appreciate it may be bad coding on my part as I'm not a guru with FM!


The question is, should I create a table called 'Balances' which holds a static version of all the summary figures and report from this (therefore without needing to refresh summaries each time I report), or has somebody managed to summarise a group of 500 records to a grand total from a found set of over 100,000 in a suitable amount of time?


I've used the warehouse approach like this with virtual lists to move the data very successfully in the past (as per Filemaker Advanced training series).  The problem I have with that is creating blank rows in the Balances/Warehouse table, or knowing how many I'd need.


Any experiences that people have had in similar heavy load reporting would be greatly appreciated.