AnsweredAssumed Answered

Multi table conditional lookup

Question asked by MarkSchwartz on Oct 31, 2010
Latest reply on Nov 1, 2010 by MarkSchwartz

Title

Multi table conditional lookup

Post

Here is my story:

I have customers in a Customer table for simplicity it has one field: CutomerID

I sell Products from a Products table which also (surprisingly) has one field: ProductID

I have unique customer pricing that I keep in a CustomerPricing table.  This table has 3 fields: CustomerID, ProductID, CustomerPrice.

There is also an Invoice table with two fields: CustomerID and InvoiceID

And there is an InvoiceDetail table where I record the purchased products.  For our discussion it only has three fields: InvoiceIDPurchasedProductID and PurchasePrice

My invoice layout is linked to the Invoice table and has the CustomerID and InvoiceID on it.  It also has a portal linked to the InvoiceDetail table.

So here is the problem.  I can't figure out how to wire the relationships, so that the when I configure the lookup for the InvoiceDetail:PurchasePrice it pulls the price for the correct CustomerID and ProductID.

Thanks for your help.

Mark

Outcomes