function readOnly(count){ }
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
+ Start a Discussion
Holly Stephens 2Holly Stephens 2 

Formula Assistance Needed!

Hello, 
I am trying to put together a formula field that will calculate a number based on the population of other fields. 
If a status field is equal to "available" and the a custom field called Role is populated with "Student Mentor" and then a Individual Goal Override is greater not equal to null, I need to subtract  another field (active students) from the individual goal override field.

However, if the status field is equal to "available" and the a custom field Role is populated with "Student Mentor" and then a individual Goal Override is null, I need to subtract Active Students from another field called Goal Load. if it doesn't meet these requirements, then it need to just show "0"

Can someone help me with this? Here is what I have done and it doesn't seem to work.

CASE( ISPICKVAL(Status__c, "AVAILABLE") && ISPICKVAL( Role__c , "Student Mentor") && IndividualMentorGoalOverride__c >0, IndividualMentorGoalOverride__c-ActiveStudents__c, ISPICKVAL(MentorStatus__c, "AVAILABLE") && ISPICKVAL( MentorRole__c , "Student Mentor") && IndividualMentorGoalOverride__c =0, MentorCollege__r.MentorGoalLoad__c - ActiveStudents__c,0)
Horst SchusterHorst Schuster
Hi Holly,
i'm not sure but the CASE cannot work with TRUE  ... Try this

CASE(  1,
IF(and( ISPICKVAL(Status__c, "AVAILABLE") , ISPICKVAL( Role__c , "Student Mentor") , IndividualMentorGoalOverride__c >0,1,0), IndividualMentorGoalOverride__c-ActiveStudents__c,
IF(and(ISPICKVAL(MentorStatus__c, "AVAILABLE"), ISPICKVAL( MentorRole__c , "Student Mentor"),  IndividualMentorGoalOverride__c =0),1,0), MentorCollege__r.MentorGoalLoad__c - ActiveStudents__c,0)
Holly Stephens 2Holly Stephens 2
No that won't work because if individual mentor goal load is greater than 0 , I want the equation for active students to be subtracted from the individual goal load. If it is equal to 0, then I want to have active students subtracted from MentorCollege__r.MentorGoalLoad__C.