Apex Code Development You need to sign in to do that
Don't have an account?
Mikeb66 Formula Help Request
I'm attempting to create a formula dependent on the "Opportunity Type" field. What I'm looking to do is display a % in the a new field called "Revenue Factor". I have attempted to create what I thought was correct below, but it is not working, I get the following error:
Error: Syntax error. Extra IF
IF ( ISPICKVAL ( Type, "New" ) , (Days_left_in_FY__c - 60) / 365)
IF ( ISPICKVAL ( Type, "Hosting" ) , (Days_left_in_FY__c - 60) / 365)
IF ( ISPICKVAL ( Type, "Renewal" ) , (Days_left_in_FY__c) / 365)
IF ( ISPICKVAL ( Type, "Upgrade" ) , (Days_left_in_FY__c) / 365)
IF ( ISPICKVAL ( Type, "Volume" ) , (100)
IF ( ISPICKVAL ( Type, "Client Integrations" ) , (100)
IF ( ISPICKVAL ( Type, "Prof Services" ) , (100)
IF ( ISPICKVAL ( Type, "Harmony Volume Upside" ) , (100) , null
Any suggestions???
That worked, thanks for your help!!
All Answers
Try this:
IF ( ISPICKVAL ( Type, "New" ) , ((Days_left_in_FY__c - 60) / 365),
IF ( ISPICKVAL ( Type, "Hosting" ) , ((Days_left_in_FY__c - 60) / 365),
IF ( ISPICKVAL ( Type, "Renewal" ) , ((Days_left_in_FY__c) / 365),
IF ( ISPICKVAL ( Type, "Upgrade" ) , ((Days_left_in_FY__c) / 365),
IF ( ISPICKVAL ( Type, "Volume" ) , 100,
IF ( ISPICKVAL ( Type, "Client Integrations" ) , 100,
IF ( ISPICKVAL ( Type, "Prof Services" ) , 100,
IF ( ISPICKVAL ( Type, "Harmony Volume Upside" ) , 100 , null ))))))))
Thanks, just tried that and got the following error:
Error: Compiled formula is too big to execute (5,748 characters). Maximum size is 5,000 charactersIf Days_Left_In_FY_c is also a formula field, then there could be too much data for the formula field to calculate.
Are there any other Types other than the ones listed here? If not, can you say:
IF (
OR ( ISPICKVAL ( Type, "New" ) ,
ISPICKVAL ( Type, "Hosting" ),
ISPICKVAL ( Type, "Renewal" ) ,
ISPICKVAL ( Type, "Upgrade" ) ,
ISPICKVAL ( Type, "Volume" )) , ((Days_left_in_FY__c - 60) / 365), 100)
This won't calculate a NULL for any other types that don't meet the condition.
That gets me almost there, there are 3 possible formulas that I need for this:
For "New" & "Hosting" the formula is:
((Days_left_in_FY__c - 60) / 365)
For "Renewal" & "Upgrade" the formula is:
((Days_left_in_FY__c) / 365)
And for All Else, ("Client Integrations" , "Prof Services" , "Harmony Volume Upside") the value should be 100
You are also correct that my custom field called Days_Left_In_FY_c is also a formula field.
Thanks again for your help, I really appreciate it!!
Do you think that there is a way to add:
( ISPICKVAL ( Type, "Renewal" ) ,
( ISPICKVAL ( Type, "Upgrade" ) , ((Days_left_in_FY__c) / 365), 100))
to the formula? Or something like that to add the other condition for Renewal and Upgrade?
I think it would be:
IF (
OR (
ISPICKVAL ( Type, "New"),
ISPICKVAL ( Type, "Hosting")),((Days_left_in_FY__c - 60) / 365),
IF (
OR(
ISPICKVAL (Type, "Renewal"),
(ISPICKVAL ( Type, "Upgrade")), ((Days_left_in_FY__c) / 365), 100)))
You may run into the same formulat calc issues with this though, but it's worth a shot.
Good luck! Let me know if it works.
Thanks, just tried that but got a different error:
Error: Incorrect parameter for function OR(). Expected Boolean, received Number
Ok, let's try this:
IF (
OR (
ISPICKVAL ( Type, "New"),
ISPICKVAL ( Type, "Hosting")),(( Days_left_in_FY__c - 60) / 365),
IF (
OR(
ISPICKVAL (Type, "Renewal"),
ISPICKVAL ( Type, "Upgrade")), ((Days_left_in_FY__c) / 365), 100))
Mine came out ok.
That worked, thanks for your help!!