Don't want to use the Out-Of-Box PowerApps form but yet have the flexibility to save your data to a SharePoint list? Look no further, this blog walks you through the Patch function available to save your items to a SharePoint list. This is a no code solution blog and hence will not focus on the Metadata and Choice with multiple select checkbox columns. This blog focuses on how to add the Patch formula to save data to a SharePoint list and does not include all the features of the PowerApps, however, I have attached the SharePoint STP files and the PowerApps MSAPP file which you can use to rebuild this solution in your environments.
Scenario This PowerApp is built for a hotel reservation check-in process. The app can be used either by the attendee or for a self-check-in process.
SharePoint list Let's take a look at the SharePoint list we'll be using.
When the list was originally built, lower-case and without any space was used to name the list and the columns. Hence the original list name was 'patchit' and then renamed to 'Patch it'. You can confirm this by looking at the URL. This will save you the stress from adding the X20… and also make fewer potential mistakes when writing the Patch function.
The same was done for all the columns. The original column names were-
Title - The column name was changed to First name lastname - The column name was changed to Last name address - The column name was changed to Address city - The column name was changed to City state - The column name was changed to State zip - The column name was changed to Zip singledoubebed - The column name was changed to Single or double bed? smoking - The column name was changed to Smoking arrival - The column name was changed to Arrival departure - The column name was changed to Departure amountincash - The column name was changed to Amount in cash
You can use the STP files to rebuild these list in your SharePoint site. First add the StateCityCounty and StateCityCode lists, then add the PatchIt list. This should add the City and State lookup columns automatically.
Brief overview of the PowerApp This entire PowerApp is built as a single screen and contains several sections as shown below.
The AddCityGallery and AddStateGallery are used to save data to the City and State lookup columns. In order to do this we've added the StateCityCount and StateCountyCode SharePoint Data Sources.
The ViewGroup is a group of all the controls made available for viewing purposes only. Each of the controls are relative to the selected item in the ViewGallery. Also the light green color signifies that you are currently viewing the existing reservations. You also have the '+' on the to right which allows you to add a new reservation.
Once the '+' is selected, the icon disappears and is replaced by the 'x' and ✓ icons. Also the light blue color signifies you can make an entry. The State and City are buttons and clicking them will display the State and City gallery respectively. Whichever value you select in those galleries will become the selected value for your State or City. This is an important and required step to save values to the lookup values.
The Patch function The naming convention you use for your controls plays an important role. In this scenario all those controls used for adding purposes end with _1. Those without were used for viewing. For those that were text I used xxTextInput, for date I usued xxDatePicker, etc. The Patch function is added to the OnSelect function of ✓. Here is a screenshot of what the entry Patch function looks like. The Set(ViewGroupVar, "true") is NOT PART of the Patch function and is used to hide or show the controls used to add new entries.
The Patch function starts with identifying the SharePoint list name, which in this case is called 'Patch it' and tell that you can apply all the default values that are available in the list columns. Hence the formula
Smoking and the Single or Double bed columns are both choice type columns, one is a radio button the other a drop-down. Since the choices are pre-defined in SharePoint, we need to use an ODATA type connection to refer to the SharePoint List using the Microsoft Azure Connector. Hence the formulas are
The City and State are both lookup columns in the SharePoint List which are connected to two pre-defined list. Hence here we need to use an ODATA type connection to refer to the SharePoint List using the Microsoft Azure Connector, however, here we need to reference both the ID and the Values. Hence the formulas are
Finally, make sure you end your formula with the } ) } )
This video walks you through the SharePoint List and the PowerApp
Conclusion As mentioned before this blog does not focus on the not focus on the Metadata and Choice with multiple select checkbox. The Multiple Select checkbox is currently not supported, however, is currently being reviewed by the Microsoft PowerApps team.