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...

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?

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.

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)

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

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