+ Start a Discussion

Reusing a filter logic in a formula field

 I have report with the logic as shown below,

Filtered BY: (1 AND ((2 AND 4) or (3 and 5))) AND 6

1. Field1__c equals 2
2. Field2_Date__c less than LAST 30 DAYS
3  Field3_Date__c less than LAST 30 DAYS
4. Field4__c equals 2
5. Field5__c equals 2
6. Field6__c equals Active,Probation


I am trying to use the same logic  to populate a number field with a value of 2


(IF(Field1__c = 2) &&  ((IF( TODAY() + 30 >  Field2_Date__c) && IF (Field4__c  =2))|| (IF( TODAY() + 30 >  Field3_Date__c) && IF ( Field5__c =2)) )) && IF ( Field6__c IN('Active','Probation'), 2,0)


However I am getting an error: Syntax error extra ','


Any suggestions will be appreciated.


I worked this out and got rid of the syntax error, however I am getting this error

Compiled formula is too big to execute (6551 characters).Maximum size is 5,000 characters


(Field1__c==2 &&
((today() + 30 > Field2__c) && (Field4__c==2))
((today()+30>Field3__c) && (Field5__c==2))
(Field6__c == 'Active' || Field6__c == 'Probation')
,NewField__c=2,NewField__c=0 )



Any suggestions. I don't want to put this in a workflow field update. Any way i can handle this in formula field itself?



I suggest you to write your formula like this,I hope it will work.


NewField__c = 


IF(AND(AND(Field1__c = 2,OR(And(Field2_Date__c < today()-30,Field4__c = 2), 
AND(Field3_Date__c < today()-30,Field5__c = 2))),OR(Field6__c == 'Active',Field6__c == 'Probation')),2,0)


Let me know if it works.





Thanks, tried as you suggested, however same error


Compiled formula is too big to execute (7,254 characters).


1. Field1__c (Number)
2. Field2_Date__c (Date)
3  Field3_Date__c(Date)
4. Field4__c (Number)
5. Field5__c (Number)
6. Field6__c (Picklist)



above are field types in developer org.


"No syntax errors in merge fields or functions. (Compiled size: 366 characters)"


Thanks ,


I am sorry, I should have specified this in the first place. These are the Datatypes -

Field1__c             (Formula:Number)
Field2__c             (Date)
Field3__c             (Formula:Number)
Field4__c             Date    
Field5__c             Formula (Number)        
Field6__c              Formula (Text)

Currently as a workaround, I have a workflow-formula and it's working fine, but I am a bit apprehensive about workflows as I have too many on the concerned object.

I guess my best option is to write the logic in a class and call the class method in trigger.

Thanks for your help.