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
Ben KnechtBen Knecht 

Need a Formula Ninja

Hi,

I have a formula written in a formula field, and I'm pretty sure the layout is correct, but I can't get the syntax right! I believe there is a parenthesis or comma out of place, but I can't find it and it's driving me nuts. I've tried for over an hour and can't seem to get it. Any help would be greatly, greatly appreciated!

Formula:
IF(PricebookEntry.Product2.CXO_Service__c=True,
IF(
AND(
ISPICKVAL(Opportunity.New_Renew__c,"Renewal"),
NOT(Product_Family__c="Learning")
)
,

IF(Amount_for_Retention__c>CV_to_Renew__c,Annual_NCVI_Amount__c,
Probable_CV_Retained__c-CV_to_Renew__c)

,
Annual_NCVI_Amount__c))

,

IF(
AND(Opportunity.TA_Migration_Transaction__c=True,ISPICKVAL(PricebookEntry.Product2.Team_Access_Product__c,"Standard"),-1*CV_to_Renew__c)

,

IF(
AND(
ISPICKVAL(Opportunity.New_Renew__c,"Renewal"),
NOT(Product_Family__c="Learning")
)
,

IF(Amount_for_Retention__c>CV_to_Renew__c,Annual_NCVI_Amount__c,
Probable_CV_Retained__c-CV_to_Renew__c)

,
Annual_NCVI_Amount__c)



 
Best Answer chosen by Ben Knecht
Parker EdelmannParker Edelmann
Whoops, I copied the wrong version, use this: 
IF(PricebookEntry.Product2.CXO_Service__c=True,

   IF(ISPICKVAL(Opportunity.New_Renew__c,"Renewal") && Product_Family__c!="Learning",
      IF( Amount_for_Retention__c>CV_to_Renew__c , Annual_NCVI_Amount__c, Probable_CV_Retained__c-CV_to_Renew__c ),
      Annual_NCVI_Amount__c
      ),

   IF(Opportunity.TA_Migration_Transaction__c=True && ISPICKVAL(PricebookEntry.Product2.Team_Access_Product__c,"Standard"),
    -1*CV_to_Renew__c,
       IF(ISPICKVAL(Opportunity.New_Renew__c,"Renewal")&& Product_Family__c != "Learning",
           IF(Amount_for_Retention__c>CV_to_Renew__c,Annual_NCVI_Amount__c,Probable_CV_Retained__c-CV_to_Renew__c),
           Annual_NCVI_Amount__c)))

Left out a parenthesis.

All Answers

Doug BeltowskiDoug Beltowski
Can you describe what this formula is meant to be doing?
Ben KnechtBen Knecht
Hi Doug,

Yes, the formula is meant to say:
IF PricebookEntry.Product2.CXO_Service__c=True,

then run this IF statement for logical test is true:
IF(
AND(
ISPICKVAL(Opportunity.New_Renew__c,"Renewal"),
NOT(Product_Family__c="Learning")
)
,

IF(Amount_for_Retention__c>CV_to_Renew__c,Annual_NCVI_Amount__c,
Probable_CV_Retained__c-CV_to_Renew__c)
,
Annual_NCVI_Amount__c))

If the above is false, then run this IF statement for logical test = false:

IF(
AND(Opportunity.TA_Migration_Transaction__c=True,ISPICKVAL(PricebookEntry.Product2.Team_Access_Product__c,"Standard"),-1*CV_to_Renew__c)

,

IF(
AND(
ISPICKVAL(Opportunity.New_Renew__c,"Renewal"),
NOT(Product_Family__c="Learning")
)
,

IF(Amount_for_Retention__c>CV_to_Renew__c,Annual_NCVI_Amount__c,
Probable_CV_Retained__c-CV_to_Renew__c)

,
Annual_NCVI_Amount__c)



I just can't seem to get the syntax right, or I might be missing a closing argument. Any help is greatly appreiated, and please let me know if you need more xontext.

Thank you!!


 
Parker EdelmannParker Edelmann
IF(PricebookEntry.Product2.CXO_Service__c=True,
   IF(AND(
           ISPICKVAL(Opportunity.New_Renew__c,"Renewal"),
           Product_Family__c!="Learning"
          ),
      IF( Amount_for_Retention__c>CV_to_Renew__c , Annual_NCVI_Amount__c, Probable_CV_Retained__c-CV_to_Renew__c ),
      Annual_NCVI_Amount__c
      )  /*)*/,
    IF(AND(Opportunity.TA_Migration_Transaction__c=True,
           ISPICKVAL(PricebookEntry.Product2.Team_Access_Product__c,"Standard"),
       -1*CV_to_Renew__c 
       /*No third argument?*/
       )

,

IF(
AND(
ISPICKVAL(Opportunity.New_Renew__c,"Renewal"),
NOT(Product_Family__c="Learning")
)
,

IF(Amount_for_Retention__c>CV_to_Renew__c,Annual_NCVI_Amount__c,
Probable_CV_Retained__c-CV_to_Renew__c)

,
Annual_NCVI_Amount__c)
I was reworking your formula, I've changed some of the indenting and other cosmetic fixes. I noticed on what is now the eighth line, that you had an extra parenthesis I think. I put in another comment on the twelfth line because it doesn't look like you have a third argument for your IF statement that I placed the comment in. After that, I coudn't follow what you're formula is evaluating. It looks to me that the first IF statement has all three arguments, but yet the formula goes on. Did I make a mistake?

Thanks,
Parker
Parker EdelmannParker Edelmann
Hold on, I think I got it: 
IF(PricebookEntry.Product2.CXO_Service__c=True,
   IF(AND(
           ISPICKVAL(Opportunity.New_Renew__c,"Renewal"),
           Product_Family__c!="Learning"
          ),
      IF( Amount_for_Retention__c>CV_to_Renew__c , Annual_NCVI_Amount__c, Probable_CV_Retained__c-CV_to_Renew__c ),
      Annual_NCVI_Amount__c
     )  /*)*/,

    IF(AND(Opportunity.TA_Migration_Transaction__c=True,
          ISPICKVAL(PricebookEntry.Product2.Team_Access_Product__c,"Standard"),
      -1*CV_to_Renew__c 
      /*)*/,
       IF(
           AND(
               ISPICKVAL(Opportunity.New_Renew__c,"Renewal"),
               NOT(Product_Family__c="Learning")
               ),
           IF(Amount_for_Retention__c>CV_to_Renew__c,Annual_NCVI_Amount__c,Probable_CV_Retained__c-CV_to_Renew__c),
           Annual_NCVI_Amount__c)
Is this what you're looking for?
Ben KnechtBen Knecht
Yes, that is exactly what I'm looking for! It's still telling me that I'm missing a parenthesis - this is always my problem, finding where the missing piece is it's driving me nuts. But I think you are really, really close!
Parker EdelmannParker Edelmann
Try this: 
IF(PricebookEntry.Product2.CXO_Service__c=True,

   IF(ISPICKVAL(Opportunity.New_Renew__c,"Renewal") && Product_Family__c!="Learning",
      IF( Amount_for_Retention__c>CV_to_Renew__c , Annual_NCVI_Amount__c, Probable_CV_Retained__c-CV_to_Renew__c ),
      Annual_NCVI_Amount__c
      ),

   IF(Opportunity.TA_Migration_Transaction__c=True && ISPICKVAL(PricebookEntry.Product2.Team_Access_Product__c,"Standard"),
    -1*CV_to_Renew__c,
       IF(ISPICKVAL(Opportunity.New_Renew__c,"Renewal")&& Product_Family__c != "Learning",
           IF(Amount_for_Retention__c>CV_to_Renew__c,Annual_NCVI_Amount__c,Probable_CV_Retained__c-CV_to_Renew__c),
           Annual_NCVI_Amount__c))
I didn't realize that even if you don't have the custom fields, you can still check some of the syntax. This should work in terms of syntax, but it may or may not give you the expected results. Be sure to test it for every outcome before you trust its value.
Parker EdelmannParker Edelmann
Whoops, I copied the wrong version, use this: 
IF(PricebookEntry.Product2.CXO_Service__c=True,

   IF(ISPICKVAL(Opportunity.New_Renew__c,"Renewal") && Product_Family__c!="Learning",
      IF( Amount_for_Retention__c>CV_to_Renew__c , Annual_NCVI_Amount__c, Probable_CV_Retained__c-CV_to_Renew__c ),
      Annual_NCVI_Amount__c
      ),

   IF(Opportunity.TA_Migration_Transaction__c=True && ISPICKVAL(PricebookEntry.Product2.Team_Access_Product__c,"Standard"),
    -1*CV_to_Renew__c,
       IF(ISPICKVAL(Opportunity.New_Renew__c,"Renewal")&& Product_Family__c != "Learning",
           IF(Amount_for_Retention__c>CV_to_Renew__c,Annual_NCVI_Amount__c,Probable_CV_Retained__c-CV_to_Renew__c),
           Annual_NCVI_Amount__c)))

Left out a parenthesis.
This was selected as the best answer
Ben KnechtBen Knecht
Parker, you are a life saver, thank you! This works from a syntax standpoint - I'll check all of the scenarios to make sure I've covered my bases. Thank you again!!!
Parker EdelmannParker Edelmann
No problem! If for some reason it doesn't return the expected result and you need some help to correct it, feel free to reach out to me!

Regards,
Parker