You need to sign in to do that

Don't have an account?

OnCloud9

# Formula based on record type

Greetings,

I have 3 record types:

1) Record Type A

2) Record Type B

3) Record Type C

Depending on the record type, I'd like do a specific calculation: if record type A, then X*Y. Exception: if X*Y > 100, then it should show 100, otherwise X*Y.

For record type B, the calculation should be X*Y > 80, then show 80, othewerwise X*Y (answer would be less than 80).

I have this as my current formula:

AND( ($RecordType.DeveloperName = "Record Type A"), IF(X*Y> 100, 100, X*Y) )

However I keep getting an error. I see where the error is coming from because it's not really calculating it with the AND expression. I feel like this is a simple mistake I'm overlooking. Can anyone help with the logic?

Thank you x 100!

OnCloud9

So I made it this far:

that seems to work fine. Now when I try to add an option for the second record type, i get an error:

OnCloud9ahh the ^^ formula doesn't work at all. the logic is wrong because it only calculates it for where X*Y <= 100, when in reality it should be calculating for all numbers, <100 or >100.

Andy BoettcherYou should investigate the "CASE" formula logic operator. That should get you moving again.

OnCloud9

Thanks for the note. Seems so easy on paper, yet difficult in the cloud! =)

Tried using case like this:

Looks ugly and it doesn't work. It gives an error based on the (<100) part, saying it found "<" as an invalid character. If i move the <100 into the expression and remove the commas, it still gives an error:

Any other idea on how to approach?

Andy Boettcher

Throwing a curveball in your line of thinking....

There have been situations where I've had to do multi-step calculations that didn't for whatever reason work within a single formula field. Can you try and break up your logic into 2-3+ formula fields building on each other to kind of "stage your calculation" through to your desired result?

-Andy

Rajesh_SFG

Hi OnCloud9,

Use CASE function for your requirement as shown below,

CASE($RecordType.DeveloperName, "Record Type A", IF((x*y<=100),x*y,100),"Record Type B", IF((x*y<=80),x*y,80))