cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Post Prodigy
Post Prodigy

Getting rid of delegation error with sum using addcolumns

Hello! 

I am looking to get rid of the delegation error using the addcolumns operator. My Sharepoint list might go over 2000 rows some day, so I need to get rid of the delegation error.

I have a database named "OpleverApp", in here we will need 3 columns. 

"ProjectGegevens", which is the project information. 
"Verdieping", which is the Floor.
"PercentageKlaar", which is a number value that we're going to use.

So this is what needs to be summed;

We need to take the ProjectGevens, then look for the same floors and then sum up the PercentageKlaar

Example;

In the screenshot below, you can see the ProjectGegevens "20009-11_3010"

The verdieping = 2

It then says 15% done. If there is another ticket with the same ProjectGevens & same Verdieping, it needs to add up the PercentageKlaar & change the 15 to the new number for each ticket. I am using a gallery & a label to show this value.

TimoMigchielsen_0-1602924160279.png


Sorry for my bad explanation, hopefully I explained it clearly enough to get helped with 😃

Thank you in advance!

11 REPLIES 11
Highlighted
Resolver V
Resolver V

Re: Getting rid of delegation error with sum using addcolumns

Hi,

 

I have a really cool process for Delegation issues, but first lest talk about what "I" feel is a process for cloud systems

 

"I" Personally use SharePoint on all my systems because I like to say "Let the cloud do the heavy lifting...

 

Th Use Powerapps for the data entry and any calculations I doe in Computed fields in SharePoint this greatly enhances the speed for all things.

 

As for Delegation many posts here use Clearcollect against ID This is not a good practice as I have found after many Hours of SharePoint and PowerApps changes. I personally set a field up that is hiden in the forms on add that looks at the ID and if the number is less then 2000 it puts an "A" on a field, 2001-4000 "B" , etc..... then i use clear collect to create the Virtual datasets in memory and add them together. I do this on load of the program... Then since tables are used for retrieval of actionable information  I call the local Virtual data and on forms I update to the SharePoint dataset. I find this greatly Increases all the response times around the entire systems.

 

I will send you my contact information if you want you can contact me directly and I will be more then happy to setup a teams meeting to help you.

 

Jay

Highlighted
Post Prodigy
Post Prodigy

Re: Getting rid of delegation error with sum using addcolumns

I am currently not available for a call this week, sadly. If anyone is able to help me figure out the code for my needs in here, that would be really awesome! Otherwise I will just have to wait untill next week.

Current code of my gallery;

SortByColumns(
        If(
            gblIsAdmin;
            OpleverApp;
            Filter(
                OpleverApp;
                Label3.Text = MontageFirma
            )
        );
    "Datum";
    Descending
)
Highlighted
Super User III
Super User III

Re: Getting rid of delegation error with sum using addcolumns

@TimoMigchielsen 

Can you explain more about where you are seeing a delegation warning in your formula?

 

Essentially, if you can narrow your data by a delegable filter, then work on the resultant set of data (which is assumed you narrowed under the record limit), then you can work with it.

 

In your Formula, you have a non-filtered set of your data returning (OpleverApp).  If that grows over 2000, then you will have to devise an alternate solution.

My question would be, does the admin *need* to see all the records of the data source?  Are they really going to scroll through over 2000 rows?  OR, are you concerned with this from a summing perspective?  If so, then it is possible to perform that in a way that would avoid delegation.  But, it would be good to know which direction you are trying to go at this point.

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up. Solved your problem? - Click on Accept as Solution. Others seeking the same answers will be happy you did.
Highlighted
Post Prodigy
Post Prodigy

Re: Getting rid of delegation error with sum using addcolumns

With the code I am using rn, I am not getting a delegation error. But with the code I need to make, I would get one. (When using the sum operator)

 

The SharePoint will reach over 2000 rows some day, maybe in like 3-4 years. The rows in the database can not be deleted, as its usefull information that my company will need for many years after that. 

The admin is not going to scroll through 2000 rows but might be interesting to see a project from 3 years ago, he still needs to be able to find them. 

Highlighted
Super User III
Super User III

Re: Getting rid of delegation error with sum using addcolumns

@TimoMigchielsen 

Perfect!  Then from what you are saying, I would consider the project to be the narrowing filter. From the data returned from that narrowing, you can apply sums, counts, etc to the resultant set of data.

If you have a particular Formula that you are having problems with, then share that so I can take a look over it and offer some solutions.

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up. Solved your problem? - Click on Accept as Solution. Others seeking the same answers will be happy you did.
Highlighted
Post Prodigy
Post Prodigy

Re: Getting rid of delegation error with sum using addcolumns

Thank you @RandyHayes 

How can I accomplish this then? 

App screenshot; https://gyazo.com/ca345d6103d309c76d57c183ca01c466

Sum the numbers in PercentageKlaar on the following; projectnumber & verdieping is the same.

Example, let's say this is my database;

Project;                            Verdieping;         PercentageKlaar;
20009-11_3010               4                          15
20009-11_3010               4                          30
18037-31_3010               2                          65
18037-31_3010               2                          15
18037-31_3010               3                          20
18037-31_3010               3                          10

The project 20009-11_3010 has 2 rows, with the same verdieping. The PercentageKlaar should be summed up and show "30% klaar van de verdieping" on the app itself (watch the gyazo link above)

The project 18037-31_3010 has 4 rows, first & second row with the same verdieping, these should be summed up to 70%. Then there also is another row that has a different verdieping, this should just stay 20. 

The third and fourth row also have the same verdieping, should be summed up to 30 too. 

Hope this is a clear explanation. I'll need help with this as I've got no clue how to do this.

Highlighted
Super User III
Super User III

Re: Getting rid of delegation error with sum using addcolumns

@TimoMigchielsen 

I think so...but I am struggling with your screenshot and your data example.  

In the picture, you have two rows with the same verdieping and I see 15% klaar for each - you want that to sum to 30%...that makes sense. However, your sited database in your message shows 15 and 30?

Then you go on to explain the first and second row (I assume from the sited database and not the screenshot) that in your database shows 65 and 15 - yet you say you want the sum of that to be 70???  Wouldn't that be 80%

 

As I see it from your database example, the data and sums would be the following:

Project;                            Verdieping;         PercentageKlaar;
20009-11_3010               4                          45
18037-31_3010               2                          80
18037-31_3010               3                          30

Please clarify.

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up. Solved your problem? - Click on Accept as Solution. Others seeking the same answers will be happy you did.
Highlighted
Post Prodigy
Post Prodigy

Re: Getting rid of delegation error with sum using addcolumns

You're right. My mistake hahah @RandyHayes 

The screenshot of the app has nothing to do with the values I written down as an example. Then I'd also like to have a textsearchbox where I can search for each project

Keep in mind that the database can get more than 2000 rows in a few years

That's exactly how I want it to look 😃

Highlighted
Super User III
Super User III

Re: Getting rid of delegation error with sum using addcolumns

@TimoMigchielsen 

Got it! 

Okay, then I would consider your TextInput to be part of your narrowing filter also (currently you indicated you were narrowing on MontageFirma as well).

 

In which case, your formula would be similar to the following:

SortByColumns(
    AddColumns(
        GroupBy(
            Filter(OpleverApp; 
                MontageFirma = Label3.Text; Project = someControlFor Project.Text
            );
            "Project"; "Verdieping"; "records"
        );
        "klaar"; Sum(records.PercentageKlaar)
    );
    "Project";
    Descending
)

 

This would give you a sorted distinct list of projects and floors (verdieping) with a klaar column that contains the Sum of PercentageKlaar.

 

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up. Solved your problem? - Click on Accept as Solution. Others seeking the same answers will be happy you did.

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Find your favorite faces from the community presenting at the Power Platform Community Conference!

Watch Now

Experience what’s next for Power Apps

See the latest Power Apps innovations, updates, and demos from the Microsoft Business Applications Launch Event.

Power Platform ISV Studio

Power Platform ISV Studio

ISV Studio is designed to become the go-to Power Platform destination for ISV’s to monitor & manage published applications.

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors
Users online (5,754)