2 Replies Latest reply on Nov 1, 2010 8:14 PM by MarkSchwartz

    Multi table conditional lookup

    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

        • 1. Re: Multi table conditional lookup
          philmodjunk

          You'll need to get the CustomerID value into a field in InvoiceDetail so that a relationship from InvoiceDetail can match to CustomerPricing by both CustomerID and ProductID. If you only need this relationship to be "one way" (you only need to access CustomerPricing records from InvoiceDetail and never the reverse), you can define a calculation field in InvoiceDetail that simply lists the field name of your CustomerID field in the Customer table. If you need it to be "two way" (you also need to be able to access InvoiceDetail records from CustomerPricing), you should make this a number field that uses a Looked up value field option to copy the customer ID number into the InvoiceDetail customerID field.

          • 2. Re: Multi table conditional lookup
            MarkSchwartz

            Phil,

            One way is good for me.  I'll give this a try.  Thanks,

            Mark