PowerApps Delegation & Gallery Filtering using multi select Choice and Lookup Columns (Part 4) 

Thanks for watching and glad it was helpful.

I use the same technique to filter data in gallery. My second step is to use the visible data in maps.But when i access this data it shows all the data because in real all the data is in gallery and we just true the visible property for the required rows. Can anyone help me how can I get the data which is visible in gallery?

@@RezaDorrani Doesn't work on large datasets, just try it on large list, you will see the problem. If you do a filter data and it is deep down in the gallery, you will see blank space on the top.

@@adamk7743 Performance is a concern with this approach for sure. There is no workaround for delegation with IN operator in SharePoint.

@@RezaDorrani Yes the UX is also bad with this, Does Dataverse also have this problem ? Too lazy to try. I need to propose a solution

Thanks for sharing this!

Glad to hear that

Great. Thanks for watching.

Thanks but it does have performance challenges with very large data sets.

Please note that this approach works ok for small data sets. For large (10k+), this would cause performance issues.

@@RezaDorrani Thank you for your response. My current requirement involves less than 10k rows. But just for my knowledge is there an alternate way other than using IN operator to filter galleries without a delegation warning?

@@tauseefshk No other option.

Glad it was useful. Thank you for watching.

Thank you for your kind words

Glad to hear that and thanks for watching

You're very welcome!

Thank you

Glad you like it!

Glad you like them!

Thanks. I have a better solution coming next week.

Thank you. Let me point to another latest video of mine which has some more cool tricks on multiple gallery filters - ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-5dSk5iOgT68.html

I use the same technique to filter data in gallery. My second step is to use the visible data in maps.But when i access this data it shows all the data because in real all the data is in gallery and we just true the visible property for the required rows. Can anyone help me how can I get the data which is visible in gallery?

Thank you

Flexible height galleries do not have wrap count. So grid styles are not possible.

Thank you David

I did that in one of my videos but do not remember where. May be - powerusers.microsoft.com/t5/Community-App-Samples/Apply-multiple-filters-to-Power-Apps-Galleries/td-p/774230

Wow! Thank you.

You are welcome!

Glad you liked it

Thanks. I have a delegation workarounds video planned for more complex use cases.

Glad I could help

I surely do mate :) Thanks for watching

Happy to help

Glad it was helpful!

I don’t think this technique will work with wrap count > 1. This requires flexible height gallery which does not have wrap count (as far as I know).

I would have to try out your scenario to provide any guidance. I would recommend you post your issue/query with screenshots on the forums at powerusers.microsoft.com

You can just use a combination of || and && functions with the different comboboxes.

It should.

Hi, You would need to add all the 3 combox box multi select check logic to show or hide the record. You can use a combination of && or || functions based on your use case. I show in the following video how to combine multiple filters using && or || - ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-pn50AKn3Q1Y.html

Thank You

I do not have a reference video on this scenario & would have to try it out to provide guidance. I would recommend posting your issue/query with screenshots on the forums at powerusers.microsoft.com

Hi Aimee, Search is not a delegable function with respect to SharePoint as a data source.

Thank you

Try breaking your problem into smaller chunks and test it. First set visisble property of gallery to true. The apply filter to gallery as Filter('All Apps', txtboxSearch.Text in 'Name') and check to see if this works (Note: this will give a delegation warning). Check CDS to see how the Name column stores data. Once above works then add the entire query to the gallery filter and test. Once it works just move the same filter criteria to the visisble property of the gallery

I would have to try it out with your scenario to provide any guidance. I would recommend you post your issue/query with screenshots on the forums at powerusers.microsoft.com

I do not have a video reference on this scenario and would have to try it out to provide guidance. I would recommend posting your issue/query with screenshots on the forums at powerusers.microsoft.com

Create your combinations using AND or OR depending upon your use case

Thanks. I have not experienced this issue hence not sure what could the cause be.

I've noticed the same thing! Not sure what's the issue here!

Yes, the non visible items will also be included. You would need to apply the non visibility logic to your filter condition for your items in forall. This will result in delegation issues though.

Yes, the key is that you need to ensure you are using a flexible height gallery.

@@RezaDorrani Thanks so much for your reply! I'm definitely using a blank flexible height gallery.(even deleted and re-added a couple of times when I re-watched your video). Seems like the labels' visible is working as expected (since only the items that match my criteria display data on the row), but the gallery line item is not (since those that don't match are displaying a blank row). Do I need to do something to set the height of the gallery item if it doesn't meet the criteria? If so, what property is that?

@@trishsparkman2436 Just flexible height should do the trick. I just built one from scratch to test and it worked. Ensure the visible property of all the controls in the gallery have the formula to show hide.

@@RezaDorrani Ah - you nailed it, Reza! Looks like I mistakenly left my separator out of the group so that was still showing for each record even though my data not visible. Thank you so much...this is truly genius!

@@trishsparkman2436 Thank You. Please remember this is a hack :)

Try powerusers.microsoft.com/t5/Building-Power-Apps/removing-last-character-in-string/td-p/395691

Thank you

Thanks! I have not come across this and hence not sure what is the cause for your issue. I recommend posting your issue/query with screenshots on the forums at powerusers.microsoft.com

I have done 2 videos on cascading dropdowns. Check those (not sure if they include lookup though).

Check ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-5dSk5iOgT68.html

Ensure you are setting the gallery height to 0 when no data exists. It is shown as part of this video.

@@RezaDorrani hello Reza, I'm sorry I guess I am missing something. Can you please let me know the exact time frame where you're mentioning about gallery height to 0

@@pkp044 This video was done years ago. I don't remember myself. If you follow all steps it should work. If issue persists, I will recommend posting issue with screenshots on the forums at powerusers.microsoft.com/

Search is not a delegable function with SharePoint and hence one cannot perform a contains string operation. You can apply the same trick as highlighted in this video to hide items by performing the contains query on each row.

Same as the gallery filter options. Just combine your filters with AND's and OR's

Large data export to excel ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-UZdngrwWsqA.html

I have a gallery filter video related to combobox where I show all items if empty. Here is the link - ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-5dSk5iOgT68.html

@@RezaDorrani Perfect!! Can't thank you enough!!

@@ellesavage308 hey what solution did you find for that pls help

Hi Gaurav For that all you need to do is apply the && condition in your creiteria when hiding the gallery item. In this video ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-pn50AKn3Q1Y.html I use the && condition to filter the gallery

Add checks to see if combo box is blank. ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-5dSk5iOgT68.html

I have done 4-5 videos on gallery filters. Check those on my channel.

I have not come across this issue and hence not sure what is the cause for your issue. I recommend posting your issue/query with screenshots on the forums at powerusers.microsoft.com

I have not come across this issue hence not sure what would be the reason for it. I will recommend posting your issue with screenshots on the forums at powerusers.microsoft.com

A gallery only loads data in batches of 100 (optimized loading) as long as the query is delegable

@@RezaDorrani Great, so it means the first 100 visible items? So if the first 100 is hidden because the filtering rules are not true, than it goes until the 100 visible where the filter is true?

@@robertpapancsev8891 That is correct. Performance may not be great if the visible items are 1 in a 100.

for sharepoint delegation.

Add checks to see if combo box is blank. ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-5dSk5iOgT68.html

Dataverse choice column is different. You cannot use numbers or strings to compare and filter. There will be a special Emum available for your column that you would need to choose the value from. I would recommend you post your issue/query with screenshots on the forums at powerusers.microsoft.com I will plan to do a video on this soon as many are facing this issue.

@@RezaDorrani , Appreciate it! Thanks a lot! I shall post it today!

The formula for hiding the gallery items expects the combox box to have values. You can add a check in there to see if combo box is empty then true else execute the condition

@@RezaDorrani Thanks a lot for your video, which really helped me, I have the same question as Pedro and I do not find how to make all the item appear when the combo box are empty

​@@lisecarivenc1003 You can check to see the number of selected items in combo box, If 0 then do not apply the hide logic. To check combobox selected items count use - CountRows(ComboBox1.SelectedItems)

@@RezaDorrani I have the same issue, the IF function you are suggesting, should go in the visible property along the concat function??

@@Fernanda-uh7iz It is tough to understand here on chat the exact nature of the request. I would recommend you post with screenshots on the forums at powerusers.microsoft.com/

Yes No type column are always a problem. Check this - ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-pn50AKn3Q1Y.html

ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-tQCBWMR7T64.html & ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-Kupz71dWYyY.html You can define what to export.

Add a Check to see if no options are selected (empty) then do not apply the hide logic for your column.

@@RezaDorrani How would you run the check? IfBlank in Visible or in Items?

@@chrisproctor2854 2 years ago :) I don’t remember. Items I guess but not sure.

@@RezaDorrani I figured it was worth a shot! Haha, thanks for the suggestion--I'll give it a shot!

@@chrisproctor2854 😆

I will have to look at your App in action to provide any guidance. I would recommend you post your issue/query with screenshots on the forums at powerusers.microsoft.com

They do work. You probably have not applied the hide show logic on every control in gallery.

I had to same issue. Once I set the Template Padding property of the gallery to 0 it worked. So happy with the solution. Thanks Reza!

Thank you!@@contactkpatel

Found the solution by using - not sure if this is the way to go but it works: If ("yes" in Concat( ForAll( combo_Characteristics_Adv.SelectedItems.Value, If( Value in ThisItem.column.Value, "yes", "no" ) ), Value ) And Not ("no" in Concat( ForAll( combo_Characteristics_Adv.SelectedItems.Value, If( Value in ThisItem.column.Value, "yes", "no" ) ), Value ) )

As long as it works :)

Glad it helped!

@@DennisHobmaier Hey Dennis, is your code here exactly what you put and it didn't throw any errors? Mine is saying only 1 argument is received, when we need 2 or more.

@@RezaDorrani Hi Reza, I tried Dennis's code and it throws an error saying only 1 argument is received, when we need 2 or more. How would we change the filter from "or" condition to "and" when selecting multiple items in the combo-box?

Thank You

@@RezaDorrani everyone in my team watches your videos.

@@fayefouladi2973 Thank You to everyone in your team. Tell your team Hi 👋 from my side

@@RezaDorrani will do.

Il be honest, the technique shown in this video is not great on performance. If you mix that with the grid (complex code), it will cause a lot of performance challenges.

@@RezaDorrani Thank you very much Reza, you are a crack (The Messi of PowerApss explaining for me). Then I assume that the best way is to implement a new specific Custom Page to implement this purpose.

@@ricardounzueta9570 Custom Page?

@@RezaDorrani yes my app is a mix of dataverse and Canvas approach so for this case the data is in a sharepoint lista and I'm implementing the functionality using custom Pagés inside the modelo driven app

@@ricardounzueta9570 Makes sense.

or hides some of them

the blank spaces were pad=5 accumulated now I need to check its performance it is too slow

Hi Daniel I will check this later in the week and let u know

Did you also set the height of the controls within the gallery based on a condition. Condition would be same as the visible property of the controls. Set height to 0 for cases that do not match.

@@RezaDorrani I have a similar issue with a text input box searching a text field. The blank rows still appear. Could I get an example of the formula you would set on the height property of the gallery?

A gallery always loads data in batches of 100. When we show and hide all the items in a flexible height gallery based on any condition, it will still try to show 100 items based on matching criteria and hence it keeps loading further data. This is not the most efficient solution but currently there is no other supported approach with SharePoint as a data source.

Even if it is not efficient, I was hoping that this would work for me. Unfortunately, it doesn't. Using this method, I got three items to show up in the gallery. I know there are more items that match this criteria. Were you able to display an item in the gallery that is not in the top 2000 in the SharePoint List, based on the Multi-Choice column?

@@skanedes4410 It does work. Something might not be right with your formula. Tough to know without looking at your App. I would recommend you post your issue with screenshots on the forums at powerusers.microsoft.com/

As long as “ac type” is a SharePoint choice column it should work. Not sure why it’s causing error / not working in your case. I would recommend posting your issue with screenshots on the forums at powerusers.microsoft.com

@@RezaDorrani Hey Reza, thanks for the quick response! In the meantime i managed to get this formular work with a column where only one selection is possible. Every other column where multiple selections are possible will return a "large data set" error message. I'll try to dig deeper into that issue.

@@herrzettpunkt Thats not an error message - that is a delegation warning. Check my video on combo box control and delegation playlist.

You need to ensure all the controls within the gallery have their visibility set to false based on the criteria defined.

If you use a combo box instead of a dropdown, it will give you search capabilities. It is not an issue. It is just how the controls are designed. I will have to look at your App in action with where you are trying to achieve this filtering and searching logic to provide any guidance. I would recommend you post your issue/query with screenshots on the forums at powerusers.microsoft.com

@@RezaDorrani Super thanks sir, you are great instructor.

@@RezaDorrani Hello sir, one more my question if user take image and after take image firstly show message to user save and reject

@@FCSSOFTWAREDEVELOPMENT Sorry, I did not understand your question. May be easier to understand with screenshots. Post your question with screenshots on the forums at powerusers.microsoft.com/

You can combine logic with && and || It gets more complex

@@RezaDorrani we need combine and logic in group visible property ?

@@sridharreddy1607 Yes, if using this video technique. I have shown newer ones as well.

@@RezaDorrani where are those newer ones can you please share the video reference

​@@RezaDorraniHey any help , can you please share that newer reference videos

I have a playlist on delegation. I don’t remember if I did show other approaches. You would need to check the videos in the playlist.

@@RezaDorrani Love the idea of this solution, but I'm also getting significant performance issues with this. Trying to use the Monitor to diagnose but it doesn't present all the info. It did show me 4000+ ForAll() processes (I think 1 per Dataverse table record). I think perhaps I need my .Items to retrieve only required columns and not entire table. Also wondering if the Flexible Height gallery takes time to 'size' each row individually (500 rows initially). I'm assessing this as an alternative to creating a secondary Text column and filtering that (e.g. Multi Choice column is 'Project'; when I create/modify a record via the Form I add data to the ProjectText column using Concat() function. Annoyingly, the Formula column type does not support Concat() so I have to do it via the Form)

@@montalbaconsulting5871 I would recommend avoiding this technique if you are facing issues with it. I did this years ago and have not revisited it since.

Every control within the gallery must have the formula set. I will recommend checking that and if it still does not work, then best to post this with screenshots on the forums at powerusers.microsoft.com

Try and first apply the query to a regular gallery (it will give delegation warning) but this is just to see if you are even getting any results back.

Check my videos on gallery filtering where I have used combo box. I have done 2-3 videos on it.

@@RezaDorrani i have checked it bit in my case the combo box has multiple choices and your video had only single choice in column so that was the issue how should i handle multiple choice column

I would need some code that says in display of that group u wrote : that display these filtered value else display all images but not sure how to code that

@@shreyasonawane8119 I have done videos with multiple choice as well.

so i need a formula in visible of group that before fetching me yes results I want both yes n no results as well

Gallery will only load data in batches of 2000. So querying gallery to get information in case of large data sets is not a good option. The technique showcased in this video is a hack that works for showcasing data in gallery based on filters. Your use case will not work with this technique. I would recommend you post on the forum to see if someone has come across this use case - powerusers.microsoft.com

@@RezaDorrani if the data set was > 2000 records, is there a way to filter the visible gallery items? I have a similar use case, but want to email the list of filtered items and I'm not sure if it's possible to filter the output to visible only.

@@dru6197 Yes, if gallery follows delegation.

This would not work for that scenario. I have not come across that scenario. I will recommend checking on the forums at powerusers.microsoft.com/ in case someone has done something similar.

I have done many videos on filtering that showcase that. Here is one of them ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-5dSk5iOgT68.html

@@RezaDorrani doing the same thing from the video. when I load the first time, the selection is empty, but there is 1 selected item anyway.

@@mondettabyakhtar2144 I will have to look at your App in action to provide guidance. I will recommend posting your issue with screenshots on the forums at powerusers.microsoft.com

@@RezaDorrani two things were found, 1: in condition used "true" instead of true, 2: Set ComboBox: Default = {View:""} :/

Agreed!

Check ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-5dSk5iOgT68.html

@@RezaDorrani thank you..

Hi reza, I tried, it works completely OK for one screen, the same is not working in other screen its populating the blank spaces too. I tired duplicating screen n using, but didn't work. What would be the problem

@@neemamotagi2404 You need to ensure gallery is flexible height and every control within gallery has visible set to the formula. I'm not sure about copy and paste. May be something got missed.

@@RezaDorrani thq. I'm done with it.

Thanks for the WARNING. Delegation is not just about filtering. It can be sorting or simply loading data in collections as well. Nothing can solve the delegation problem other than using delegable queries. Video simply does not apply any formula on the items property rather shows/hides items. So as you scroll down, it will load the data and hide the items based on the condition.

This video was done years ago. I dont remember running into blank problem on start. I recommend posting your issue with screenshots on the forums at powerusers.microsoft.com

@sabrinakraus-zy1dr could you solve that problem?

nevermind realised it was different i needed to use Title not value

looks like you get it fixed :)

Nice!