How to Overcome Delegation with SUM function

[Anonymous]

Good day @RezaDorrani ,

I've seen your Youtube videos with regards to overcoming delegation - very useful thank you especially with the advice on setting indexes for SharePoint to allow lists larger than 5000 items !!

I couldn't however find a solution to my problem, perhaps you could help?

I am trying to find the sum of a sharepoint column in another list on my Powerapps form, and run into the delegation issue.

My formula below for reference:

Sum(Filter('01. CASH APPROVAL', CASHRECEIVEREMAIL = EMPEMAILLABEL.Text, REQUESTSTATUS = "05. PAID - PENDING PROOF" || REQUESTSTATUS = "07. SUBMISSION OVERDUE" || REQUESTSTATUS = "09. FLOAT OPEN" ), AMOUNTREQUESTED)

Thanks in advance

[v-xida-msft]

Hi @Anonymous ,

Could you please share more details about the Delegation issue within your formula?

Are the CASHRECEIVEREMAIL column, REQUESTSTATUS column all Single text type columns in your '01. CASH APPROVAL' List?

I have made a test on my side, the issue is confirmed on my side. Currenty, the Filter formula could not delegated within Sum function. As an alternative solution, please consider take a try to modify your formula as below:

Sum(
    ForAll(
           Filter('01. CASH APPROVAL', CASHRECEIVEREMAIL = EMPEMAILLABEL.Text, REQUESTSTATUS = "05. PAID - PENDING PROOF" || REQUESTSTATUS = "07. SUBMISSION OVERDUE" || REQUESTSTATUS = "09. FLOAT OPEN"), 
           AMOUNTREQUESTED
    ),
    Value
)

Please consider take a try with above solution, then check if the issue is solved.

More deails about ForAll function, please check the following article:

ForAll function

Best regards,

[Anonymous]

Hi @v-xida-msft 

Thank you so much for the detailed response

It does indeed remove the delegation "Warning" and seem to work 

However when I change the Value for "Data Row Limit for non-delegable queries" to 10, the result of the formula your proposed does not return the correct number anymore; I assume this issue will replicate when my items exceed 2,000 (which is the Value limit for non-delegable queries"

Any way we can overcome this?

Thanks again 😃 

[v-xida-msft]

Hi @Anonymous ,

Is the amount of your SP List reocrds more than 2000?

If the amount of your SP list is not more than 2000, you could ignore this Delegation warning issue. Currently, the ForAll function is not delegable function in PowerApps, so the ForAll function could only process at most 2000 records locally.

If the amount of your SP list is more than 2000, I afraid that there is no way to achieve your needs (due to the Delegation limit) in PowerApps currently.

Best regards,

[Anonymous]

Thanks @v-xida-msft 

I've seen other solutions as a workaround, such as collections - I have no knowledge about this therefore just asking.

Alternatively, Is there possibly a way this can be performed on MSFLOW? I could perhaps place a button that runs the flow to calculate and return the value to powerapps?

[Anonymous · Accepted solution]

Okay, so being new to PowerApps, I did not know all possible functions to use.

Basically, there is a simply way to overcome this, by using Collections

I entered this formula on my "OnStart" property of my app:

ClearCollect(MySPCollection, ShowColumns('01. CASH APPROVAL', "REQUESTSTATUS", "AMOUNT_x0020_REQUESTED", "CASHRECEIVEREMAIL"))

Here I am pulling 3 columns from my SharePoint List called "01. CASH APPROVAL', the columns are:

• REQUESTSTATUS
• AMOUNT_x0020_REQUESTED (the xx0020 is a space in SharePoint naming methodology"
• CASHRECEIVEREMAIL

This pulls the data from my SharePoint list to the current app for processing; however it still falls under the 2000 limit

A workaround for loading additional data can be found below:

Concurrent(
            ClearCollect(col1, ShowColumns( Filter('01. CASH APPROVAL', ID >= 1 && ID <= 2000), "AMOUNT_x0020_REQUESTED", "CASHRECEIVEREMAIL","REQUESTSTATUS")),
            ClearCollect(col2, ShowColumns( Filter('01. CASH APPROVAL', ID >= 2001 && ID <= 4000), "AMOUNT_x0020_REQUESTED", "CASHRECEIVEREMAIL","REQUESTSTATUS")),
            ClearCollect(col3, ShowColumns( Filter('01. CASH APPROVAL', ID >= 4001 && ID <= 6000), "AMOUNT_x0020_REQUESTED", "CASHRECEIVEREMAIL","REQUESTSTATUS")),
            ClearCollect(col4, ShowColumns( Filter('01. CASH APPROVAL', ID >= 6001 && ID <= 8000), "AMOUNT_x0020_REQUESTED", "CASHRECEIVEREMAIL","REQUESTSTATUS")),
            ClearCollect(col5, ShowColumns( Filter('01. CASH APPROVAL', ID >= 8001 && ID <= 10000), "AMOUNT_x0020_REQUESTED", "CASHRECEIVEREMAIL","REQUESTSTATUS")));
ClearCollect(MySPCollection, col1, col2, col3, col4, col5)

The formula is simply broken down as follows:

• Concurrent (this executes all the lines in this function
  • ClearCollect (This initiates your collection function
    • col1 (Name of the 1st collection table)
      • ShowColumns (this specifies which columns you want to import from SP)
        • Filter (this initiates your filter function)
          • '01. CASH APPROVAL', ID >= 1 && ID <= 2000 (name of the SP List, first 2000 items)
            • "REQUESTSTATUS", "AMOUNT_x0020_REQUESTED", "CASHRECEIVEREMAIL" (names of the columns you want to import"
  • Repeat everything till you reach 10K (in 2K increments)
• ClearCollect(MySPCollection, col1, col2, col3, col4, col5) (simply collect all the data into the main collection List "MySpCollection"

This basically imports all the data from my second SharePoint list into several collections, which are then collected into my main collections "MySPCollection" - out of which I could start using sum and filter directly with.

The number 10,000 is an example, you can call more or less totally up to you

Hope this helps anyone facing the same issue

[bfing]

@v-xida-msft,

Would your response be different if the data source was CDS?
My function reads:

Sum(Filter(ProjectPhases,ProjectTask.ProjectTask=locCurrentTaskRecord.ProjectTask),PhaseCompensation)

ProjectTask is a parent entity, ProjectPhase is a child entity with greater than 2000 records. 

Another thought, might the 'source' argument of the Sum function not be at issue?  The PhaseCompensation field in the 'expression' argument is a Currency data type.  My assumption is Currency was treated as a number, but perhaps that is not correct due to the *(Base) and Exchange Rate fields associated with PhaseCompensation.  Thougths?

Thanks in advance,

[bfing]

@v-xida-msft 
So I did the following quick test:

1. Created a new field in CDS for ProjectPhases called 'numberCompensation'  and made it a Decimal Number data type instead of Currency.
   
   
2. I updated the aforementioned Sum function to read: 
   Sum(Filter(ProjectPhases,ProjectTask.crec7_projecttaskid=locCurrentTaskRecord.crec7_projecttaskid),numberCompensation) 
   (note: instead of the previous 'PhaseCompensation' field that was a Currency data type)

The delegation warning went away. 

Am I mistaken, or does this deviate from the "PowerApps Data type Mappings" for "Number" on the following documentation?
https://docs.microsoft.com/en-us/connectors/commondataservice/#powerapps-data-type-mappings

Thanks again.

[bfing]

@v-xida-msft 

I ran the following test:

1. created a new field in CDS that was a Decimal Number data type
2. replaced the previously used PhaseCompensation (Currency data type) field in my Sum function with the newly created field (Decimal Number data type)

The delegation warning went away.  Does this result deviate from the PowerApps data type mappings listed in the following documentation where both Currency and Decimal Number are mapped to Number in Powerapps?
https://docs.microsoft.com/en-us/connectors/commondataservice/#powerapps-data-type-mappings

[RezaDorrani]

Hi @Anonymous ,

I came across a useful API in SharePoint to get aggregate function values like Sum,

Check it out:

https://youtu.be/qsIn0mytC_8

--------------------------------------------------------------------------------
If this post helps answer your question, please click on “Accept as Solution” to help other members find it more quickly. If you thought this post was helpful, please give it a Thumbs Up.

Thanks,
Reza Dorrani, MVP
YouTube
Twitter