I have a field called Price. I need to create another field called calculated price that will add a percentage depending on the value of the original Price field.

 

Example:

If the Price is $100.00 or more Subtract 10% from the price

If the price is $10.00 or less Subtract .25%

If the Price is between $10 and $50.00 Subtract .5%

If the Price is between $50 and %100.00 Subtract .75%

 

and so on and so on...

So if I have a record with a Price of $1.00 the calculation will fill in the Calculated Price field with $.75

Edited February 5, 20205 yr by OneStop

Ok, so I may have stumbled into somewhat of a solution....
I used this:

Let (  [
$Price = Price ;
$Discount  = Case  (
$Price <  10  ; .0025 ;
$Price < 50  ;  .005;
$Price < 100  ;  .0075 ;
$Price > 100  ; .01 )];
$Price - Price * $Discount)

 

I'm positive someone on here has a more elegant solution?

I would suggest you do NOT use a calculation field to calculate the rate of the discount. The price limits and their associated discount rates are subject to change - and if you change the calculation's formula, you will affect history records.

A better solution is to have a table of discounts and lookup the applicable rate from there into a local Discount field. Then you can make the calculation =

Price * ( 1 - Discount )

 

---
P.S. It is not necessary (nor is it good practice) to use $variables within a Let () calculation. These variables persist until the end of the current session and may interfere with subsequent evaluations of the same formula or another formula using a variable of the same name. You can define variable with no prefix to their names; the scope of these will be limited to the Let() function in which they were defined.

 

I agree with everything you said...best practices and what not.

However in this case, I just used FMPro to generate a list of Price Discounts for a 1 time deal to spit out some Excel spreadsheets for the beancounter nitwits in the office. I could have done the same thing in Excel but then I would have had to find a suitable closet rack to hang myself from when I handed them an Excel file with formulas in it and watched them burn the building to the ground like a scene from Planet of the Apes.

I was mainly curious if there was a more "direct" way to achieve what I did with the inordinately verbose Let statement I used.

I don't think your calculation is "inordinately verbose". The only redundant part is the default result, which could be simply that: a default result. And there is no need to define a variable whose value is a field. So you could shorten it to:

Let (
discount = Case ( 
Price < 10 ; .0025 ;
Price < 50 ; .005 ;
Price < 100 ; .0075 ; 
.01 )
; 
Price * ( 1 - discount )
)

I don't think there is a more "direct" way to do this, simply because the price breaks 10, 50 and 100 do not follow a pattern.

--
P.S. Note that your version will return no discount for a price of exactly  $100.00. 

 

Edited February 5, 20205 yr by comment

Good catch on the 100, I actually fixed that after I posted. I was hoping there would be some kind of magical function of which I wasn't aware that would allow me not to have to explicitly list each Case.

On 2/6/2020 at 12:02 AM, OneStop said:

I was hoping there would be some kind of magical function of which I wasn't aware that would allow me not to have to explicitly list each Case.

Well, if you really wanted, you could calculate the discount as =

0.0025 * ( 1 + ( Price ≥ 10 ) + ( Price ≥ 50 ) + ( Price ≥ 100 ) )

but I don't see what advantage this would bring. You still need to list the price breaks explicitly, because - as I mentioned earlier - they do not form a pattern. I would rather use Case() here on account of code readability.

 

Well see this is why you're a genius and I'm not.