This vid just fixed my problem; slowly getting my head around all of this and thought I had my solution with a collection to modify access levels. The solution it turns out is a patch using the drop down. Thank you my friend you are a genius!!!!!
THANK YOU! This video saved my app. I spent hours scouring the powerapps forms for '@odata.type': "#Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference"
"I don't ever recommend lookup columns because they are so weird and complicated." Ha! Wish I had heard that a week ago before I set out to build my first production Canvas app that's based on SharePoint list and not the relational CDS territory where I normally roam 😄. Thanks for another helpful video, Shane!
Thank you so much. I was struggling with my case for almost two days and finally found a solution thanks to your video! In my issue i couldn't save (datacard > update) a choice field. Choice options were taken from a separate SharePoint list.
Thanks for all the videos you upload, they really helped me to solve some tough issues. Could you explain how you would work with a multi-select choice field where it is allowed to add new/customer field values to it. That is really giving a me tough time. Specially when i need to add several new values.
Hello Shane I have a doubt, is there a way to Patch a SharePoint List using the dropdown selected value as the column to Patch? Hope you are having a great day and I really appreciate all your videos, you are incredible. Thanks once more.
Thanks Shane, this is quite a useful video. Can you please help me out with one doubt: I have a combo box on Power App which is a multi select and this combo box is holding the value from the lookup column. Now how we will patch it.
Shane, many many many thanks! Your explanation aided me tremendously. I got the DropDown working as intended/expected. However, I still cannot get the proper code required for the DefaultSelectedItems. I am using Dataverse as the dataa source and the syntax used for the SharePoint implementation is not working. Do you have a video on making this work with Dataverse?
This video isn't your exact answer but it is a deep dive into DefaultSelectedItems that should get you on a better path ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-AXAbmy9zYTU.html
Hi Shane, in this video you have used a combo box to insert data in a LookUp field. I was wondering if I can use a TextInput to enter data in a LookUp field in Sharepoint? P.s. your videos has helped mi a lot.
Thank you! The video was really helpful! I m struggling with a scenario where I have a collection and I need to patch it with a SharePoint look up Column.. in this case how can I give my ID and value?
Hi Shane, I love your videos, I'm trying to patch a lookup column from a powerapps form using a dropdown, does this works the same? please correct me if I'm wrong, should I patch on the datacard update containing the dropdown? I have been looking for a solution for weeks man! I really need help, thanks!
Hi Shane, thanks again for another great and clarifying video. I got one little question. I'm using your combo box example and it works now. The choices function by items and the DefaultSelectedItems function did the trick. Only thing is that the use of choices only works with an online connection. I use this for offline purposes. What do I need to fill in by Items to get it working when offline? Thanks in advance!
Thank you so much, it is really helpful :) I'm still struggling with something, in a form with different SharePoint columns types, what should I use to submit the form updating simple fields like text and people picker?
@@ShanesCows Thank you. I've removed the form and add it again and now when an edit the item, all content is update in the SharePoint list, including the people picker content. Maybe it was a temporary bug. Thank you for your videos and your reply!
Hmmm, wonder if we could take this one step further and use Collect along with patching complex columns? I would love that as I often need to patch multiple new rows at one time. Working with Lookup columns is such a headache!
Great videos. they have been helping me a lot. I was wondering, is it possible to use choice columns in collections? i have issues starting a collection with a choice column in it. I see can find sources for patching a choice column, but my issue is that my collection is expecting a text input. i feel like there is some basic foundation I don't understand here :/ where am I going wrong?
@@ShanesCows i am slowly learning powerapps with no priori experience. Do you by any chance have a video om this that I have missed? Your videos have helped me a lot:)
I don’t think I do. Not something I do a lot. Try this Collect(TestCollection, YourList) then you can look at that collection to see how the data is arranged
Hi Shane, thank you very much for your videos and help, again coming to you for the following: I have a multiple selection listbox with 8 different options, but when saving the record, these selected items need to go to the corresponding column or field, no I have succeeded, that is, it saves the last selected item and leaves the rest blank or false, (SelectMultiple is true) thanks for your help.
@@ShanesCows seems like BC has some limitations with powerapps compared to dynamics. one being the search function, which will give you an error. I found in a forum that this is so, but can be circumvented by using collect function but dient explain how. I didn't know if you knew how to do it and if there are other cases like this. I have followed side by side on at least 10 of your videos and so far this is the only problem. again Great series I try to do one a day.
Thanks Shane..as always informative.. Got a question though. Say I want to patch a searchtext or maybe the user input text in my choice column. How could we achieve this? This will solve all my problems for now..
@@ShanesCows sorry my bad.. well to be exact a choice box from SharePoint allowing users to provide manual values..need not be patch but use the update property of the datacard too..but yes if you share for both or more cases would be pretty good too..
Thanks, I have learned a lot from your videos! I like to know whether it is possible to patch (maybe collect first) from a gallery, a Dropdown outside the galley, 2 DatePickers (also outside) and a look up (like in your; PowerApps SharePoint LookUp Column video) into another SharePoint list (different from the gallery, mentioned) all together as one item. A short Yes/No answer is fine, just so I know whether I shall move on or find another strategy :-) Thanks in advance...
This video should help. PowerApps SharePoint Complex Columns - Patch person, choice, and lookup fields ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-gsk14D-CYRE.html 😎
Hey Shane, love your videos. Came across a weird thing the other day. The people picker patch doesn't seem to work if the Sharepoint List is created from an Excel Spreadsheet. Any idea why? I've managed a workaround but couldn't for the life of me see the difference between the Blank Created list and the Excel Created one. Especially as I added the people fields manually on both. It has been quite the head scratcher !
Hi shane ..thanks for what you have shared...it helped a lot...but i have a question..how to patch a multiselect choice column if controls in app are checkboxes...suppose we have checkboxes for ABCD and if checkboxes for A and C are selected we have to patch AC...plz help on this
Hi Arun & Shane, I had a similar situation and was able to figure out a solution. First, I used the handy walkthrough presented here: 365basics.com/powerapps-using-checkbox-controls-with-multi-valued-choice-columns/. The part that didn't work for me in that walkthrough was Step #2. I instead used the following for the Default value: If(.Text in ThisItem.''.Value,true,false). Hope this helps!
Hi Shane, is it safe to say that whenever updating a SharePoint lookup field, a choice function is required? I have tried other variations and that is the only one that seems to work. Also, can you provide an example of a filter on this kind of function? Choices(Details.Parts)
Patch(Details, RecordToUpdate, {Parts: {Value: "Buddy"}) Would update the Parts column to Buddy. No Choices required. Filter(Details, Parts.Value = "Buddy") Would return all of the rows where Parts was set to Buddy 🤩 You got this!
@@ShanesCows what I need to do is filter "Filter(Details, Parts)" based on what's is selected in a dropdown connected to another list. I have this formula where there are no errors but the filtering is not happening. I am thinking the reason is; "because it is finding where the condition "is" true and hence it will show everything". Filter(Choices(Details.Parts), LookUp(Period, CorrespondingMonth = Text(Month(Now())), true))
Shane. Thanks for all your vids. I'm having an issue with creating new record with lookup field and pushing the data to common data service. I have a combobox to select the field. Entity: Asset Lookup Field: Location ComboBox1: (Choices(Assets.Location)) Code: Patch( Asset, Defaults(Asset), {Location: ComboBox1.Selected})
@@ShanesCows. Its not an error. all the data except the lookup field goes into CDS. i also tried creating a collection with the lookup field value to bring over to CDS and failed because it requires a "record" type...
Hi Shane, thanks for the video. I notice the MultiSelect Choice Column part is only to modify the line that already in the SP, right? How about submit a new line with multiple selection to SP from a ComboBox? How does the Patch function should be?
Yes you can patch title column also on a button click...just remember that patch is written after submit-form otherwise submit is gonna overwrite patch
Hi Shane, great work as always. You help me a lot through my discovery of Powerapps. But now I'm facing a wall: I need to display and edit (in 2 differents screens) the content of a Person/Group column in SP ... Except it allows multiple choices, without limits on the number of possible people. I read here and there that it is currently impossible to do. I also found a work around via the use of patch, but it seems to work with a limited number of persons only. Do you have any idea? Thx in advance !
Hey Shane, thanks for all your awesome videos, really helps a lot. Quick question, my SharePoint list column name has a single quote, for example, "Special Instruction / KPI's" and when I try to patch it with " Patch('Client Briefs',Gallery2.Selected, {'Special Instructions / KPI's', DataCardValue35.Text} " it comes up with an error saying the column name does not exits. I am thinking of changing the column name and trying again, but my whole power apps is built already, and I fear if I change the column name, it will affect my app. Please help.
Best way I found to avoid this is always use underscore "_" instead of "space" when creating column names. If you don't then sometimes your column name will be Example : "columnx21398000" which is annoying.
Thanks Shane - your videos are awesome! Slightly different scenario I'm struggling with: I have a multi-select choice column in SharePoint. In my PowerApp I want to give a list of choices that is pulling from an excel table (it's a long dynamic list coming from a query, hence I need it to be in excel). I manage to get the list in the items of my combobox, but I cannot get it to write back to SharePoint. I tried both via patch and via update through an actual form. As soon as I change the Items to the excel column, the update function doesn't work anymore. Any thoughts?
Datasource shouldn’t matter at all. I would look at the output of your combobox and make sure it looks like what you want. Also what type of column are your writing to. If it is not a single line of text then you have to get fancy. This video will help ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-gsk14D-CYRE.html
UPDATE: it turned out it was the 'column' name because I was pulling the Items for the combobox from another source. I was able to fix it by using RenameColumns() function and rename to "Value". It even works with update function in a form now, so don't even need to patch. Hope it helps anyone else with the same problem!
So I want to add a twist to this. I have added a gallery to simulate the actual check box functionality found in the SharePoint Multi-Select choices column into a form. I need to edit the DataCard update property. Currenty I have GalleryChoices.Selected but that only saves a single selection regardless how many it allows you to select. I know it is looking for the table of data but I don't know how to modify the Update property statement. I think it needs to be something similar to what you show when patching a multi-select choice column. How would I modify that to work properly in the update property of a data card?
Yikes Juli. I don't doubt it is possible but I don't know if I know how to do that off the top of my head... I think you would have to use the data shaping functions (dropcolumns, addcolumns, renamecolumns) to turn the selected items into the table format it wants.
Do you have any advice for having a Gallery with 2 drop downs, one is a connected to a Sharepoint choice column, the other is connected the “created by” column (but it’s blank for some reason) I would also like the results filtered by ID in descending order. I seem to be able to get the gallery sorted right and have the drop down for the choice column work, but not the “created by” part Using Filter(SortByColumns(SharepointList,”ID”,Descending), Dropdown1.Selected.Value in ChoiceColumn.Value) I’ve been trying for days to get the function to work with 2 drop downs, even tried a combo box instead, but drop down 2 (created by) is always blank and I can never make a function seem to work Any advice is appreciated :)
Hi Shane, thanks again for an amazing video. Regarding patching a look up field, I’m finding that this works if the lookup is to a list on the same site, but it doesn’t seem to work if the look up is to a list on a parent site. It doesn’t seem to recognise the fields. Any thoughts?
I have some concerns about this. The value is patched through to the record as soon as the control is updated. I can easily see a scenario where a user clicks a couple of different values, DOESN'T click save and asks why his record was updated. Second question, (I just don't know how PowerApps work yet) when you create a new record, change the drop-down - what is that writing to? Is there a record automatically created when you start to add a record? What happens if the user clicks Cancel? Are you left with a record with just the drop-down field set? I know, I know, too many questions but the details matter! By the way, I've learnt so much from these viideos, I want to say a big thank you.
Hi Shane, thank you so much for this video. I have tried to follow all your instructions but probably I made an error somewhere. I have a multi select combo box and the following Patch : Patch(testlist; Defaults(testlist); { Title:"Name"; dayoftheweek:ComboBox3.SelectedItems}) Where dayoftheweek is a column of type Choice in the list testlist. In last line I have an error message saying that the type of the argument dayoftheweek does not correspond to the expected type 'Record', instead it is of type 'Table'. Can you plese help ?
Hi Shane. Thanks for the video, it's very helpful (as always). Could you please advise how to patch a multi-value person field with values from a multiple selection combo-box? Thanks in advance.
@@ShanesCows Yes, sorry. Unfortunately the below code throws an error: "Expecting a table value, but of a different schema. Your formula is missing a column 'Claims' with a type of 'Text'" Patch(Requests, Defaults(Requests), {Owners: Owners_CB.SelectedItems} ) ComboBox items: If(Members_ComboBox.SearchText "", Office365Users.SearchUser({searchTerm:Members_ComboBox.SearchText})) Any idea how to make it work?
Ignore that! I managed to get it working. If anyone is wondering how to do that: Use "ForAll" with the ComboBox, create a collection and then pass the collection in the Patch.
Could you make a video about patching and submitting blank values back to the datasource, seems to be tricky with certain field types like the people picker
Sir, very nice informative video, I wanted to ask you that is there any solution to bring the sharepoint account Profile Pictures in Power Apps? as I am making a Time Log App and wanted to show their pictures when they open the App. with their ID's. How can I achieve this?
Hi Shane, i have been looking for the past 4 days at your video's concerning combo boxes, i have a problem connecting the selected data in the combobox to the sharepoint list column that should receive the data, the column is defined as choice(700+ possible choices), i can select the data in the combo box, but it doesn`t go to the sharepoint I`m patching the data from 10 screens all at one to sharepoint Patch( P01_YUL_PC, Defaults(P01_YUL_PC), { Title: User().FullName, Date: DatePicker1.SelectedDate, LMSnrSO: ComboBox7, Question1: Radio1.SelectedText.Value, Question2: Radio1_1.SelectedText.Value, Question3: Radio1_2.SelectedText.Value, Question4: Radio1_3.SelectedText.Value, Question5: Radio1_4.SelectedText.Value, Question6: Radio1_5.SelectedText.Value, Question7: Radio1_6.SelectedText.Value, Question8: Radio1_7.SelectedText.Value, Question9: Radio1_9.SelectedText.Value, Question10: Radio1_8.SelectedText.Value } ); Navigate(EndScreen) Just the LMSnrSO won`t work Ty, Marc
I noticed you made some really popular videos about powershell and I'm asking if you would know any way to remotely access a computer and get into the bios to set a bios password using powershell. Thanks.
Hi Shane, Thanks, I have learned a lot from your videos. Can you please help me to solve this issue. I am getting the below error when I am trying to pull the documents from sharepoint docuemnt library to canvas app edit form. Error: The query cannot be completed because the number of lookup columns it contains it contains exceeds the lookup column threshold enforced by administrator. But my document libray has only 5 colums in the view and it had only one view. Do you know how this happens and how to solve it. Thank you in advance!!
Hey Shane, your videos are super useful! Can you post a solution for using multi-value checkbox in powerapps from a choice column in sharepoint list? Or if there is any other workaround for using multi-value checkbox out-of-box? Please please please! :)
Hello Champ, Great session, but I am unable to update a lookup field in a edit form of a particular entity even combo box is not working. kindly suggest.
Hi Shane, great video!! Any chance you know how to patch the Attachments SP field? I am sure it is something like the table example in your video. Just getting red squiggly lines... Within my patch statement, here is my attempt: {Attachments: ForAll(DataCardValue53.Attachments, {'@odata.type':"#Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference", Name:Name, Value:Value})}
thanks for the video... solved one of my issue... always gets something to learn... another one is, i have collected multiple lookup values (department1, department2, department3) of department column, now i need to patch it in my sharepoint list... please help me
Another amazing video you are awesome. I have a problem that I can't solve. I need to set a default value in a form field, whose data is of the LookUp type. Insert {Value: varData.ID} in the DataCard Dafaut, the value I need appears in the field but is not saved in the list of the shrpoint when I submit the form. Can anyone tell me what am I doing wrong?
Hi Shane, is there a watch to patch interdependent (cascading) comboboxes with multiple options enabled? I always get the message of expected value text vs table and the Concat function won't help...
Shane this doesn't work if you use a combo box with something like this to handle a large list. I have items set to : ShowColumns(Filter([@Customer], StartsWith(Title, CustomerSearch.SearchText)), "Title"). What do you suggest I use to update sharepoint? I tried everything but nothing works.
Thanks for the video! However I need help! I have a Dropdown control that pulls data from a collection and also a form tha loads its content from a gallery. When I try to patch the dropdown value to my sharepoint list it doesn’t do it. It doesn’t send an error message. My formula is: Patch(Sharepointlist, Gallery.Selected, Form.Updates, {SPcolumn: Dropdown}). The dropdown formula is: Search(Collection, TextInput.Text, CollectionColumn)
How do you do this without a gallery, Gallerys and data tables give you more flexibility but for more advanced controls when you dont want a gallery what is the best way to retrieve the data from the sharepoint list not using a gallery or other embedded form, just a label or a text input box. How do you pull in the current items data?
Hello! I have a SharePoint list which has column names from 1 to 52. I have a Text Box whose name is "WeekCount". When this name for instance equals to 3, I want to write someting on column name "3". I tried to use your code like that: Patch(LeaveList; {ID: Value(CellID.Text)}; Switch(WeekCount.Text; "1"; {1: DayCount.Text}; "2"; {2: DayCount.Text})) But program doesn't accepts {1: DayCount.Text} part. It doesn't detect column name. Do you have any idea to fix it?
Sorry I am slow. I haven't tried that one before. Did you check to make sure 1 is the name of the colum in SHarePoint? This video would show you how to check ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-mwXnJUkTug0.html
Hey Shane, If I wanted to simply add to the multiple entry Person column rather than replacing that person how would I do that? My project requires a multiple entry person column that will function like a sign up list. I want the user to be able to click a button and sign themselves up but with this solution the person is replaced every time I run the patch. Any ideas on this? Thank you in advance!!
Shane, what about a multiline text input.. how to Patch to a multi-line Sharepoint column? I'm using the same formula as the regular TextInput to single-Text column formula and am getting an error on the formula. Is there a trick I'm missing?
Hi Shane, I created a survey on Power apps and I'm wondering if I can patch to my Sharepoint list that have columns labelled with a number of text and spacing. My column name where labeled Question1,Question2, and my patch was successful, however I'd like to change the name for each column to respective survey questions. Will it be possible to patch?
Hi, can you please upload a detailed video on how to upload attachment from canvas app with Media -> Add Picture control and Patch to sharepoint Attachments? Tried this and it created a blank line item in SP list - Patch(Mylist,Defaults(Mylist),{Attachments: UploadedImage1});
Hi, Shane! Great series. Can you help me to customize an app with SP list, form control, combobox control with itens in excel and insert the record on the same SP list? Thanks a lot!!
Hi Shane, I have a combobox and I am patching user field with selected (searched) person in the combobox and it works fine, But when I delete the person in combobox it does not allow blank entry and throws error that user profile not found. How do I patch person field with blank entry if combobox is blank ?
I once had to use a multi select user column and be able to add the current user in (it was an event attendance app where the user could click the "I'm Going" button to be included) Achieved with this which some might find useful. I wrote this a year ago so cant actually remember why I used the ForAll to read through the current users but there must have been a reason #Create collection from existing multi user column and add current user then patch back Clear(varUserList); ForAll ( glrTestGallery.Selected.UserColumn, Collect ( varUserList, { Claims : Claims, Department : "", DisplayName: DisplayName, Email : Email, JobTitle : "", Picture : "" } ) ); Collect ( varUserList, { Claims : "i:0#.f|membership|" & Lower(User().Email), Department : "", DisplayName: User().FullName, Email : User().Email, JobTitle : "", Picture : "" } ); Patch ( DemoList, glrTestGallery.Selected, { UserColumn : varUserList } )
@Shane How do I view what has been patched after submitting a form using a combobox? When i view a submitted item, the combo box does not populate the saved value (but it shows fine in the list view). Datasource is SQL and SP list column is single text.
I have a customised form for a sharepoint list which has several dropdown fileds (choice, person) as well as date fields. Once I have entered data into them or made aq choice it will not accept a blank entry later. So if I selected a particular person, but later decided to delete that selection in the form it will not update the field in the list. Any suggestions?
its posible conect two list of sharepoint how on database sql azure? for example : lookUP('[DataBase].[Customer]',ID = customerID, Customername). how is it done in sharepoint?
I don't know. I have never tried and I wouldn't be surprised if it is not possible. But I don't know. Please let me know if you do figure it out though. :)
Great video, however have a problem with populating the dropdown with the choices. I added a dropdown and in Items I entered choices(mydata, mychoicecolumn). The dropdown is completely empty.
It seems that it was a MS Bug as I tried my app again and it is now working. I did not even do any changes. I did notice that there was an update in between so that fixed the issue. Tks
A Person dropdown when the sharepoint column uses a sharepoint group for person lookup. I only want to see those in the sharepoint group. Not supported in powerapps as far as I have found. Workaround?
@@ShanesCows not what I meant but I learned how to do it. I used the other list to create a collection... And then replaced the text box with a combobox. And then added the collection as the items. And turned off multiselect and set the default selections to [Parent.Default]
Shane Young Thanks Shane for the quick response. I have another doubt, I have a people picker field with multiple items, how I can filter a gallery with the current user if the people picker has more tan 1 user selected?
Hi, this is a great video. I am trying to patch a DatePicker selection back to a SharePoint list, but it get an error. 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, do you have any suggestions?.
I have never done that one. Did you try this? CertificationRenewalDate: { '@odata.type':"#Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference", DateValue:DatePickerCertificationRenewalDate.SelectedDate}
Hi, I am, unable to bind the lookupfield value of one list to a field in another list based on the ID. If i try writing the {@odata.type,value}. It throws error. Pls help
@@ShanesCows I tried taking the values of SharePoint fields in collection using show columns. When my collection shows all values this lookup fields show as some record which internally shows Id and value. Couldn't figure out a way to get that value.
Has anyone ever found any MS documentation on the @odata type field? I've been using lookup columns for a year or more now and have been happily copy and pasting in that line each time but have never found anything actually explaining what it really is.
OK this is odd. I have a choice field like shown in your video. I am using a ListBox vs a ComboBox and my Patch is using Defaults to create a new record. If i use ListBox1.Selected is works. It i use ListBox1.SelectedItems i get "does not match expected type Record found type Table. What can i do? I even have tried a Forall with with Azure.Reference object and get the same error. Even tried Concat to make it text and no dice. I must be missing something here.
I'm having issues passing a Record to a ComboBox to set its default. "DefaultSelectedItems" If(IsBlankOrError(Scr_gallery.Selected.Employee_Person),{ /* '@odata.type':"#Microsoft.Azure.Connectors.SharePoint.SPListExpandedUser",& */ Claims:"i:0#.f|membership|" & Lower(Scr_gallery.Selected.Employee_Email), Department:"", DisplayName:"", Email:Lower(Scr_gallery.Selected.Employee_Email), JobTitle:"", Picture:""}, Scr_gallery.Selected.Employee_Person) The records looks correct but it doesn't show the person as default ?
Sometimes when I save my form , then come to edit the submitted item, and submit form, then ETAG mismatches error appeared. Do you know why this happens,and how to solve it?