How can I create the following cash receipts / payments form... (Question has been in my mind since 2006...)
I'm just looking for proper methodology, not a full solution here.
FORM / TABLE SETS / REQUIREMENTS:
While creating a cash receipts / payments routine, the form/layout must do the following:
1.) NEW PAYMENT SCENARIO:
Build a form / subform displaying all unpaid invoices for customer to display in sub form.
(individual amounts can be assigned to each unpaid invoice.
Within this form, the underlying query (or table set) must:
-be retrievable in the event user wishes to edit an existing payment. (see UPDATE PAYMENT SCENARIO below)
-include one extra field to record payment amount applied to individual invoices.
-show a sum of all previous payments applied to each specific invoice. (one invoice on each line of subform.)
Upon re-edit of a previously entered payment, this extra field must also populate with the prevous entered payment amounts (which could be $0), and then be editable for change. (Again, see UPDATE PAYMENT SCENARIO as follows.)
2.) UPDATE PAYMENT SCENARIO:
Use the same form/layout, but now show previously entered applied payments via transaction lookup:
-Form must be able to retrieve existing (previous) transactions, and show by line item how payment was applied to individual invoices.
THE FOLLOWING IS ABOUT AS FAR AS I EVER GET IN MY THINKING PROCESS:
SQL / table / sets scenario:
customers have many invoices. (1 - ~)
invoices can have many separately applied payments. (1 - ~)
And each payment transaction therefore can have many line items (aka invoices.) (1 - ~)
(but can also have 0, since it is possible to make an advance payment where no invoice yet exists.)
POSSIBLE APPROACH #1: (using summary table)
Because I want to display one line item for each outstanding (unpaid) transaction...
First create a new "temporary" summary table based on existing payments for customer.
-This table (table T) is generated every time a new payment is made. (becomes part of the subform / details data)
-This table shows the totals paid to date summed by invoice / purchase transaction.
-The payments details (subform) is then based on a set as follows:
summary (temporary table T) table is linked (the many side) to the payment transaction detail table (table P).
Regardless, the problem I keep postulating under this approach is as follows:
The above scenario would presumably work when pulling up existing payments for re-edit.
But when appying new payments, I can't figure out how to display (on the subform) one line for each unpaid
invoice, because table P (on the one side of the one to many set relationship) has no itmes to display. (until
the new transaction is actually saved.)
POSSIBLE APPROACH #2: (subform based on different data)
Then I begin to wonder if I need to create a form which has a subform based on different sets depending on whether user is entering a new payment, or editing a previous payment.
Under this scenario, my logic is as follows:
For new payments...
-Make the subform use a crosstab query for data, and then append this data to a payments detail table.
And while editing/deleting payments...
-Make the subform display existing payment data. (in much the same way as a simple invoice pulls up details)
-This would solve all the editing problems, including deleting the entire transaction. (A delete could either set
a flag so this transaction info is not included in the crosstab query above (when making new payments), or a
delete records function could be used.
Here the problems I encounter is:
How do I create a form with a subform based on a set which is dynamically assigned from within the header. Maybe
one solution here is to have a "NEW" button which loads an alternate form. But this seems like cheating.
POSSIBLE APPROACH #3: (table set linking back to itself)
Create a table set, somehow linked back to itself. (Using include all even if no matching child exists.)
Then I could append this table back into the payments table and use some of the methods previously mentioned above.
But here, how can I display the sum of all previously entered payments for each line item?
FINAL IDEA:
**With version 9, I'm also wondering if this a scenario which can be somehow greatly simplified by using the new "mapped tables" feature...
Maybe this is just a exercise for a SQL guru, I don't know.
Any insight here on the correct / viable / preferred way to do this will be greatly appreciated....
AAAaaaarrrrrrgggggg.....
I'm just looking for proper methodology, not a full solution here.
FORM / TABLE SETS / REQUIREMENTS:
While creating a cash receipts / payments routine, the form/layout must do the following:
1.) NEW PAYMENT SCENARIO:
Build a form / subform displaying all unpaid invoices for customer to display in sub form.
(individual amounts can be assigned to each unpaid invoice.
Within this form, the underlying query (or table set) must:
-be retrievable in the event user wishes to edit an existing payment. (see UPDATE PAYMENT SCENARIO below)
-include one extra field to record payment amount applied to individual invoices.
-show a sum of all previous payments applied to each specific invoice. (one invoice on each line of subform.)
Upon re-edit of a previously entered payment, this extra field must also populate with the prevous entered payment amounts (which could be $0), and then be editable for change. (Again, see UPDATE PAYMENT SCENARIO as follows.)
2.) UPDATE PAYMENT SCENARIO:
Use the same form/layout, but now show previously entered applied payments via transaction lookup:
-Form must be able to retrieve existing (previous) transactions, and show by line item how payment was applied to individual invoices.
THE FOLLOWING IS ABOUT AS FAR AS I EVER GET IN MY THINKING PROCESS:
SQL / table / sets scenario:
customers have many invoices. (1 - ~)
invoices can have many separately applied payments. (1 - ~)
And each payment transaction therefore can have many line items (aka invoices.) (1 - ~)
(but can also have 0, since it is possible to make an advance payment where no invoice yet exists.)
POSSIBLE APPROACH #1: (using summary table)
Because I want to display one line item for each outstanding (unpaid) transaction...
First create a new "temporary" summary table based on existing payments for customer.
-This table (table T) is generated every time a new payment is made. (becomes part of the subform / details data)
-This table shows the totals paid to date summed by invoice / purchase transaction.
-The payments details (subform) is then based on a set as follows:
summary (temporary table T) table is linked (the many side) to the payment transaction detail table (table P).
Regardless, the problem I keep postulating under this approach is as follows:
The above scenario would presumably work when pulling up existing payments for re-edit.
But when appying new payments, I can't figure out how to display (on the subform) one line for each unpaid
invoice, because table P (on the one side of the one to many set relationship) has no itmes to display. (until
the new transaction is actually saved.)
POSSIBLE APPROACH #2: (subform based on different data)
Then I begin to wonder if I need to create a form which has a subform based on different sets depending on whether user is entering a new payment, or editing a previous payment.
Under this scenario, my logic is as follows:
For new payments...
-Make the subform use a crosstab query for data, and then append this data to a payments detail table.
And while editing/deleting payments...
-Make the subform display existing payment data. (in much the same way as a simple invoice pulls up details)
-This would solve all the editing problems, including deleting the entire transaction. (A delete could either set
a flag so this transaction info is not included in the crosstab query above (when making new payments), or a
delete records function could be used.
Here the problems I encounter is:
How do I create a form with a subform based on a set which is dynamically assigned from within the header. Maybe
one solution here is to have a "NEW" button which loads an alternate form. But this seems like cheating.
POSSIBLE APPROACH #3: (table set linking back to itself)
Create a table set, somehow linked back to itself. (Using include all even if no matching child exists.)
Then I could append this table back into the payments table and use some of the methods previously mentioned above.
But here, how can I display the sum of all previously entered payments for each line item?
FINAL IDEA:
**With version 9, I'm also wondering if this a scenario which can be somehow greatly simplified by using the new "mapped tables" feature...
Maybe this is just a exercise for a SQL guru, I don't know.
Any insight here on the correct / viable / preferred way to do this will be greatly appreciated....
AAAaaaarrrrrrgggggg.....
Comment