cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

Refinement of something that works ish..

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 galleryThe second galleryOverviewOverviewThe full query and the two galleriesThe full query and the two galleriesThe two galleriesThe two galleries 

 

 

 

 

 

 

 

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Anonymous
Not applicable

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. 

 

 

2.png1.png

 

 

View solution in original post

2 REPLIES 2
v-micsh-msft
Community Support
Community Support

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

 

Community Support Team _ Michael Shao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Not applicable

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. 

 

 

2.png1.png

 

 

View solution in original post

Helpful resources

Announcements
PA User Group

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

Power Query PA Forum 768x460.png

Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps

V3_PVA CAmpaign Carousel.png

Community Challenge - Giveaways!

Participate in the Power Virtual Agents Community Challenge

Carousel 2021 Release Wave 2 Plan 768x460.jpg

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

Top Solution Authors
Top Kudoed Authors
Users online (3,548)