SharePoint Form Conditional Formulas

SharePoint Lists *are* Microsoft Lists, and in these lists you can create conditional formulas, so that certain fields hide and show according to conditions that you define.  This is just out-of-box in SharePoint, without any other product, no Power Apps needed.

Here’s how it works.  In this example, I’ve created a list using the travel request list template, in SharePoint Online in Microsoft 365.

In your list, click the New button at the top, as if you were going to fill out a new form.

Click Edit columns.

sharepoint-form-edit-columns

The first thing to notice about this next screen, is that it allows a quick way to uncheck boxes to hide or show fields, or drag them up and down to rearrange the way they show on the form.  For example, I have a column/field here called “Approved?” and I don’t want anyone to ever change it when they’re filling out the form, because I have a flow that is changing it based on approval process.  So that’s a good reason to completely hide a field from the form.  Also notice here that if a column is required, it will be grayed out.  You can’t hide it on the form if you want it to be mandatory, basically.

sharepoint-edit-columns-in-the-form

Hover your mouse over the fields, click the ellipsis next to a field, and you’ll notice that there’s an option to Edit conditional formula.

sharepoint-columns-edit-conditional-formula

This means that you can use conditions to hide and show fields.  Instead of having to choose whether a field is always showing or always hidden, you can say under what circumstances you’d like that column to be hidden.  In this example of a travel request form, I don’t need the “Estimated airfare” to be displayed until I’ve chosen an airline, and I don’t need the “Estimated hotel cost” to be display until I’ve selected a hotel.

sharepoint-edit-conditional-formula-field-form

The hyperlink on this little screen is very helpful, and will show you exactly how to write the condition formulas.  Learn to use conditional formulas in a list form.  There is also a list of field types on that site, that aren’t supported by this functionality.

In the documentation, you’ll find that all of the formulas are IF statements that result in a TRUE or FALSE.

TRUE = the column will be hidden

FALSE = the column will show

Basically, their documentation shows that each column type has a slightly different syntax.  In my example above, I want to show the estimated airfare field only if an airline has been selected. Airline is not blank. This one isn’t listed in the documentation, but here’s how it goes:

=if([$Airline] == ”,’false’, ‘true’)

When using these formulas, the system names for fields will need to be used.  So for example, if the column you’re referencing is called “Estimated hotel cost”, that’s not what you’re going to type in the formula.  To get the system name for a column, go to the list settings, scroll down to the columns, and click on that column.  Then look at the URL in the address bar, at the Field=

sharepoint-column-system-name  So my column is really called EstimatedHotelCost

If I wanted to only show a column if the estimated hotel cost is greater than zero: =if([$EstimatedHotelCost]>0, ‘true’,’false’)

If I have an “Approver” column, but the travel request only needs an approver if it is greater than $1,000, then I could do something like:

=if(([$EstimatedHotelCost]+[$EstimatedAirfare])>1000, ‘true’,’false’)

At the almost end of this video, at 53:12, I did a demo of this functionality:

sharepoint-rules-form-editor.png

11 comments

Leave a reply or question

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.