Dumb & Dumberer
The obvious finally hit me this morning. Talk about a blind spot. First, allow me to restate the "problem" (given my convoluted explanation in this thread):
The Problem:
Roughly 1 out of 5 payments is to pay off all or part of multiple invoices. The remainder usually pay off one invoice each. Thus most payments were allocated on the parent (i.e. check) level. The remainder were allocated on the child level (to multiple invoices). The allocations for both tables were performed with a simple posting rule (very reliable by the way). But, of course, this violates db normalization rules and forced me to "jump thru hoops" to make things work properly in various places - such as the project_invoice_payments set and the client_invoice_payments set - both of which I need.
The solution (I'm surprised no one thought of this):
I simply moved all of the allocations to the child table, period. Thus the 4 out of 5 single payment parent records now must have one child record to allocate the payment. Multiple allocations continue to use the child table as before. The sum of the child record(s) must equal the check amount field ("The Enforcer"). End of story.
See the attached ugly form (under development) images which demonstrates this simple (and obvious) solution...
The obvious finally hit me this morning. Talk about a blind spot. First, allow me to restate the "problem" (given my convoluted explanation in this thread):
The Problem:
Roughly 1 out of 5 payments is to pay off all or part of multiple invoices. The remainder usually pay off one invoice each. Thus most payments were allocated on the parent (i.e. check) level. The remainder were allocated on the child level (to multiple invoices). The allocations for both tables were performed with a simple posting rule (very reliable by the way). But, of course, this violates db normalization rules and forced me to "jump thru hoops" to make things work properly in various places - such as the project_invoice_payments set and the client_invoice_payments set - both of which I need.
The solution (I'm surprised no one thought of this):
I simply moved all of the allocations to the child table, period. Thus the 4 out of 5 single payment parent records now must have one child record to allocate the payment. Multiple allocations continue to use the child table as before. The sum of the child record(s) must equal the check amount field ("The Enforcer"). End of story.
See the attached ugly form (under development) images which demonstrates this simple (and obvious) solution...
Comment