CODEWITHSUNDEEP
sharepointpowerapps
kanderson
kanderson

Reputation: 177

Filtering Sharepoint Data Beyond Delegation Limit in Power Apps

I recently encountered an issue on a Power App developed by someone else using Sharepoint data as a source where new records stopped appearing due to the delegation limit.

The data is largely split across 3 nested tables.

Table A is the parent of table B, Table B is the parent of Table C.

The Code being used is structured as below (part of the SharePointIntegration OnEdit property):

ClearCollect(
    Items,
    AddColumns(
        ShowColumns(
            Filter('Table B','Table A'.Id = SharePointIntegration.SelectedListItemID),
            "TABLE_B_COLUMNS"
        ) As TBL_B,
        "Table_C",
        ShowColumns(
            Filter('Table C', 'Table B'.Id = TBL_B.ID),
            "TABLE_C_COLUMNS"
        )
    )
);

I have been scrounging around various youtube videos, documentation, articles, etc., but cannot seem to find a solution that works in this case.

The delegation notes in the documentation states the below, which seems to be part of the problem:

The SharePoint ID field for a table is a number field in Power Apps. However, SharePoint only supports the equal ('=') operation for delegation on an ID field.

I am looking for a workaround that ideally will not require changes to the structure of the Sharepoint source data.

In case it was unclear, for this specific application this only applies when a record is selected in Sharepoint to be edited or viewed. The individual records for one item will never be beyond the delegable limit, but the combination of all items can be which is why newer items are no longer appearing.

I have attempted the method shown on this source, which does not seem to work with Sharepoint sources: https://powerusers.microsoft.com/t5/Building-Power-Apps/500-item-limit-in-CDM-entity-search-filter-need-to-switch-to-asp/m-p/22980/highlight/true#M9872

I have also tried various methods like using 'StartsWith' instead of '=' etc. but nothing seems to be working.

This seems like a common problem so I am sure someone has come up with a solution but I have not been able to find anything straightforward or specific, so any ideas or advice would be greatly appreciated.

Upvotes: 0

Views: 2156

Answers (3)

Tyler Kolota
Tyler Kolota

Reputation: 51

You can try this delegation workaround that uses Power Automate to query SharePoint lists far beyond the delegation limits. No collections required. https://powerusers.microsoft.com/t5/Community-App-Samples/No-Delegation-Limit-SharePoint-List-Power-App/td-p/2330721

https://youtu.be/EH-YndEPIiI?si=3vzU6Q6Ob1JG2IlM

Upvotes: 1

SeaDude
SeaDude

Reputation: 4365

It takes some thought to get around delegation.

  1. Ensure your functions and data types are delegable.
  2. Ask the users (or the users Manager/Champion/etc.) what the goal of the app is.
    • There are very few times a user needs to scroll through 2000+ records in an app
    • Just by asking the right questions you can find an additional column to filter on which will bring down the number of records a magnitude.
      • Hints are: status, phase, etc.
  3. When all else fails and your users/manager/champion say "We need em' all", then use a Concurrent() function and grab all the records
    • Try something like:
Concurrent(
    Collect(col1,
    Filter(LIST, 
        And(
            ID_COPY >= 1,
            ID_COPY <= 2000
        )
    )),
    Collect(col2,
    Filter(LIST, 
        And(
            ID_COPY >= 2001,
            ID_COPY <= 4000
        )
    )),
    Collect(col3,
    Filter(LIST, 
        And(
            ID_COPY >= 4001,
            ID_COPY <= 6000
        )
    ))
);

ClearCollect(colAll, col1, col2, col3)

Caveats:

  • Performance will take a hit. Not so much from the Collect()'s, but from lugging colAll around your code and into your Galleries.
  • Unfortunately, the lovely ID column is not delegable. So you'll need to make an ID_COPY column and filter off that.
    • IIRC, it must be a number data type to work
  • You'll need to make sure that you hardcode the number of records into the app.
  • You also use a Power Automate Flow to get around some of the delegation issues, but it requires additional dev, lag and introduces a whole new set of tools

Upvotes: 0

Werner7
Werner7

Reputation: 333

It may be that your SelectedListItemID is a string and not an Int in your Power App. Use it like ID = Int(SharePointIntegration.SelectedListItemID).

The reverse is also true, if you are trying to avoid delegation issues searching a text field with a number use textField = Text(numberValue)

Upvotes: 0

Related Questions