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

 

 

2018-09-13_15-05-54.pngThe second gallery2018-09-13_15-05-12.pngOverview2018-09-13_14-55-17.pngThe full query and the two galleries2018-09-13_15-05-29.pngThe two galleries 

 

 

 

 

 

 

 

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Anonymous
Not applicable

Re: Refinement of something that works ish..

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
Community Support Team
Community Support Team

Re: Refinement of something that works ish..

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

Re: Refinement of something that works ish..

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
thirdimage

Power Automate Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

sixthImage

Power Platform World Tour

Find out where you can attend!

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

fifthimage

Microsoft Learn

Learn how to build the business apps that you need.

Top Kudoed Authors (Last 30 Days)
Users online (4,813)