3 Replies Latest reply on Apr 7, 2011 6:09 AM by lpnoires

    Lookup Price based on multiple criteria



      Lookup Price based on multiple criteria


      Hey all, i'm very new to Filemaker so please forgive me if there is an obvious answer to my issue.


      I'm customising FM Starting Point in Filemaker Pro 10 (mac) for use in my organisation.

       All fairly simple except for the pricing of products. we make custom products and the pricing is dependant on 4 factors Type, Finish, Height & Width.

      Height and Width of custom parts are entered in invoice line items.


      As the size combinations are infinate (down to the millimeter) we use pricing brackets. Combinations of height and width price brackets create a single price dependent on which type and finish is used.


      I have this all in a table as follows:


      TYPE (4 OPTIONS)






      the result is a table with 3200 prices in it. 




      How do i get Filemaker to select the correct price based on multiple criterea & rounding up of height and width?


      Each invoice line item has Type, Finish, Height and Width defined.


      so I need to  perform a lookup for the UNITPRICE field, that searches the PRICING table FIRST matching by Type, THEN matching by Finish, then rounding up to max height (next highest), then rounding up to max Width (next highest). to tell me the item price.


      I've tried creating multiple self defined relationships for the table but either i'm doing it wrong or its the wrong approach. 


      Your help is much appreciated. 

        • 1. Re: Lookup Price based on multiple criteria

          Define the relationship between LineItems and Pricing as:


          LineItems::Type = Pricing::Type


          LineItems::Finish = Pricing::Finish


          LineItems::Height ≤ Pricing::MaxHeight


          LineItems::Width ≤ Pricing::MaxWidth


          On the Pricing side of the relationship, sort the records by MaxHeight and MaxWidth - both descending. This will make the closest matching record the first related record - and the source for the price lookup.




          • 2. Re: Lookup Price based on multiple criteria

            Ha! how easy was that.


            Worked a treat...Many thanks for your help! 

            • 3. Re: Lookup Price based on multiple criteria

              Lookup Price based on multiple critera : Having read previous various other posts I concluded , for my personal purpose, that I needed, besides  my product table enclosing in it item code and the price per unit, another table enclosing the various quantities discounts for each code of item, (one line of record with code of item, quantity minimum, quantity maximum, discount).

              In my line items table, with the relationship many to one with the products table, a look up gives me the relevant informations about the item, including it's price. Works fine. 

              To get, in my line items table,  the quantity discount and compute the correct extended price based on the quantity, I guessed I need a relationship based on the item code between the line items table and the quantity discount table, allowing the use of a look up. Drawing the relationship I get, between the line item table and the quantity discount table, a many to many relationship. 

              Putting it to word, no show of quantity discount in the line items table, and looking back at my readings did not gave me answers.

              What would you suggest keeping the two tables, table product, and table quantity discount, to make it work ?