SharePoint Lookup Columns for Power Apps and Power Automate: Create, Filter, Patch, and more

SharePoint Lookup Columns, Power Apps, and Power Automate: Create, Filter, Patch, Odata, and more
You cannot sort or sortbycolumns on a Lookup column
Additional columns don't work the same
Create lookup column in SharePoint
Create blank app
Add data source
Show it a gallery
Filter gallery
Saving to SharePoint
Choices
Patch
Flow
Write to the column
Query the column
Create item
Power Apps Consulting and training at www.PowerApps9...

[@cmacdhon]

The more I use PowerApps, the more I wish I had gone to veterinary school. Or joined the Navy. Or became a hockey player. Or worked on a cruise ship. Was a voiceover actor. A dentist. A pharmacist. Zoo keeper. A male model. Etc... Thanks for the awesome video, Shane. It's going to be a big help to me.

[@leaflacher]

Hello Shane, You mention in this video that is impossible to sort a Lookup columns. I thought you and the community would be interested to hear that I found a wa.! I used a AddColumns function in my gallery. With the syntax: AddColumns(SharepointList,"NewColumnsName",LookUpColumn.Value) You should then display those columns in the gallery instead of the lookup ones and use it for any other kind of function you may want to use (including the Sorting or Searching ones). Thank you very much for all your videos. It is the most helpful and funny PowerApps content you can found. This video and your AddColumn video was really helpful to solve this problem. Than you again!

[@ShanesCows]

Thanks for sharing! Good job. :)

[@finnpower92]

3 months into PowerApp, this video contains so much information that i couldn't process right now, will probably return in future again. Your videos are great.

[@ShanesCows]

Awesome. Keep learning and it will all click one day. 🐶

[@maxclyde1901]

Hi Shane. Just want to say YOU ARE A GOD. I had been stuck on trying to get my lookup columns via automate to no avail for over an hour. Your video saved my life. Keep up the great work :) You made at least one person very happy today

[@ShanesCows]

Thanks Max! Making one person’s day is worth it to me. 😁

[@juanbravo5597]

Thank you so much Shane! I been watching your vides/blogs since years ago (installing SP)! You rocks man!

[@ShanesCows]

Thank you Juan. I appreciate the support. 🤩 SharePoint days? I think that makes you and me old. 🤣

[@sandramcgechan5817]

Hey Shane, I need to see some videos on multiple choice lookup columns! Would love for you to do a video on this. I'm currently failing to build a flow that updates an item in a list and I think it relates to the fact that there are a few multi-choice lookups in the list (even though those aren't the columns I need to update, nor are they mandatory, but it seems to try to apply the item ID to those column ID fields and then gives a 'bad gateway' error message).

[@JennyJenn831]

Preach it! They're slowly killing me! Good luck, I hope you find your solution!

[@tauseefshk]

Videos on multiple choice lookup columns! is certainly need of an hour for me. Can someone help with this??

[@WilliamPalmer-h3z]

I would like to see that as well.

[@kashifhaque2085]

11:46 I ran into a similar issue today and was able to sort my result based on the 'Value'. You can also sort by 'Id' if you want. So, my formula looked like : Sort( filter(.....), Value, Ascending) and it worked!

[@ShanesCows]

Awesome 😎

[@laboun822]

Merci Shane, t'es vidéos sont super 👍

[@ShanesCows]

😊🐶

[@FernandoJSantesteban]

Thank you Shane for your great content!

[@ShanesCows]

Thanks

[@itsybitszy]

THX for the video. Flow think is really informative.

[@ShanesCows]

Happy to help 🤩

[@Mx81]

Hi Shane, thanks again for this nice overview. I also patch Lookup-columns to SharePoint. My experience: * If you patch something to the SharePoint List, you don't need the odata-information and you can leaf the Value as an empty string. It only need the Id. * Easiest way is of course using a ComboBox * Not so easy: Patch an blank value to Lookup columns to overwrite an existing value. Here you need to use Id = -1. Because if you don't have select something in your Combobox, the value want be overwritten in SharePoint. (The same for Person, Choice, and Date-Columns). So my PowerFX code is: Patch( 'Demo List', galDemoList.Selected, { Lookup: { Id: If(IsBlank(cmbLookup.Selected) || IsEmpty(cmbLookup.Selected), -1, cmbLookup.Selected.Id), Value: "" } } )

[@ShanesCows]

Interesting I need to try the -1 thing. Thanks. 😎

[@fasnywebinars4296]

Hi Shane, I have a lookup field I needed to populate via a create item action in PA, and it wasn't showing. After watching your video, I see if I show the related ID column by editing the lookup column and checking ID, that field should show up in the create item action, however it is not. What am I missing?

[@ibrahimhnehme]

Hi Shane, great video. Thanks for that. Are you planning to make a new video regarding power apps SharePoint lookups with multiple values? Thanks

[@evertdejongh2710]

Hi. The video about the way you did the 'lookup' functionality yourself. Which one is that?

[@ShanesCows]

I think it is this series. PowerApps Repeating Tables like InfoPath Part 1 - Enter the data kzbin.info/www/bejne/rpjdn55qjdGDedE

[@richardshaw8326]

Hi Shane, love your videos. Quick question, I noticed in the 3-minute mark, you mentioned that spaces in the lookup column are bad. What if the space pre-existed from SP 2013 and was migrated to SPO--how to I get around that, and what considerations do I have to keep in mind? :)

[@ShanesCows]

You have to use ' around the name. So here is my Lookup where First Name and Last name have spaces in the column names. LookUp(Employees, 'First Name' = "Chewy").'Last Name' It works fine, just harder to type and remember.

[@louiscutmore]

Hey square head 😂 Do you use the "Format Text" in the Fx dropdown - it's quite handy! Thanks for the video, VERY useful. Didn't know you had to use the ID in ODATA queries!!

[@ShanesCows]

Thanks 😀

[@jhhatcher1885]

Hi Shane - I just posted the following question on your "PowerApps SharePoint LookUp Column" video on KZbin: Thanks for showing me how to sort a converted text to dropdown button. But - can you set the sort order on a SharePoint lookup column in PowerApps? For example - I have a column in SharePoint that has: Column1 Name Column2 Order New Entry 1 In Progress 2 Reviewed 3 Approved 4 But the dropdown in Power Apps displays it in alphabetical order: Approved In Progress New Entry Reviewed Is there a way to have them displayed in column (process) order? If I understood your comments on this video correctly then there is no way to sort a SP lookup column. Correct?

[@otanzion9584]

Hi Shane, Thanks so much for these great videos. Please can you do a video on updating existing row in a list using user login as the unique identifier.

[@abeekeeshvaraantt9096]

Hey Shane, amazing video, I have a small doubt whether this is the same logic that would apply to connect Microsoft forms with SharePoint lists through Power Automate?

[@alfredotorres5961]

Great! I was missing the ".Value" attribute

[@ShanesCows]

Awesome. Happy to help. Have a great day. 🐶

[@dwinston91]

Great video. Do you have any videos that show how to Update a Lookup Column with multiple values in Power Automate Flow?

[@NoahThompson-q4n]

Hi Shane, I have a flow that is grabbing responses from a MS Form and creating a list items in sharepoint. One of the responses is a plate number that needs to be added to a lookup column in the share point list. The Create Item field in the flow only asks for the ID number of the lookup item. I am having trouble getting the ID dynamically. PLEASE HELLLLLLLLPPPPPPPPP!

[@JohnFulford-y3d]

this is driving me nuts...I have 2 lists (data sources) with a primary code common to both lists. Primary List consists of 5 columns (Primary code + 4) and Secondary List consists of 2 columns (Primary Code +1). How do I get that Secondary +1 to present in the same form Detail screen the Primary list info sits? Lookup appears to be the answer but I guess i am making more complicated then i need to. I'm stuck

[@SheIsObiquitous]

Great video Shane, any workaround to filtering a list by lookup column?

[@ShanesCows]

I got nothing other than what I showed in this video. Which works well enough I think.

[@imranozcanyay901]

Hi Shane, how did you make selectable Department Name and Department Name with link in Lookup column?

[@rrq177738]

Hi Shane great content!! Your videos are very informative! I have a question about lookup columns. How can I set my default value of a combo box to the user who is logged in for the filter? In other words can I default combo box to filter my gallery by the user who logs in?

[@marielcolonsantos3999]

Hi, Great videos i have learned soo much from them. I started experimenting and came to a dead end where i am clueless in which I'm trying to figure out if its possible to create a calculated column on SharePoint where choices appear based on previous selection. ex. selects a phone manufacturer and proceeds to select phone model based of the selected manufacturer. Any help would be greatly appreciated.

[@rekarp]

Have you dealt with creating PowerApps for SharePoint lists that have more than 12 lookup columns? Obviously PowerApps can't pull the columns in, so there needs to be a Power Automate step to pull in the data to a collection and then another Flow to send it back to SP, but I haven't been able to quite make it work.

[@ShanesCows]

I haven’t had to deal with that one before. 😎

[@rekarp]

Well, if you need a challenge...

[@rekarp]

This is a major barrier for my organization to move away from InfoPath and embrace Power Apps.

[@lymitutit3046]

Hello very interesting video .. but my porblem is similar to your power automate example with the lookup column but on on the other side of filtering. For example SP list ( A) has a lookup column which take the values form another list ( B) from the colimn Title ( string) . Now I'd like to filter the list B by the already chosen value at the lookup column at the list A. I'm struggling to find a reliabel solution,, but I can't . All sugestion on this subject would be very very helpful.

[@KaiW76]

Hey Shane, thanks for your great videos but I have one question, is it possible to the the values of an secondary lookup field in an edit form if I update the primary lookup?

[@viswanthnune2462]

Hi Shane, Thanks for the content and guidance. Definitely amazing stuff. I do have a question regarding how do I make a lookup in power app where it looks for row match and column match in Share-point and updates the value depending on user input. I tried Patch function etc. but no luck. Could you help me?

[@ShanesCows]

Patch(SharePointList, LookUp(SharePointList, Columnname = “Chewy”), {Columnname2 = “Dog”})

[@troylatterell]

Developing a prototype app for internal use tracking real estate and pad-sites. This comes in very handy for the 5-10 users. Does using SharePoint Lists and using PowerApps truly "SCALE" if this was an app we refactored into a public-facing app? Thousands of potential users? Can you give me some idea what challenges exist in licensing and performance? Thx

[@ShanesCows]

Troy Power Apps don't really work for public apps. You have to have a Power Apps account to access it.

[@troylatterell]

@@ShanesCows Indeed we're using it internally and prototyping for now and that is the answer I anticipated. thanks for confirming. Though still curious, is Performance at all a consideration for 10 simultaneous users for the environment - not talking hyper-sophisticated or complex logic, pretty simple list viewing and editing.

[@samuelyounis6314]

Hey Shane, is there a way that people can enter custom values into a look up column. - like if there is a department that is not on the list because its confidential or something can you make it so they can write a custom value?

[@ShanesCows]

I think so but I haven’t tried. You would need to add an entry to the lookup target list.

[@vikingord5030]

I have a dropdown in a SP customized form that is dependant on choices from another dropdown. It works like it should but I want it to be a required field and that doesn't work. The required star is present, but the required error message never apears and you can save the form without data. Any ideas?

[@matthewmaybery597]

How do you filter by a lookup column if the lookup column "Allows multiple values"? When this box is checked I get Data type errors.

[@myatix1]

Hi Shane, Great video as always! Do you have a workaround for getting Managed Metadata (Terms) into powerapps? There seems to be a limit of pulling 25 terms into a PowerApp. Not very useful for a large term set. Keep up the great work! :)

[@ShanesCows]

I don’t Alan. I haven’t worked with them much.

[@hurkaholic]

Thanks for this informative video! Would it make sense to add more columns in the doggie data of the same list (lookup doggie department column) and then change the "main" column to different columns. So you would be able to use more lookup columns in powerapps. Or would that just complicate things?

[@hurkaholic]

So basically adding DepartmentManager as an extra column in doggie data list.

[@ShanesCows]

I don't think it would work that way but I haven't tried. 😀 You should give it a go and report back. 🤩

[@shaunussy]

Hi Shane. Bit of a general question. I have a possible data type issue, creating a situation where a condition that should really evaluate true is instead yielding false. The value for Parent.Default is the text value 'on hold' - and yet when I compare "on hold" = Parent.Default, I get a false. To clarify... Parent.Default returns the text value 'on hold'. Parent.Default = "on hold" returns false. Why is this? The reason for this is I have a lookup with manually provided values ["on hold","raised", etc etc], and it's parent is correctly returning a value from a row, and I want to make sure my dropdown is selecting the appropriate value from the parent. But it's not :'(.... Thanks you super hero you.

[@shaunussy]

Just a quick update to this. Whilst I don't know of a way to check, I think the edit form was created before a primary key was given to the table it's bound to. None of the fields contents updates in the database. As to whether or not this fact would have any impact at all in the way a dropdown's selected value is selected, but maybe.

[@cartern4836]

This might be a long shot but worth the try... Overview: I have a skills matrix list that has a couple lookup columns for employees to associate several skills and certificates to themselves. The idea is that each employee has a single row in the Skill Matrix List but each row can be associated to several rows in other lists aka the lookup columns Skills and Certificates. How can I allow a person to add a new value for their lookup column on the 'Skills Matrix List'? I've created a form for collecting the data for a Skill and once they submit, I want that new skill to be associated to their row in the "skill matrix list". I'm trying the patch function below onsuccess but am receiving a data type error: The type of argument 'Skills' does not match the expected type 'Table'. Found type 'Record'. Patch('Skills Matrix', Defaults('Skills Matrix'), { Skills: { ID: Form1.LastSubmit.ID, Value: Form1.LastSubmit.Title, '@odatatype':"#Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference" } }) Thanks in advance

[@ShanesCows]

Check out this video. PowerApps SharePoint Complex Columns - Patch person, choice, and lookup fields kzbin.info/www/bejne/naTOYmd6YqiMiKc You have a multi select lookup so you need to patch it as a table. 😎 Video shows how.

[@cartern4836]

@@ShanesCows thanks for the reply! I found that an hour later or so! Thanks for the great content! Super informative and helpful!

[@darkspeed62]

Is there a way to do this without using fucking galleries?