Power Apps LookUp Function

PowerApps LookUp is used in almost every app. And it has a few tricks up its sleeve. So, in this video I try to cover the basics, the thing I didn't know, how it interacts with SharePoint and Dataverse choices, and more. Nothing profound but good skills to have.
Power Apps Consulting and training at www.PowerApps911.com

[@hiram4578]

always good to listen to you shane! thank you so much.

[@ShanesCows]

I appreciate that!

[@daniellemartineau4065]

Thank you so much, this is very helpful as I am just learning power apps! Spent a morning trying to figure out why my drop down lookup wasn't working needed .value. Much appreciated!

[@ShanesCows]

Glad it helped!

[@simonb7556]

A clear easy to understand tutorial. Thanks Shane.

[@ShanesCows]

Very welcome!

[@juansepowerplatform]

Thank you Sensei, always great to look back at the basics. Learned something new, did not know that Lookup can return multiple values on the same Lookup. Usually was doing two lookup functions to do that. 😣

[@ShanesCows]

You bet! Was news to me also. So cool.

[@charles-antoinedolbeau5215]

Thank you for the great video. The result inside the lookup is really cool, until now I was using the with function to avoid several lookup calls. Now I need to go update my apps ! 😅

[@ShanesCows]

I was too. Glad I found it and nice job using With!

[@smisra]

You are too good. Love your support!!

[@ShanesCows]

Thank you! Cheers!

[@rivernet62]

As an aside, I do appreciate having CC on your videos as I generally watch with sound off. Thanks!

[@ShanesCows]

Good to know. 😀

[@jacobparker6460]

You my friend, are a hero.

[@ShanesCows]

Happy to help. Have a great day. 🐶

[@aamirghanchi9487]

Thank you Shane! For the dataverse/entity, what should be the syntax to use a particular View of the entity. One of the views of my entity is sorted in a particular way for some columns so that when the Lookup function picks the first match, it is the one I need based on that ordering. But I am not able to get the Lookup formula to work and getting errors.

[@bloxy3135]

You, Sir! Are brilliant. Thank you.

[@ShanesCows]

You're very welcome!

[@syedhussain6393]

Hi Shane, should we patch with lookup because lookup always picks first row from query and we may want to update 2nd row not first row returned by look up function, for example in your case you put in lookup column with patch something like this "last name = young", so this query returns multiple rows and we just updated first row but we wanted to update 2nd row or any other row. so again should we patch with lookup though we can use ID column in look up function as well ? :)

[@louiscutmore]

Brilliant stuff! Thanks Shane 👍

[@louiscutmore]

If you had multiple users that wanted to update 1 field in SharePoint (goal: to collect comments), would you use Patch and Lookup in the same formula?

[@ShanesCows]

Sounds like where I would start for sure :)

[@louiscutmore]

@@ShanesCows awesome! Thanks 😊

[@harshadamagar4962]

Hi Shane, Thank you for the wonderful videos as always. Can you please make one video on difference between search, filter and lookup function in Powerapps. Thanks again! Love from India 😊

[@amandaheisel2272]

How would this code “look” if you’re using data verse but the field you want to look up values for is based on the text typed in another data verse column? The two tables have a relationship in that the text typed should match a column in the 2nd list but you’re wanting the value from another column in that same table that matches in value.

[@Neerajkumar-zs3vq]

Hi Shane, thanks for the great video. I have a question regarding using multiple conditions in the Lookup function. Some time it works fine or sometimes not, so is it correct to use multiple conditions in a lookup function. For example:- 1) LookUp(Employees, 'Last Name' = "Peter" Or 'Last Name' = "Taylor" Or 'Last Name' = "Taker", true) 2) LookUp(Employees,Or( 'Last Name' = "Peter" , 'Last Name' = "Taylor" , 'Last Name' = "Taker"), true) Thanks!!

[@jeanpierreragequit1726]

perfect !! it was perfect !

[@ShanesCows]

Thank you 🐶

[@skmd2005]

Hi Shane, Can we use lookup columns in dataverse inside the lookup function? If yes, how..can you post an example? I need to go 2 levels deep in dataverse tables with lookup columns to get a value

[@jasonholland83]

Hello Shane, This might be impossible without submitting the form but here is the question. If I have a lookup in SP to another list that populate in another list when selected one item. Is there anyway when a user choose the device type, which comes from the lookup in SP, the make and model connected to that device type auto pop in power apps without submitting the form. I HOPE explained that right.

[@JenMayB]

Thank you, as always, Shane! Unfortunately, the dot value did not work for my SharePoint column. I am getting the red squiggly under my =. The SharePoint column is a multi line and I’m trying to pull it into a text box in my power apps as a multi line just to show information about the value selected in another drop-down box. They are all in the same line (record) of the SharePoint list. 😢

[@gustavobaeza3890]

Great video!! Nice background!! I will try to use this to bring the records that are due within 30 days from today...

[@ShanesCows]

Remeber if you want multiple records (that is a table of data) and you would use Filter 😀

[@gustavobaeza3890]

@@ShanesCows yes filter is my friend and not so much my friends the delegation warning when i add the

[@jcolehatcher]

Can you use lookup to look in a dataverse to get the primary key column number so you can patch it to a different dataverse table into the lookup column you made where user info is going? This is all in a collection btw. Feel free to reach out to me as I realize this can be complex. But I'm trying to achieve it. I won't to patch a testname and that testname primary key number. Thanks!!

[@LORD_ORiGiNALz]

What if I have a drop down label that has a lookup function but its giving me back the first result but I want to get it to give me a different one how can i do that?

[@colby4390]

Is there a way in power apps to use the lookup function that is similar to Excel's VLOOKUP function?

[@SaurabhSharma-ov9ox]

Hi , Is it possible to do a lookup on lookup field. I have 2 SharePoint lists SP1 and SP2. I have created a lookup column in form2 for Field1 (using Field1 of form1) but now I want to lookup Field2 in form2 using Field1 of form2 (which is already a lookup field.) Is there any possibility to achieve this without using Lookup field?

[@kennyrobertson9027]

Hey, thank you for your video, I tried your lookup function but the = sign gives an error and don't process, can you assist. Thank you.

[@sundrapillay6668]

Thanks Shane excellent

[@ShanesCows]

Glad you liked it Sundra! 🐶

[@user-qv5jp7mk9t]

i have found that i can not combine two lookup functions (a text field and a number field) to return the data. Lookup does not find. When i change the text to a number and i am looking up two numbers then i find the data. Have you seen this?

[@agoetzIES]

I am trying to make a drop down box with a list of users in my Teams powerapps. I am making the expense app that you did for Microsoft. When entering receipts for others, I would like to choose their name in the dropdown. Can you point me in the right direction to make a data card that will lookup our user list?

[@latrobe15]

Another great one Shane. Point of clarification. You say the First is not delegable which is true, but I thought that as Filter is and as long as the the Filters operators are delegable, then First() will return the result required

[@ShanesCows]

Correct. I was trying to avoid the confusion that brings but you are correct. Do a delegable Filter and then First will return the first hit from that result.

[@zulnordin8041]

Do you know if I can reference Labels in the lookup function?

[@chatchayaprasongdee8757]

Great video i would like to adding more options to Choice Column In The PowerApp (User's Input). Do you have solutions for me ?

[@samirprusty8491]

Hi Shane, can we expect model driven or power portal related playlist series from you, anytime soon? it would be quite helpful for us to understand all the nuances in building those apps.

[@ShanesCows]

I doubt it. I don't use either in our customer projects.

[@mustang6514322]

Shane, Because there are weird things to deal with when using a choice type field within Salesforce what I have been doing is where a choice would be using a plain text field and creating a separate list to house my choices. So the values of a dropdown would be a separate list and the value selected would be entered into a text field in a different list. Now I am wondering if this is the best approach. The other reason I do this is so that I can add values to the Choice list and have a column for active which means I don't have to edit the App to add or remove choices as they need to be changed, to me this makes maintenance a bit easier. Wondering of you have any thoughts on this?

[@ShanesCows]

I like it Mike. We have done this before for customers.

[@TheAmazeer]

Gallery doesn't refresh if filled with filter but with lookup it does but lookup only provide the record this so frustrating

[@sdubbya1]

Hey Greg, new subscriber here... You videos are great! I followed your combobox video to use a choice box to auto fill in a form. My problem is the dropdowns wont come in with the .selected etc... how should I go about formatting the dropdowns to pull from the combobox...btw I am using Dataverse tables and canvas app

[@arasbane]

Another great video Mr. Shane. Have you ever made a video to show how to see versions of a SP list column in PowerApps when one has versioning turned on? I find written tutorials online but I can’t get them to work and no one from what I can tell has made a video.

[@ShanesCows]

I haven’t. I have never tried either so I can’t even offer you a hint. 😑

[@BhupendraSingh-nd4nb]

Thank you Shane, it is always good to watch you videos. I need your help to know that is there any function like "Index- Match" in power apps, or any similar way to lookup a value from dynamic columns?

[@ShanesCows]

Not that I know of

[@BhupendraSingh-nd4nb]

@@ShanesCows thanks Shane for taking out time and replying on my query👍

[@dineshvasamshetty8031]

Well explained. Thanks

[@ShanesCows]

You're welcome!

[@lanjarnugroho3318]

thanks shane. your video enlighing me

[@ShanesCows]

Glad to hear it!

[@ShanesCows]

Happy to help

[@fredbarnes196]

What is the syntax when you are looking up a value in a dataverse table and the column is a lookup column: Lookup(Audits, Project = "P1234").ID Where Project is a lookup column into the Projects table. This gives you the can't compare records, text error

[@rogumbo]

Thank you so much for this. What about if instead of asking the formula to lookup for "Young" I want it to lookup for a value that is inside a Text label? It does not seem to recognize the data/text that is "inside" the text Label. What is inside of the text label is a numeric value

[@ShanesCows]

It should. Label1.Text should work.

[@salahsaleh5505]

Hi, Thank you for the video. Learnt a lot. Honestly all the powerapps I learnt its from all your videos. Thank you For this lookup video in reduction parameter (third parameter), can you use more complex columns like Users/people, choice, Yes/No, look up columns. Thanks

[@ShanesCows]

Yes. Try columnName. and then see what it offers. LIke a choice column is ChoiceColumnName.Value = "Red"

[@yourface2616]

Thank you soooo much

[@ShanesCows]

Welcome 😊

[@hostrup001]

Greate video 👍👍

[@ShanesCows]

Thank you 👍

[@markconnor8667]

Hi Shane, how does it work with barcode values. Let's say find a value in column C when the barcode value is between column A and B

[@ShanesCows]

Sorry Mark I don't understand the question. :( LookUp will return the first record that matches your criteria. So as long at you write the condition to return the correct Record doesn't matter if you got it from a barcode. Hope that helps

[@playthekeys]

Hi Shane - how does it work if your lookup column in a child table is a reference to a parent table in dataverse? For example, I'm trying to use a gallery list to show all the maths results from a maths table. Each row in the table has a lookup value (column name called Fullname) which references a parent table of pupils. If use something like this it tells me that the types can't be compared (Guid, Record) LookUp(Pupils, Pupil=ThisItem.Fullname).forename. I am trying to just add a label into the gallery list showing all the math records with the pupil's name. Really appreciate the help as I'm going round in circles!

[@fredbarnes196]

This too is my question.

[@jameslupton3315]

Hi Shane, I have several fields on the screen I want to populate from the one lookup. Do I "Set" varRecord and use it or just do lookups on all 4 individual fields? can you show me the syntax of the set and how to use it on the second field?

[@ShanesCows]

Lookup VarRecord once. That will get the whole row. Then use varRecord.columnname in each control as you need.

[@conaxlearn8566]

Hi Shane, I cannot figure out why this code works: Set( varOrderRecord, If( varOrderDisplayMode = DisplayMode.View, First(Filter( 'Cash Register Order', 'Order number' = varOrderNo )), Defaults('Cash Register Order') ) ); But PowerApps does not like it if I use Lookup instead: Set( varOrderRecord, If( varOrderDisplayMode = DisplayMode.View, Lookup( 'Cash Register Order', 'Order number' = varOrderNo ), Defaults('Cash Register Order') ) ); If Lookup() returns one record, which it does, then it would be identical to First(Filter()). But PowerApps is throwing error message 'The function 'If' has some invalid arguments'.

[@MarcNFN57]

Hello Shane! I'm looking for some help. Trying to replicate some logic that I've build in excel using PowerApps. This is the formula in excel for example, =IF(COUNTIF(F2:L2,-1)>0,VLOOKUP(AVERAGE(F2:L2),Q:T,4,TRUE),VLOOKUP(AVERAGE(F2:L2),Q:S,3,TRUE)) This takes the scores of 7 questions and based on the overall score (0-100%) and then introduces a rule that downgrades the end result if any of the questions scored a detractor. The potential results are Silver, Gold or Platinum. But cannot be Platinum if a question response is a detractor. Any help with writing this in PowerApp would be greatly appreciated. P.S. You've taught me everything I know about Power Apps. Thank you so much for sharing your expertise with the world!

[@Drew7795]

Shane, hi! Is there a way to make a hash with texts? Concatenate two texts, for example and create a hash? In the documentation I can't find anything that can help me. Do you have any tips?

[@ShanesCows]

“Shane was “ & “here?” Is that what you are looking for?

[@geoffroydauby7482]

Hello Shane, my filtering and lookup got polluted with ID that I use to make the link between tables that get insidiously converted to "text" format. Any solution?

[@ShanesCows]

Value(TextVersionOfID) should get it back to a number.

[@Jonathan-ob3lx]

Hi Shane, what if i want to get the value of a choice which is being used in a drop down list? how can i use lookup function for that?

[@ShanesCows]

If you are using a Dropdown, then typically you reference what the user has selected by: Dropdown1.Selected.Value Though the .Value can vary based on what table you fed the Items property of the dropdown. So type Dropdown1.Selected. and Power Apps will tell you the available values.

[@tonya3308]

Is there any way to join two lists or tables based on a common id? For example, have a gallery present all the records from table B that match table A like a list of students for this semester and all the current classes for this semester where the student list has the class ID and a gallery would Sort by Columns by Class and show students, next class, show the students, etc.

[@ShanesCows]

I show exactly that in this video. kzbin.info/www/bejne/eqrPqaaOfrqsesU

[@davidgraf8012]

Do you have a video which covers how to keep two or more users from editing the same records in PowerApps? Do you have a video which covers how to assign a value to a datacard right before you submit a form? The value relies upon a lookup done against the SharePoint list. Lastly, do you have a video which covers how to update fields directly from a gallery? Thank you.

[@ShanesCows]

Hey David. First two question I don't. I have done it for one customer and it is complex 😯 I can't imagine ever trying to do it in a video. For the last one I do have that. It is video 3 of a multi-part series. kzbin.info/www/bejne/rpjdn55qjdGDedE That is the link to part 1. You need at least some context before jumping to 3 I think. kzbin.info/www/bejne/foa7kGN3dplgftU

[@manmitrahevar7589]

Amazing! I am facing one challenge in canvas app which is embedded in Dynamics CRM Model Driven app. It works like a Jem of course thanks to your videos. But when I open D365 mobile app, UI of embedded canvas app is very small. I want it to be at least readable. If I increase sizes and fonts, it becomes ugly in Browser. Kind of stuck😟any idea to get out of this?

[@ShanesCows]

I think you need to write If formulas to change based on app size? I have a couple of videos on responsive also

[@tomasgazsi6608]

Another amazing video! Please Shane, can you give me advice how to solve problem in form. I have SP list with vehicles which have serial number and owner of vehicle (columns). In another SP list i have information about deliveries. In deliveries I've saved information about serial number of vehicle (lookup column into vehicles). But in new form I want show in dropdown controler only cars of some owner. I think it is conditional dropdown controller. Can you give me some advice or your webinar or documentation. Thanks

[@ShanesCows]

In the dropdown Items property you would have a Filter. Filter(VehicleList, Owner = "Bob" Or Owner = "Chewy") for an example.

[@TheToni2123]

Thx shane

[@ShanesCows]

You are welcome 😎

[@nigelprice9474]

Hi Shane excellent as ever. i have a use case where i want to get a record and then get the value of 2 or more fields from it. what is the best way of doing that ? is it just Record.. ie Set(myvar,Record.) ? Thanks Nigel

[@ShanesCows]

Looks right to me Nigel

[@CHAMONIX552]

My form has a Car Make drop-down field. Can I use the formula below at Default property: to auto-populate current user's existing CAR Make as the selected value in the drop-down if it is available. LookUp(Choices([@'Registration'].Vehicle1CarMake), Value=LookUp(Registration,'EmailAddress'=User().Email).Vehicle1CarMake)

[@asimrehan73]

Super thanks

[@ShanesCows]

Welcome

[@melissaburwell3378]

Hi Shane! Is it possible to do the look up function on an edit form from a text input box without adding the gallery first? I'm continuing my search, but so far I'm coming up with "no" lol. Thanks for any input!

[@ShanesCows]

You can do a LookUp directly to the data source. LookUp(Datasource, Column = Value) that will return the whole record that matches your criteria.

[@melissaburwell3378]

OMG THAT WAS SO EASY!! Thank you so much!!!

[@kimgaskins6966]

Hi Shane. How can I capture a value from a lookup and store it in a context variable. The value I'm capturing is a date that's > today. I tried the following syntax... doesn't work. UpdateContext({lclMeetingDate:Lookup('Agenda','Meeting Date' > Today()}) Is this even possible?

[@ShanesCows]

That shoudl work. you just have a typo. UpdateContext({lclMeetingDate:Lookup('Agenda','Meeting Date' > Today())}) Try that you were missing a )

[@uciluis8013]

hi Shane can you help me? i try to lookup, before update or create new my item from collection(dataOffline) to SPlist (Stock). but the colomn is different. here's the code but it's doesn't work: ForAll(dataOffline,If(LookUp(dataOffline[@ScanResult_Change] = Stock[@Title]),Patch(Stock,{'Scan Result':ScanResult_Change},Patch(Stock,Defaults(Stock),{'Scan Result':ScanResult_Change})))) theres notif "Imcompatible types for comparison,type cant be compared,Text,table" Please help me with the code, thankyou :)

[@ShanesCows]

Lookup returns a record. Stock.Title is a single column table. Not sure what you are trying but you need to rethink your logic. 😬

[@uciluis8013]

@@ShanesCows i just want to create new record or update record for all data in collection to SPlist. With different colomn name. I think i need to lookup before patch, but i dont have any idea how the code should be. Can you give me an example code?? Thankyouu

[@heloisaheng3189]

Look up look up 🙄 Shane is cute ~

[@ShanesCows]

😊 Thanks 😊

[@sininv1]

Hi Shane, this is really useful. Thanks Can you show if I want the data to match from PowerApps to SPlist eg Person A register for A event on X date and using PowerApps to check IN on X date how to match the register data of Person A register to enable CheckIN to collect the date and time? Is it patch or lookup??? and what is the formula to write. Thanks.

[@ShanesCows]

See if this helps where I show how to find duplicates kzbin.info/www/bejne/d6GWkHiXbq1rmJI

[@Trysomieflexntjes]

Wut wuuut

[@ShanesCows]

🤪

[@steveprether9363]

Get to the point. Stop the fluff, it wastes people's time