function readOnly(count){ }
Sign Up ›
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
Learn More

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
OnCloud9OnCloud9 

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! 

OnCloud9OnCloud9

So I made it this far:

 

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

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

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

IF(
 AND(
  ($RecordType.DeveloperName = "Record Type B"),
  (X*Y <=80)),
   X*Y, 80))

 
OnCloud9OnCloud9

ahh 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 BoettcherAndy Boettcher

You should investigate the "CASE" formula logic operator.  That should get you moving again.
OnCloud9OnCloud9

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

 

Tried using case like this:

 

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

 

 

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: 

 

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

 

Any other idea on how to approach?
Andy BoettcherAndy 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_SFGRajesh_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))