function readOnly(count){ }
Sign Up ›
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
Learn More

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
Steve CairneySteve Cairney 

IF statement where the formula checks for another field

Hi all,

I have to create a formula that I am unsure on the the syntax for.

This is for a formula field on the Opportunuty record. It will reference one of 12 fields that are populated on the User record.

What I need to do is have the formula check what Month the CloseDate is and then use one of the correllating fields on the User record to work out a percentage

So if the CloseDate is April, the formula will use the April field in the User record, if it's March, it will use the March field in the User record.

To throw more complexity in I am using ranges and different percentages based on user and profile ID.

The below formula works but it only references one field (the current month)
  
IF( 
OR(Owner.Profile.Id = "00e20000001V4Xd",Owner.UserRoleId = "00E2000000151Hy"), 
Margin__c * 0.15, 



IF( 
AND(Users_Total_Margin__c <= 10000,Users_Total_Margin__c >= 1, 
OR(Owner.Profile.Id <> "00e20000001V4Xd", Owner.UserRoleId <> "00E2000000151Hy")), 
Margin__c * 0.06, 



IF( 
AND(Users_Total_Margin__c >= 10001,Users_Total_Margin__c <=20000, 
OR(Owner.Profile.Id <> "00e20000001V4Xd", Owner.UserRoleId <> "00E2000000151Hy")), 
Margin__c * 0.11, 



IF( 
AND(Users_Total_Margin__c >= 20001,Owner.UserRoleId <> "00E2000000151ID"), 
Margin__c * 0.15,0 
))))

The fields on the users record are March__c, April__c, May__c etc and are rollups.

Any ideas?
Steve CairneySteve Cairney
I've made some progress. I created a new field called Owner Total with the following formula
  
IF (MONTH(CloseDate)=3,Owner.March__c,
IF (MONTH(CloseDate)=4,Owner.April__c,
IF (MONTH(CloseDate)=5,Owner.May__c,
IF (MONTH(CloseDate)=6,Owner.June__c,
IF (MONTH(CloseDate)=7,Owner.July__c,
IF (MONTH(CloseDate)=8,Owner.August__c,
IF (MONTH(CloseDate)=9,Owner.September__c,
IF (MONTH(CloseDate)=10,Owner.October__c,
IF (MONTH(CloseDate)=11,Owner.November__c,
IF (MONTH(CloseDate)=12,Owner.December__c,
IF (MONTH(CloseDate)=1,Owner.January__c,
IF (MONTH(CloseDate)=2,Owner.February__c,0
))))))))))))

This successfully displays the correlating SUM from the User record into the Opp.

So I thought the next bit would be easy! Simply reference the new field using the original formula:
  
IF( 
OR(Owner.Profile.Id = "00e20000001V4Xd",Owner.UserRoleId = "00E2000000151Hy"), 
Margin__c * 0.15,
 
IF( 
AND(Owner_Total__c <= 10000,Owner_Total__c >= 1, 
OR(Owner.Profile.Id <> "00e20000001V4Xd", Owner.UserRoleId <> "00E2000000151Hy")), 
Margin__c * 0.06,
 
IF( 
AND(Owner_Total__c >= 10001,Owner_Total__c <=20000, 
OR(Owner.Profile.Id <> "00e20000001V4Xd", Owner.UserRoleId <> "00E2000000151Hy")), 
Margin__c * 0.11, 

IF( 
AND(Owner_Total__c >= 20001,Owner.UserRoleId <> "00E2000000151ID"), 
Margin__c * 0.15,0
))))

BUT it won't compile! "Compiled Formula is too big to execute"
es
How is this happening? It compiled ok previously, is it because the other formula references 12 possible outcomes?