Power Apps LookUp Function

[@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!

[@hiram4578]

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

[@ShanesCows]

I appreciate that!

[@rivernet62]

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

[@ShanesCows]

Good to know. 😀

[@nandivdiaries4845]

Thank you Shane for sharing these trick and tips. Could you share information on how to store the Lookup value and use that record all over flow in a variable/Collection so that we dont have to do multiple Lookups for each cloumn of same record. Thanks in advance.

[@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?

[@TheAmazeer]

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

[@michaelpineros1170]

Hi Shane, thanks a lot for the video! is very helpful!!... currently I´m working on an app that has a bit more complex lookup...... I want it to look up a match in a SP List A under specific record ( choices column) and then go to other list B and find 2 matches (one a value on a choice column) other ( a value I give that should be found in the second column, text type) if "true" to bring a third column value on List B ( people picker, multichoices) , but I´m struggling to make it work. is with text, people choice, etc. columns.. gets messy... appreciate any advise. Thanks!!

[@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!!

[@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

[@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.

[@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.

[@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

[@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.

[@GregFleck]

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?

[@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.

[@estefaniacantu8172]

i want to lookup in several lists, since i have diferent lists for each client, , and if i dont find in one list i want lookup to search in next list, how could i do that?

[@ShanesCows]

You could use an If statement or learn about Coalesce. Sorry, I don't have a video that shows this scenario but Coalesce is how I would tackle it. Coalesce(Lookup(list1), Lookup(List2), etc) it will return the first non blank value. Becareful, you could have a performance nightmare.

[@estefaniacantu8172]

@@ShanesCows thank you shane!!! ur the best! i hope to be able to sign up for your courses soon!! love ur videos!

[@estefaniacantu8172]

@@ShanesCows Saw yourr coalesce video from 3 years ago,. and it worked like a charm! Thank you so much!!

[@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.

[@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.

[@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

[@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.

[@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

[@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?

[@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.

[@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. 😢

[@colby4390]

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

[@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!!!

[@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?

[@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

[@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

[@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.

[@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

[@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.

[@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'.

[@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?

[@simonb7556]

A clear easy to understand tutorial. Thanks Shane.

[@ShanesCows]

Very welcome!

[@smisra]

You are too good. Love your support!!

[@ShanesCows]

Thank you! Cheers!

[@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 ?

[@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.

[@jacobparker6460]

You my friend, are a hero.

[@ShanesCows]

Happy to help. Have a great day. 🐶

[@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

[@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. 😑

[@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

[@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 😊

[@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 ? :)

[@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.

[@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

[@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.

[@bloxy7]

You, Sir! Are brilliant. Thank you.

[@ShanesCows]

You're very welcome!

[@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 )

[@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👍

[@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!!

[@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!

[@jeanpierreragequit1726]

perfect !! it was perfect !

[@ShanesCows]

Thank you 🐶

[@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)

[@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 😊

[@hostrup001]

Greate video 👍👍

[@ShanesCows]

Thank you 👍

[@lanjarnugroho3318]

thanks shane. your video enlighing me

[@ShanesCows]

Glad to hear it!

[@ShanesCows]

Happy to help

[@dineshvasamshetty8031]

Well explained. Thanks

[@ShanesCows]

You're welcome!

[@sundrapillay6668]

Thanks Shane excellent

[@ShanesCows]

Glad you liked it Sundra! 🐶

[@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"

[@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

[@TheToni2123]

Thx shane

[@ShanesCows]

You are welcome 😎

[@yourface2616]

Thank you soooo much

[@ShanesCows]

Welcome 😊

[@hhmaster789]

Look up look up 🙄 Shane is cute ~

[@ShanesCows]

😊 Thanks 😊

[@asimrehan73]

Super thanks

[@ShanesCows]

Welcome

[@Trysomieflexntjes]

Wut wuuut

[@ShanesCows]

🤪

[@steveprether9363]

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

[@clairevoyance26]

Hi Shane, I have a gallery with multiple labels that need information from another data source. Is there a way to store the LookUp function in a global/local variable? Instead of using the LookUp function for each label to get the information?

[@ShanesCows]

Set(varLabel, Lookup(Your logic here)) that would work. :) YOu might consider caching the whole data source you are looking up against in a collection. Too many lookups to the network can cause performacne issues. This video will help kzbin.info/www/bejne/imeunWuDfKh_a6c with perf issue