Power Apps StartsWith Filter to avoid Delegation with SharePoint Galleries and Comboboxes

in today's show we're going to talk about the power app starts with filter so it starts with this handy little trick that lets us get around a lot of the delegation challenges that you're having with things like sharepoint so we're going to learn how to use the startswith operator we're also going to apply it to a combo box because it was like a mind-blowing thing that juan showed me earlier on how to do this so i'm gonna share it with you should be fun should be fast but first here's our intro hi my name is shane young with powerapps 911 those guys and today we're going to do the powerapp starts with filter and so the idea here is it starts with is a function or operator i don't know i get confused what you call them too but a piece that we can add to our filter formulas and the really interesting part about it is that it is delegable to our sharepoint data source it's available also dataverse and to sql server but not salesforce but anyway it solves some of the delegation challenges you have because with sharepoint one of the number one complaints is that we can't use the search function and so since we can't use the search function starts with gives us a a halfway push there so not something i use often enough i haven't put in any videos so i was like wait we should probably talk about this so let's switch over to my desktop and take a look okay so over here on the desktop um you can see i just opened up a blank white app i haven't done anything to it and so the first thing i want to do is i want to add one of our sharepoint lists remember this works with other data sources we're going to focus on sharepoint um which i think that's the one that's most interesting to a lot of you and so what we're going to do is in my sharepoint list i have one called giant it has 35 000 items and so we know that i use that same list when we talked about delegation and how search was indelible and we all went right search not being delegable makes us all angry so we're going to pull this in over here so let's go over here we'll say data and then we'll add data and then we'll search for sharepoint there it is i'll use one of my connections and then over here we're going to use the shane team site that's where i keep the giant list for some weird reason and so there's giant we'll add that and connect and now keep in mind if like when i'm talking about delegation if you have no idea what i mean then you should go watch the delegation video first because a you can't be a powerapps maker without understanding delegation and b you'll appreciate this one more once you see that one so with that said now that we've got our list here i'll go here and so we'll say we're going to insert a gallery so insert gallery we're going to tell it to use giant and then i'm just going to set it to good old title subtitle and body something like that right so just your normal sharepoint list and so we all know that this being in a gallery in theory we could scroll to the list at the bottom of 35 000 items right no big deal it would be happy and our delegation settings are just at their standard the out-of-the-box 500 row limit so what does that mean right that means that if we were to go up here and write a function like search giant um where you know dog or just do d d o is in the column animal like that we get the blue underline right we get the yellow triangle and if we were to scroll let's just scroll real quick to the bottom you would see that it only searches literally the first 500 records and so 495 is the last record in the first 500 that has it and if we scroll down right it won't go any further that's the delegation problem we have and it is search who does that right it turned blue because it's like hey search is not delegable to this data source so that makes us angry no big deal what we can do is use this filter function so i shouldn't type the word is a shipboard filter so we're gonna say filter no not that filter giant oh my goodness who is knows how to type filter giant and then here we're going to say starts with and so then it's like all right what column do you want to look in i want to look in the animal column and then we'll say it starts with do so just like that so now you'll notice here that we're going to get results back in just a moment and it is only the ones that have dog right so item title 2 5 10 etc but there are no delegation warnings and more importantly if we scroll way down here at the bottom there's our 495 list we just kept on going oh we loaded more we loaded more right and so we in theory could scroll through the you know i don't know 12 000 or so records that have uh the item title of dog or sorry the animal of dog you will also notice up here that starts with that it is not case sensitive so i did do so capital letters just so we could see that it didn't have to be case sensitive but there you go so with the start with function you can start with right now this is a little bit different than search remember search is a true full text search index starts with it has to be the first two letters right if we change this to be like starts with og right for original gangster or the second half word dog whatever you're going to see that we get no results because nothing starts with og so keep that in mind it does have to get it correct but when it starts with a d that should work just fine as well okay so kind of interesting so you can start to think about providing that full text search to your users but it's not a full text search it is a beginning with so how would you make this a little more dynamic well we could insert a text input up here at the top there you go we'll just get rid of the default textile there and now we can come in here and replace this portion with text input one dot text and so then now if we hit play and we clear this out once the text input is nothing so if you have it completely blank it shows you everything right two three four five but if we type in do now now it is starting to filter down the data and more importantly we can keep scrolling forever and ever and ever you know down here and we're able to work our way through so a way for you to get search results for your users now one thing to be cautioned from this came from anthony on my team new guy just started uh this week so hi anthony um one of the things he pointed out that he'd seen in the past with this is people accidentally had spaces at the end so remember that is now the string d o space nothing starts with d o space so that causes issues so what anthony suggested and i liked was you can go right here and you can say trim and the trim function takes the spaces off the beginning and end and so then now even though my user typed in do with a space right and do a bunch of spaces it is still going to work just fine so that's a nice little bonus tip okay so that is very helpful now the next thing you might be saying all right shane well that works for one column what if i wanted to search where either the animal column or the title column started with that that is fair all you do is come up here and say it starts with and you're just going to write your typical filter so or capital o starts with so you can't nest it inside starts with we have two starts with functions but then we'd say starts with title and it also just starts with this exact same thing right i'm too lazy to type it again so ctrl c ctrl v and then that's the end of that one more closed parenthesis and so then now our little search should be working so there's our item titles uh dogs but if we start to type in item title 25 200 250 right a really big number you can see that it gets the 250 the 200 2 500 it will get the 25 thousands right it's going to return a lot of search results um no delegation challenges here either right we're good shape we get all the results um and so and if we go right back to uh co for cal then now we're just seeing the cows so that's the other good thing it starts with is like it doesn't make a new set of rules you have to follow you can continue to have your ores or your ands right so we could be like all right i want to do starts with let's just do that and then we'll get rid of this portion for a second right we'll pull that back out make sure it works right we have to test some baby steps so i didn't break my formula there and then we'll just go right here and say and um color which is the last column in there equals red oh yes capitalize red like that and so then now we should only see the ones that start with co and their color is red so you know having a multi-part uh filter here starts with doesn't care either so very cool right you see where starts with can help you now what's interesting is it starts with um you know if we look at the delegation starts with works for dataverse text columns it works for sql text columns it works for sharepoint text columns and sharepoint complex columns so like you could do starts with and then person name and against a person field so kind of fun stuff right it would just be let's see so we could say let's try this let's see if we can write this real quick i didn't practice this one but we're going to be brave and try it on the fly so it starts with um let's say we'll say created by dot display name and then we want to see if it starts with shane because i created all of them and so if we get a bunch of results here in a second please work please work please work yay i didn't try that i meant to try before i started recording oops so but there you can see that that's how that is going to let you do it against some of your different complex columns so i haven't played with that one a lot but just a reminder that you use your shovel right your dot to dot notate in so it's just not against created by its created by dot display name starts with you know sha and so that would return it's returning all the results because i created everything um but that's just a quick way to prove that that worked okay so kind of interesting now ready for the next super interesting one i did not think of this this was all one i was asking for ideas so if we throw a combo box in here okay so one of the things we know about using a combo box against sharepoint lists right so if we just say use giant that you know with our combo box we get the yellow triangle right away because the search portion doesn't work right underneath the hood the combo box uses the search um uh what do you call it the search function to do its thing and that's not supporting its sharepoint so you always get this warning when you use sharepoint but what juan came up with which was genius was he's like hey let's go over here so set the field so we're showing let's just show item title and then we're gonna set the search against title okay so he does that then he comes up here and we're going to modify this formula and what we're going to do is we're going to say hey i want to filter giant where starts with and so what column we want the title column and what does it start with self dot search text so if you haven't used sir self before that's just a way to reference the control right myself so it's saying hey so instead of writing combobox1.searchtext we write self.search text and the beauty of this is if i take this formula and copy it to another combo box it would just work because it would always just be referencing itself but so we just close that up and then we close this up now now when we do this we're going to hopefully see if i did that right let's try it i'll hit play where's my mouse i've lost my mouse come here mouse there you go hit play so we'll go up here so we see all of them all you know what i need to do i need to make it searchable no big deal right what do we do is go over and we'll say drop down and say is searchable set you to true there you go so it's angry again because knows that sharepoint can't handle being a searchable so you still see the warning but interestingly enough if we start going in here and be like all right search for item title uh 250 right look at that we're seeing past 500 we're seeing the 2500s we're seeing all the way through the 25 thousands so really neat little trick that juan came up with and just to prove that it's really wand's trick that did it if we go back here and we just changed the items back to regular old giant so let's just put in giant and then oh let's try again put in giant giant so if we put in giant like that and then we make sure is searchable stayed true nope see they broke it set that back to true now if you um where are you at when you're what are you mad about okay i just said no that looks right if we go to items um is it just not like this let's remove this control x nope it's okay with that which field did i mess up who's surprised at the combo box as being rude face oh i hate this control so much if we delete all that out it's okay if we type in giant it's okay if we then say is searchable is true it's the exact thing we just did whatever i always leave the screw up so you're new to the channel i leave the screw ups i find people enjoy watching it even i get really angry aggravated power apps sometimes but so now if we search for item title 250 we literally only get item 250 because it's not delegable so that means it's only searching the first 250 items if we do 45 you know right so i get 45 450 it stops at 459. um if we try item title five then we only go to 50 or we get 501 and the reason for that is because 501 is the 500th record because i started at 2 for some bizarre reason don't overthink it i promise delegation is not working um here so juan's little formula was a very nice ad for us and i've lost that formula now what was it it was filter giant starts with title and then self dot search text there you go and then we have to turn searchable back on back to true and then if we hit the drop down we would see we go way down yay so there you go that is a little bit of fun with starts with there is also a uh companion function called ends with ends with is never delegable as far as i can tell so i have literally never used ends with so take that for what it's worth so hopefully you enjoyed this um remember that you know i come up with these things from the comments and things you leave so leave me ideas feedback below always love to hear any of that also remember we have a bunch of live training classes coming up in the first part of next year we try to run those once a quarter so go to training.powerapps911.com you can download this video this app if you sign up for the curated library or you can uh you know sign up for one of the actual training classes to get a little more in the the weeds and the details of all this so with that i'm going to say thanks and have a great day hey me again before you go click on the subscribe button right join the list of hundred thousand plus people that have subscribed already or if you need any help right check us out at powerapps91 we do big projects little projects we do training we do everything and we can help you or if you want to see more videos you probably do then just click on the playlist above cool thanks and have a great day

Channel: Shane Young

Views: 35,678

Keywords: Shane Young, powerapps911, PowerApps, Power Apps, powerapps startswith filter, power apps startswith filter, powerapps delegation workaround, how to remove delegation warning in powerapps, powerapps gallery filter delegation, powerapps combobox search not working, powerapps combobox search field, powerapps endswith, powerapps trim function, powerapps search delegation

Id: ldhy2MhqGiQ

Length: 16min 12sec (972 seconds)

Published: Wed Nov 24 2021