PowerApps SharePoint LookUp Column 

Hello Shane. I am pretty new to powerapps and your videos are a real treasure. Keep up the excellent work even if you move on to different topics. On the moment when you delete the datacard to create a dropdown instead of it, i usually rename the datacard first to the name that makes sense, the formulas update behind, then i delete it and when i add the new drop down and rename it to what i set, voila. Errors fixed while keeping the origibal locations. Towards the end of the video you have a couple refresh issues - i solved those by addint the refresh() command for all the related databases on the forms OnSuccess function. This comes really handy when you edit an item and change the role. No refreshes needed anywhere (sometimes in the backfround if a user edited from option 1 to option 2, without refreshing the databases and immediately editted the same item he would get the old version displayed and so another save would revert the initial change). Let me know if my workarounds make sense and are good, as they might seem good to me, but as i said, new here :) still discovering.

I'm getting better Shane, thanks to you. Last week I showed to some colleagues from my company. I think they saw me like an expert hahahhaha but I'd show them your channel. You're the master!

This is excellent stuff - please don't change a single thing. Your video's are soooo helpful. Much appreciated.

Thanks a lot Shane, I've been looking for this solution for 2 days. The list below is just my notes for my studies. Customizar Formulário dentro do Sharepoint para PowerApps 2:11 Apagar o campo do Card do campo que deseja conectar 3:40 Inserir um dropdown no Card 4:12 Adicionar a lista da origem dos dados que deja ir buscar 5:04 Adicionar a nova fonte de dados ao dropdown 5:45 Ajustar os erros do Card 6:57 Ajustar o campo Update 7:52 Ajustar campo Default 8:35 Ordenar as informações do Dropdown 9:18 Adicionar outras informações da lista de origem 15:45 Apagar formulário personalizado 22:53

Your videos have helped me through so many workflows! I can't believe I am just now (in the last couple months) finding out these exist. Not everything in 2020 is pure abysmal, after all :)

You're like an angel Shane, this is what I was looking for, the explanation is so detailed and then BOOM! The function worked perfectly. Thanks a lot!

The way you explain the concepts are amazing and interesting....... Each and every 30 mins of your videos feels a great difference in my understanding in powerapps..... You are teaching the best things in world of powerapps for free of cost is what make you difference from rest of the world. Thanks mate have a great year God bless you Thank you so much for everything from one your fan...

Thank you Shane. That's another video from you that released me from hours of trouble.

Shane - just wanted to say THANK YOU! Your videos are the bomb and enable me to build out my convoluted PowerApps in my "spare" time. Thanks for sharing your knowledge!

You just solved like 3 issues in one video :) I only wish I had seen this video sooner!

I wish I had started my attempts with watching this video first. Would have saved me literally hours of irritation. I know going forward, I'll be checking your videos first before trying to do anything I haven't before. Thank you!!

Thanks Shane. Great video. Thanks for cutting out the back and forth time. This must take you time, but it save all of us time. Which is exactly what your videos do, for us. Thank you, you are truly making me look good. I’ve advised all of our internal PowerApps champions of your channel. Many many thanks.

Great Shane, would be impossible to make this without you.

Thank you, Shane! Excellent video!

Thanks again, Shane. I've been trying to realise a m:n-relation for about two weeks now. Lookup-Columns ruined my day. I think your way should work.

These videos are great Shane, you really seem to capture real world scenarios. Could you show one where a drop down list is controlled by another selected value in the form but the controlling column can have multiple values. For example Customers shop at multiple stores and this is recorded in a list called 'Customers' with columns 'Name' and 'Store', 'Store allows multiple entries. Another list called 'Store' has details of all the stores and has column 'Store Name'. On my form drop down when I select a Store from the 'Store' List a subsequent drop down is restricted just to those customers who had visited the store.

Thanks for the video, very informative, useful, and entertaining! Nice way to learn :)

Thanks a lot Shane! your videos are very helpful and this one in particular saved me a lot of time... kind regards from Argentina

Great videos, saved me hours - thanks. One gotcha for people where the lookup does not work by default from SharePoint. Even with data connections to all lists, the lookups do not work if there are similar names in the Lists. Eg If you have a table named Activity and a field somewhere named Activity SharePoint seems to get mixed up

I used to be able to set this up in under a minute, with InfoPath. This is insane.

I wish I found this video months ago 😅 I thought it was just me. Turns out it’s just SharePoint. Amazing that three years on we still can’t get these two to talk to each other! Specifically I had a form that refused to write the items in combo boxes to the SharePoint columns that were lookup columns. I had the combos set to have their default from another page, and even though the field had something in it, it wasn’t actually “selected” so after submitting that form the data wasn’t there.

Thank you Shane for the excellent video. This is exactly what I was looking for.

AMZING - I know you made this 4 years ago but it just blew my mind and is going to potentially solve a big issue I have been trying to work around. Question though if you don't mind - I have things set up so that a column in "SharePoint List A" is looking up values from "SharePoint List B". If someone updates the value in the looked-up column in SharePoint List B, is there a way to have that value automatically updated in SharePoint List A without opening the custom Form? Always appreciate your videos! Thanks!

You can have the dropdown default to Blank without adding anything to the SharePoint list. All you have to do is set the AllowEmptySelection property to true. Then set the default value to "".

this video was awesome. I learned a lot, but not quite enough yet... In my case i want to filter the items that come in through the second list. I wrote the formula =Filter('MAS Projects Index'.ProjNum,Not(IsBlank('MAS Projects Index'.'Record Summary Projects'))), where 'MAS Projects Index'.ProjNum is doing the same thing as your 'Departments Master'.Title, and 'MAS Projects Index'.'Record Summary Projects' is another field in that other list designed to be empty to fail this test. The filter is not working unfortunately.

Hi Shane. This video is spot on. I only have one question. In your example, what if there are duplicate departments (for some odd reason) that need to be filtered to only distinct entries? I tried using the Distinct function instead of sort, but that didn't work. Thanks for your guidance!

Thanks for an excellent tutorial. I have a droplist with some blank fields. Is it possible to ignore these blank fields?

Hi Shane, thanks again for a brilliant video. I have managed to accomplish a look up but have one issue. I am looking up to a sharepoint list but on my form field the default property is not pulling through the ThisItem.’Related Project’ it’s just showing as blank. I have put a label on the data card as well and set it’s text to ThisItem.’Related Project’ and it displays fine on the label, so I know it’s there. I just can’t see it in the default of the combo box field. Any thoughts are much appreciated. Thanks

excellent - I was wondering if you would do sharepoint power app forms.....I have been living in them for weeks! thanks! keep em comin!

hi Shane! ,thank you for your toturials it help a lot, here you expalin about use lookup in drop down and it work perfectly with me, but i try use another data source value as ( list ), but no result for that , what i want to do is take the value from another datasource and show it in the table ad i click start and end time calculate time button to add the data with the name i pull it from the another dat a source to the time calculating data source !

Hello Shane,the way you explain the concepts is very good👏👏👏...could you please give me some suggestion how to iterate the collection object which has date picker,drop-down,text values and need to be stored in SharePoint lookup column...Thank you in advance 😊

Hi Shane, thanks for this, another brilliant video. I don't think you mentioned it but am I right in thinking that the user needs read access to the Departments Master List on the other SharePoint site?

Thank you Shane, great video!. Would love to see one on using lookup fields in Dynamics 365 (or CDS), especially adding new records, searching for a contact where the contact is a lookup field on for example a custom entity. :-)

Hi, Shane! Yes, I am still struggling with this! I would appreciate your help very much! Again, thank you very much.

Your videos have proved extremely helpful!

Shane, I just found your videos and wanted to thank you. I didn't realize you could use powerapps for sharepoint lookup columns. What i'm trying to do is very close to what you did but with one change. I have a contract list and made the original dropdown like you did using contract name. I then want to populate another End Date column in Sharepoint from the Lookup Table so I can use calculated columns for employee contract roll off. I can do this with static date fields but can't figure out how to use the lookup so if the contract date is updated so is the sharepoint field. Any ideas are appreciated.

Thank you for sharing! You are great!

Great video again! I feel like in this video, you were subtly making a recommendation to use separate tables for all your fields/columns that will probably be drop-down control types. like, instead of selecting Choice column in SharePoint, just set it to single text column and then create your supposed values in a separate list/table and then just let Power Apps handle the lookup and relationships. Is that best practice or you just really wanted to show that it's possible as well? I'm thinking it might be best practice because passing values to single line text is always less complicated than passing values to lookup controls (say from a gallery row to a form in edit mode).

Love it, now I go back to my ForAll>Patch>Defaults>and try it without the craziness of Lookups.... hpe that was the idea of the video, if not, why bother :), and also, although the form changed as I wanted, in the QuickEdit view it still shows the DropDown values from the column setting (typical Enter Choice #1)... is there any way to render the same connected drop down here?

So helpful. I have a very large list of Locations that I put into a ComboBox with selectmlultiple set to false, I was able to fix the Update property to be ComboBox.Selected.Title. The ComboBox, works and posts back to the SharePoint list, New/View forms work as expected. My issue comes when a user Edits a row, the Location shows the correct city from the data that was added to the list in the New form, BUT the form forces the user to re-add the location. The required setting on the location is called even though it is pulling the data from the list.

Hi first off great video! Love these. Second, there are still 2 pieces of functionality that lookups give you that this video doesn't cover. I was wondering how you 'guide' your clients around them? 1) Lookup values in a SharePoint list view are clickable and can pull up information about the lookup value. 2) If a user goes into bulk edit mode, then the lookup values are presented as a drop down. Both of these functionalities won't exist based on the methods shown in this video. Love to pick your brain :)

Thank you very much Shane

Hi Shane. Awesome video. Thank you very much. One thing I would like to see though: how do you filter a lookup column? Say I have a list that has a Status column, and in a lookup field I only want to see the items that have a particular status. Maybe a idea for another video? Pretty please? :)

Hi Shane, Great video! I was referred here by Greg the Sharepoint Maven.. I was wonder would this development work for a one-many relations, in that I have an asset number primary key linked to many child assets that have the asset number as a secondary key, and then show these nested under the 'parent' asset.. I can probably do this using a simple view, it just means I have to enter heaps of data manually! Kind regards and thanks for the tutorial!

Thanks for an excellent tutorial. It just fit to my needs. However, your tutorial is based on a customized list form. How I can make the dropblank shown a blank option from a powerapps app. The FomeMode.New as used in your tutorial doesn't work in my app environment where I create an app from a sharepoint list. Any help please.

Is there a way to do multiselect lookups (from a related entity) in the model-driven apps mode? Single select lookups works just fine, but was wondering if you could share any guidance on that multiselect as well. Thank you.

Hi Shane, thanks for the video it was helpful. Is it possible to look up the value of another column using power apps, the value came from different column but it will only show in one column? Thank you very much. I hope you will notice me..

Hi Shane, thanks for the video...I just have a little problem because my column has multiple items with the same name...when i applied distinct, it gives an error. This related to the video at 6:30. Thanks in advance for the support.

Hi Shane! So we can just redo the 'Update' field instead of using the 'Patch' function?

All good stuff again. I was working on a Power app for taking our meeting notes and also assigning tasks or action items coming from that meeting, however I had to revert back to the standard SharePoint form because Power Apps right now does not support RTF functionality for editing. I can create a new record in SharePoint (as the end users want to be able to do bulleted lists, etc) but when you go back and edit, sadly the multiline text box is showing in raw HTML. I have added this as needing functionality in the Power apps forums and they do say they are working on it. I hope soon because this was a deal breaker for me using Power Apps. And now if I can get the javascript I found online to do some filtering and cascading on a dropdown (like you did with manager for department, but mine are a list of dates for a selected meeting title).

Thank you, Shane, another great tutorial :-)

another life saver, thank you so much!

Hi Shane, i learned a lot from this video, i was however wondering is it how to pull back more than one item in a LookUp from a ComboBox that pulls from more than one place

This video did not solve my problem, however it gave me ideas to come around it, and solve the issue. THX Shane - and have a great day ;-)

What if you wanted to add multiple items from lookup field?

Is there a way to use your LookUp('Departments Master', Title = Dropdown1.Selected.Title, Manager) function in conjunction with Concat(ListBox1.SelectedItems,Value&"; ") . I am using a list box where users can select multiple items but instead of the pretty text in the title column, I want the to concatenate the data from the second column. Example: my list box is connected to a list with two columns, Name and Email address. The names show in the list box but my label field needs to concat the associated email addresses. Any ideas would be greatly appreciated.

I am trying to connect to another list. I have a couple questions. My list im connecting to had two columns. I am trying to use that to create cascading columns in another list.I was able to create the first column with no problems. however, my second column is throwing errors. Its stating it doesnt recognize dropdown1 in the formula when i clearly see its there. Have you seen this issue before. Also, once i have it working. How do i connect the columns to the list im creating the powerapp in? Thanks

Wait you don't need that complex formula for default. Yu only need "-" because default triggers only for new item anyway. Right? Great videos btw!

Hi Shane, I've customised my SharePoint form and it works well. However how do I handle an item in the source list for the dropdown being renamed or deleted? This doesn't immediately change existing records but the display form no longer displays the content of the lookup field as it has either been deleted or renamed. We often have the situation where we want historical records to remain unchanged but the dropdown shouldn't allow obsolete options to be selected. Is it possible to limit the options in the dropdown to a view of the source list that doesn't contain obsolete options but still have the obsolete options in the source list so that they appear in the display form but can't be selected from the dropdown? Hope that is as clear as mud :)

Thanks for skipping the 30 seconds! :D

Hi Shane, Is there a way to enter a formula in the lookup column such as "Thisitem.ID"? I am trying to pull parent list information into child list by setting a lookup column and selecting parent ID in it. But I am getting an error : "The property on this control expects Record values. The rule produces Text Values which are incompatible" This is what I have: Default : ThisItem.Parent_ID) Update : Value(TextInput2.Text) If there is another better way to combine child and parent SP list can you please direct me to that. Thanks a lot

Dude. Love the video, but I cannot get the lookup to work at all. I have a list called Programs that has a list of programs. each program is one of 5 or so program types. In my project list I want the user to be able to select a program and then have the program type updated from the programs list. I have the program dropdown working fine just like you show in the video with the department. However, when I try to populate the program type from the programs list when the user selects the program. I created the label. and when I try to use the lookup to set it the lookup function errors, It simple will not work! The error is "Expected Text Value" ?WTH?

Hi, Shane! Thank you very much for all your videos, they have helped me a lot in understanding PowerApps and this video, in particular, helped me override one of lookup columns limitations which is to use lists from different sites. Another limitation that I would like to solve is the fact that lookup can only look at text, number and dates columns in other lists. Is there any way to lookup a lookup column? For instance, suppose I have three lists: a list of cars, a list of people and a list of parking tickets. The list of cars has two columns: car plate number and car owner, being car owner a lookup column to the people list. The list of tickets has three columns: ticket number, plate number and car owner. For each record, I would like to enter the ticket number manually, lookup up the plate number in the cars list and automatically fill the owner's name from the cars list. Due to the "double lookup limitation" I cannot do it. Is there a way around this, using PowerApps? (Right now I am using two separate lookups: I lookup the car's plate number in the cars list and the car owner in the people list, but this is not what I want: I need to make sure I keep the relation between the car plate number and the owner as stated in the car list). I apologize if this is confusing. Please, if you think there might be a solution I would appreciate your thoughts. Again, thank you very much for all your work and generosity.

Shane, thank you for your videos. As a small business owner, they are incredibly helpful. I’m trying to accomplish what you’re showing us how to do in this video however I’m using a combo box. This is proving to be a disaster. Do you have another video that specifically addresses Cabo boxes in this context?

What if the pseudo parent record (department in the video) is renamed? Given that the pseudo child table column is only a text field, wouldn't it remain if the parent record is updated? My only guess would be to somehow using the parent's Title to select a parent record from the child form, but only storing the parent ID with the child record, and then using the lookup function thereafter to update all other parent-sourced fields... is that possible?

Hi Shane. I know this video is old, I rewatched it to try help solve a problem I am having. I am using the lookup function in conjunction with AddColumns to essentially create a collection of data across multiple tables that can be used to export to CSV using a flow. The lookup function works as expected in a gallery, but it does not return values for the new column in the collection when using the ClearCollect function. The two functions are as below: Gallery: LookUp('Loan Requests',ID = ThisItem.Title,Number) Collection: ClearCollect(CollectionItemTrack,AddColumns(Filter('Loan Lines',ItemLoanStatus="Active"),"Number",LookUp('Loan Requests',ID = Title,Number))) Any advice as to why the lookup would work in one instance but seemingly not in the other. There are no errors other than delegation warnings coming up.

Thank you for cutting out all those 30 seconds!

Hey Shane quick question. I have a scenario similar to this. I am trying to return values from a text column from "List B" back into PowerApps Gallery text field based on the author. Both list have unique Id's. Thanks

Hi Shane, I"m connecting to a SharePoint list that has lookup columns, however when I select the fields in PowerApps the lookup columns are not available. Another work colleague has done the same thing and it worked fine. Could it be some settings in my list or PowerApps?

Thank you so much for this video. It helped me a lot.

How might one add a refresh button, if possible, for the Dropdown1 field after adding a new record? I've successfully created a button to create a new record using the "launch" function "on select". However, I have to exit and renter the SharePoint edit/new form in order to select a newly created record from the master list. Thank you so much for such a helpful tutorial.

Hi Shane - 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?

Hi Shane, could you create a video where you show how to make joins on sharepoint lists with i think "add column". My personal requirement is to count the rows in a relatet sharepoint list and if count ist 0, the master item shoudn't be displayed in the gallery... Thanks in advance! BR Mario

Question....My form will always be in Edit Mode when updating the dropdown, but wondering how would I use the "-" as the default. My dropdown is a pulled from a separate list and I save that value to my sharepoint list form. Would I do an If Statement? If the SP field is blank, display the "-" as the default?

hey, about the blank field, if it's a person/ group field, I can't have a value to be a dash. How do you work around the blank in that scenario ? Thanks great vid !

Thanks for the video Shane. At 4:11 after I removed the textbox and added a drop down, in View mode of the form the drop down appears as a textbox with a border. It should look like a drop down. How can I fix this? Thank you again.

Wow. Great Job. Pretty cool tricks. Thanks a lot.

Hi Shane , I learned a lot from all of your video's . Not enough unfortunately :-( One thing still puzzles me (maybe old style programmer issue) . How are relationships for sharepoint list organized> There is the lookup-field in Share point but this is hiding the mechanics for me (Giving an output column doens't say that the relationoship is based on that ) . Then you show a relation based on a Text-field so the relation is build in the app. ( Which is fine but based on a Text-Field ..... not sure I dare ) But what about the ID field : isn't that supposed to enforce the relations? Why I am asking : I try to build a filtered dropdown list for accountmanagers that can be selected in de Sales Pipe for a specific relation ) I cannot base that on name because that will never be unique.

HI Shane, your videos are fantastic. I have a question that need your help. I have a sharepoint list that i updated the department staff list. I have also created a Powerapps and connect this list to the form. I have this a Department dropdown list and from this list i filtered to the Team Staff Names. Now the difficult to me, how can i from this dropdown staff names and populate the staff email, Manager's name etc that all already in the Sharepoint list Data. Layman, i select IT, then select Mary then Mary's email address, her manager's name etc will autopopulate on the Text Input box. Please help.

Hello Shane, How would you handle a Cross Site Lookup Column that allowed a Fill in Choice that would then update the List being looked up? So if i have a list of customers that is being looked up from a orders list and find that the customer i am adding the order for does not exist in the list. I would like the to enter the Customer Name which would then also add it to the Customer List so it will be there on future orders.

Great video Shane. For the one list can you customise the SharePoint form, as in this video, and create a separate Power App for the list including the lookup column without causing issues?

Love your videos. Thanks for trimming out the wait time. If I could afford to give you a dollar for each minute saved, I would. One quick question - you mentioned that a future video would show how to include tabs at the top of a custom SharePoint form. Is that one available? Thanks!

Thank you for this video Shane, this helped me a lot. However, I currently run into a problem with my adapted version of the solution in this video and I am now at a point where I am desperate enough to ask you guys for ideas. I used a ComboBox instead of a Dropdown. Reason: I wanted to make the lookup choices searchable (~400 Articles). So far so good, my combobox works, it is searchable and it saves the values to my SP list. The problem I have now is that the articles (from the combobox) the user choses is not showing in Sharepoint when the users clicks on the list entry. Does anyone one has any suggestions?

Thank you so much! Perfect to our needs.

My lookup column is a person/group field. So when I did the above thing, no options are visible in the dropdown menu. What to do?

Hello Shane Thankyou for posting such an amazing contents. I am writing this comment as I am facing an issue while trying to create an custom powerApp using sharepoint list as database. I have a lookup column in my list which inherits data from some another list, when i imported that lookuo column in my power apps everything seems fine, but when I am trying to submit the form [SubmitForm(form3)] is the query is am running for submitted the form. Its start showing error as DepartmentName: Field 'DepartmentName' is required Location: Button .OnSelect Can you please guide me how to resolve it as i am new to powerApps and still learning through our videos

Hi Shane, I have a lookup column in Sharepoint List that indicates that one employee can have more than one role. However, the trouble that I am having is that in Power Apps, I do not know how to limit role column to just the one or two roles of the employee, instead, I keep getting the list of all of the roles in the company. Any ideas what to do?

You have probably answered this already, and I apologize for asking it again: In this example, it appears that the contents of the Title field are being used as the foreign key in a many-to-one relationship. If two records on the One side of the relationship (that is, in the Departments table) happened to have the same text in the Title field, I would expect the lookup to fail. Is there a way around this?

Can this be done in POWER AUTOMATE? I'm using MS Forms/SP List/Power Automate combination. When someone selects an office I want to look up the respective contact for that office and send them an email. Pls let me know if that's possible! Thanks 🙏

Really nice, many thanks Shane. Maybe Managed Metadata Field next and Multi-line Field ? :P

Hi Shane, thanks for your all your great videos. Am still very new to powerapps, I am trying to create customize a form / app with a lookup field from sharepoint to the customer list. I would like to be able for the user to filter / search the list as they type instead of having to scroll through the list, in the field it currently says Choices([@Quotes], Customer_x0020__x002f__x0020_Sit). Your help would be greatly appreciated, been googling this for days...

Hi Shane, thanks for the video, learnt a great deal about powerapps with this one. However, a sharepoint Lookup function links two data tables such that if Table A looks up something in Table B and that item value in Table B changes, then the Item in Table A should also change. This video doesnt seem to have that functionality unless im missing something! Its more like a choice field from a data source rather than a lookup. Do you know if its possible to link the data sources as per a proper lookup? Thanks in advance.

Hi Shane, I’m a big fan of your work! I am new in Powerapps. According to your example I have a PowerApp that " Pulls data from a deferent sharepoint list" Instad of a Dropdown I used a ComboBox with ShowColumns. One column "Stores data" in to another "DataCardValue" by default in the same Form. Saving the Form works fine, it’s saved to the Sharepoint List. My problem is that the data won't pull back to the "ComboBox" and "DataCardValue" After saving the form, the field returns blank or empty. Do you have a tip for me to solve this? Remko

You're doing gods work.

Hi Shane thank you so much for this, how do select multiple items in lookup column like the OOTB lookup column?

Can we use lookup function inside patch fiction? Can we use varable as columns in patch function? Can we use reference instead of putting column name in patch function? Please help if it can be done.

Thank you so much Shane, awesome video and very helpful!! I have one question regarding look up column. In SharePoint if we edit look up source column it will update wherever it is referenced. Please advise your thoughts on this implementing in power apps

Hi Shane. Great video !! .. Just one question. If I want to update SharePoint list using the function "patch" what is the right syntax's for that? Thanks in advance for your help

Shane. I would like to seach one value between 2 columns l. ex: Column A, number 1, column B number 10 e column C name " House". If I put number 3 on one label, It return " House". What`s struture I need to use?

Shane, thanks a lot for these videos, probably this one was uploaded long ago and there is something that is not working when I try to replicate that you do here. I can link both Lists into the same app, but when I consult the record saved, this shows me the value before, I think I can't get it how on yours shows what (in this case) the Title that you saved selecting from the other list into the main list (in theory this is with Lookup in Sharepoint but I am just stuck on this)