SharePoint Online: Show or Hide a Form Field based on another Field using Conditional Formula
Requirement: Show or Hide a field based on another field’s value in the SharePoint Online list form.
How to Show or Hide a Field based on Another Field in SharePoint Online?
In the past, we used to achieve this using jQuery Show or Hide SharePoint List Form Fields based on Another Field’s Value using jQuery. Now in SharePoint Online, we have Conditional Formulas Out of the box to achieve that functionality. Here is what we wanted: Show the “Project Classification” field only when the “Project Status” field value is set to “Yes”. Otherwise, hide it in the list Form. In SharePoint Online, you can use a conditional formula to specify what should happen based on certain conditions in a column. Here is how to configure the conditional formula to show/hide columns based on other columns:
- Go to your SharePoint Online list >> Open the New Item form by clicking on the “New” button in your list.
- On the New item page, click on the “Edit Form” button and then “Edit Columns” as in the below screen.
- Now, on the “Edit Columns” form, click on the three dots (:) and choose “Edit Conditional formula” from the menu. In my case, it’s the “Project Classification” field I want to show or hide.
- In the conditional formula, Enter the formula to show and hide the field. In my case, I have entered “=if([$IsActive] == true, ‘true’, ‘false’)” and hit save to commit your changes. Be sure you are using Internal Names of the columns, instead of the display name.
- Now, the “Project Classification” field will be visible only when the “IsActive” field value is set to True!
The conditional formula must start with the “=” symbol followed by an “IF” condition. If the condition is satisfied, it returns true, and that shows the field. Else, it returns false and hides the field.
Please note that not all columns are supported in conditional formulas as of today. E.g., Managed Metadata column, Person or group/choice with Multiple selections enabled, Currency columns, Location, Calculated columns, etc., are under the “Unsupported” list. Also, on columns marked as “Required”, we can’t apply conditional formulas to show or hide (that’s obvious, isn’t it?). Here are some examples:
| Column Type | Formula Example |
| Yes/No (check box) – Boolean | =if([$ProjectStatus]==’completed’,’true’, ‘false’) |
| Number | =if([$Budget]>5000,’true’,’false’) |
| Choice | =if([$Department]==’IT’,’true’, ‘false’) |
| Date | =if([$StartDate]==Date(’01/01/20221′),’true’,’false’) |
| Person | =if([$Approver].email==’[email protected]’,’true’,’false’) |
| Column with calculation | =if([$Column1]+[$Column2]<5000,’true’,’false’) |
PnP PowerShell to Set Conditional Formula
Here is how to set the conditional formula using PnP PowerShell:
#Parameters
$SiteURL = "https://crescent.sharepoint.com/sites/PMO"
$ListName="Projects"
$FieldInternalName = "Project_x0020_Classification"
$Formula = "=if([{0}] == true, 'true', 'false')" -f '$IsActive'
#Connect to the site
Connect-PnPOnline -Url $SiteURL -Interactive
#Get the Field
$Field = Get-PnPField -List $ListName -Identity $FieldInternalName
#Apply conditional formula
$Field.ClientValidationFormula = $Formula
$Field.Update()
Invoke-PnPQuery
More on conditional formulas in SharePoint Online List forms: https://docs.microsoft.com/en-us/sharepoint/dev/declarative-customization/list-form-conditional-show-hide
Hi
how we can create a calculated column in sharepoint using the below excel formula.
=IF(E3=”Regular”,”N.A.”,IF((SUMIF($C:$C,$C3,J:J))>55,”Long”,”Short”))
Instead of column References Like E3, use the SharePoint Column name!
@salaudeen thanks for replaying
But what about the range? Can we not take a sum of an entire column?
The formula inside sum if ? How to write in sharepoint?
I’m having trouble with the basic conditional statement for a choice column.
It’s just not working!
I’ve tried the visible name and the internal field name and neither seem to be working.
Is there any other tips or tricks I’m missing??
=if([$field_0]==’COLOUR BLUE’, ‘true’, ‘false’)
What do I need to enter into the condition column name if the Displayed column Name is Edit Status with a space between the two words?
You need to nest your if statement. So it executes the first one, if it’s not true, it moves to the second one. See your example below…
=if([$ColumnA] == ‘Red’, ‘true’, if([$ColumnA] == ‘Pink’, ‘true’, ‘false’))
Thanks – very useful
Great stuff thank you for sharing. I was inspecting the network calls and it seems that the request method merge is used. When I take that payload and create the same request using PowerShell I get 415 unsupported media type. Would that mean I am doing something wrong, or is it just not doable programatically for now?
Found it. Multiple value conditions.
=if([$FormStatus] == ‘Condition1’ || [$Condition] == ‘Condition2’, ‘true’, ‘false’)
Is it in any way possible to use multiple Conditions? something like “OR”. Lets say for instance Column – A Has two choices, Red and Pink.
I want to show Column B if “Red” is selected.
But i want to show Column “B” AND “C” if i select “Pink”.
I have been trying to syntax it like: =if([$ColumnA] == ‘Red’, ‘true’, ‘false’) || if([$ColumnA] == ‘Pink’, ‘true’, ‘false’)
on Column B, but without success.
I dont only want to rely on Powerapps for these simple conditions, but for me it seems like this is still not available or atleast ‘officially’ documented.
Any suggestions?
Operator Description Operator Description
== Equal to != Not equal to
> Greater than = Greater than or equal to <= Less than or equal to
|| OR && AND
Great! Do you have how to Apply With power Shell
Post has been updated with the PowerShell to set conditional formulas in list/library forms.