Next Gen Data Learning – Amplify Your Skills

Blog Home

Blog

SharePoint Column Validation In Two Ways

by | MS SharePoint, Power BI

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.

sharepoint column validation

In our example, you’ll see that the First Name field says “You can’t leave this blank.

sharepoint column validation

To change it, just click the First Name column and click Column settings.

sharepoint column validation

Next, click Edit.

sharepoint column validation

Then, click More options on the Edit column panel.

sharepoint column validation

Next, toggle No on the “Require that this column contains information” field.

sharepoint column validation

Finally, click Save.

sharepoint column validation

Now, if you add a new item, you won’t be required to fill in information for the First Name column.

sharepoint column validation

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.

sharepoint column validation

Let’s start by adding a column by clicking Add column.

sharepoint column validation

Next, click the Single line of text.

sharepoint column validation

On the Create a column panel, we’ll input the details of the column.

sharepoint column validation

In the Name field, we’ll type “Repair Shop Type”.

sharepoint column validation

Then, for the Type, we’ll choose Choice.

sharepoint column validation

For the first Choice, we’ll type “Independent”. Then we’ll type “Affiliated” for the second Choice.

sharepoint column validation

Finally, click Save

sharepoint column validation
sharepoint column validation

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.

sharepoint column validation

Then click Save.

sharepoint column validation

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.

sharepoint column validation

Next, we’ll add an item on the list called “Ford”, and for the Brand Ambassador, we’ll type in “Luke”.

sharepoint column validation

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

Related Posts