PowerApps: Patch SharePoint Lookup, Person and Choice Fields
ฝัง
- เผยแพร่เมื่อ 14 ต.ค. 2024
- #PowerApps #Beginner #SharePoint
In this video I show how to work with and Patch complex SharePoint field types in Power Apps including: Lookups, Choice and Person Fields.
![ILLSLICK - WINTER IS COMING [Official Video]](http://i.ytimg.com/vi/dwTMeM1zIhQ/mqdefault.jpg)
These are tricky! I had to work out the person one and the lookup one for a project recently
Yeah they don't make it that intuitive to figure out for sure!
lookup i have solved, but cant figure out the person field display in edit
Dang. All that in 8mins and 20secs. You are so good at editing these tutorial videos together, April. GREAT vid. Really enjoy the straight-forward approach to your content.
Thanks Matt!!
Greetings April & at the cost of sounding old fashioned, God bless you! Life saver! Thanks so much!
You're so welcome!
Fantastic, April! I was trying to figure out how to write a text value to a new column from the selection made in a lookup from a SharePoint list to kick off a work flow to update the lookup based on the text field value (whew😅). This explanation totally saved my bacon. I was able to solve my problem so much easier. Nice work - thank you!
The lady with short and sweet videos to pull the citizen developers from their road blocks.
A very big shoutout to you April for all your time and help.
Thanks Sudheer 😊
Thank you April for your video. well worth tips! I was searching solution for this for hours and finally found this! You saved my day!
I'm so glad!
Thank you, April! You provided the answer I've been seeking for a week, or maybe a month!
You're welcome - glad it was helpful!
@April you are my angel , was struggling for the whole day for these field types.Thanks a lot.
Glad I could help!
OMG you are AWESOME - thanks this worked like a charm, and let me tell you I looked everywhere for the answer on dropdowns. WHOOO HOOOO - THANK YOU. Can you do a walkthrough on adding a datepicker?
So glad to hear this was helpful! Is there something specific you're needing to know about date pickers? There isn't much to them as there is with dropdowns
@@AprilDunnam True, I have searched Hi and Low and the closest thing I have found is:
CertificationRenewalDate:
{
'@odata.type':"#Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference", DateValue:DatePickerCertificationRenewalDate.SelectedDate}.DateValue
I tried it several different ways but couldn't get it to work like your example of the Dropdown and select complex SharePoint field types. Do you have any suggestions?
Hey April, first, love all the material, I have learned a ton, my only critique is (which is probably because I am a beginner), is that you run everything within the app. How is a user accessing this? Are they creating new item from SPO? Is this a mobile App and that's how they will view it vs going into SPO. I guess I'd like to see the functionality outside of the application.
Hey Douglas,
I appreciate the constructive feedback. I'm usually so focused on showing how to do things from the edit experience of the app that I tend to stay there and overlook the holistic experience like you pointed out. I will definitely take this feedback into account for my future videos and make an effort to also demo how the end user would open and interact with the app.
Thank you! I'm finally able to wrap my thick head around these concepts ✌✌
I'm so happy to hear that Brian! Glad I could help!
@@AprilDunnam 👍A quick follow-up inquiry: if I have a screen with an EditForm control, would there be a conceivable use-case for using Patch versus SubmitForm? I have an EditForm control that only includes 1 Data Card intended to be updated, and thus, the others intentionally set to DisplayMode.View. My original thought process was, "Why submit the entire form (to SharePoint) if only 1 field is being modified at a given time?".. But in retrospect, wouldn't I be better off using SubmitForm (versus Patch) so as to leverage the OnSuccess/OnFailure properties?
Usefull video.
I'd like to understand more about those fields for person column.
The best video I ever seen about powerapps tricks
Thanks Daniel!
Love your videos. I'm trying to find out how to patch a SharePoint person column with multiple values. I'm my Powerapp I'm using a combo box for the people picker. I've found videos on how to patch multiple person field, it but the values are hard coded. I need a way to patch multiple people from the combo box people picker input from my user. So far, I can't find out how to do this. Any ideas? Thanks again.
Hey Bobby - What you'll want to do is take the concept in those videos on how to patch multiple but wrap that inside a ForAll() function and loop through the selected items in your combo box. So you can take the selected values in that loop and output that into a collection that you can then pass in to your patch statement
Thanks !
So, if I have a DropDown control (related to another SP list) in my form, I can't use SubmitForm, but Patch only ?
Correct ?
Or perhaps to use SubmitForm for populating MOST of the fields and THEN Patch to fill this ONE DropDown (related to another SP list).
Feasible or not ?
Hi April, This is a really helpful video. I understood how to patch combo box to lookup column in SharePoint List. I have a requirement to patch multiselect combo box to lookup field that allows multiple values. I think that makes it more complex. What changes I need to make for that?
Hey Prasad - I think this video should help with that :) th-cam.com/video/bmft4JFWJiY/w-d-xo.html
@@AprilDunnam - You are really great and helpful. I second the first comment on that video.. 'This is EXACTLY what I needed to solve a problem I've been having!'... Thanks a million
Hi April, Your videos' are always very help, Thank you very much!! I have an issue, once click the "Patch" button, the values (two combox : people & status) saved the sharePoint which are good, then the app crashed, pop up window shows "You have exited the app", "refresh the page to reload the app", then empty screen, no form showing up, no matter create new or edit exist record (customized the form using app, still use Sharepoint), I don't know what is wrong, any suggestions? many thanks!!
hi great video... how to display and edit those complex columns from gallery can u plz upload?
Thanks! Sure I'll add that to my list.
Thank you! I am currently running into an issue on how to edit person or group of person fields. When I use an editform, I can create, delete but update is only working if I populate other fields but if I remove fields it's not reflecting on the data source even though those fields are not required. Any help?
Great video, April! I would like to understand how to map these fields when you are using Chart options (pie, line, bars) instead of a form. You happen to have a tutorial on that already (sorry if you do, I searched for it but failed to find)? Again, thanks a lot for all the explanation, you rock :)
Excellent, If there is a need to approve adding another record to combobox which relates to a lookup column that retrieves vendor from vendors list, how can I go about it, any help is appreciated.
In the patch for the lookup Combo Box I do not have the ".Id" property available. I do have the ".title" available. I can wrap the .title in the value() function without error but it does not work when submitting ("the specified column is generated by the server and cant be specified" (passing a number in the id field (2) returns this same error)) and it does not display an ID value.
Great video! Love your work! God bless! Thanks for doing what you do=)
Great Video. i have a request if possible can you create a video for auto select fields of a form using SQL server tables
Hello, great guidance thanks for this. I want to get distinct values from lookup column, I am having lookup column Business unit, it is getting values from another list with duplicate values, I want to show unique value in my drop-down, please help, district isn't working for me.
Say you use the custom people picker combo box to lookup a user and then Patch the value for a user to the SharePoint List, how do you get the person's name to display in the combo box when you edit the record and want to change the person to someone else?
Cool video, however, nobody seems to show how you can remove a Person column value. I have a list with a person column populated but what if I need to remove the value and left it blank. It seems that I cannot patch an empty claims value. Is there a way of doing that?
Hi April,
I don’t think you need the odata type of you submit it like this:
For choice fields:
Patch(
SharepointListName;
Defaults(SharepointListName);
{ChoiceFieldName:{Value:"NewValue"}}
)
Lookup:
Patch(
SharepointListName;
Defaults(SharepointListName);
{
LookUpField:
{
Id: 4; //ID of lookup column. Must be written with a small "d"!
Value: "ChoosenValue" //Value can be left blank
}
}
)
Person:
Patch(
SharepointListName;
Defaults(SharepointListName);
PersonFieldName: {
Claims: Concatenate(
"i:0#.f|membership|",
User().Email // Person email
),
Department: "",
DisplayName: "",
Email: User().Email, // Person email, can be blank
JobTitle: "",
Picture: ""
}
)
Thanks for the tip!
Hi April, after hours, I thought I'd found my solution with your excellent video! Alas, not so much!
I am using a combo box that pulls the choices from a collection. Your suggestion works perfectly UNTIL I get to the ID part -- I'm putting in ID:cmboxCity.Selected. and it only gives me Title as a choice; not ID or any other fields. However, I can see ID in my collection.
Any idea what I might be doing wrong?
Thanks so much! Always love your work.
Hey Cindy - I realize that this has been a couple months since you asked this. Sorry for the delay, I got behind :( What is your items property for your combobox set to? I wonder if it's set to a collection or something else that might not have a reference to the ID
Hi April. Do you have a solution for clearing out a person picker field in Power Apps? I'm not doing a patch though.
My Power App is connected to a SharePoint list form and I have a field called "Current Approver". I have the Update property of the Current Approver data card set to a variable: varCurrent Approver. When the approver clicks "Approve" and clicks the Submit button, I have this code in the Submit button:
Set(
varCurrentApprover,
{
Claims: Blank(),
Department: Blank(),
DisplayName: Blank(),
Email: Blank(),
JobTitle: Blank(),
Picture: Blank()
}.
But nothing happens. No errors. Apparently, when you send blank values to it, Power Apps thinks you are not wanting to change anything so it keeps the current value. Are you aware of this issue?
I found the answer:
You can create another person column in your SharePoint list and leave it blank. In your Submit button, use a lookup to this column and set your original person column to the value of this blank value column.
For example: You have a person column called "Current Approver" and you want to clear this out with a submit button. Create another person column in the list called "Current Approver Reset". You will never touch this new column. Just leave it blank always.
In Power Apps, set the "Current Approver" data card Update property to a variable like "varCurrentApprover". For the submit button, before the SubmitForm(form) action, add this code:
// Set the varCurrentApprover to the value from the "Current Approver Reset" field for the selected item in SharePoint
Set(
varCurrentApprover,
LookUp(
'Your List Name',
ID = ThisItem.ID, // Match the current item by its ID
'Current Approver Reset' // Retrieve the "Current Approver Reset" field value
)
);
There you go
This was interesting. I have a question. If I have already selected a person in the Person field and then want to remove that selected person from the Person field and make it blank but I couldn't do it. It does not update the field with Blank value and retains the previously selected person. Any ideas?
Hey there - this post on the power users community shows a method of passing a blank value to a person field: powerusers.microsoft.com/t5/Building-Power-Apps/Clear-people-picker-box-in-SharePoint-list/td-p/178204
Hi @April
I created collection onchangeproperty of combobox which I am using in updateif function and patch for bulk update to sharepoint list.
if select combobox options for different items in gallery,the patch is passing same value to sharepoint list instead of different values for each item.
Appreciate your response.
I am working with a list that has a site column based on a master list and the patch command doesn't recognise the name of the column on the list I am writing back to. The list that the site column looks data up from exists in another site. Does the patch command work with lookup columns which are site columns.
Great Work
Thank you! Cheers!
This is a great video. I have it working except for patching with the person field. I cannot get the .Mail identifier to populate/be recognized. Only the .DisplayName is populating as a recognized identifier for some reason. Any idea what I am doing wrong?
Apparently the .DisplayName identifier works in the Claims: and Email: portions of the person filed. I thought I needed the .Email but so far so good with using it my way. Strange.
Him that's odd, the .email should work. Either way glad you got it working
Thank you for that
Though it is more like an addition than a patch, where we would need to find the record we are updating using filter function..
Why not microsoft add function to simplify manipulation of those complex fields. It doesnt look like easy low code to business users.
Hi Jeff - Patch isn't just an addition. The Patch function can handle both adding net new items to a data source AND handling changes/additions to an existing item
Thanks April much appreciated
You are so welcome
So useful! Thank you
Glad it was helpful!
Bro you are crack!! but I am stuck in a problem, I am using choices in a combo box since I have a look up field, the thing is that I want this combo box to be prepopulated with the last record in the sharepont list, I wrote this formula "Filter(Choices(SampleLog8kDef.Batch_ID);Id=Max(Id)" but then it returns in the combo box the first record not the last, I hope you can maybe help me thanks yougotthisyougotthisyougotthis
Hi April, I have a list in sharepoint which record temperature every 30 min, how do I find the temperature difference, between the lastest and last reading on powerapps? .in sharepoint it doesn't allow as the data is listed in rows. thank you
So there are a few different ways to do this. I'm not sure how you are wanting this to surface up in the app. But one way that I would do it is to do a filter query to get the top 2 most recent temp readings for the current logged in user. So assuming that in your SP List you are using the Title field to store the email of the user and another number field called Temperature, I would add a Label with the following formula to do the difference calculation:
First(FirstN(SortByColumns(Filter(Temperatures,Title = User().Email),"Created",Descending),2)).Temperature - Last(FirstN(SortByColumns(Filter(Temperatures,Title = User().Email),"Created",Descending),2)).Temperature
Hope that helps
@@AprilDunnam thank you so much April will try it out
Any idea on how to submit to submit multiple rows from a collection to a lookup? I can get it to submit but the lookup publishes the first choice in the collection to every row.
You'll need to leverage the ForAll() function to loop through the selected rows. For example:
Patch(
'My List',
Defaults('My List'),
{
Title:"PowerAppsTest",
MulitChoiceLookupField:ForAll(ComboBox1.SelectedItems,{'@odata.type':"#Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference",Value:Value})
}
)
@@AprilDunnam so I should clarify, I'm using a dropdown not multiselect, its simpler for my needs. In the collection there could be 3 rows of data and when I patch it to sharepoint the lookup defaults always to whatever the choice is in row 1. My belief is that this is a syntax issue and just cant figure out the syntax.
Here is my code for reference:
ForAll(
OppCollection,
If(
!IsBlank(NetwProgName),
Patch(
'Proposal Opportunity Tracker',
Defaults('Proposal Opportunity Tracker'),
{
'FXN Network Program Name': {
'@odata.type': "#Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference",
Id: NetwProgName2.Selected.ID,
Value: NetwProgName2.Selected.Title
}
Thank you!!!
You're welcome!
Hi, how do i patch without combo box on the interface for column Person
How do I pull the person's name from my organization's sharepoint list?
I got a lookup column and the user selects multiple items. Then I save it to the collection. Then on a final screen, I got a submit button, I patch the collection to the Sharepoint list. But for the lookup column, I am getting errors. I am trying Metadata(SP):{Value: metadata(collection).Value, Id: metadata(collection).Id}
Hey there - You should be able to do this without a collection like so:
Conditions: ForAll(MultiSelectDropdown.SelectedItems, {
'@odata.type' : "#Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference",
Id: ThisRecord.ID,
Value: ThisRecord.Title
})
Thanks April, it works like a charm
@@fasawan2477 Great!
April, how do you pass a blank person in a Person field?
Hey there - this post on the power users community shows a method of passing a blank value to a person field: powerusers.microsoft.com/t5/Building-Power-Apps/Clear-people-picker-box-in-SharePoint-list/td-p/178204
How can a I patch data with an image using the cámara control???
🙏🙏🙏 thanks
You’re welcome 😊
Hi April, how do i patch an existing record (not new)
Yes, you certainly can. Just one small tweak needed. Instead of using Defaults(Your data source), you need to put in a Lookup to your data source of some kind.
For example:
Patch(MyDataSource, Lookup(MyDataSource, ID = ThisItem.ID), {Title: TItle, etc})
@@AprilDunnam amazing! and thanks! what if there was several items with the same lookup value ie looking up with non unique value and i wanted to patch all of them
The more I learn about Power Apps, the more I miss InfoPath 😒
😢