Using ShowColumns has fixed the delegation error..

[johnjohn123]

I have 2 SharePoint lists:-

1) Project Master

2) Activities

The Activities list has a lookup field to the Project Master, as follow:-

now inside the Projects Gallery we want to show the:-

Total number of Project's completed Activities / Total number of Project's Activities .

in other words we need to show the percentage of the tasks completion under certain Project. to do so >> i added a label inside the Project's Gallery and i define the following formula :-

but we received a delegation warning.. so i changed the formula to use showColumn, as follow:-

and the delegation error has gone.. so i am totally confused .. on how did using Showcolumns fixed the delegation error?

[RandyHayes]

@johnjohn123 

It did not solve any problem.  It masked it.

Everything in PowerApps is subject to record limits.

Your ShowColumns function will only return the maximum record limit of records in the app.

The function returns a table.  You are then filtering on that table. So, Filter no longer has any delegation problems because it is not delegating anything...it is filtering on the resulting table of the ShowColumns - which will be a max of your record limit setting.  In other words, the ShowColumns becomes your prefilter, but it will just be the first x number of records in your datasource (x being your record limit)

Change your formula to the following:

With({_acts: Filter(Activities, ProjectTitle.Value = varProjectdetails.Title)},
    Text(
        RoundUp(
            (
                CountRows(Filter(_acts, Status.Value = "Completed")) /
                CountRows(_acts)
            ) * 100,
            2
        ),
        "0%"
    )
)

The above is similar in that it is prefiltering, but, it is doing a real pre-filter with a Filter that is delegable.

The formula inside of the With then will use that table as its source. 
And...you'll have no delegation errors and it will be accurate.

I hope this is helpful for you.

[johnjohn123]

@RandyHayesThanks for your reply.. but i am not sure if i 100% understood what you mean.. so how the ShowColumns work? and how the with works in my case? sorry i have no used With or ShowColumns before? any why only my first formula will raise a delegation error then?

Thanks

[WarrenBelz]

Hi @johnjohn123 ,

Delegation is not an error, it is a warning that the formula will only return data up to your Delegation limit. All you have done is "hidden" it (there are a number of other "hidden" Delegation limitations such as GroupBy, With and Collect). If you have not read my blog on this, it may be useful for you.

Now to your issue - assuming you have less than 2,000 records in the particular project (and you have your limit set to this), the below should work

With(
   {
      wList:
      Filter(
         Activities,
         ProjectTitle.Value=varProjectdetails.Title
      ),
      wComp:
      Filter(
         Activities,
         ProjectTitle.Value=varProjectdetails.Title &&
         Status.Value "Completed”
      )
   },	  
   Text(
      Roundup(
         CountRows(wComp)/CountRows(wList)*100,
         2
      ),
      "0%"
   )
)

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

[RandyHayes]

@johnjohn123 

It's like this...let's say your Record Limit in your App settings is set to 500

Let's say you have 4000 records in the list...

If you use your datasource directly in a formula i.e. 

Activities

This will result in a table with the first 500 records in it and all columns.

If you use the following formula:

ShowColumns(Activities, "ProjectTitle", "Status")

You will now have a result that is a Table with the first 500 records and just the two columns.

BUT...you still only have 500 records!!

If you then feed that table to your next function as the datasource (which is all you are doing in a chained formula...using one function to return a table to use for another function) then you ONLY have 500 records.

In your next function, you are trying to filter, but at this point you are now filtering on the above resulting table from ShowColumns...it's all in memory at this point, the ONLY function that went to get data from the session datasource was the ShowColumns function...and it returned 500 records.

From this point on, you are working with the results of that.  

Your filter is NOT going to go back to the datasource, because you told it your datasource was the ShowColumns, which has already been evaluated and is in memory.

So, there will be NO delegation warnings because you're no longer delegating.  Filters against in memory tables are not constrained by record limits and thus do not need delegation.

But remember, at this point in time you are filtering the resulting in memory table of records from the ShowColumns...AND, it will only have 500 records in it.

SO...if you have project records beyond 500 in your datasource, they will NOT be included in the ShowColumns result, and thus your formula will not be accurate.

In the formula I provided you, the goal is that you are doing a prefilter to get all the records that have the project title.  This IS a delegable filter!  So, as long as the results of that prefilter are less than your record limit, you have now got an in memory table in the _acts variable that will then be used for the rest of your logic...and, as mentioned above, there are never delegation issues against in memory tables as there is no need for delegation.

NOW...technically, based on what I just said above, your original formula should have been fine.  However, the formula editor changes the way that formula works and essentially turns it into a non-delegable criteria.  Separating this out by using a With statement resolves the confusion for the editor and will function the way you want.

[RandyHayes]

@WarrenBelz 

No need to double filter though.  As I showed in my formula originally, the second filter is a subset of the first, so only one trip needed to the datasource, the remaining narrowing filter can be done in memory.

[johnjohn123]

@WarrenBelz  when you say `assuming you have less than 2,000 records in the particular project ` .. you mean if i have less than 2000 Activities items under a specific project (linked to the project using the lookup field) then i will be fine? or you mean if i have less than 2000 inside the whole Activities list?

Thanks

[RandyHayes]

@johnjohn123 

2000 in the whole list.  You are limited to 2000 records as the maximum...always.

[johnjohn123]

@RandyHayes  yes this what i was doing on my original formula, to filter based on the lookup fields:-

but i got a delegation error? so why your formula will not return a delegation error, although both of them are filtering based on the lookup field? this is somehow confusing to me..to be honest..

Thanks

[RandyHayes]

@johnjohn123 

I mentioned what the difference was:

However, the formula editor changes the way that formula works and essentially turns it into a non-delegable criteria.  Separating this out by using a With statement resolves the confusion for the editor and will function the way you want.

The formula editor makes assumptions when you chain the formula like you had.  When you pull it into a prefilter With variable, it will not make that assumption and it will work properly.