Contains Function for a Validation Rule


Userlevel 3
Badge +8

Hi all,

I'm trying to create a validation rule on a person or group form field to keep users from selecting certain AD accounts like contractors and admin accounts.

I see this runtime function "contains" I was thinking I would write the formula with this like =contains(string, element).

In this example, what is the string and what is the element? I've tried it with the person or group field as the string and the word I would like to exclude "CONTRACTOR" as the element and visa versa.

I'm sure I'm just doing it wrong.

Could any of you give me some pointers?

Thanks

Kassie 


4 replies

Badge +4

Kassie,

It might not work for you,but the way we do is by creating a SharePoint Group with all the users that they are allowed to select. Again, this worked for us because, we only had handful of them that we need to restrict on. if you have many, this might not be the right approach.

Then in SharePoint List create a new column of person field and then set the value to only allow to select from the SharePoint Group that you just created above.

Add this field to the form and they will only be able to get a valid user which exists in the Group defined.  This way, no special validation is required, all we then need to check is whether that field is empty or not.

Userlevel 3
Badge +8

Thanks for the reply Shaju!

The selection could be any of the two thousand employees that work here so the list would be a lot of upkeep and a lot to set up initially. :-/

I'm sure this could be done with a list and web call to pull the people from AD and remove the contractors and admin accounts but this sounds like a lot of work for something that I feel should be doable in the form with a formula.

Userlevel 4
Badge +7

Hi Kassie

Do you have something in the login name that identifies all these accounts?

The login format we use in my organisation is: i:0#.w|domainlogin. My login is the first 4 letters of my surname (craw) and 4 random numbers.

I set up a contains validation rule, contains(string,element) where string is the string of my login name and element is what i am looking for as below:

Capture.PNG

where people is the Named Control of the people picker field

This is the result:

Capture.PNG

As long as you can easily identify the people you want to exclude you should be able to base your solution on this.

Let meknow how you get on

Regards

Paul

Userlevel 3
Badge +8

You're right, the field was pulling : i:0#.w|domainuserID

I ended up doing a user profile lookup to pull something that I would validate with.

Here's the final formula:

contains(userProfileLookup(ServiceAccountOwner,"LastName"),"CONTRACTOR")

I had to look up the info in our AD accounts to see where we added whether someone is a contractor or not... apparently its after their last name. lol

Thanks so much for your help. happy.png

Kassie

Reply