+ Start a Discussion
WPCMSWPCMS 

Formula for Validation: How to add mulitple Profiles to this long formula

Status__c ="Active"&&Text(Service_Frequency__c)="1"&& Serv_Freq_Count__c<>0&&
Status__c ="Active"&&Text(Service_Frequency__c)="1"&& Serv_Freq_Count__c<>1 ||
Status__c ="Active"&&Text(Service_Frequency__c)="2"&& Serv_Freq_Count__c<>0 &&
Status__c ="Active"&&Text(Service_Frequency__c)="2"&& Serv_Freq_Count__c<>2 ||
Status__c ="Active"&&Text(Service_Frequency__c)="3"&& Serv_Freq_Count__c<>0 &&
Status__c ="Active"&&Text(Service_Frequency__c)="3"&& Serv_Freq_Count__c<>3 ||
Status__c ="Active"&&Text(Service_Frequency__c)="4"&& Serv_Freq_Count__c<>0 &&
Status__c ="Active"&&Text(Service_Frequency__c)="4"&& Serv_Freq_Count__c<>4 ||
Status__c ="Active"&&Text(Service_Frequency__c)="5"&& Serv_Freq_Count__c<>0 &&
Status__c ="Active"&&Text(Service_Frequency__c)="5"&& Serv_Freq_Count__c<>5 ||
Status__c ="Active"&&Text(Service_Frequency__c)="6"&& Serv_Freq_Count__c<>0 &&
Status__c ="Active"&&Text(Service_Frequency__c)="6"&& Serv_Freq_Count__c<>6 ||
Status__c ="Active"&&Text(Service_Frequency__c)="7"&& Serv_Freq_Count__c<>0 &&
Status__c ="Active"&&Text(Service_Frequency__c)="7"&& Serv_Freq_Count__c<>7

 

We currently have a formula that lets us know if the days of the week checked match the number of times it is picked up a week

 

So if the service frequency is 3 and Mon, Wed, Fri (three days) is checked, the user can save the record.

 

If the service frequency is 3 and Mon and Wed (two days) are checked, the user can not save the record.

 

Also this validation allows no service days to be checked

 

Now I need to exclude profiles from being affected by this validation. How do I add several profiles to the validation above?

 

 

 

Best Answer chosen by Admin (Salesforce Developers) 
thomastthomast

I'm not good enough with the order of operations on the && and || operators to follow the logic of your formula completely, but if looks like your goal is to allow System Administrator and Accounts Receivable profiles to bypass the formula. In that case, those two conditions need to be parenthetically OR'ed (||) against each other, and then ANDed to the rest of the formula. Beginning or end makes no difference, but something like:

 (NOT($Profile.Name = "System Administrator" || $Profile.Name = "Accounts Receivable")) &&
[rest of formula]

An alternative approach, especially if you want to force those profiles to bypass the validation very intentionally rather than through erroneous data entry, is to create a checkbox field, perhaps Validation_Override__c, that is read-only for all profiles except the two you want to allow. Then those users have to check the box in order to override the validation, and your formula looks like this at the beginning:

NOT(Validation_Override__c) &&
[rest of formula]

Also, I'm a little confused by couple aspects of your formula - why are you converting Service_Frequency__c to text, rather than using the numeric comparison? It seems like you could make it a lot less complex by doing a single direct comparison of Service_Frequency__c and Serv_Freq_Count__c, with an OR condition to allow Serv_Freq_Count__c to be 0. Not relevant to your immediate question - just curious/nosy.

All Answers

SteveMo__cSteveMo__c

here you go

 

NOT( $Profile.Name = "System Administrator")

 

 

WPCMSWPCMS
NOT( $Profile.Name = "System Administrator"&& $Profile.Name = "Accounts Receivable,

Status__c ="Active"&&Text(Service_Frequency__c)="1"&& Serv_Freq_Count__c<>0&&
Status__c ="Active"&&Text(Service_Frequency__c)="1"&& Serv_Freq_Count__c<>1 ||
Status__c ="Active"&&Text(Service_Frequency__c)="2"&& Serv_Freq_Count__c<>0 &&
Status__c ="Active"&&Text(Service_Frequency__c)="2"&& Serv_Freq_Count__c<>2 ||
Status__c ="Active"&&Text(Service_Frequency__c)="3"&& Serv_Freq_Count__c<>0 &&
Status__c ="Active"&&Text(Service_Frequency__c)="3"&& Serv_Freq_Count__c<>3 ||
Status__c ="Active"&&Text(Service_Frequency__c)="4"&& Serv_Freq_Count__c<>0 &&
Status__c ="Active"&&Text(Service_Frequency__c)="4"&& Serv_Freq_Count__c<>4 ||
Status__c ="Active"&&Text(Service_Frequency__c)="5"&& Serv_Freq_Count__c<>0 &&
Status__c ="Active"&&Text(Service_Frequency__c)="5"&& Serv_Freq_Count__c<>5 ||
Status__c ="Active"&&Text(Service_Frequency__c)="6"&& Serv_Freq_Count__c<>0 &&
Status__c ="Active"&&Text(Service_Frequency__c)="6"&& Serv_Freq_Count__c<>6 ||
Status__c ="Active"&&Text(Service_Frequency__c)="7"&& Serv_Freq_Count__c<>0 &&
Status__c ="Active"&&Text(Service_Frequency__c)="7"&& Serv_Freq_Count__c<>7)

 

I don't know where to add it. I know this is wrong.

 

 

 

 

SteveMo__cSteveMo__c

You need to append it the the rest of your VR, like this:

 

AND (ISNEW(), Probability >= 0.50, 
NOT( $Profile.Name = "System Administrator"), 
NOT( $Profile.Name = "CSDS Executive Support"), 
NOT( $Profile.Name = "Executive Support"), 
NOT( $RecordType.Name = "Bundle Sales Opportunity") )

 

 

thomastthomast

I'm not good enough with the order of operations on the && and || operators to follow the logic of your formula completely, but if looks like your goal is to allow System Administrator and Accounts Receivable profiles to bypass the formula. In that case, those two conditions need to be parenthetically OR'ed (||) against each other, and then ANDed to the rest of the formula. Beginning or end makes no difference, but something like:

 (NOT($Profile.Name = "System Administrator" || $Profile.Name = "Accounts Receivable")) &&
[rest of formula]

An alternative approach, especially if you want to force those profiles to bypass the validation very intentionally rather than through erroneous data entry, is to create a checkbox field, perhaps Validation_Override__c, that is read-only for all profiles except the two you want to allow. Then those users have to check the box in order to override the validation, and your formula looks like this at the beginning:

NOT(Validation_Override__c) &&
[rest of formula]

Also, I'm a little confused by couple aspects of your formula - why are you converting Service_Frequency__c to text, rather than using the numeric comparison? It seems like you could make it a lot less complex by doing a single direct comparison of Service_Frequency__c and Serv_Freq_Count__c, with an OR condition to allow Serv_Freq_Count__c to be 0. Not relevant to your immediate question - just curious/nosy.

This was selected as the best answer
WPCMSWPCMS

The service_Frequency is a picklist and I have to change it to text.

SteveMo__cSteveMo__c

You can use a CASE or a TEXT function to convert Picklist value to Text.