IF, Nested IF, AND() and ISPICKVAL and CASE all in one formula

Question

I am working on a business segment rule that brings in aspects of the parent and assesses the child account record elements. I have pieces of this working but when I put them together it errors. My goal is:

If the parent exists (test = Parent.id <> null) AND as long as the parent type is not equal to investor, run case statement 1, else run case statement 2.

I have been messing around with the parens, but I believe the following is so close. I am getting this error now:

incorrect parameter type for function 'AND()'. Expected Boolean, received Text

IF(Parent.Id <> Null,
     (AND(ISPICKVAL(parent.type, "investor"),
          CASE(Sales_Business_Segment__c , 
                   'Growth', 'Growth', 
                   'Enterprise', 'Enterprise',  
                   DataFox_Account_Definition__c),      
          CASE(Parent.Sales_Business_Segment__c , 
                   'Growth', 'Growth', 
                   'Enterprise', 'Enterprise',  
                   Parent.DataFox_Account_Definition__c)     
)))

Answer 1

Error means, that parameters of AND function needs to be type boolean (TRUE/FALSE), and not text, as returned by CASE formula below.

It seems that AND is not being used in correct way here.

Since it is required for parent id to be available and picklist value on parent.type to be investor, then proper syntax should be:

AND(Parent.Id <> Null,NOT(ISPICKVAL(parent.type, "investor")))

And given statement needs to be first operand of IF operator, which leads to the following formula:

IF(
   AND(
        Parent.Id <> Null,
        NOT(ISPICKVAL(parent.type, "investor"))
   ),
   CASE(Sales_Business_Segment__c , 
                   'Growth', 'Growth', 
                   'Enterprise', 'Enterprise',  
                   DataFox_Account_Definition__c),      
   CASE(Parent.Sales_Business_Segment__c , 
                   'Growth', 'Growth', 
                   'Enterprise', 'Enterprise',  
                   Parent.DataFox_Account_Definition__c)     
))

Answer 2

I believe you're using AND wrong for what you're trying to do. From your description, you want to check that Parent.Id (you can say ParentID instead) exists, and the parent's type isn't 'investor'.

So for that, you'd need to wrap those two in the AND() block like so:

AND( NOT(ISBLANK(ParentId)), NOT(ISPICKVAL(Parent.Type, "investor")) )

This now reads, ParentID is not blank and Parent Type is not investor. Notice that I changed the <> null to NOT(ISBLANK()), Salesforce recommends using isBlank instead of null checks in formulas when dealing with Strings.

This is the AND case, which is what you described. This means that if this is true, you'll actually want to swap your two outputs in the IF block. Alternatively, you could remove the NOTs from both and swap it to an OR like this:

OR( ISBLANK(ParentId), ISPICKVAL(Parent.Type, "investor") )

So knowing that we need to re-order those case blocks, that'd give us this:

IF( AND( 
     NOT(ISBLANK(ParentId)), 
     NOT(ISPICKVAL(Parent.Type, "investor")) 
   ),
   CASE(Parent.Sales_Business_Segment__c , 
       'Growth', 'Growth', 
       'Enterprise', 'Enterprise',  
       Parent.DataFox_Account_Definition__c
   ),
   CASE(Sales_Business_Segment__c , 
       'Growth', 'Growth', 
       'Enterprise', 'Enterprise',  
       DataFox_Account_Definition__c
   )
)

I find spacing it out like this and indenting in the formula builder helps keep track of parentheses.

So you were really close, you just used AND incorrectly. Just remember this is a functional language meaning you wouldn't say A AND B, you'd say AND(A,B)

Answer 3

I think you are close.

Let's start with IF, which has the following structure

IF(logical_test, value_if_true, value_if_false)

The key with your error here is in the AND.

AND(logical1,logical2,...) and replace logical1,logical2,... with the values that you want evaluated.

You added a "logical1" with ISPICKVAL(), but you never closed off your AND() as it's missing the end parenthesis.

Also, if it's one evaluation then you don't need AND. You would use AND if evaluating multiple conditions...which you are doing except you nested them. If I understand your requirements right, you can just use an AND() and get rid of the nested evaluation. Also to note, you said "parent type is not equal to investor" so I assume it should be NOT(ISPICKVAL(parent.type, "investor")). I separate the formatting for readability-sake

IF(
    AND(
       Parent.Id <> Null, NOT(ISPICKVAL(parent.type, "investor"))
    ),

    CASE(Parent.Sales_Business_Segment__c, 
    'Growth', 'Growth', 
    'Enterprise', 'Enterprise',  
     Parent.DataFox_Account_Definition__c),

    CASE(Sales_Business_Segment__c,
    'Growth','Growth', 
    'Enterprise', 'Enterprise',  
     DataFox_Account_Definition__c)     
)