In this short demo video, Jan Kokott, Senior SharePoint Developer, will show you step by step how to create SharePoint Online lookup fields to large lists with Power Apps.
Transcript
Hi and welcome to a short how-to video about SharePoint Online.
As most of you might know there are some limitations to SharePoint and items list libraries and views. For the SharePoint on-premise version, some of those limitations could be changed. One of those is the list view item limit. You cannot have more than 5,000 items in one view.
But it is not only the view that complains about hitting this threshold, there are some fields like for example lookup fields that experience some issues when hitting that limit. In SharePoint on-premise, you have the option to go into the Center administration and change those thresholds to higher numbers.
In SharePoint Online, however, you cannot do that. There is, however, an option to create a lookup column that consumes a list that extends this threshold. I want to show you how this can be done.
First off, I created a test page in a test tenant and edited the lookup list which has 5,000 entries, so it’s barely hitting that limit.
If we now take a look at the document library which is using that lookup field, we can see that it’s still working as it should and is not yet complaining about hitting that limit.
If we now go back to the lookup list and just add a new entry so we surpass that threshold we can now see, that the lookup field itself is complaining about the limit and does not really work anymore. So one way of getting around this issue is using Power Automate to modify the form that SharePoint uses for the new edits and view forms.
To make this work, we’re going to make heavy use of the delegation function inside of Power Apps, and I’m not going too deep into details about how delegation works and what’s delegable and what is not. I would encourage you to search for videos on YouTube or how-to on the Internet. A really famous one is from Shane Young on YouTube about Power Apps delegation and the 500 item limit. You should watch that to understand how delegation works and what kind of functions we can use and what we can’t use.
So after SharePoint created this default Power Apps form, we can directly see that it’s already complaining about hitting the limit. OK, so to make this work we need to do four simple steps. The first one is we need to add the lists where we’re doing the lookup against inside of Power Apps itself. So we’re just gonna use the default SharePoint Connector to connect to the page where the lookup list is in, and just choose the lookup list as a data connection. So now we can access the lookup list by ourselves and don’t have to depend on the default functionality that’s been set by Power Apps.
For the next steps, we are replacing the default functionality that loads the items for this field, which is that part that doesn’t work with our custom code. To be able to do that, we’re going to change the advanced panel and unlock the property so we can modify those. I will just replace that one with a piece of code. I’m just going to copy-paste in here. That is really easy. So what this thing is doing is it looks into the lookup lists and filters every value in here by the value that the user typed as search text in this little dropdown.
If we would use only that part, the field itself will always be empty until the user types something, so we’re just going to add another little nifty trick. I’m just going to wrap an “if” around the information inside, so we’re going to look for if the search text, is empty or not. If it is, we create another filter, which is exactly the same as the first one we entered just with the modification, slight modification, that this is hardcoded searching for something. So what you have to do is you have to find out what’s actually inside the title column in your lookup list. If it starts with a number or letter or special character, just enter a search here which will give back the first results before the user enters any filter information.
So after setting our custom delegable query for the second modification, we now have to go into the field for the third modification and modify the OnSelect information. What the user just selected has to be transmitted back to SharePoint and to be able to do that, we’re just going to create a collection inside the Power App and that collection is just called lookup field response. You can use any name you want. The only important thing is we have to store the ID and the value. So I’m going to look into the selected ID from the data card we just modified and the selected title and both will be stored in ID and value.
And for the fourth and last modification, we now have to tell Power Apps to use the collection we just created as a response value. We’re doing that by just switching over from the field to the data card itself and the data card has a property which is called Update and instead of just giving back the selected value, which doesn’t work, we have to use the collection we just created and as I’m not using a multi-choice lookup value, I’m just going to use the first from the entry in the collection we just created.
And with that, we are done. We just have to double-check some minor things because sometimes Power Apps likes to change the display field and the search field. Here we want to use the title. And here we also want to use the title and make sure that the field is still searchable. So we’re going to go down to more options because sometimes it happens that this is just a searchable false, which is wrong. We want to make it true.
And with this, you’re done. The only thing left to do is go into file save and after saving publish it so it is actually visible and usable in SharePoint and after doing so we’re just going to go back and there’s a little button “Back to SharePoint” on the top.
The only thing left to do now is to check if our modifications work. So we’re just going to select the document, click on Edit all, to launch the Power App, and you can now see our lookup field. If we click on it, it will do the default query, which we just hardcoded. It starts with a 1. If you start typing, however, it will start searching for the entries and it might not be as quick as a default field is, but it’s still working, and you can search for anything you want and just use an entry form here.
And as soon as you click save, information is saved and entered for the lookup value.
Thank you for watching this short demo video. I hope you found it insightful. If you have any questions or need help, do not hesitate to reach out to us.
Written By:
Jan Kokott
Jan Kokott is a Senior SharePoint Developer and a pioneer in the SharePoint Global Community with over 10 years of solid experience as SharePoint Developer and Trainer in the IT Consulting, Telecommunications, CPG and Insurance Industries.
More By This Author
