4 Replies Latest reply on Sep 7, 2013 10:42 AM by philmodjunk

    calculation lookup question



      calculation lookup question


           On the order layout I want to pick a material which is a drop down match of the list of materials on the materials layout, then I want to type a width and a thickness. 

           I want the system to look at the materials a return an “order::price” based on a material match which is based on width less than or equal to “Materials::Width” and thickness less than or equal to “Materials::Thickness” but return closest value to both width and thickness and not all values less than or equal to.

        • 1. Re: calculation lookup question

               this might help explain

          • 2. Re: calculation lookup question

                 And what tables, relationships have you set up?

                 The basic set of invoicing tables and relationships are a good start:


                 WHere you  use a portal to lineItems and add new records in that portal to list the materials.

                 From there, there are several different approaches that can supply a thickness based price:

                 You can match by LineItems to Materials by both a Material ID and a thickness to look up the price. This requires one record for each thickness of material.


                 LineItems::_fkMaterialID = MaterialsThicknessPrices::_fkMaterialID AND
                 LineItems::Thickness = MaterialsThicknessPrices::Thickness

                 For an explanation of the notation that I am using, see the first post of: Common Forum Relationship and Field Notations Explained

                 Or you can set up several fields in each Material record to document the different thicknesses and their prices. An auto-enter calculation in LineItems can then copy data from different fields in Materials by what thickness is specified.

                 The first option is more flexible as you can have as many different thicknesses as you need and adding more thicknesses is a matter of adding more records to your table, and reports listing out all your thicknesses and current prices are pretty straight forward. But if your materials only have a few thicknesses, the second option may be simpler to set up and to maintain the different prices.

            • 3. Re: calculation lookup question

                   it's not the relationship i'm having problems with here its more the calculation side of things, i.e.  i will have lots of materials with lots of different widths and thicknesses.  However if have have a material that is 120 wide and another one that is 150 wide but both are 50 thick and i need  a material that is anywhere inbetween 121 and 150 than i want it to lookup the price for 150. so it looks at my table of matching material names then looks at thickness and then looks at the nearest width to the next width up.  i don't think an if statement on its own can handle this and i was looking at a case statement but still with no joy.

              • 4. Re: calculation lookup question

                          it's not the relationship i'm having problems with here

                     Yet it's the relationships that you choose to use that will control what pricing info is looked up. Since you didn't describe what tables and relationships that you are using, I had to start with those for any answer that I post.

                     There are two basic approaches you can implement and I can't choose one for you.

                     If you can devise a formula that uses dimension info to calculate the "piece price" from a looked up unit price, we can implement that. But I'd need to see that formula. Such a formula can even be different for each type of material as the formula can be looked up right along with the unit price.

                     Without a formula, you need some method for using a) the type of material and b) it's dimensions to look up that price. My previous post described an approach for that. If you need to use more factors than the thickness, (such as the width), a relationship that matches by more fields could be used or a more complex calculation and multiple fields can be employed.


                          i don't think an if statement on its own can handle this

                     I agree and did not suggest that you use one. A CASE function, on the other hand, could do this, but the other option where you have one related record for each individual price will be much more flexible.