Jump to content
Salesforce and other SMB Solutions are coming soon. ×

Nested Case / If statements


RustyT
 Share

Recommended Posts

Hi

 

I'm hoping someone might review a calculation for me. I was having great trouble getting a nested case / if statement to work. It did eventually come to me as I was writing this post but I'm thinking there is probably a more elegant way of writing it.

 

The situation is this.

 

  • I have an id_dealtype field
  • I have five other fields leasePerAnnum, purchasePrice, rentalValue, leaseStartDate and term
  • If the dealtype = 1 AND four of the five fields are empty leasePerAnnum, rentalValue, leaseStartDate and term, then I want to display the message "Please complete all relevant fields for an accurate profit figure"
  • OR if the dealtype = 5 AND a different four of the five fields are empty purchasePrice, rentalValue, leaseStartDate and term, then I want to display the same message
  • otherwise if neither if these criteria are met, no message will be displayed.

 

The resulting calculation is this - which appears to work.

 

Case(

(id_DealType = 1 and Evaluate (IsEmpty(leasePerAnnum) or IsEmpty(rentalValue) or IsEmpty(leaseStartDate) or IsEmpty(term))) or (id_DealType = 5 and Evaluate (IsEmpty(purchasePrice) or IsEmpty(rentalValue) or IsEmpty(leaseStartDate) or IsEmpty(term))) ;"Please complete all relevant fields for an accurate profit figure";

"")

 

Is there a better way of writing this?

 

Many thanks in advance.

 

Russell

Link to comment
Share on other sites

I'm not at all clear on what you mean by "four of the five fields are empty" followed by a list of four (not five) fields, but I am guessing that you either mean none of the remaining four fields can be empty or else you mean that at least one of the remaining four fields must have a value. (??) Also unclear on what you mean by "a different four of the five fields" followed by the exact same listing of four (not five) fields.

 

But here's a more elegant way of assessing whether any, or all, of a bunch of fields are empty:

 

 

Let ([

lpa= Left ("x"; length (leasePerAnnum));

ppr=Left ("x"; length (purchasePrice));

rtv=Left ("x"; length (rentalValue));

lsd=Left ("x"; length (leaseStartDate));

trm=Left ("x"; length (trm))

];

 

Case (

id_DealType = 1 and length (lpa & ppr & rtv & lsd & trm)

 

id_DealType = 1 and length (lpa & ppr & rtv & lsd & trm)>0; "I see you filled out at least one field, good for you";

 

id_DealType = 1 and length (lpa & ppr & rtv & lsd & trm)=3; "I see you filled out exactly three fields; please fill out a fourth field";

 

id_DealType = 1 and lengh (lpa & ppr & rtv & lsd & trm)=0; "You didn't fill out any of the fields"

 

)

)

Link to comment
Share on other sites

 Share



×
×
  • Create New...

Important Information

Terms of Use