Ask Search:
Diego AparicioDiego Aparicio 

Help with validation rule for cases

Hi There,

I am trying to work out a validation rule for cases that will prevent any case owner to update the status or close the case without first make sure that some fields are populated. The problem that am facing is that one particular field is only available to an specific record type while the remaining record types are using the same fields. 

I came up with the syntax below however is now failing and not allowing anyone to update anything at all. please help! 

As you can see below record type solutions VR should kick off based on Case_Reason while the remaining record types should use Reason instead.. All other fields like type, subject and description are the same across all record types

AND(
$Profile.Name != 'System Administrator',
$Profile.Name != 'Integration',
OR(
ISPICKVAL (Status, 'In Progress'),
ISPICKVAL (Status, 'On Hold'),
ISPICKVAL (Status, 'Escalated'),
OR(
AND( RecordType.DeveloperName='Solutions',ISPICKVAL (Case_Reason__c, "")),
AND( RecordType.DeveloperName='Sales_Support',ISPICKVAL (Reason, "")),
AND( RecordType.DeveloperName='Billing_Support',ISPICKVAL (Reason, "")),
AND( RecordType.DeveloperName='Business_Collection',ISPICKVAL (Reason, "")),
ISPICKVAL (Type, "")
),
IsClosed
&&
OR(
ISBLANK (Subject),
ISBLANK (Description)
)))
Best Answer chosen by Diego Aparicio
Tony TrinhTony Trinh
Try this out:
OR(
  AND(
  OR(
  $Profile.Name != 'System Administrator',
  $Profile.Name != 'Integration'),
  OR(
    ISPICKVAL (Status, 'In Progress'),
    ISPICKVAL (Status, 'On Hold'),
    ISPICKVAL (Status, 'Escalated')),
  OR(
    AND( RecordType.DeveloperName='Solutions',ISPICKVAL (Case_Reason__c, "")),
    AND( RecordType.DeveloperName='Sales_Support',ISPICKVAL (Reason, "")),
    AND( RecordType.DeveloperName='Billing_Support',ISPICKVAL (Reason, "")),
    AND( RecordType.DeveloperName='Business_Collection',ISPICKVAL (Reason, ""))),
  ISPICKVAL (Type, ""),
  ),
  AND(IsClosed),
  OR(
    ISBLANK (Subject),
    ISBLANK (Description)
  )
)

 

All Answers

Tony TrinhTony Trinh
Diego,

It might be a simple change as adding an OR FOR your profiles. It's looking for:
$Profile.Name != 'System Administrator' AND $Profile.Name != 'Integration'

Give this a shot:
AND(
	OR(
	$Profile.Name != 'System Administrator',
	$Profile.Name != 'Integration'
	),
	OR(
		ISPICKVAL (Status, 'In Progress'),
		ISPICKVAL (Status, 'On Hold'),
		ISPICKVAL (Status, 'Escalated'),
		OR(
			AND( RecordType.DeveloperName='Solutions',ISPICKVAL (Case_Reason__c, "")),
			AND( RecordType.DeveloperName='Sales_Support',ISPICKVAL (Reason, "")),
			AND( RecordType.DeveloperName='Billing_Support',ISPICKVAL (Reason, "")),
			AND( RecordType.DeveloperName='Business_Collection',ISPICKVAL (Reason, "")),
			ISPICKVAL (Type, "")
		),
	IsClosed
	&&
		OR(
			ISBLANK (Subject),
			ISBLANK (Description)
		)
	)
)
Diego AparicioDiego Aparicio
Hey Tony, 

thanks for helping. Yeah, I included those profiles so the VR will not fire off for them but it should for any other profile. I hope I got it right, I will give it a try now, thanks again!

Diego 
Diego AparicioDiego Aparicio
Hey Tony,

tested and the validation rule keeps firing for anyone... doesn't ignore the admin profiles. There must be something am not doing right with the formula syntax although seems fine to me. 

Diego 
Tony TrinhTony Trinh
Try this out:
OR(
  AND(
  OR(
  $Profile.Name != 'System Administrator',
  $Profile.Name != 'Integration'),
  OR(
    ISPICKVAL (Status, 'In Progress'),
    ISPICKVAL (Status, 'On Hold'),
    ISPICKVAL (Status, 'Escalated')),
  OR(
    AND( RecordType.DeveloperName='Solutions',ISPICKVAL (Case_Reason__c, "")),
    AND( RecordType.DeveloperName='Sales_Support',ISPICKVAL (Reason, "")),
    AND( RecordType.DeveloperName='Billing_Support',ISPICKVAL (Reason, "")),
    AND( RecordType.DeveloperName='Business_Collection',ISPICKVAL (Reason, ""))),
  ISPICKVAL (Type, ""),
  ),
  AND(IsClosed),
  OR(
    ISBLANK (Subject),
    ISBLANK (Description)
  )
)

 
This was selected as the best answer
Diego AparicioDiego Aparicio
Hey Tony,

That worked well, thanks!

Diego
Steve MolisSteve Molis
A few things:

I wouild not mix AND OR with && || in the same Formula, pick one syntax and stick with it throughout the Formula 

Also I using use ISBLANK(TEXT(fieldname)) intead of ISPICKVAL (fieldname, "") 

And Salesforce does not recommend using IsClosed in Validation Rules, use the Status picklist value instead. 

And you don't want to use an OR statement with NOT or <> because a User Detail record can only have 1 Username, 1 User Profile, 1 User Role, etc... 

For example if you wanted to block any other User with Profile <> X, Y, Z from doing something and:   
Joe has $Profile.Name = Q
Moe has $Profile.Name = X
Larry has $Profile.Name = Y 
Curly has $Profile.Name = Z 

If your Formula uses Logic like this:

OR(
$Profile.Name <> "X",
$Profile.Name <> "Y",
$Profile.Name <> "Z",
)

Joe is blocked because Q is <> X or Y or Z 
great!

BUT...
Moe is also blocked because X is <> Y or Z
Larry is also blocked because Y is <> X or Z
Curly is also blocked because Z is <> X or Y 

So now every User is blocked, not just the one's you want (and that's not so great) 

Unless the User is Schrodinger's Cat, then all bets are off...  
User-added image
Steve MolisSteve Molis
Try something like this
OR(  
AND(
$Profile.Name <> 'System Administrator',
$Profile.Name <> 'Integration',
CASE(Status, 
'In Progress',1,
'On Hold',1,
'Escalated',1,
0) = 1,
OR(
    AND( 
	RecordType.DeveloperName='Solutions',
	ISBLANK(TEXT(Case_Reason__c))
	),
    AND( 
	RecordType.DeveloperName='Sales_Support',
	ISBLANK(TEXT(Reason))
	),
    AND( 
	RecordType.DeveloperName='Billing_Support',
	ISBLANK(TEXT(Reason))
	),
    AND( 
	RecordType.DeveloperName='Business_Collection',
	ISBLANK(TEXT(Reason))
	)
	),
  ISBLANK(TEXT(Type)),
  ),
  AND(
  TEXT(Status) =  "Closed",
  OR(
    ISBLANK (Subject),
    ISBLANK (Description)
  )
)
)

 
Diego AparicioDiego Aparicio
Hey Steve,

Thanks for jumpin in. I updated the formula to your and it worked well yesterday when I tested. For some reason today when I was doing some User Acceptance Testing again, the rule did not fire up at all. Yes, I made sure to check the VR is Active. I am just figuring out what is different... I even rolled back to the previous formula Tony provided and still would not fire. I then went back to the one I originally posted and (obviously wrong) and for sure it fire up.

The idea of the validation rule is that those fields should always be mandatory for any kind of record type with the specific Case_reason__c only appliying to the specified above and obviously making an exception for admin profiles. 

Any thoughts?