Group By - SharePoint Delegation

[ARAlmac]

Hey PowerApps,

I have a SP list of Sales data that has 16000 records.

There a projects that have multiple sales lines.

Here is my current screen (see image) - as you can see this project is showing all the lines,

I would like it so that there is one gallery item for that project then you click the arrow and it displays all the sales lines for that project in the next screen.

So I thought about using distinct although this did not allow me to work with other data such as displaying the Business unit etc.

I then used this formula for Group BY: 

Filter(
GroupBy(
'Sales Details List',
"Project",
"Project_x0020_Manager",
"Business_x0020_Unit",
"HomeFilters"
),
(DropdownBU.Selected.Result = "All Business Units" || Business_x0020_Unit = DropdownBU.Selected.Result) && (DropdownPM.Selected.Result = "All Project Managers" || Project_x0020_Manager = DropdownPM.Selected.Result) && (StartsWith(
Project,
SearchProject.Text
))
)

but it seems that this is hitting the delegation limit (without giving me an error).

Is there a way I can achieve what I am trying?

[RandyHayes]

@ARAlmac 

So first off - GroupBy is not Delegable.  So your results will be based on a maximum of the first 2000 records.  Several of the non-delegable functions (GroupBy, AddColumns, DropColumns, etc.) do not produce warnings in the Editor - although they should!

I would approach your issue with trying to specify as much as possible in your Filter for the sales details list to give you a result set that is less than 2000 records.

StartsWith is delegable, so that is fine and it leads me to the next question - will your Sales Details List data returned from that Filter be more than 2000 records?  If so, then you will not be able to return the results completely without the use of a collection process to gather (in stages) all of the records associated with that project.

I hope this is helpful for you.

[ARAlmac]

@RandyHayes 

Thanks for the reply!

There are multiple records for a project number.. which is what you see with the current filter I have in my screenshot.

I would like to have just the one project record there because on my next screen I have a gallery that shows all the records based on the project that was selected.

So I need to use Distinct to get the Unique project numbers but I know this returns a one-column table which works fine with StartsWith. Although I cant use other filters of Data such as the business unit and project manager. 

I'm kind of in a catch where GroupBy allows me to work with search and filters but doesnt return all the data because of delegation and then on the other hand using Distinct and not allowing the users to use dropdowns to filter the gallery by other data from the SP list.

Hope this makes things more clear?

[RandyHayes · Accepted solution]

@ARAlmac 

So then you need to pre-filter in your GroupBy and you should be fine (as long as the pre-filter is less than 2000 records)

In the formula below, you are pre-filtering the results that go into the GroupBy instead of how you were grouping first and then filtering.

GroupBy(
    Filter('Sales Details List',
        (DropdownBU.Selected.Result = "All Business Units" || Business_x0020_Unit = DropdownBU.Selected.Result) && 
        (DropdownPM.Selected.Result = "All Project Managers" || Project_x0020_Manager = DropdownPM.Selected.Result) && 
        (StartsWith(Project, SearchProject.Text)
    ),

    "Project",
    "Project_x0020_Manager",
    "Business_x0020_Unit",
    "HomeFilters"
)

I believe your GroupBy may be too "grouping".  You are grouping 3 different levels.  That may be what you want, but I just wanted to point that out.

[Drrickryp]

Hi @ARAlmac 

It is my guess that your list is not properly normalized and that makes it very difficult to work with as noted by @RandyHayes above. It appears to contain several one to many relationships in one table.  Likely it should have been 4 or 5 lists, project managers, business unit, projects, home filters, and sales details. You would then determine the relationships and build the app accordingly. In  the second part of this blog, I explain how to normalize a table of data.   https://powerusers.microsoft.com/t5/News-Announcements/Relational-database-design-and-PowerApps-Step... .  IMHO, your dilemma illustrates why flawed design leads to problems down the line.  If you decide to rebuild your app, I would first read @WarrenBelz 's blog post https://www.practicalpowerapps.com/data/constructing-your-first-power-app-before-you-start/  and his post on delegation in SharePoint. https://www.practicalpowerapps.com/delegation/power-apps-delegation-sharepoint/ 

[ARAlmac]

Thanks for the advice @Drrickryp @RandyHayes ,

Yeah this is working with Legacy data that is over 15 years old from a spreadsheet!

Quick question when I have you guys here - 

I have a column called actual month that ranges from "Jan-14" to "Aug-28" and when I filter in the excel I can filter by them all. Ive pulled the excel data into SP list but when I go to filter by actual month on SP list i can only filter from say "Jan-14" to "Jan 15".

This is reflecting in my powerapp too using a collection and also just trying to use Distinct.

Does this have to do with the 5000 SP view limit?

I thought using a distinct with a collection would pull all?

Thanks!

[Drrickryp]

@ARAlmac 

I suggest that you get rid of the need for Distinct() by saving your list back to Excel (or use the original Excel file that was used to generate your list).  Using Power Query, under "Select Columns", remove all of the columns except for the one that you want to use as a filter.  Then under "Remove Rows", select "Remove Duplicates" . Then sort and use Close and Load to save the result as a separate table.   Do the same for all of the columns that you will be using as filters.  Then import them back as separate lists in SharePoint.  Use those lists in dropdowns to filter your original list. 

[ARAlmac]

Thanks very much @Drrickryp!