In Microsoft form, when we have a question of type-choice there is option for end user to add input in option ‘Other’.
Sometimes it is very difficult to include all choices in a SharePoint choice column. Though we have manual input option only when we allow multi-select in choice column setting. But we want end user select only one choice and if choices are not listed then H/She select ‘other’ choice and type their input manually and same needs to be patched with SharePoint.
To achieve such functionality, we will integrate SharePoint form with PowerApps and customize it accordingly.
Solution-
- I have created one simple list- Visitor List and created following columns-
- Person Name- Person type field.
- Purpose of visit- Choices: Working from Office, Business travel
- City- Choices- Bangalore, Chennai, Surat, Delhi, Others
- Our objective is that when visiting person select purpose of visit as’ Working from Office’ then City will have dropdown values- Bangalore & if H/She selects purpose of visit as’ Business travel’ then city will have dropdown values- Bangalore, Chennai, Surat, Delhi, Others. Here if visiting person will select Others then H/She can type city name manually.
- Now I have opened my SharePoint form in PowerApps and added one Text input control in City card-
- Now we need to set city dropdown values based on choice from purpose of visit. For this I have set City items-
If(
DataCardValue2.Selected.Value = “Working from office”,
Filter(
Choices([@’Visitor List’].City),
Value = “Bangalore”
),
[
“Bangalore”,
“Chennai”,
“Surat”,
“Delhi”,
“Others”
]
)
- This kind of filtering will restrict user from selecting City choices.
- Now Set the visibility of text-input control based on the city choice- Others. If Visitor selects Purpose of visit as- Working from office, then text-input control should be hidden and if H/She selects Business travel then text-input control will be visible only when H/She select city choice as Others.
- To give better look of this form, I have set width of the control based on city choice-Others
If(DataCardValue3.Selected.Value=”Others”,192,384)
This will provide flexibility to contract and expand city input column accordingly.
- So, our form design is ready, but we did not patch text-input control input with SharePoint City Column. Select city card and write following function for card update-
{
Value: If(
DataCardValue3.Selected.Value = “Others”,
Concatenate(
DataCardValue3.Selected.Value,
“-“,
txt_city.Text
),
DataCardValue3.Selected.Value
)
}
- We want visitor must provide input for choice- others. To achieve this, we need to show error message for text-input control. Use SharePointIntegration Save option and write following code-
If(
DataCardValue3.Selected.Value = “Others” && IsBlank(txt_city.Text),
Notify(
“City Name is required”,
NotificationType.Error
),
SubmitForm(SharePointForm1)
)
- Everything is ready. Now Save & publish your form.
Output-1: Purpose of visit: Working from office; City: Bangalore
Output-2: Purpose of visit: Business travel; City: Chennai
Output-3 Purpose of visit: Business travel; City: Others
Output-4 Entries saved in Visitor List
Thank you
-Gopenly