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.
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 :)
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.
Nice I am running 11 advanced but have never tried a custom function. I would love to give that a try.
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.
That works like a charm! Perfect!