Client Payments: Portal Ideas?

[poslaw]

I am trying to conceptualize how to set up a relationship and/or portal. Any thoughts on this one...

Tables: Orders, Clients, Payments

Relationships:

Orders::ClientID=Clients:ClientID

Orders::OrderNumber=Payments::OrderNumber

I thought this was a great setup, as it allowed for partial payments. But here's the issue:

The user previously liked to browse Client Records and enter payments though this layout, as it was quicker since there was a portal to show ORDERS. The payments were contained in a payment field in ORDERS. The 3rd PAYMENTS table did not exist as the payment was recorded in the ORDERS table.

How can I show ORDERS in a portal on a CLIENTS layout and still allow for the Payments to be entered?

Perhaps I should scrap the PAYMENTS table and just use a repeating field? Any thoughts...

[Ender]

Perhaps I should scrap the PAYMENTS table and just use a repeating field?

Hold On! : Don't give up so quickly. The related table is the correct choice here, it's just a matter of getting the structure right and possibly adjusting the interface.

You said you wanted to allow partial Payments. Would a Payment be made on only one Order at a time, or could a Payment be split among multiple Orders?

[poslaw]

Payments come in to be split among multiple orders but they manually split them up and apply them to each order becuase the old system simply had a payment field for each order.

[Ender]

Okay, so a Customer has many Orders, and an Order has many Payments. But to enter Payments from a Client layout, you'd need to grab the OrderNumber from the specific Order that the payment is to be applied to (stick it in a global in the Client table,) and use a relationship directly from that to the Payment table. The interface for this might be clicking on a line in an Order portal, which sets the global and shows the correct Payments in a Payment portal.

Another structure you might consider, involves tracking Payments separately from Orders. For example, a Customer has many Orders and a Customer has many Payments, where the Payments aren't tied to a specific Order, but simply tied to the Customer. In this design, each Order doesn't necessarily have a balance due, but rather the Customer table calculates the balance:

Balance Due (calculation, number result) = Sum(Order::Total) - Sum(Payment::Total)

[flapjacks]

In my DB I created a seperate "A/R" table. When I close an invoice I send the InvoiceID, Date Of Invoice, Ammount and CudtomerID to A/R with set field. I can now do partial payments and anything else in AR with miminal effort. It was a bear to write and get the business rules right but the customer is pleased with the ease of use.

John

[poslaw]

Thanks, I'll try some of these suggestions out and let you know what happens.