cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ARAlmac
Helper II
Helper II

Group By - SharePoint Delegation

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?

ARAlmac_0-1611830453855.png

 

1 ACCEPTED SOLUTION

Accepted Solutions
RandyHayes
Super User
Super User

@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.

 

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
NOTE: My normal response times will be Mon to Fri from 1 PM to 10 PM UTC (and lots of other times too!)
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

Really want to show your appreciation? Buy Me A Cup Of Coffee!

View solution in original post

7 REPLIES 7
RandyHayes
Super User
Super User

@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.

 

 

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
NOTE: My normal response times will be Mon to Fri from 1 PM to 10 PM UTC (and lots of other times too!)
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

Really want to show your appreciation? Buy Me A Cup Of Coffee!

@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
Super User
Super User

@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.

 

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
NOTE: My normal response times will be Mon to Fri from 1 PM to 10 PM UTC (and lots of other times too!)
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

Really want to show your appreciation? Buy Me A Cup Of Coffee!
Drrickryp
Super User
Super User

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/ 

 

Pisa tower.jpg

 

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
Super User
Super User

@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. 

_1.png

Thanks very much @Drrickryp!

Helpful resources

Announcements
PA Virtual Workshop Carousel 768x460.png

Register for a Free Workshop

This training provides practical hands-on experience in creating Power Apps solutions in a full-day of instructor-led App creation workshop.

Microsoft Build 768x460.png

Microsoft Build is May 24-26. Have you registered yet?

Come together to explore latest innovations in code and application development—and gain insights from experts from around the world.

May UG Leader Call Carousel 768x460.png

What difference can a User Group make for you?

At the monthly call, connect with other leaders and find out how community makes your experience even better.

Top Solution Authors
Top Kudoed Authors
Users online (2,420)