Power Apps Patch function with SharePoint Columns 

Thank you

Thank you Vikram

@@RezaDorrani Hi , but i wanted to store multiple users in one record, that multiple users are coming from another share point list in a gallery, on check i am creating collection and i want to patch the selected users by checking check box and store that into collections and wanted to patch that to store in one column

@@amanchourasia1870 I do not have any video references on this use case. I recommend posting your scenario on forums powerusers.microsoft.com/

It works only if you are actually using a form control. Without form control (form1.updates), you cannot patch to attachments column.

Thank you Ami

Thank you for watching

Wow! Thank you.

You are most welcome Aroh

感谢您观看和喜欢视频

Glad it was helpful! Thank you for watching.

Great to hear! Thank You so much for the feedback.

Thank you Mauro

Glad to hear it!

Thanks Marc

Thank you for your kind words. Will try my best to keep posting relevant and useful information.

Thanks for watching David

Glad to hear that! Thanks for watching.

Welcome 👍

Thank you for the wonderful feedback. The timing on the summary was indeed perfect 😉

You're very welcome!

Glad it was helpful!

Your most welcome

Thank you Jim. Glad it was useful. Audrie is one of my favorites too.

I concur with @Jim Fitch. I was banging my head on my drop-downs using lookup columns, i realize I need to do the drop-downs as choices(…), then every else is easy.

Good observation. Did not include that as I wanted to keep the video basic. But glad you noticed and are using it!

Thanks for listening

Thanks Brian

My pleasure!

You are most welcome

It’s never too late :)

Thank you for watching

Thanks for watching. Hoping to make this channel cover most bases of power platform.

@@RezaDorrani Thanks! I'm working on migrating my MS Access apps to MS PowerApps. I'm very interested in the topic of generating reports, especially complicated reports, which Access is very capable of. Watching your tuts on pdf, very helpful and practical (of course!). Greatly appreciate!!!

Thanks for watching!

You are welcome!

You're welcome!

Yes, great video!

Glad it was helpful!

Thank you

Glad you liked it Ryan. I plan to add videos on advanced Patch functions & the Form control soon.

Eager to see the next videos you mention here!

Ευχαριστώ

Glad it was helpful!

You're welcome!

Glad to hear that!

Glad you liked it!

Check the community tab on my channel. I share all info about upcoming videos there.

Glad you liked it

@@RezaDorrani sir, i have need some help about main category item and sub category item display in power app same gallery and how to control because they are two list main category and sub category how to manage pleases guide me how to solve this

@@imranfc your best bet would be to post your query with screenshots on the community forum at powerusers.Microsoft.com since it would be difficult to provide help without looking at your data source, column types and use case.

Thank you

@@RezaDorrani Sir, give me some help about order Id actually I am using function patch forall function and data save correctly but my problem when I am show this data in gallery and search repeat order id with multiple rows. I want show only one order id and then I am click to show other all rows expand with same Id. Kindly help me about this problem

I am actually working on a video that showcases how to patch data held in collections for entire record data. That video should be out coming Tuesday.

You should store the existing values related to the field in a variable. Then let user make new selections and prior to patching merge the existing and new selection values. Also, ideally you would want to show the existing values to the user so they can just update them. If you need more assistance I would recommend posting your query with screenshots to the community forums powerusers.microsoft.com/t5/General-Discussion/bd-p/PowerAppsForum1

Sie sind herzlich willkommen

You would need to add termguid and label with structure as shown in video. I did not cover managed metadata in video but my blog showcases the syntax rezadorrani.com/index.php/2020/05/04/power-apps-patch-function-with-sharepoint/

Thanks Pavan. Even in case of power bi and power apps integration, the home screen will have a gallery. Check ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-5y2jJyH4Eu0.html Defaults is used only when you want to create new item. When editing data for form, you will need the item context.

Patch function syntax would be same. But the column types and formats used for Patching would be different.

Which Tables?

Depends on how the combox is being populated with data. At the end of the day, when you patch the format should be as described in this video.

You would need to use the office 365 users connector to get that information. I do not have a video reference on this scenario. I will recommend checking on the forums at powerusers.microsoft.com/ in case someone has done something similar.

Thank you and you really asked me a question that got me thinking. I found the solution and I would surely be sharing this soon on my twitter handle @rezadorrani. Credit to you. Your need to do 2 things 1 - Go to experimental feature and turn on formula level error management 2 - Patch your person column as follows PersonColName: Blank() Let me know how it goes.

I have done multiple videos on Patch and Form control.

Patch and SubmitForm are your options

You can use the Office 365 Users connector to list all the users in your Power App

Hi Ian, Great question. I have not tried it. My guess would be that you would need to include some sought of relationship with the root site. Modern SharePoint world is flat, meaning you build site collections and not sub sites. You can also relate sites together using the new hub features. There is also new CT hub publishing features coming soon.

@@RezaDorrani Many thanks for your reply. I am finding your videos very helpful. My issue comes from having a legacy sharepoint set up with a large hierarchy of sites and sub sites. Following the demise of sharepoint designer it’s a shame MS didn’t consider backward compatibility with power apps functionality. I’ll investigate the hub features you’ve mentioned and see what they have to offer as a solution. I don’t suppose you have done a video on them as well, have you. Many thanks again.

@@iancrosby7265 I have not done videos on SharePoint but have been working on SharePoint since 2007 :) Here is the reference documentation for hub sites - docs.microsoft.com/en-us/sharepoint/planning-hub-sites

I would have to see your code to know why this is happening. I would recommend you post your issue with screenshots on the forums at powerusers.microsoft.com/

Patch function does not support attachments. Check ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-3dNeMVLZDHo.html

I do have a backlog of planned videos. Will add it to my list. I do plan on creating a voting feature for subscribers in future. That ways I am creating content in line with subscriber requests.

Just directly patch that column with numerical value. Example: numberColumn: 1

Using defaults(datasource) in patch will post data as a new item to data source. This will automatically generate a new ID in SP.

Currently No. But is a high priority video in my list.

ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-tPxE_-DcWkg.html For Patching you have to follow the format of a person type column as described in this blog post - rezadorrani.com/index.php/2020/05/04/power-apps-patch-function-with-sharepoint/

SharePoint already has a column called ID which is a unique auto number column.

@@RezaDorrani Thank you. And do you have any recommendations on how I can generate graphs for my PowerApp survey? I tried web part chart on SharePoint but it didn't work out because my survey responses do not contain number values. All responses are I'm text.

@@itstiktok8765 I do not have a video on this scenario. I would have to try it out to provide guidance. I will recommend posting your query on the forums at powerusers.microsoft.com

Patch statement does not support attachments. You would need flow - ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-g9ChYuTdNd4.html

@@RezaDorrani thank you Reza.

Start with blank App - Yes. Publish like InfoPath and create columns - No

Hi Pricilla , If you are using Patch to create a new record, then all mandatory columns need to have values. If you are using Patch to update a record, then you can update your fields of choice.

@@RezaDorrani got it,thank you

I have done plenty of videos. Do not remember in which one I did that. Check either the combo box video or one of my editable grid videos.

I will have to look at your App in action to provide any guidance. I would recommend you post your issue/query with screenshots on the forums at powerusers.microsoft.com

@@RezaDorrani Thanks I will do that.

Tough to understand your question without looking at your App in action. I would recommend posting your query with screenshots on the forums at powerusers.microsoft.com

Yes, works with all column types except attachments. I did a recent video on Patch function (check if that helps) + video description has link to a blogpost I wrote on Patch function and all SharePoint column types.

​@@RezaDorrani Thank you, I missed the link to the blog. Very informative and have added my email. How am I able to add not only my multi select lookup choices and my other fields to the same patch and how is patch used with Table as you show? I know your busy, so I apologize for bothering you; I'm just struggling to make this work for my project.

@@golfnutt8 I do not have a specific video on that. And would have to try it out to let you know how. I would recommend posting your query with screenshots on the forums powerusers.microsoft.com

@@RezaDorrani I did and I have found multiple functions that look like they would help but not with what I am doing. For all I know, being somewhat new to this, is I may be going about this all wrong. I will keep looking. Thanks again.

Load all filtered data into a collection and patch it in one go. ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-VzrarcM9W5k.html

This video is purely focussed around Power Apps. For CRM (CDS) the format for patching is a little different. When you are Patching for Yes/No type column in CRM use 'colName (EntityName)'.Yes where colName - is name of your field and EntityName is name of your entity.

@@RezaDorrani thanks very much will try that

Patch does not force to add optional fields. You can avoid patching that column in formula.

I will have to look at your App in action to provide guidance. I recommend posting your issue/query with screenshots on the forums at powerusers.microsoft.com

If they are form controls, you can simply use the SubmitForm function to submit the data to your database.

You can use the same screen to update or add records. I have seen a lot of questions around this on the forums. I will recommend you check on the forums at powerusers.microsoft.com/

For collection patching, check ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-VzrarcM9W5k.html

Patch collection is way faster than forall and more performant. Check - ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-VzrarcM9W5k.html

rezadorrani.com/index.php/2020/05/04/power-apps-patch-function-with-sharepoint/

I do not have a video reference for this scenario. I would recommend posting your issue/query with screenshots on the forums at powerusers.microsoft.com

I will have to look at your App in action to provide guidance. I would recommend posting your issue/query with screenshots on the forums at powerusers.microsoft.com

SubmitForm concept is different from Patch function. I would recommend posting your query on the community forum at powerusers.microsoft.com/t5/General-Discussion/bd-p/PowerAppsForum1

Power BI data set is read only. I have done videos on how to connect back to source from power bi and update using patch. Check ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-5y2jJyH4Eu0.html

@@RezaDorrani Thanks for the reply. I don't want to write back to the data source though I want to read the Power BI data to a Sharepoint List through the Power App visual. Is this possible even with power automate?

@@fenrir433 Please check my videos on power bi integration with apps and flows. I have done videos on them. You can read power bi data in both apps and flows (read-only data) which you can take and write to any data source of your choice.

Make sure output of combo box control when patch is in following format Table({Value:"a"},{Value:"b"}) or [{Value:"a"},{Value:"b"}]

@@RezaDorrani Thanks. New Courses':Table({Value:ComboBox1.Selected.Value},{Value:ComboBox1.Selected.Value})}) New Courses: Is SharePoint List Column name. EveryTime I am patching, getting the item patched. Example: PowerApps as subject. Tried SelectedItems too but not working

Patch('Student Fee Tracker',{Title:TextInput1.Text,Name:TextInput1_1.Text,Fees:Toggle1.Value,'Fee Submitted Date':DatePicker1.SelectedDate,Batch:{Value:Dropdown1.Selected.Value},'Number of Student':Value(TextInput2.Text),'New Courses':Table({Value:ComboBox1.Selected.Value},{Value:ComboBox1.Selected.Value})})

@@fakharahmedkhan5157 Cannot guess here by looking at chat. It should work. But seems u r simply assigning same value twice. I will recommend posting your issue with screenshots on the forums at powerusers.microsoft.com

Class is a lookup column associated with the list "Class". Hence using Choices(Student.Class) works. You could also directly get the values from the Class list. Both approaches will work. Both approaches will show you all the data from the Class list even ones that are not saved in Students list. Display people from gallery selected? Do you mean filtering gallery data based on people picker column? Check this - ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-tPxE_-DcWkg.html

@@RezaDorrani hi again a great video... may be if u please give an idea how to fetch the people field in a combo box and change / edit on it

I do not have a video reference on this scenario and would have to try it out to provide guidance. I recommend checking or posting your query on forums in case someone has done something similar powerusers.microsoft.com

@@RezaDorrani thank you. I will post this on forum.

I have not come across this issue, hence not sure what the resolution would be. I would have to reproduce your scenario to know the next steps. I recommend posting your issue/query with screenshots on the forums at powerusers.microsoft.com

@@RezaDorrani I did and not getting any responses that are helpful. A couple people asked me to post my patch function which I did but without response

@@golfnutt8 Forums is a free platform. You may or may not get the best response. I do not have any bandwidth to validate this and hence asked to check on forums.

@@RezaDorrani Its no problem, I was just wondering if you had heard of anything concerning this. Totally understand you are busy. Again, your videos are amazing and we learn a lot from them.

@@golfnutt8 Thanks for understanding. I have not heard anything on this one.

If department is a text column, you can simply patch it with combobox.selected.department Something on these lines.

@@RezaDorrani Sorry, didn't mention that the 'Class' column is a lookup column to another list that has the corresponding grade for the classes. Ie class 9w is a grade 9 class thank you for the quick response.

@@techdrift73 This is something I would have to look at or try out. I will recommend posting your query with screenshots on the forums at powerusers.microsoft.com

@@RezaDorrani Done, title is 'Patch lookup column from another list'

@@techdrift73 I go to forums to assist in my free time only. There are many other folks who provide guidance.

I have not run into this issue, hence I do not know the reason for this issue. I recommend posting your issue with screenshots on the forums at powerusers.microsoft.com

You could use collections and Patch the collection in one go. Concept of collection Patch ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-wI6SHGQ9ATg.html

​@@RezaDorrani Hi thanks for the reply. I tried checking the video and it seems you still have to input it one by one? my dilemma is i need a code for 1. Submit - New ID 2. Edit -Old Data, Old ID 3. Save As New - Old Data, Save into New ID 1 and 2 seems easy, but 3 seems to lengthy given that my form contains so many item to be filled up sadly Currently it seems stupid honestly... Was looking for a way to shorten this. The first part are text inputs so Update seems to work, the 2nd batch are choices allowing users to input if not in the choices... overall seems like there should be a better way than this? *EDIT cant seem to attach the code due to its length, but basically there are over 600 Datacards in the Screen. I had to use Excel to make it easier to create the Code since it was repetitive. Patch('PADMOUNTED TRANSFORMER',{'Test ID':Text(DataCardValue2.SelectedDate,"yyyy-mm-dd-PDMT-")& Last('PADMOUNTED TRANSFORMER').ID+1, 'Date of Test':'Date of Test_DataCard1'.Update, 'Project Code':'Project Code_DataCard1'.Update, Client:Client_DataCard1.Update, Address:Address_DataCard1.Update, Equipment:Equipment_DataCard1.Update,................................ //2nd part// 'Tap Setting':If(!IsBlank(DataCardValue22.Selected),DataCardValue22.Selected,If(!IsBlank(DataCardValue22.SearchText),{Value: DataCardValue22.SearchText,'@odata.type': "#Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference"},Blank())), 'TTR Test Equipment':If(!IsBlank(DataCardValue23.Selected),DataCardValue23.Selected,If(!IsBlank(DataCardValue23.SearchText),{Value: DataCardValue23.SearchText,'@odata.type': "#Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference"},Blank())),.......................... Any idea is appreciated. TIA

​@@grapegh0stx I would have to look at your app in action (screenshots) to provide any guidance. I will recommend you post your issue with screenshots on the forums at powerusers.microsoft.com/

Is your lookup column multi select or single? A collection is an array - you can only pass 1 value at a time to the Value and Id property.

Hi Mustafa, Lookup columns need the following format { Value: "", Id: 2// Id of lookup column } So a multi values lookup would be Table( { Value: "", Id: 2// Id of lookup column }, { Value: "", Id: 1// Id of lookup column } ) Table(Value:Dropdown.selected.value) is not working for you since format is not right. Also dropdown.selected.value will only give you the text. Try the format shown in this message.

ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-BzZ0a3zRFUk.html

Did you try the technique in this video?

ID is created automatically in SharePoint.

@@RezaDorrani Thanks for the quick response, but how do I get that ID in Powerapps to use after it is automatically created in Sharepoint?

@@ronpalyok3212 When you get the item, you will get the id also.

@@RezaDorrani Wow how quick!!! If I patch a record, how do I get the ID

@@ronpalyok3212 Simply save the response of patch in a variable. Set(x, Patch(...)) x will have all the data you need.

I have not come across this issue and hence not sure what the cause could be. I recommend posting your issue on forums in case someone has experienced something similar powerusers.microsoft.com

Hi Ashish, I would have to see the code base to help. I would recommend posting your issue with screenshots on the forums at powerusers.microsoft.com/t5/Building-Power-Apps/bd-p/PowerAppsForum1

It should simply be datepicker.selecteddate. I dont think there is much more to it. Probably has to do with format of date. See what today() returns and compare it with what you are providing from date picker. I have not faced any issues with dates hence my advise is limited to what I know. I recommend posting your issue with screenshots on the forums at powerusers.microsoft.com

​@@RezaDorrani today() works. and when I substitute in any of the following: I've tried: ApprovalDate: dateApprovalDate.SelectedDate (it's last in the list so no comma) ApprovalDate: { '@odata.type':"#Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference", DateValue:dateApprovalDate.SelectedDate }.DateValue and just for fun: ApprovalDate: { DateValue:dateApprovalDate.SelectedDate }.DateValue I get nothing. I see it in the gallery and then I can see the patch running (little dots moving) then as it tries the date, the frame around it goes bold and the date disappears out of the gallery and isn't in the collection, so when I patch the collection, it's not on the sp list b/c it gets wiped out of the collection. I've looked at every event to see if somehow *I* wiped it out and I don't see anything. I will look into formats and mess around, if anything I've replied with gives you more ideas, I'm just stuck. Thanks Reza for replying. I appreciate it.

@@RezaDorrani Hey Reza, I changed the format, it still didn't work but I noticed when I changed to long format, it first put the longformat date into the gallery field and then it switched to what the format really should be which is mm/dd/yyyy briefly and then blanked out again. Very odd behavior I will check with the forum but, again, if that sparks an idea please share. thanks, Sir.

@@AmyCustomer It has to be format related. I’m sorry but I cannot assist as I cannot reproduce the issue. Forums will be your best bet.

@@RezaDorrani I totally understand and thank you for pointing me to the forums. And thanks for all the goodness you share. Cheers, Sir.

I have not come across this issue and hence not sure what the cause could be. I recommend posting your issue on forums in case someone has experienced something similar powerusers.microsoft.com

If it's multi select - then array of choices video description has link to blog post

@@RezaDorrani generally choice columns have more than one option even if it's not a multi-select column. Otherwise what's the point of having one choice?

@@sudosaurus What I meant was single select vs multi select. Options would be many.

@@RezaDorrani so on the blog post do I need to pay attention to "Choice Column" or "Multi Select Choice column " - if the first (as it's a choice and not-multi-select), do I just need to enter "{Value: "choicevalue1", "choicevalue2"}" etc..?

@@sudosaurus if single select {Value:”data”} If multi Table({Value:”data”},{Value:”data”})

SharePoint has a limit of 12. That cannot be changed in sharepoint online. You would either have to change the column types or look for a different data source.