cancel
Showing results for 
Search instead for 
Did you mean: 
darogael

Building PowerApps from scratch? Use Patch!

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.

 

Capture1.png 


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.

 

Capture1a.png

 

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.

 

Capture.JPG

 

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.

 

Capture2.JPG


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.

 

Capture3.JPG

 

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.

 

Capture4.JPG

 

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.

 

Capture5.JPG

 

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

Comments
Anonymous

How do you load the STP files into Sharepoint? 

ok i figured it out... thanks

Thanks for this information!

 

I have built a similar app but uses the collection function to bundle multiple entrys before sending it to SharePoint.

 

I am struggling to get the patch function to work, how would you do that based on this example? @darogael

Thanks for this great example, keep them comingSmiley Happy

Brilliant example - makes it really clear.

 

However - could you also show how to Patch to a Person column - Ive been trying to do this - the syntax is accepted but then on running it errors with The requested operation is invaliid, Server response a value must be provided for item. clientrequest ID (lots of numbers and letters)

 

 

Working with Patch. Just got my syntax out of the yellow warning and now just blue informational. I would like to know is I can have my button wiht my patch formula / OnSelect, retun confirmation. So on confirmation, I can transition screens or provide an error message to my users.

 

Chris West

I am currently using Patch to create a new record in a SharePoint list.  One of my columns is Report Type with the choices being 1. Report an issue 2. Make a suggestion 3. Ask a question

 

I would like to modify my patch statement so if 1 or 2 is selected, it will send the information to the original datasource. But if 3 is selected, I would like to patch the question to a SharePoint Discussion Board.  

 

First, is this possible?

And, if so, how does one patch to two data sources based on a selection?

 

Thank you.

Hi tianaranjo,

 

Yes this is absolutely possible to patch two datasources. You would simply have an if statement with two patch statements.

 

If(column1value="1. Report an issue" || column1value="2. Make a suggestion" , Patch(OriginalDatasource, {field1="value"}) , Patch(NewDatasource, {field1="value"}))

 

I am not too sure about posting this to a SharePoint Discussion board but I suspect this would be similar to posting to a list which is supported. Maybe also consider using actionable messages in Teams for this functionality, to post the question to a team of people and allow them to interact with the question directly from Teams. Blow is a great doc on how to achieve this.

https://docs.microsoft.com/en-us/outlook/actionable-messages/actionable-messages-via-connectors

 

Below is what this could look like.

 

 

 

 Hei !! good article but iam having difficulty in writting patch because i have different language of studio . any help ?? 

Anonymous
Very good tutorial. I get an error The type of this argument XYZ does not match the expected type 'Record'. Found type' Text' I also tried collect but the same error. What am I doing wrong?

Hi yugpatel

 

Can you send us the formula you are using?

Anonymous

I r esolved the error. My SharePoint list had an inherited column Title. I was not populating that field in my Patch syntax. But after some google search, I came across one post that mention that you have to populate all the fields. In any case here is my syntax:

UpdateContext({AddRecord:Patch(toolsweeklyverification, Defaults(toolsweeklyverification),{Title: "",bwtoolnumber: txtBWToolNumber.Text,.........