Calculating periodic totals

[bluearrow]

Hi,

I am currently using FMPro/FMServer5.5 (win domain), and plan to update soon to FM8.

I have a design problem that I would like to solve and implement in v8.

I need to calculate and show to the users some numeric field totals. For instance, in a client-invoicing database, the user chooses a client and needs to know the weekly, monthly and annual invoicing totals. My problem is not how nor when to calculate those totals, but to find the best way to store the calculated totals, which I think is compulsory (I need to avoid having to calculate this data when the user request it).

Currently, I calculate these totals during idle hours (when the database is not being used, at night). However, I need to previously create the fields to store this information. This means, for instance, that I need one field for every week / month / year. And this is a nightmare and makes the database dependant on the developer (myself), which I would like to avoid as much as possible.

There must be a standard way to do it; maybe automating the creation of fields (I do not like it too much, because the database will soon be populated by many fields). Maybe using repetition fields (I am not sure how to implement this, anyway; for instance, if I store the values in a repetition field, how do I identify, select and show to the user the value for a specific month or year?).

It is clear to me that the fields need to be previously calculated and stored. In the current situation, with thousand of invoices per month, it is just not acceptable for the user to wait until the calculation is done (for instance, even if the fields is indexed, calculating the monthly or annual invoice total could take several minutes in a fast computer).

I would appreciate your help.

TIA

[Ender]

You could store the totals in a separate table. Using separate records by Client for each week, month, and year. They could then be accessed via a filtered portal, showing the Type of total to view.

[bluearrow]

You could store the totals in a separate table. Using separate records by Client for each week, month, and year. They could then be accessed via a filtered portal, showing the Type of total to view.

Thank you Ender.

From your answer I understand that I should treat this situation like any other one to many relationship. Is this a common practice for storing this type of values?

(please excuse my ignorance, but I have not seen any "standard" solution to this issue, and I do not know if FM8 changes this somehow).

[Ender]

This idea of storing totals is useful on slow networks, where recalculating takes a long time, and the data doesn't change much during the day. For previous weeks, months, years, this will be the case. For updating the current week, month, and year, you'll need to decide how you want to do it (and how often.)

One possible solution is to update the current totals when each invoice is closed (or shipped.) The update would simply increment/decrement the totals by the amount of the invoice. You have to be careful about record locking issues, but the cost of this calculation at the time the invoice is closed is very minimal.

You'll probably still need to have a script to do a recount for the week/month/year, in case the totals get out of sync. This script could be run nightly, weekly, or just as needed.

FM7/8 has a big advantage when it comes to filtering the invoice relationship by a date range. You can (manually or automated,) set a Start Date and End Date for a particular week/month/year, and then have your total calculated from the related invoices in the specified range.

[bluearrow]

Thank you Ender,

I have implemented the ideas you sketch in your message, and it seems to be working fine in my trials. The recount script is an excellent advice, because although it does not seem easy for the values to get out of synch, if it happens, it will be a big problem, and one that is very difficult to spot.

I am working now in the reporting side of the problem. I will probably store all value in a "Totals" table, and use that table as the base of my report.

Regards