Help with relationship and fixed quantity in invoice database

[triplee23]

Hi,

I am trying to build a standard invoice solution, the only difference being that the quantity for every purchase being fixed. Some more details:

I have a COSTUMERS, INVOICES and PRODUCTS tables in addition to a LINES table. The database is standard with a relationship between COSTUMERS and INVOICES and LINES as a joint table between INVOICES and PRODUCTS. But there is one relationship that I can't work out. I want the quantity to be dependent on both COSTUMERS and PRODUCTS. So if costumer X buys product Y, the quantity added into LINES (portal) should be inserted automatically.

I am not able to sort out the relationship. I have created a table called MEMBER_QUANTITY but have no clue as to adding relationships. Do I relate MEMBER_QUANTITY to COSTUMERS or PRODUCTS or maybe both.

I add records to LINES through a portal and want the quantity to be added automatically depending on the chosen product in the portal and costumer the invoice is created for.

Can anyone help me with a solution?

Thanx!

Edited March 11, 2008 by Guest

[DavidEman]

If I understand you correctly, you'll need to use a new table MemberQuantity to contain Customer, Product and Quantity. Then set the Relationship from Lines to MemberQuantity to be based on Customer and Product. Have Quanity in Lines lookup from MembeQuantity. You could have Customer lookup from Invoice, so you won't have to populate it. Then when you select the Product it will pull Quantity from MemberQuanity. Note that when you have a few lookups the timing can get funky, so you may need to put a script in to get the timing right. What I mean is that the portal row needs to be committed with the Customer before it will lookup the Quantity.