AnsweredAssumed Answered

Multi table conditional lookup

Question asked by MarkSchwartz on Oct 31, 2010
Latest reply on Dec 21, 2018 by user2148642


Multi table conditional lookup


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.