You need to sign in to do that
Don't have an account?
Formula Help
Hi all!
I am creating a formula that counts events for a company. The hitch is - if the "meeting type" field (which is a custom picklist) on the activity record is "conference call" or "web meeting" - I do not want it to count it.
I put together the formula below - but it does not seem to be working. Any help would be very appreciated.
IF( AND(Activity_Type__c = "Event", OR( NOT( ISPICKVAL(Meeting_Type__c, "Conference Call")), NOT(ISPICKVAL(Meeting_Type__c, "Web Conference"))))
, 1, 0)
Actiivty_Type__C is a text formula field that will either be "Event" or "Task".
Best regards,
John
You could also modify your formula like this:
IF( AND(Activity_Type__c = "Event", NOT(OR(ISPICKVAL(Meeting_Type__c, "Conference Call"), ISPICKVAL(Meeting_Type__c, "Web Conference")))), 1, 0)
Switching the OR and the NOT fixes the logic. It now reads: If the activity type is Event, AND the meeting type is NOT Conference Call OR Web Conference, then 1 else 0.
If this helps, please mark it as a solution, and give kudos (click on the star) if you think I deserve them. Thanks!
-Glyn Anderson
Certified Salesforce Developer | Certified Salesforce Administrator
All Answers
Hi John,
The problem is in the logic... specifically in the OR part of the logic statement. If Meeting_type__c is "Conference Call" then the left hand side of the OR statement evalutates to False, but the right hand side of your OR statement evaluates to True. Therefore the IF statement is always evaluating to true and so every record is being counted.
Try rewriting your statement as a nested IF as follows:
IF (Activity_type__c = "Event", If(ISPICKVAL(Meeting_Type__c, "Conference Call"), 0, If(ISPICKVAL(Meeting_type__c, "Web Conference"), 0, 1)), 0)
So now it will evaluate Activity_type__c as an event. If that is true, then it looks to see if Meeting_type__c is Conference Call. If it is, the result returns 0. If Meeting type is not Conference call it then evaluates Meeting type for Web Conference. If that is true then 0 is returned otherwise 1 is returned. If the Activity type is task, then a value of zero is returned.
Hope that helps get your activities counted.
You could also modify your formula like this:
IF( AND(Activity_Type__c = "Event", NOT(OR(ISPICKVAL(Meeting_Type__c, "Conference Call"), ISPICKVAL(Meeting_Type__c, "Web Conference")))), 1, 0)
Switching the OR and the NOT fixes the logic. It now reads: If the activity type is Event, AND the meeting type is NOT Conference Call OR Web Conference, then 1 else 0.
If this helps, please mark it as a solution, and give kudos (click on the star) if you think I deserve them. Thanks!
-Glyn Anderson
Certified Salesforce Developer | Certified Salesforce Administrator