Blog

SharePoint - How to programmatically set and clear single select choice items in a combo box on a form

There is often the requirement to programmatically set and clear combo box values with formula, and the syntax to do this may not be entirely clear. This post walks through a use case scenario of how to set and clear combo box values depending on the value of another control.

When we build data entry screens with single-select SharePoint choice columns, there can often be the need to set or to clear combo box values through formula, and to retain the ability of the form to load existing values. This post walks through an example of how to build this type of functionality.

As an example, let's take the example of a form that's based on a SharePoint list of issues. This form includes two pertinent fields - 'close datetime', and issue status. The functionality that we'll build will configure the form so that when a user enters a close datetime, the form will set the issue status to 'closed'. If the user clears a 'close datetime' value that has been set, the form will clear the issue status value. The structure of the SharePoint list looks like this:

IssueStatus is a single-select choice column and the acceptable values are: - In Progress, Open, and Closed.

Setting and Clearing a Combobox item

To build this functionality, the technique we'll use sets the value of a variable when the 'close datetime' value changes. The DefaultSelectedItems property of the IssueStatus comboboxrefers to this variable to determine what to show in the dropdown.

Taking an auto-generated app that's based on our SharePoint list, we open the edit form and set the OnChange property of the date picker control (DateValue2) to the following formula.

If(IsBlank(DateValue2.SelectedDate),
   UpdateContext({locNewStatus:"null"}),
   UpdateContext({locNewStatus:"Closed"})
)

This formula specifies that if the value of the DateValue2 picker is blank, we set the value of a variable called locNewStatus to the literal string value "null". If the value is not blank, we set it to "Closed".


Next, we set the DefaultSelectedItems property of the Issue Status combo box to the following formula:

If(locNewStatus="", 
   Parent.Default, 
   If(locNewStatus="null", 
      {}, 
      LookUp(Choices(Issue.IssueStatus),Value=locNewStatus)
   )
)


This formula specifies that if locNewStatus is equal to an empty string (that is, the user hasn't amended the close datetime value), we set the DefaultSelectedItems of the combobox to Parent.Default in order to load the existing value, if the form is in edit mode.

If locNewStatus equals "null", we set DefaultSelectedItems to an empty record in order to clear the contents of the combobox. Otherwise, we lookup the choice value that matches the value of the variable - "Closed" in this example.

For the technique to work, it's important to initialise the value of locNewStatus to an empty string when the form loads, and we can do this by applying the following formula to the OnVisible property of the screen.

UpdateContext({locNewStatus:""})
There is a final key step that is needed to clear an existing choice field value. When a user clears the 'close datetime' field and the issue status combobox displays an empty record, this alone isn't enough to remove the existing choice value when the user attempts to save the record through a call to SubmitForm. To clear a choice value, it's necessary to set the Id of the choice value to -1 by amending the formula in the Update property of the data card that contains the IssueStatus combobox using the formula beneath (DataCardValue14 is the name of the combobox control):

If(locNewStatus="null", 
   {
      '@odata.type':"#Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference",
       Id:-1,
       Value:""
   },
   DataCardValue14.Selected
)



At this stage, we can run our app and we can update the value of the Issue Status combobox by modifying the value of the CloseDateTime control.

Conclusion

There is often the requirement to programmatically set and to clear combo box values, and this post highlighted the formula we can use to carry out this task, based on the use of a variable.
Previous
Next
Related posts