Refinement of something that works ish..

[Anonymous]

I have the below app and it queries a SharePoint list which at the moment have about 200 items. As you can see i have a gallery inside a gallery and do a query to get the items from the list. This query has become rather big and it does seem to work but i have random issues with things coming through that I'm trying to filter away.

Below is the query

SortByColumns(Filter(GroupBy(Filter('Case Management',spgSupportStage="IP"||spgSupportStage="Drafting an IP (notify EPO)"||spgCaseStatus.Value="Active",spgSupportStage="Identifying & clarifying support needs"||spgSupportStage="Preliminary Advice re: significant concerns (notify EPO)"),"spgSupportStage","SchoolGrouping"),TextSearchBox1_1.Text in spgSupportStage),"spgSupportStage",["IP","Drafting an IP (notify EPO)","Preliminary Advice re: significant concerns (notify EPO)"])

I query the list called Case Management. From this list i want only specific items which have

A status of "active".

And the items are grouped as such in this order. This is the grouping which is a column in the sharepoint list, and then the items are loaded into the groupings.  ["IP","Drafting an IP (notify EPO)","Preliminary Advice re: significant concerns (notify EPO)"])

At the moment the query randomly filters on 'Active' cases and i seem to get some cases that have a status of 'closed' to come through. 

The sorting of the status works ok and in the order i have set above. However i can only search on spgSupportStage and not anything else. And i get a delegation warning on the query.

Any help would be great as I'm now beyond my own capabilities and just struggle. 

Thank you

The second galleryOverviewThe full query and the two galleriesThe two galleries 

[v-micsh-msft]

Please take a try to switch the code as below:

SortByColumns(
    Search(
         Filter('Case Management',
               spgSupportStage in ["IP","Drafting an IP (notify EPO)","Identifying & clarifying support needs","Preliminary Advice re: significant concerns (notify EPO)"],
               spgCaseStatus.Value="Active"),
   TextSearchBox1_1.Text, spgSupportStage, XXX,XXX....),
"spgSupportStage",["IP","Drafting an IP (notify EPO)","Preliminary Advice re: significant concerns (notify EPO)"])

GroupBy function was removed, as it will only keep the spgSupportStage column value, and keep others into Table archives.

Modified the inner filter to make sure the cases are filted with Active status,

Switch the outer filter () with search() function, if you would like to search more fields, replace the XXX, with the corresponding field name.

Let me know if you have any further concern.

Regards,

Michael

[Anonymous · Accepted solution]

First of all thank you for helping. I used your formula and after a litte tweak it seems to work

SortByColumns(Search(Filter('Case Management',spgSupportStage in ["IP","Drafting an IP (notify EPO)","Identifying & clarifying support needs","Preliminary Advice re: significant concerns (notify EPO)"],spgCaseStatus.Value="Active"),TextSearchBox1_1.Text, "spgSupportStage"),"spgSupportStage",["IP","Drafting an IP (notify EPO)","Preliminary Advice re: significant concerns (notify EPO)"])

So if the group function was removed the layout breaks as its not grouping anymore? Do i need to come up with another way of grouping it all? My nested gallery of course is looking for the This.Item.SchoolGrouping column.Sorry if i don't get it.