3

Trying to create a text formula field that returns certain texts based on combinations of possible values in other fields. Every time I update the syntax, I get an error message either about parameters, parentheses or commas. Any insight??

IF(TEXT(Next_Refinement_Kit_Action__c,"AWAIT Refinement PHOTOS"),"Photo Stage",

IF(OR(
ISPICKVAL(Next_Refinement_Action__c,"PATIENT REQUESTS REFINEMENTS"),
ISPICKVAL(Next_Refinement_Action__c,"SEND REFINEMENT PHOTOS TO ORTHO")),"Photo Stage",

IF(OR(
TEXT(Next_Refinement_Kit_Action__c,"AWAIT Refinement KIT"),
TEXT(Next_Refinement_Kit_Action__c,"AWAIT Refinement Redo KIT"),
TEXT(Next_Refinement_Kit_Action__c,"GRADE Refinement PVS"),
TEXT(Next_Refinement_Kit_Action__c,"REQUEST Refinement REDO KIT"),
TEXT(Next_Refinement_Kit_Action__c,"SCAN KIT"),
TEXT(Next_Refinement_Kit_Action__c,"SEND STLS TO CC")))))),"PVS/Scan Stage",

IF(OR(
ISPICKVAL(Next_Refinement_Action__c,"AWAIT REFINEMENT PVS"),
ISPICKVAL(Next_Refinement_Action__c,"SCAN REFINEMENT PVS"),
ISPICKVAL(Next_Refinement_Action__c,"SEND REFINEMENT TO MESH/SCULPT"),
ISPICKVAL(Next_Refinement_Action__c,"AWAIT REFINEMENT MESH/SCULPT"),
ISPICKVAL(Next_Refinement_Action__c,"SEND REFINEMENT STLS TO CC"),
ISPICKVAL(Next_Refinement_Action__c,"REQUEST Refinement REDO KIT"),
ISPICKVAL(Next_Refinement_Action__c,"AWAIT Refinement Redo KIT"),
ISPICKVAL(Next_Refinement_Action__c,"SEND Refinement STLS TO FC"))))))),"PVS/Scan Stage",

IF(TEXT(Next_Refinement_Kit_Action__c,"AWAIT Refinement TP"),"TP Stage",

IF(OR(
ISPICKVAL(Next_Refinement_Action__c,"AWAIT REFINEMENT TP"),
ISPICKVAL(Next_Refinement_Action__c,"SEND REFINEMENT TP TO ORTHO"),
ISPICKVAL(Next_Refinement_Action__c,"SEND REFINEMENT TP TO CUSTOMER"))),"TP Stage",

IF(
ISPICKVAL(Last_Refinement_Comm__c,"Customer approved TP"),"Production/Active",

IF(OR(
ISPICKVAL(Next_Refinement_Action__c,"MANUFACTURE REFINEMENT ALIGNERS"),
ISPICKVAL(Next_Refinement_Action__c,"REFINEMENT ALIGNERS SENT TO CUSTOMER",
ISPICKVAL(Next_Refinement_Action__c,"ACTIVE REFINEMENTS CUSTOMER"))),"Production/Active",

IF(
TEXT(Next_Refinement_Kit_Action__c,"AWAIT Refinement TP"),"Production/Active"

)))))))))
| improve this question | |
  • 1
    One suggestion (not really an answer) I could give is for you to choose and stick to an indentation strategy. Yes, it's more typing, but you're well past the point where I'd expect anyone to be able to keep track of every parenthesis and comma and maintaining some discipline here would help you prevent these type of issues (and spot any issues that still make it through). I'd suggest putting each function on its own line, and add another level of indentation each time you need to open another paren. – Derek F Jun 5 '19 at 19:11
  • What is 'Next_Refinement_Kit_Action__c' field type? I think the multiple or() and so many if() are throwing you off. Sometimes if you start building one section at at time then combine them together you'll catch the compile error. – Dan Wooding Jun 5 '19 at 19:12
  • By the time you get to ISPICKVAL(Next_Refinement_Action__c,"SEND Refinement STLS TO FC"))))))),"PVS/Scan Stage",, the parentheses are unbalanced. Derek's suggestion may help you figure out what you meant to do here. Remember that IF() always takes three parameters. – David Reed♦ Jun 5 '19 at 19:13
  • It's hard to demonstrate this in a comment, but if you take \n as a new line (i.e. hit the "enter"/"return" key), and \t as a tab (or 4 spaces), then the first few lines of your formula would look like IF( \n \tTEXT(Next_Refinement_Kit_Action__c,"AWAIT Refinement PHOTOS"), \n \t "Photo Stage", \n \t IF( \n \t \t OR( \n \t \t \t ISPICKVAL( ... – Derek F Jun 5 '19 at 19:15
5

Welcome to SFSE! There are a few things going on here:

  1. Incorrect use of the TEXT() function. The TEXT() function takes a single parameter, and just returns the text value of that parameter, which could be a number, date, or picklist field. Then you can compare that value to something, eg, TEXT(Next_Refinement_Kit_Action__c) = "AWAIT Refinement PHOTOS".
  2. Too many close parentheses - you've got them at the end of your OR() sections, and then again at the end of the whole formula. For a complex formula like this, you'll be well-served by using a text editor like VS Code that will show you the matching parenthesis when your cursor is on one, or something like the Salesforce Enhanced Formula Editor Chrome extension.
  3. No default/false value. The opening IF() needs to return something if it's FALSE, ie, if none of the conditions elsewhere in the formula are met. While validation rules & field requirements might make that impossible in actual use, the formula won't compile without it. It can be an empty string: "".

Correcting those issues gets you to this version, which I believe will compile and work. But there are a couple other things that are probably worth addressing:

  1. Mixed use of TEXT() and ISPICKVAL(). You didn't specify the field type of Next_Refinement_Kit_Action__c, but it looks like maybe it is also a picklist, along with Next_Refinement_Action__c. If that's the case, then I'd recommend using either TEXT() or ISPICKVAL() consistently throughout. Mixing and matching might work, but makes it harder to understand and maintain.
  2. Multiple options with the same result. This is a bit more of a stylistic thing - you could certainly argue that having two IF() blocks that both return PVS/Scan Stage makes the formula easier to read and maintain, with one for each field. But you could also combine them to make the formula shorter and less likely to hit length limits, especially if it grows over time with more picklist values.

Assuming that Next_Refinement_Kit_Action__c is a picklist, and that the more compact formula would be preferred, gives you this (which also incorporates Derek F's suggestion of indenting). I can't actually test this in an org without those fields, but I believe this should work:

IF(
    OR(
        ISPICKVAL(Next_Refinement_Kit_Action__c,"AWAIT Refinement PHOTOS"),
        ISPICKVAL(Next_Refinement_Action__c,"PATIENT REQUESTS REFINEMENTS"),
        ISPICKVAL(Next_Refinement_Action__c,"SEND REFINEMENT PHOTOS TO ORTHO")),"Photo Stage",

    IF(
        OR(
            ISPICKVAL(Next_Refinement_Kit_Action__c,"AWAIT Refinement KIT"),
            ISPICKVAL(Next_Refinement_Kit_Action__c,"AWAIT Refinement Redo KIT"),
            ISPICKVAL(Next_Refinement_Kit_Action__c,"GRADE Refinement PVS"),
            ISPICKVAL(Next_Refinement_Kit_Action__c,"REQUEST Refinement REDO KIT"),
            ISPICKVAL(Next_Refinement_Kit_Action__c,"SCAN KIT"),
            ISPICKVAL(Next_Refinement_Kit_Action__c,"SEND STLS TO CC"),
            ISPICKVAL(Next_Refinement_Action__c,"AWAIT REFINEMENT PVS"),
            ISPICKVAL(Next_Refinement_Action__c,"SCAN REFINEMENT PVS"),
            ISPICKVAL(Next_Refinement_Action__c,"SEND REFINEMENT TO MESH/SCULPT"),
            ISPICKVAL(Next_Refinement_Action__c,"AWAIT REFINEMENT MESH/SCULPT"),
            ISPICKVAL(Next_Refinement_Action__c,"SEND REFINEMENT STLS TO CC"),
            ISPICKVAL(Next_Refinement_Action__c,"REQUEST Refinement REDO KIT"),
            ISPICKVAL(Next_Refinement_Action__c,"AWAIT Refinement Redo KIT"),
            ISPICKVAL(Next_Refinement_Action__c,"SEND Refinement STLS TO FC")),"PVS/Scan Stage",

    IF(
        OR(
            ISPICKVAL(Next_Refinement_Kit_Action__c,"AWAIT Refinement TP"),
            ISPICKVAL(Next_Refinement_Action__c,"AWAIT REFINEMENT TP"),
            ISPICKVAL(Next_Refinement_Action__c,"SEND REFINEMENT TP TO ORTHO"),
            ISPICKVAL(Next_Refinement_Action__c,"SEND REFINEMENT TP TO CUSTOMER")),"TP Stage",

    IF(
        OR(
            ISPICKVAL(Last_Refinement_Comm__c,"Customer approved TP"),"Production/Active",
            ISPICKVAL(Next_Refinement_Action__c,"MANUFACTURE REFINEMENT ALIGNERS"),
            ISPICKVAL(Next_Refinement_Action__c,"REFINEMENT ALIGNERS SENT TO CUSTOMER"),
            ISPICKVAL(Next_Refinement_Action__c,"ACTIVE REFINEMENTS CUSTOMER")),"Production/Active"
    ))), ""
)
| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.