Power Apps Gallery Multiple Filter - Choice, Lookup and Yes/No column - SharePoint & delegation

hello everybody my name is razzle - Ronnie and this is part three of my delegation series video which talks about delegating power app queries to SharePoint as a data source so in part one of the video we spoke about delegation in general and how it works with SharePoint and what is delegation in part two of the video we spoke about specifically the date fields and how they are not delegate in part three we are going to talk about three different types of columns choice fields single select lookup fields can single select and yes/no columns in SharePoint which are the boolean versions now if you look at the delegation documentation for SharePoint complex is where the choice and look up columns fall into as you can see you can use them in filter operations you cannot use them in sort operations you just cannot and that's for the complex type type columns which are your choice and lookups the yes/no which is boolean you can use them in filter sort and so and so forth so getting back to our use case which is our students power app in this power app right now as you can see the gallery is being filtered based on the SharePoint list also I have created three filters on the top region class and active ok I'm going to talk through each one of these let's first start with the region column so if I go back to the SharePoint list the region column is a choice field so it's simple choice field which has four options north south east and west heading back to the power app I have a drop-down that I added and if you look at the items property for this drop-down it's coming from a collection now where is this collection being loaded on start of my app all I am doing right here is I am creating a collection called collection region and I'm using a function called choices connecting to the students list and I'm getting the choice options for the region drop-down that's all I'm doing so choices students of region and if I run this and if you look at collections what it's going to do it's going to get me all the values which is not south east and west so now if you head back to the drop down it's getting populated from that same collection my next step is to filter this gallery based on the selection in the regions drop-down remember this is a choice field it's a complex type column it's not a simple type column so how do i filter this so if I do filter so filter the students where the region column in normal terms this is how I would do it right region column is drop down region dot salon right this is this is one thing and I can do part as you can see it says invalid argument type and the reason is because because region is a complex type column so always an complex type columns you need to go to the column and then put a dot and then the intelligence will guide you through as you can see in this case region is a complex field value is where the property is being held in so region dot value is equal to drop-down region dot selected which is the selected value in my drop-down and also if you look at my collection for region there is a value property in there so what I need to do is select dot value now the types of matching so because it's complex I had to do region dot value and then drop down dot selected dot value okay and this should give me everything pertaining to the data set based on the filtered region so regions are not south so as you can see I have a lot of students in South of students and East region I have students in West Region and I have no students in the north region and just to confirm that if I go back to my students list and if I pick any student let's say I pick the student edit and let's change the region to North hit save and if I head back to my power app if I change my query once and come back just to confirm that that change to the value north let's look at this one again so if I edit it oh yeah the region is north so if I head back here and this is a delicate operation so just in case I'm gonna refresh my data source there you go as I play this as you can see north south east west right I'm getting all my results which is which is perfectly which is exactly what I wanted so that's how it works for choice columns now let's look at lookup columns I have a lookup column called class now in case of class if you again look at the backend data source which is my students list and this is a delegate function both choice as well as lookup so if I go to list settings and if I look at the class column it's a lookup and it's looking up to another list of type title so for this cases well it's exactly the same as the choice field so if I go back to my on start of my app Ken I'm creating a collection right here called class choices of the students list dot class column so it'll get me all the choices that are available those are my five classes it's added to a collection the drop-down items property is that same collection and I'm using if you notice it gives me an option to choose the value or ID whereas in the case of this I don't have an option why because if you look at the collection for region when I did choices dot region it was a choice column it just gives me the values in case of class because it's a lookup column it's looking up to another list it's giving me two things the actual value and the ID of the item in the list - Richard is looking up - so I have two things okay that's why in my drop-down for class I have to decide what is the value the user sees if I pick ID the user sees the IDS which is not too intuitive so I'm gonna pick value okay so we are done with this watch now in order for me to filter this based on class it's exactly the same concept so instead of using region dot value I'm going to do class dot value is drop-down class dot selected and again if I do dot I'll get both options your value and ID because I'm comparing values I'm gonna pick value and now this should work exactly the same way as the first one so now if I pick class two this should give me everything related to class two this should give me class three plus four there's nothing plus five days some results so as you can see I was able to filter both based on the class as well as the region right and they worked pretty well now let's take the third scenario which is the boolean type column which is yes/no now for representation purpose what I've done is in my gallery have a label here which gives me the value of the boolean column so as you can see in SharePoint it's a column of type yes/no but in power apps it shows us true or false right now you would think that if I was to filter this gallery based on the boolean type column which is a yes/no type field active equal to true would give me all the results I need but as you can see it does not it's empty right if I try equal to false again it doesn't return any results if I do not equal to false should give me all the ones that are true and it does so but it does so with a delegation error now why all this confusion right why is this not giving me based on the boolean values why is it throwing a delegation error despite boolean under filter being reported as a yes so if you go to my blog post my website is residue Ronnie comm one of my blogs is on power apps filter SharePoint yes/no column Bob and the thing is with relation to yes/no columns in SharePoint and let's just test this out if I go to the SharePoint list itself and let's say if I filter on this column and I say show me everything where everything is yes right now so show me everything where the filter is active if I apply this filter if you note on the top the value of this filter is one so the thing is SharePoint stores it as zeros and ones in binary format where zero means false and one means true that means if I go back to my power app and I say show me active equal to one this should work for me and show me all the values which it does it again gives me all the true ones and if I do active equal to zero it will show me all the ones that are false but all of this with delegation warning so I'm limited to the number of items power apps can hold which is the limit that you said in your app setting so this is a bug so how do I get around this how do I work around this the issue is why is this a delegation issue right now the reason is because if you notice in this case when I'm using zeros and ones it's saying incompatible type why because in SharePoint it's a boolean when in power apps I'm comparing it to numbers 0 & 1 whereas if I do true and false ideally it should give me the value but it does not so my recommendation would be until this gets resolved I would avoid using yes/no type columns and just create a choice column which has two values yes and no that would be that would be one way of avoiding the scenario so that's so that's on there so be very careful when using yes/no type columns very very careful I'm going to delete this filter right now and the next step what I wanted to do so let's just get all the students and that's just filter based on as a no filter right now just just showcase all the students now many a times you don't warrant and I know this is a very frequent request very frequent requirement I've seen this question a lot on the community forums is okay my drop-down has these values I want the user to pick a filter value from but what if I want a blank value in there or I want the select option under which only which basically defaults to everything because right now it's defaulting to north right if I was to apply a region filter to this which said filter students where region dot value is equal to drop-down region dot selected taught the value so if I do this by default it's gonna get filtered by the item that's here that's not but that's not what I want by default I wanted to show everything as well so I need another option how do I do this so if you notice in my on start of my app I have this clear collect collection of regions what I can do right here is I can do this so collect clear collect in the collection of regions of blank value and then collect all the remaining choices of regions if I do this and if I just run on start if I go to my region collection if you notice it added the blank value and then the choices that it got so now I've got a blank value in there and if I head back to my app I got region defaulted to blank but if you notice my filter there's a problem here why because it's trying to compare region dot values blanks up but I need to see all the results so how do I ensure that blank shows me all the results now one option is and I've seen this done very frequently as we do something like this if is blank right you can check to see if this is if something is blank or not so this selected value is blank right or if this is not blank right then you filter based on where the region dot value is this right so I can do something like this but this but this is editing out right because in this case I think I have a bracket missing and there you go right normally this is the scenario that I've seen a lot of folks do is they say I'm gonna format this just to show you so what we are doing is further students if if this is not blank that means the drop-down of regions has a value in it only then you do the filter otherwise you don't but you see it results in a delegation warning okay it results in a delegation so how do I get around this delegation warning and add the same logic right problem with delegation as you know is it will only get me certain set of Records but I want this to work with large datasets so how do I do this well the workaround for this is there's a pattern that you can use in this pattern I frequently use in almost all of my projects this is how I do it so what you can do is you can say where region dot value is drop-down regent or selected value dot value right so in this case it will work when I have the correct filter selected right click not give me not south east west all the four filters will work but the blank value would so how do I compensate for the blank value without introducing a delegation error so how do I do that is I introduce an or then add an if statement is blank okay I'm checking to see if the selected value is blank if this is blank then true else false I'll explain what this is okay if I run this now you notice when it's blank it's giving me everything right it's giving me nod Southwest everything if I pick South not will only give me South if I pick blank it's giving me blank there is no delegation warning as well so how is it getting me everything if it is blank so think about the blank scenario if it is blank it's gonna first query all the regions where the value is blank it's gonna get nothing there is an or operator you're I'm checking to see if this is blank it is blank so if it is blank true that means get the record just get the record okay otherwise false do not get the record that means if I pick North's in my drop-down it will first query and get the ones that are not or there's an order operator this will always evaluate two fourths so always go to always work for the first case so as you can see by introducing this pattern what happens now is this will work for any region that I pick even for for cases where it is blank it will give me all the regions now I want to do the same thing for class how do I do this very easy I'm going to go back exactly the same thing that I did for region I'm gonna do clear collect in this case for the collection of class I'm going to put a blank value okay and then I'm going to remove this clear collect and make this collect so now it will first collect a blank value and then get the collection of classes from the SharePoint list and now if we look at collections for class you see it adds the blank and then it has all the values right if I head back to this I need to add my filter what I'm going to do in this case is I need to add a filter which is an and operation so let's say I want to do an end operation between this and this how do I do this first thing I need to first create braces around these so this is my one function right this is the entire region and now what I'm going to do is I'm going to introduce an and operation and I'm going to do exactly the same pattern here so instead of region it's going to be class dot value right and this will be DRT class dot selector dot value class don't select it so exactly the same pattern and now if I go back and I play the app when both are empty it's going to give me everything if I pick a class like let's say I pick class two this will only give me class two you see this it only returns class 2 right if I now pick a region north it's gonna give me North and class two and there's nothing South nothing west there are values east there's nothing so it works in combination now including the blank if I go back to blank it ignores the class if I go back to region it ignores the region right so that's how this works and if you don't like blank what you could also do is instead of blank you could do something like select right it just replace this with select here and I replace the same thing with select here and if I run this if I head back to my brow screen you see they populated with select of course I'll have to change my function a little bit in this case what I will have to do is if this instead of now blank I need to check for select so I can do if this is equal to and again this is exactly has to match what I entered right so this is select and if this select there you go and I don't need this extra back thank you so instead of checking blank I'm checking select so it's the same logic it's now getting me everything if I pick a class it will only give me that class right now you see in this case class 3 is fake so for class 3 I have these options if I pick rest it will only give me West and class 3 if I pick South will give me South in class 3 so works and it works really well no delegation warning and all the results are your to see so use this pattern it's a very useful pattern for whenever you're introducing a blank value in there and that is it for delegation and filtering with columns of type choice lookups be aware of yes/no and if you ask me just just do not use yes/no and use choice columns the regular choice columns are just put two values yes and now thank you for watching the third part of my series do subscribe to my channel I will have a fourth part to this as well wherein I will show you how you can filter on multi select fields we look up or be a choice I will demo that one in the next video thank you so much for watching

Channel: Reza Dorrani

Views: 17,387

Rating: 5 out of 5

Keywords: powerapps gallery multiple filters, powerapps gallery filter multiple columns, powerapps gallery filter sharepoint list, powerapps filter sharepoint list choice field, powerapps filter sharepoint list column, powerapps filter sharepoint list lookup, powerapps filter sharepoint list lookup column, powerapps delegation sharepoint, powerapps delegation filter sharepoint, filter choice column powerapps, powerapps filter lookup column, filter yes no powerapps

Id: pn50AKn3Q1Y

Length: 19min 22sec (1162 seconds)

Published: Thu Sep 05 2019