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

32 comments

  • What a great post and awesome feature.

    One question, I know that this can be done in powerapps, can you hide field based on if a person is creating a new item or not? Use cases is a form where you only fill out a few questions and someone else comes and fill the manager section.

    Like

    • Yes,
      If(TheNameofYourFormControl.Mode=New, true, false)
      Put that in the visible property.

      Like

    • Jeremiah Benjamin

      @Daniel, if you’re asking how to do this in an SP form vs. PowerApps, here’s what I’ve done: I created a hidden date column (StartDate) and then a Flow populates it after the list item is created. A conditional formula hides several fields in the form until that StartDate has a value in it, effectively making those additional fields available on submit: =if([$StartDate] != ”,’true’,’false’)

      It could be another type of column (I don’t think there’s anything magical about date), but for our process the StartDate was already in play and convenient. I think for future lists I may set a standard for myself and create a hidden choice column like “formStatus”. Based on the status, fields can be shown or hidden by using a flow to change the status at appropriate times in the process.

      Like

    • Laura,
      May you please give more details about the formula If(TheNameofYourFormControl.Mode=New, true, false) ?

      Where can I find the name of the FormControl ? As is I have an error “invalid formula”

      Like

    • Click on your form in your PowerApp, and look at what the name of that control is. By default, it will be called “Form1”.

      Like

  • I had totally missed this, and it rather useful. Many thanks Laura – I’ll even do a mini session on it at our User Group (crediting you, of course)

    Like

  • Brilliant and important capability that my (onprem only) organization won’t get immediate access to. Thanks for another simple and incredibly useful post, Laura!

    Like

  • Awesome! Thanks!

    Question: Is there a way to conditionally require the field that is displayed, since ‘required’ fields can’t be conditionally displayed?

    Like

  • Pingback: SharePoint List Form – Form Editor | @WonderLaura

  • Hi Laura,
    I have tried using this referencing “date” type column using the Microsoft recommended script – =if([$StartDate] >= Date(‘4/6/2020′),’true’,’false’), however can’t make it work for date column that was created by user. It works only for default fate field (Created, Modified). The internal column is correct (only one word).
    Have you tried using this for date columns?

    Like

  • What is the formula when the condition depends on Metadata column. I’ve tried $xxx.label but is not working.

    Like

  • Is there to do this by identifying the current user or more usefully by identifying if the current user is a list admin or site member?

    Like

  • Hi Laura,
    Is there a conditional to make a field read-only on the SharePoint List form?

    Like

    • Jeremiah Benjamin

      @Preston, I’ve been faced with a similar challenge and managed it this way (if I’m reading your question correctly):

      1. Create a calculated column directly referencing the column you want to “conditionally” make read-only
      2. In the Form formatting, set your Condition to “show” the calculated field when you want the “read-only” field visible; test it to make sure it works
      3. Copy the text from the Condition you just created, and Paste it into the condition for the *original* field; just reverse the ‘true’ and ‘false’ statements

      I use this method to show an empty date field, but once it’s got a date in it, the fillable field goes away and the calculated field (read-only) becomes visible, essentially locking the user out from making changes to it. My condition is just testing to see if the date is not null =if([$StartDate] != ”,’true’,’false’)

      Liked by 1 person

  • Can you add more than on conditional formula to a column? If so, how do you separate them. I have two IF statements I need to use for one column.

    Like

    • There may be a way to do that, but Microsoft doesn’t make it clear. The syntax examples on their site don’t show if there is a way, either. I don’t know.

      Like

  • how would you code an OR? e.g. if status = ‘Active’ or ‘Cancelled’ then Blue else Black

    Like

  • Hello Laura,
    Thank you, for all the posts that you have made until now. They have been really helpful.
    I have a question. I want a condition for one column based on the length of another column.
    Is there a way to do it? I have tried len(columnName) formula but it doesn’t work. I didn’t find any formula online for this requirement.

    Like

  • Can we perform this formatting on document libraries forms?

    Like

  • Is is possible to hide columns on the NEW form but not the EDIT form?

    Like

  • Thanks Laura, stumbled across this in a quest to get some guidance on this topic, and amongst the plethora of articles/Youtube videos I found, yours gave the key information on how to get the system name for the column in question. This saved me tonnes of time so thanks very much!

    Liked by 1 person

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.