You need to sign in to do that
Don't have an account?
Holly 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)
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)
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)