6 Replies Latest reply on Jul 28, 2010 1:31 PM by cstowell@crown-point.com

    Multiple Discounts using new totals

    cstowell@crown-point.com

      Title

      Multiple Discounts using new totals

      Post

      I have a table to hold all discounts. I am trying to find a way to run a calculation that runs each discount and re-totals and runs the next until all discounts are complete. I know this could be accomplished via script but I would like to avoid breaking normalization on such an important number (the final price!!!).

      Example:

      total before discount $100.00

      15% off $85.00

      10% off $76.50

      This puts the total discount @ 23.5% not the 25% you would get by summing the discounts and then dividing.

      Thank you in advance,

      Craig

        • 1. Re: Multiple Discounts using new totals
          philmodjunk

          I don't see how using a script for this "breaks normalization", but a calculation may be possible.

          How do you record your list of discounts? In a related table?, In dedicated fields? a repeating field? Is there always the same number of discount "steps" or could the number vary?

          Depending on what you need, your only choice may be to use either a custom recursive function or a script.

          • 2. Re: Multiple Discounts using new totals
            cstowell@crown-point.com

            The discounts are in a table and they can vary.

            I would say it breaks normalization because you have to store the results.

            Thanks for getting the ball rolling :)

            Craig

            • 3. Re: Multiple Discounts using new totals
              philmodjunk

              Yes, but the stored result isn't stored in more than one place which is how Normalization is usually defined.

              I get your point though that with a scripted approach, you have to also script updates for any relavant changes that might be made. For an Invoice system where amounts aren't supposed to change once the transaction is "final", that doesn't strike me as a major issue.

              Hmm, a recursive custom function stikes me as the cleanest approach here if you have a copy of filemaker advanced to use to define and add that function to your file. Let me know if you want to see that.

              Let's say you have up to 3 discount steps in your related table, I'll call it Discounts for this discussion:

              Let ( d = list ( Discounts::Rate ) ; Total*GetValue ( d ; 1 ) + Total*GetValue ( d ; 1 ) * GetValue ( d ; 2 ) + Total*GetValue ( d ; 1 ) * GetValue ( d ; 2 )*GetValue ( d ; 3 ) )

              If you have more than a max of three possible, you'd add more terms and it gets progressively more complex.

              • 4. Re: Multiple Discounts using new totals
                cstowell@crown-point.com

                Nice I am running 11 advanced but have never tried a custom function. I would love to give that a try.

                • 5. Re: Multiple Discounts using new totals
                  philmodjunk

                  I find recursive custom functions fun so I was hoping you would want that. It's also much cleaner looking and works with any number of discount amounts.

                  Open Manage | Custom functions, click New and paste the text below into the custom function window:

                  /* This function deducts a sequence of discount percentages listed in Dlist from the amount in Amt */
                  If ( IsEmpty ( Dlist ) ; Amt ;
                              SteppedDiscounts ( Amt - Amt * GetValue ( Dlist ; 1 ) ; RightValues ( DList ; ValueCount ( Dlist ) - 1 ) ) )

                  Name the function SteppedDiscounts or edit the last line to match a name you choose to use. Add Amt and Dlist as parameters, Amt should be listed before Dlist to match the order in the last line of the function.

                  To use this function, define a calculation field like this:

                  SteppedDiscounts ( UndiscountedTotal ; List ( RelatedDiscounts::RateField ) )

                  This field will now compute the discounted total of your invoice.

                  • 6. Re: Multiple Discounts using new totals
                    cstowell@crown-point.com

                    That works like a charm! Perfect!