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: InvoiceID, PurchasedProductID 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.