+ Start a Discussion
Matthew BracewellMatthew Bracewell 

Complex Formula attached to a button

Hi All,

First time poster on here - I basically have a button on my opportunity that sends a contract using echosign,

I wanted to put some rules around this based on both the opportunity type and the annual contract value

We have a custom field called Current ACV at risk where we monitor the current value of the contract.

Basically i want to look at a different echosign template for each of the following scenarios:

Opportunity Type = New Business, Existing Business Same Service, Existing Business New Service
- over 10k - Template A
- Under 10k - Template B

Opportunity Type = Extension with Rebid, Extension without rebid
- Current ACV at Risk - Amount ACV - More than 10k - Templace C
- Current ACV at Risk - Amount ACV - More than 3k - Templace D
- Current ACV at Risk - Amount ACV - More than 1k - Templace E
- Current ACV at Risk - Amount ACV - More than £500 - Templace F

Below is my code that i thought would work:

/apex/echosign_dev1__AgreementTemplateProcess?masterid={!Opportunity.Id}&templateId={!IF(AND(OR(ISPICKVAL(Opportunity.Type, "New Business"),ISPICKVAL(Opportunity.Type,"Existing Customer Same Service"),ISPICKVAL(Opportunity.Type,"Existing Customer New Service")),Opportunity.Contract_Amount_ACV__c>10000),"a0M9000000C1LPS",
IF(AND(OR(ISPICKVAL(Opportunity.Type, "Extension with Rebid"),ISPICKVAL(Opportunity.Type,"Extension without Rebid")),((Opportunity.ACV_at_Risk__c - Opportunity.Contract_Amount_ACV__c) < 10000), "a0M9000000DVgx2"),
IF(AND(OR(ISPICKVAL(Opportunity.Type, "Extension with Rebid"),ISPICKVAL(Opportunity.Type,"Extension without Rebid")),((Opportunity.ACV_at_Risk__c - Opportunity.Contract_Amount_ACV__c) < 3000), "a0M9000000DVgx7"),
IF(AND(OR(ISPICKVAL(Opportunity.Type, "Extension with Rebid"),ISPICKVAL(Opportunity.Type,"Extension without Rebid")),((Opportunity.ACV_at_Risk__c - Opportunity.Contract_Amount_ACV__c) < 1000), "a0M9000000DVgxC"),
IF(AND(OR(ISPICKVAL(Opportunity.Type, "Extension with Rebid"),ISPICKVAL(Opportunity.Type,"Extension without Rebid")),((Opportunity.ACV_at_Risk__c - Opportunity.Contract_Amount_ACV__c) < 500), "a0M9000000DVgxH"),"a0M9000000C0Vo8"))

Would really appreciate some help if anyone can - it is bringing up a syntax error saying i am missing a ")"

Best Answer chosen by Matthew Bracewell
Vivek DeshmaneVivek Deshmane
Yes, try this and let me know.
Matthew BracewellI changed it to:

 

/apex/echosign_dev1__AgreementTemplateProcess?masterid={!Opportunity.Id}&templateId={!IF(AND(OR(ISPICKVAL(Opportunity.Type, "New Business"),ISPICKVAL(Opportunity.Type,"Existing Customer Same Service"),ISPICKVAL(Opportunity.Type,"Existing Customer New Service")),Opportunity.Contract_Amount_ACV__c>10000),"a0M9000000C1LPS",
IF(AND(OR(ISPICKVAL(Opportunity.Type, "Extension with Rebid"),ISPICKVAL(Opportunity.Type,"Extension without Rebid")),((Opportunity.ACV_at_Risk__c - Opportunity.Contract_Amount_ACV__c) < 10000)), "a0M9000000DVgx2",
IF(AND(OR(ISPICKVAL(Opportunity.Type, "Extension with Rebid"),ISPICKVAL(Opportunity.Type,"Extension without Rebid")),((Opportunity.ACV_at_Risk__c - Opportunity.Contract_Amount_ACV__c) < 3000)), "a0M9000000DVgx7",
IF(AND(OR(ISPICKVAL(Opportunity.Type, "Extension with Rebid"),ISPICKVAL(Opportunity.Type,"Extension without Rebid")),((Opportunity.ACV_at_Risk__c - Opportunity.Contract_Amount_ACV__c) < 1000)), "a0M9000000DVgxC",
IF(AND(OR(ISPICKVAL(Opportunity.Type, "Extension with Rebid"),ISPICKVAL(Opportunity.Type,"Extension without Rebid")),((Opportunity.ACV_at_Risk__c - Opportunity.Contract_Amount_ACV__c) < 500)), "a0M9000000DVgxH","a0M9000000C0Vo8")))))}


Best Regsrds,
-Vivek
 

All Answers

Vivek DeshmaneVivek Deshmane
Hi 
Please try below and let me know.
/apex/echosign_dev1__AgreementTemplateProcess?masterid={!Opportunity.Id}&templateId={!IF(AND(OR(ISPICKVAL(Opportunity.Type, "New Business"),ISPICKVAL(Opportunity.Type,"Existing Customer Same Service"),ISPICKVAL(Opportunity.Type,"Existing Customer New Service")),Opportunity.Contract_Amount_ACV__c>10000),"a0M9000000C1LPS",
IF(AND(OR(ISPICKVAL(Opportunity.Type, "Extension with Rebid"),ISPICKVAL(Opportunity.Type,"Extension without Rebid")),((Opportunity.ACV_at_Risk__c - Opportunity.Contract_Amount_ACV__c) < 10000)), "a0M9000000DVgx2",
IF(AND(OR(ISPICKVAL(Opportunity.Type, "Extension with Rebid"),ISPICKVAL(Opportunity.Type,"Extension without Rebid")),((Opportunity.ACV_at_Risk__c - Opportunity.Contract_Amount_ACV__c) < 3000)), "a0M9000000DVgx7",
IF(AND(OR(ISPICKVAL(Opportunity.Type, "Extension with Rebid"),ISPICKVAL(Opportunity.Type,"Extension without Rebid")),((Opportunity.ACV_at_Risk__c - Opportunity.Contract_Amount_ACV__c) < 1000)), "a0M9000000DVgxC",
IF(AND(OR(ISPICKVAL(Opportunity.Type, "Extension with Rebid"),ISPICKVAL(Opportunity.Type,"Extension without Rebid")),((Opportunity.ACV_at_Risk__c - Opportunity.Contract_Amount_ACV__c) < 500)), "a0M9000000DVgxH"),"a0M9000000C0Vo8"))))}

Best Regard,
-Vivek
Matthew BracewellMatthew Bracewell

thanks Vivek

Just done that and it solves the ")" problem but now i get the error - 

Error: Incorrect number of parameters for function 'IF()'. Expected 3, received 2

Matthew BracewellMatthew Bracewell

I changed it to:

 

/apex/echosign_dev1__AgreementTemplateProcess?masterid={!Opportunity.Id}&templateId={!IF(AND(OR(ISPICKVAL(Opportunity.Type, "New Business"),ISPICKVAL(Opportunity.Type,"Existing Customer Same Service"),ISPICKVAL(Opportunity.Type,"Existing Customer New Service")),Opportunity.Contract_Amount_ACV__c>10000),"a0M9000000C1LPS",
IF(AND(OR(ISPICKVAL(Opportunity.Type, "Extension with Rebid"),ISPICKVAL(Opportunity.Type,"Extension without Rebid")),((Opportunity.ACV_at_Risk__c - Opportunity.Contract_Amount_ACV__c) < 10000)), "a0M9000000DVgx2",
IF(AND(OR(ISPICKVAL(Opportunity.Type, "Extension with Rebid"),ISPICKVAL(Opportunity.Type,"Extension without Rebid")),((Opportunity.ACV_at_Risk__c - Opportunity.Contract_Amount_ACV__c) < 3000)), "a0M9000000DVgx7",
IF(AND(OR(ISPICKVAL(Opportunity.Type, "Extension with Rebid"),ISPICKVAL(Opportunity.Type,"Extension without Rebid")),((Opportunity.ACV_at_Risk__c - Opportunity.Contract_Amount_ACV__c) < 1000)), "a0M9000000DVgxC",
IF(AND(OR(ISPICKVAL(Opportunity.Type, "Extension with Rebid"),ISPICKVAL(Opportunity.Type,"Extension without Rebid")),((Opportunity.ACV_at_Risk__c - Opportunity.Contract_Amount_ACV__c) < 500)), "a0M9000000DVgxH","a0M9000000C0Vo8")))))}

And there are no errors - will need to test to see if it works though

Vivek DeshmaneVivek Deshmane
Yes, try this and let me know.
Matthew BracewellI changed it to:

 

/apex/echosign_dev1__AgreementTemplateProcess?masterid={!Opportunity.Id}&templateId={!IF(AND(OR(ISPICKVAL(Opportunity.Type, "New Business"),ISPICKVAL(Opportunity.Type,"Existing Customer Same Service"),ISPICKVAL(Opportunity.Type,"Existing Customer New Service")),Opportunity.Contract_Amount_ACV__c>10000),"a0M9000000C1LPS",
IF(AND(OR(ISPICKVAL(Opportunity.Type, "Extension with Rebid"),ISPICKVAL(Opportunity.Type,"Extension without Rebid")),((Opportunity.ACV_at_Risk__c - Opportunity.Contract_Amount_ACV__c) < 10000)), "a0M9000000DVgx2",
IF(AND(OR(ISPICKVAL(Opportunity.Type, "Extension with Rebid"),ISPICKVAL(Opportunity.Type,"Extension without Rebid")),((Opportunity.ACV_at_Risk__c - Opportunity.Contract_Amount_ACV__c) < 3000)), "a0M9000000DVgx7",
IF(AND(OR(ISPICKVAL(Opportunity.Type, "Extension with Rebid"),ISPICKVAL(Opportunity.Type,"Extension without Rebid")),((Opportunity.ACV_at_Risk__c - Opportunity.Contract_Amount_ACV__c) < 1000)), "a0M9000000DVgxC",
IF(AND(OR(ISPICKVAL(Opportunity.Type, "Extension with Rebid"),ISPICKVAL(Opportunity.Type,"Extension without Rebid")),((Opportunity.ACV_at_Risk__c - Opportunity.Contract_Amount_ACV__c) < 500)), "a0M9000000DVgxH","a0M9000000C0Vo8")))))}


Best Regsrds,
-Vivek
 
This was selected as the best answer
Matthew BracewellMatthew Bracewell
I'll do some testing and make sure it works but it has no errors which is a great start - thanks for your help vivek

I'll update after doing some testing
Vivek DeshmaneVivek Deshmane
Hi,
If it works, please mark the best answer.
Best Regards,
-Vivek
Matthew BracewellMatthew Bracewell
will do
Matthew BracewellMatthew Bracewell

Hi Vivek,

Thanks for your help - needed a few tweaks once i started testing but they were down to my errors - thanks for your help - here was my final code for anyone else:

 

/apex/echosign_dev1__AgreementTemplateProcess?masterid={!Opportunity.Id}&templateId={!IF(AND(OR(ISPICKVAL(Opportunity.Type, "New Customer"),ISPICKVAL(Opportunity.Type,"Existing Customer Same Service"),ISPICKVAL(Opportunity.Type,"Existing Customer New Service")),Opportunity.Contract_Amount_ACV__c<10000),"a0M9000000C0Vo8", 
IF(AND(OR(ISPICKVAL(Opportunity.Type, "New Customer"),ISPICKVAL(Opportunity.Type,"Existing Customer Same Service"),ISPICKVAL(Opportunity.Type,"Existing Customer New Service")),Opportunity.Contract_Amount_ACV__c>10000),"a0M9000000C1LPS", 
IF(AND(OR(ISPICKVAL(Opportunity.Type, "Extension with Rebid"),ISPICKVAL(Opportunity.Type,"Extension without Rebid")),((Opportunity.ACV_at_Risk__c - Opportunity.Contract_Amount_ACV__c) > 10000)), "a0M9000000DVgx2", 
IF(AND(OR(ISPICKVAL(Opportunity.Type, "Extension with Rebid"),ISPICKVAL(Opportunity.Type,"Extension without Rebid")),((Opportunity.ACV_at_Risk__c - Opportunity.Contract_Amount_ACV__c) > 3000)), "a0M9000000DVgx7", 
IF(AND(OR(ISPICKVAL(Opportunity.Type, "Extension with Rebid"),ISPICKVAL(Opportunity.Type,"Extension without Rebid")),((Opportunity.ACV_at_Risk__c - Opportunity.Contract_Amount_ACV__c) > 1000)), "a0M9000000DVgxC", 
IF(AND(OR(ISPICKVAL(Opportunity.Type, "Extension with Rebid"),ISPICKVAL(Opportunity.Type,"Extension without Rebid")),((Opportunity.ACV_at_Risk__c - Opportunity.Contract_Amount_ACV__c) > 500)), "a0M9000000DVgxH","a0M9000000C0Vo8"))))))}