>In my VFP app, I have a certain important table that is the header table for what we call a Job. Each Job will have many collections of records in various other tables that are associated with it: Time records, Material records, Invoice records, etc. So, the data is fairly well normalized and granular and that is good. However, the original architect of our system decided to keep track of the totals of each of these collections in its own field in the main Job table so that he could easily display or report things like "Total Material Cost" or "Total Labor Cost" or "Total Invoiced Amount", etc. So, all the forms and methods that manage the individual related collections, are also burdened with keeping the reference totals in the Job header table up to date. Kind of a nightmare, and creates a lot of coding that really should not even be necessary. And, despite all the best coding effort I can muster up, the reference totals are sometimes not correct, so I have made another maintenance prg to audit and correct the incorrect data.
>
>Am I right that this is a classic bad example of how to manage these reference totals? The values are indeed needed often throughout our system, but I am convinced that this is a bad way to do it.
>
>Now then, before I get too far into reworking how this is handed in my system, would some of you smart folks please tell me how you handle this common issue.

I maintain (someone else's) old FPD app that does this all over the place. As you point out, mostly it's a maintenance nightmare, updating "business logic" scattered across multiple forms any time one is changed. This particular app was designed by someone with a dBASE IV background, who was learning FPDOS while developing this app. It has no SELECT - SQL statements, it's all SET RELATION etc. While SQL makes it almost trivial to get things like totals from multiple joined tables, it's not trivial if you don't know SQL. That could be why your app was designed that way in the first place.

One rare benefit of that practice can be to help recover from data loss. In the FPD app, I've run into some cases where child rows lose column entries, or are lost entirely due to logic bugs, workstation crashes etc. Just by knowing what the order total value was (from the redundant value stored in the header table), an experienced operator can often determine what that order's line items must have been. This is uncommon, though: I've been maintaining that app for over 15 years, I'd say that "feature" has helped in this fashion once or twice per year.

I agree with the others, the main reason to do this with modern code is for performance, if it's computationally expensive to regen the totals from the normalized data. With fast CPUs and enough RAM to cache large parts of tables in RAM, it takes pretty complex logic to make properly coded VFP queries slow.

One thing you could look at to ease maintenance would be to implement database events or triggers. That way, anytime a child row is CRUDed, the parent total(s) could be updated automatically, without requiring business logic in the forms. Sort of a poor man's business logic tier.

If there's concern about creating complex SQL code to get totals, and having to do that in multiple places for multiple reports, one approach would be to create a view, which would be the same as the header table but with added Total column(s) generated by SQL. Then, for reports you could just USE or SELECT ... FROM the view, rather than the plain header table, and you know the total-generating logic is all done for you.