0

I am trying to use a formula field where we are using IF statement but I am getting an error that it should have 3 return type and received two.

IF(CONTAINS("Re-Admit, re admit, readmit,Readmit,Re admit,ReAdmit,Re admit,Re-admit", TEXT(LastName), "1",
IF(CONTAINS("Re-Admit 2 , re admit 2, readmit 2,Readmit 2,Re admit 2,ReAdmit 2,Re admit 2,Re-admit 2",TEXT(LastName), "2"),

IF(CONTAINS("Re-Admit 3 , re admit 3, readmit 3,Readmit 3,Re admit 3,ReAdmit 3,Re admit 3,Re-admit 3",TEXT(LastName), "3"),"")
1
  • Please note, formulae is plural and formula is singular. When you are talking about one instance, it is a formula, not a formulae.
    – Adrian Larson
    Aug 30, 2016 at 22:07

3 Answers 3

2

Your parentheses are wrong in several places. Proper indentation can make these errors more clear:

IF(
    CONTAINS(
        "Re-Admit...",
        LastName // <-- remove TEXT(...) call if this is a text field
    ), // <-- missing closing parenthesis added
    1, // <-- if your return type is Number, don't wrap the return value in quotes
    IF(
        CONTAINS(
            "Re-Admit 2...",
            LastName // <-- remove TEXT(...) call if this is a text field
        ), // <-- missing closing parenthesis added
        2, // <-- extra closing parenthesis removed
        // ^^^ if your return type is Number, don't wrap the return value in quotes
        IF(
            CONTAINS(
                "Re-Admit 3...",
                LastName // <-- remove TEXT(...) call if this is a text field
            ), // <-- missing closing parenthesis added
            3, // <-- extra closing parenthesis removed
            // ^^^ if your return type is Number, don't wrap the return value in quotes
            0 // <-- if your return type is Number, your fallback value should be 0 or null
        ) // <-- close third IF formula
    ) // <-- close second IF formula (added)
) // <-- close first IF formula (added)

I believe the first CONTAINS check will actually catch all the values from the subsequent checks, so you should also reverse the order, checking the "1" case last.


After reviewing the comments, nearly every single clause you implement has an error. It may be more legible to list them here:

  • Your IF syntax needs to be IF(logical_test, value_if_true, value_if_false).
  • Your CONTAINS syntax needs to be CONTAINS(text, compare_text).
  • Composing these functions should yield IF(CONTAINS(text, compare_text), value_if_true, value_if_false)
  • Composing three nested IF statements should yield IF(condition1, value_if_condition1, IF(condition2, value_if_condition2, IF(condition3, value_if_condition3)))
  • You can't call TEXT on a field that is already of the type Text.
  • To return a number, use values like 1 instead of "1".
  • The value "" is not a valid number. Use 0 or null instead.
2
  • Hi Adrian, I have choosen number as return type on formulae field but I get an error : Review all error messages below to correct your data. Incorrect parameter type for function 'TEXT()'. Expected Number, Date, DateTime, Picklist, received Text (Related field: Formula) . I have used the formulae as : Aug 30, 2016 at 22:06
  • You can likely remove the TEXT function entirely. I thought that was weird...you would only do that if it were a picklist, not if it's a Text field. Also if the return type is Number, you need to remove the quotes around the numbers.
    – Adrian Larson
    Aug 30, 2016 at 22:08
0
IF( CONTAINS( "Re-Admit:re admit:readmit:Readmit:Re admit:ReAdmit:Re Admit:Re-admit", LastName),
    IF( CONTAINS( " 2", LastName),
        2,
        IF( CONTAINS( " 3", LastName),
            3,
            1
        )
    ),
null)
3
  • Hi When I try to use the above formulae when the return type is number on the field, the formulae does not check the syntax but does gets saved. Also I get blank in the result. please let me know the return type field I should be using Aug 31, 2016 at 2:22
  • Does you last name value contains any from the values that formulas is validating? It returns null (blank) if it doesn't contain any.
    – o-lexi
    Aug 31, 2016 at 6:06
  • LastName = Prakash readmit, LastName = Prakash readmit2 Lastname = Prakash readmit3 Aug 31, 2016 at 11:59
0

I have also used the formulae IF( CONTAINS( "Re-Admit:re admit:readmit:Readmit:Re admit:ReAdmit:Re Admit:Re-admit", LastName), IF( CONTAINS( " 2", LastName), 2, IF( CONTAINS( " 3", LastName), 3, 1 ) ), null)

but it shows blank value.

The last name has values mentioned as : LastName = Prakash readmit, LastName = Prakash readmit2 Lastname = Prakash readmit3

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .