Building PowerApps from scratch? Use Patch!

[darogael]

Introduction

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

UpdateContext({AddRecord: Patch('Patch it', Defaults('Patch it'),

It is important to know that now you have a ( { ( { bracket operation started and hence you will have to close this operation at the end.

Now we focus on each of the columns.

First name, Last name, Address and notes are all  text and hence have the same formula. They end with .Text

Title: FirstNameTextInput_1.Text,
lastname: LastNameTextInput_1.Text,
address: StreetTextInput_1.Text,
notes: NotesTextInput_1.Text,

Arrival and Departure are date and time columns. They end with .SelectedDate

arrival: ArrivalDatePicker_1.SelectedDate
departure: DepartureDatePicker_1.SelectedDate

Zip and Amount in cash are number columns. These behave similar to the text, however, need to be added to the Value function

zip: Value(ZipTextInput_1.Text);
amountincash: Value(AmountInCashTextInput_1.Text)

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

smoking: {Value: SmokingDropdown_1.Selected.Value, '@odata.type': "#Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference"}

singledoublebed: {Value: SingleRadio_1.Selected.Value, '@odata.type': "#Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference"}

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

city:{'@odata.type':"#Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference",Id:AddCityGallery.Selected.ID,Value:AddCityGallery.Selected.City}

state:{'@odata.type':"#Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference",Id:AddStateGallery.Selected.ID,Value:AddStateGallery.Selected.Title}

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.

Important Links

Know issues and limitations with SharePoint Online
Adding a lookup field to a PowerApp by Laura Rogers
Adding Managed Metadata columns by Paul Culmsee

Patchit.zip