+ Start a Discussion
SabrentSabrent 

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.

SabrentSabrent

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

 

IF(
(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?

*rdinakaran**rdinakaran*

Hi,

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.

 

 


 


SabrentSabrent

Thanks, tried as you suggested, however same error

 

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

*rdinakaran**rdinakaran*

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 ,

SabrentSabrent

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.