In this blog, you’ll learn how to do SharePoint column validation via conditional logic and description. Let’s say, for example, you want to limit a user from accessing a certain field unless they input the right value.
By using formulas and proper descriptions for SharePoint column validation, you can easily limit and validate inputs from your users.
Adding An Item In Your SharePoint List
Let’s first add an item to your SharePoint list. So, click New.
Then, fill in the details on the New Item panel.
In our example, you’ll see that the First Name field says “You can’t leave this blank.”
To change it, just click the First Name column and click Column settings.
Next, click Edit.
Then, click More options on the Edit column panel.
Next, toggle No on the “Require that this column contains information” field.
Finally, click Save.
Now, if you add a new item, you won’t be required to fill in information for the First Name column.
Adding A SharePoint Column
Keep in mind that we’ll use these columns for validation purposes. In our example, we’ll use a list that we previously made, and it’s called Brands.
Let’s start by adding a column by clicking Add column.
Next, click the Single line of text.
On the Create a column panel, we’ll input the details of the column.
In the Name field, we’ll type “Repair Shop Type”.
Then, for the Type, we’ll choose Choice.
For the first Choice, we’ll type “Independent”. Then we’ll type “Affiliated” for the second Choice.
Finally, click Save
Next, let’s edit them by clicking Edit in grid view.
Under Repair Shop Type, we’re going to set BMW and Honda to Independent. On the other hand, Mazda and Mercedes will be Affiliated.
Lastly, click Exit grid view.
Adding Another Column On SharePoint List
Now, let’s add another column on this list. We’ll name it “Affiliate Shop Name”. Just make sure that its type is Single line of text.
Then click Save.
Since we have two “Independent” for the Repair Shop Type in our example, we can leave the Affiliate Shop Name blank. However, the other two – Mazda and Mercedes are Affiliated. Hence, we’ll type “Mr. Lube” for Mazda, and “Oil True” for Mercedes.
Then let’s click Edit in grid view, and apply the changes. Once done, click Exit grid view to save the changes you made.
Next, we’ll add an item on the list called “Ford”, and for the Brand Ambassador, we’ll type in “Luke”.
Next, we’ll add an image.
For our example, we have saved pictures, so we’ll just use our Ford picture.
And for the Repair Shop, we’ll type “Ford’s Repair”.
Then the Repair Shop Type is “Independent”.
For the Affiliate Shop Name, we’ll type “?? Ford”.
Finally, click Save.
SharePoint Column Validation Using Conditional Logic
As you can see, you can change anything even though there shouldn’t be any option to do so. For example, even if the Repair Shop Type is set to “Independent”, you will still be able to type anything. But with validation, we can fix this.
When you add a new item to your SharePoint list, on the New item panel, you can add a formula where the users can only enter information for the Affiliate Shop Name column when the Repair Shop Type column is set to “Affiliate”.
To do that, let’s click New to add an item, and click the Edit form icon on the top right.
Then, click Edit columns.
After that, you’ll see the available columns on your list.
Next, we’ll click the 3 dots beside the column we want to edit. In our example, we’ll edit the Affiliate Shop Name column because we don’t want it to appear when the Repair Shop Type column is set to “Independent”.
Then, click Edit conditional formula.
One of the best things about the SharePoint list is that you can also input formulas in your SharePoint columns.
Editing The Conditional Formula For SharePoint Column Validation
The Edit conditional formula for Affiliate Shop Name field text box is where you’ll type the conditions you need for your column validation.
Keep in mind that if you want to clear the condition, you just need to leave the text field blank. Let’s click the Learn to use conditional formulas in a list form link in the box.
This will redirect us to Microsoft’s documentation of formulas. Take note that you can use this to know more formulas that could make your SharePoint list more convenient to use.
In our example, the simplest way is to use the formula “=if([$Category] == ‘Product Management’, ‘true’, ‘false’)”.
This means if a field equals to a certain value, then show that field to the user. If the expression is true, it’ll show us the field. But if it’s false, the field will not be shown.
So let’s go back to the text box on your SharePoint, and we’ll type the formula “=if([$RepairShopType] == ‘Affiliated’, ‘true’, ‘false’)”. This basically means that if the Repair Shop Type value is Affiliated, the expression will return true. Else, it’ll be false.
Always remember that if your column name has spaces, Microsoft just condenses it. That’s why we didn’t put spaces between the words.
Next, click Save.
Then, click Save on the top right of the Edit columns in the form panel.
After that, we’ll click Cancel.
Finally, we’ll refresh the page.
Adding Another Column In SharePoint List
Next, let’s add a new column to try it out. First, click New.
And you’ll see that the Affiliate Shop Name column is not yet visible.
However, once you choose “Affiliated” on the Repair Shop Type field…
…the Affiliate Shop Name field will appear.
And when we change the Repair Shop Type field to Independent, the Affiliate Shop Name field will disappear.
But keep in mind that this formula doesn’t stop you from making complex conditional formulas. If you want to learn more about other formulas you can use, just click the link that says Learn to use conditional formulas in a list form in the box when you set a condition on a column.
And the best thing about adding a formula is that you don’t need to redo it as it applies to all the items on the SharePoint list. Whether you are editing an item or adding a new item, the condition set still applies.
SharePoint Column Validation Using Descriptions
Another way to let people know what to put in each field in your SharePoint list is to add a description. It will let the user know what the field represents.
In our example, we’ll put a description on the Affiliate Shop Name column. Click the column, choose Column Settings, and click Edit.
In the Description box we’ll type “This refers to the aim of the affiliate that the repair shop is tied to. Please Only populate this field if the Repair Shop Type is “Affiliate””
Then, click Save.
After that, refresh the page.
Now, if you add an item to the list, you’ll notice that the description we set under the Affiliate Shop Name text field is there.
Adding a description can help the users identify what they need to put in the text field. It might be a simple way but it surely is effective. Keep in mind that the description won’t stop them from typing other values, but it surely helps to guide the users.
***** Related Links *****
Calculated Columns In SharePoint | An Overview
SharePoint Lists: An Overview
Adding SharePoint List Navigation
Conclusion
To wrap things up, you’ve learned how to use conditional logic for SharePoint column validation. You can use basic to complex formulas to make your columns more efficient and convenient to use. Also, you’ve learned how to add a description to guide the user on what the field is for.
Just remember that you can search for other formulas from Microsoft which can certainly work well for your SharePoint list columns. If you’d like to know more about this topic and other related content, you can certainly check out the list of relevant links below.
All the best,
Henry
