Getting rid of delegation error with sum using addcolumns

[Anonymous]

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.

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

Thank you in advance!

[JayMagness]

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

[Anonymous]

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
)

[RandyHayes]

@Anonymous 

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.

[Anonymous]

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. 

[RandyHayes]

@Anonymous 

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.

[Anonymous]

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.

[RandyHayes]

@Anonymous 

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.

[Anonymous]

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 😃

[RandyHayes]

@Anonymous 

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.