7 Replies Latest reply on Nov 12, 2012 5:01 PM by comment

    Help with finding proper price based on entered information

    rdgeib

      Hello,

       

      I am looking for some helpwith a database we are building. We have a table of products. Each product has 3 different price points based on volume. Each of the prices is a record in the table. When building an invoice, we ask for the quantity and size of the particular product being produced. We then use these figures to calculate the square footage of each particular product to be produced. Now, here is the issue, we would like the pricing to automatically be pulled from the table based on the square footage of product to be produced. We currently have the field set up as a calculation using a Case statement and a GetField function to evaluate the number of square feet needed. The problem is that I cannot figure out how to use the GetField function to choose the proper field for the proper product. The Product table has maybe 30 products in it. Each product has 3 price points, so that is about 90 records in the table. If there was only 1 product in the table with 3 different price points, I can get it to work. But, since all products are in 1 table, I am struggling to get it to choose the correct field from the correct record. ANy ideas that might help? I am working in FM V12.

        • 1. Re: Looking for some help
          pminich

          I think that you need to rethink your product table.

           

          Without knowing more I would suggest that your product table have a record

          for each product/price point then you can creat a relationship that will

          pull in the correct price. Instead of 30 records with 3 prices each you

          would have 90 records with 1 price each.

           

           

          --

          iPhone

          Pete Minich

           

           

          Direct/FaceTime/Text  - 781.223.8884

          • 2. Re: Looking for some help
            rdgeib

            Pete,

             

            I do have 90 records with 1 price in each. I am having trouble figuring out how to get the Case (GetField) statement to choose the proper price based on the calculation of square footage. FOr instance, a salesman chooses a product (drop down), enters the quantity and sizes. This information is sued to figure out how much square footage is needed to be produced. Now, I use the Case (GetField) state ment to evaluate the amount of squarefootage (3 levels). But, once I process that statement, I do not think the statement knows which product was entered and therefore, which price point to pull. Does this make sense? I am not sure if I am going down the wrong road and maybe should be approaching it differently. Thanks in advance for ANY help.

            • 3. Re: Looking for some help
              rdgeib

              Maybe I should delete the table and rebuild it. I think I am going to try that. It makes sense (your response) and this is what IO was thinking when I was building it. Maybe my relationship is off or something. Please let me know if you can think of a different approach as well.

              • 4. Re: Looking for some help
                comment

                rdgeib wrote:

                 

                I do have 90 records with 1 price in each. I am having trouble figuring out how to get the Case (GetField) statement to choose the proper price based on the calculation of square footage.

                 

                You should be using a lookup to choose the price, not a calculation. Otherwise your existing records will recalculate when you update your price list.

                 

                A lookup is easy to do once you have a relationship based on:

                 

                LineItems::ProductID = Prices::ProductID

                AND

                LineItems::Quantity ≥ Prices::FromQuantity

                 

                with the Prices records sorted by FromQuantity, ascending descending.

                 

                 

                 

                ---

                P.S. I suggest renaming the thread with something more descriptive.

                 

                 

                 

                Message was edited by: Michael Horak

                • 5. Re: Looking for some help
                  pminich

                  Exactly. You shouldn't be using a getfield. I would set each price record

                  with a min/max sq footage that it applies to. Then build your relationship

                  for the invoice line price based on the type of relationship described.

                   

                  --

                  iPhone

                  Pete Minich

                   

                   

                  Direct/FaceTime/Text  - 781.223.8884

                  • 6. Re: Looking for some help
                    rdgeib

                    Michael,

                     

                    Thank you very much for your response. I am going to look into this.

                    • 7. Re: Looking for some help
                      comment

                      pminich wrote:

                       

                      I would set each price record

                      with a min/max sq footage that it applies to.

                       

                      Actually, the min boundary is quite sufficient, for example:

                       

                      ProductID
                      FromQuantity
                      Price
                      123125.00
                      1235022.50
                      12310020.00

                       

                      This way, any amount above 100 is provided for, and you don't need to match the min to the previous max.

                       

                      Message was edited by: Michael Horak