8 Replies Latest reply on Jun 25, 2009 5:36 PM by footumpth

    Confused with what should be a simple calculation.

    footumpth

      Title

      Confused with what should be a simple calculation.

      Post

      Hi there, I am trying to do something which I would have thought simple however each and everything I have tried has given me a duff result. Now I've tried this so many ways I think I've gone snow blind! 

       

      I have a database of products, each product has 3 fields to enter potential discounts, i.e. you can set the quantity to buy to get a discount. Thus there are three potential price breaks, and three field to set the discount. 

        

      There is also a 4th field to set an override discount which takes presedent over all else. 

       

      So then to calculate the result I want it to look at the overide discount if thats not empty then it can base the final calc on that. I.e qty*price*override discount.

       

      or pass it through to the 3 discount fields which then must assess if the qty required >= one of the price breaks then use that for the final calculation. i.e. qty*price*discount field

       

      or if there is nothing entered then it just goes into a final calc of qty*price.

       

      This is set into a global value to pass off to another database. 

       

       

       What I can't seem to master is getting the whole thing to work, I can get some bits working but not all. I have tried various versions of case or if requests and nesting them but its going wrong somewhere. I dunno now if I'm even using the right statement.

       

      where i seem to be having problems is if the discount fields are left blank and handling the price breaks. 

       

       Any advice would be appreciated as I just can't seem to get my brain around this one!

        • 1. Re: Confused with what should be a simple calculation.
          philmodjunk
            

          The case function should do what you want.

           

          Case (isempty(OverrideDiscount), qty*price*OverrideDiscount,

                   qty > LargestBreakPoint, qty*price*DiscFld3,

                   qty > NextLargestBrkPt, qty*price*DiscFld2,

                   qty > SmallestBrkPt, qty*price*DiscFld1,

                   qty*price)

           

          Case will perform the calculation with the first matching expression that returns true. Thus you test first for the override quantity then each discount break point in decreasing order. The final clause: qty*price will be used if none of the expressions return true.

          • 2. Re: Confused with what should be a simple calculation.
            footumpth
              

            thats exactly what I'd expect and have used that formula already other than it doesn't work quite like that 

             

            Case (isempty(OverrideDiscount), qty*price*OverrideDiscount, (this doesn't work if the field is empty, so used (not isempty(override discount)

             

                     qty > LargestBreakPoint, qty*price*DiscFld3,

                     qty > NextLargestBrkPt, qty*price*DiscFld2,

                     qty > SmallestBrkPt, qty*price*DiscFld1, (this bit works but only if there is something in the break field, if its blank then all I get as a result is 0 and not the default result)

             

                     qty*price) 

             

            and thats whats causing me the headace as only some of the products have the price break or some may not use all three

             

            ?? 

            • 3. Re: Confused with what should be a simple calculation.
              comment_1
                

              Try:

               

              Case (

              not IsEmpty (  OverrideDiscount ) ;  ...

              not IsEmpty (  LargestBreakPoint ) and  Qty ≥ LargestBreakPoint ; ...

               

               

              ---

              I am puzzled where you are when you are doing this calculation. Usually, one would do this in a LineItems table as a lookup/auto-enter, so that future price changes do not affect the invoice.

              • 4. Re: Confused with what should be a simple calculation.
                philmodjunk
                   Comment as usual caught my mistake. The Not was missing. I had a glitch in my system that forced me to retype this  and I left it out the second time around.
                • 5. Re: Confused with what should be a simple calculation.
                  mrvodka
                     In am in full agreement with Comment. You really should look into using a lookup to grab the correct discount. You could then override the discount right there without even an additional field
                  • 6. Re: Confused with what should be a simple calculation.
                    footumpth
                       cool that did it :not IsEmpty (  LargestBreakPoint ) and  Qty ≥ LargestBreakPoint ; ...
                     it was the and I was missing to get it to evaluate both varibles :)
                     
                    Basically I have the product database which has all the info in and some globals to store data so this isn't actually used to huld any other record data, then this passes over data to an invoice data file which then looks up the remaining info setting that into the invoice data, thus future product or price changes don't impact that historical data.
                     
                    There is another databse that sits inbetween the two which actually dispalys the related invoice data, so this is mostly just a portal of layouts.
                     
                    I wanted to parse the right info first before then handing it on to the invoice data.
                     
                    That does the job nicely tho :)
                     
                    Thanks you to all. 
                    • 7. Re: Confused with what should be a simple calculation.
                      comment_1
                         Just FYI, the standard method to do this is to have a Prices table (a child of Products) with fields for FromQuantity and Price. The line item can then lookup the matching price directly, based on ProductID and Quantity.
                      • 8. Re: Confused with what should be a simple calculation.
                        footumpth
                          

                        I do have a seperate table of prices, actually I have a number of tables of seperate price lists from different manufacturer's however have kept these simple to make changes easier. I.e a manufacturer can send me a price list in excel, we can convert that and stitch it straight into the data base. Then if the manufacturer changes the price list we can comprare the bare bones list for changes or swap to replacement without loosing our own added data such as discounts etc.

                         

                        I can add in addional price lists without affecting any other lists.

                         

                        These then all suck into a central product data base that then adds lots of other info such as description, images and how products relate to each other (as some form into kits made from several parts)

                         

                        This main product database can then generate a compiled price list, upload to website etc etc, it seemed a pretty simple step to use it to throw items into an invoice. Maybe slightly topsy turvy but it works :)

                         

                        well it does with a little help!