2

I'm getting this delegation warning and that why I'm not able to fetch records more than 500 but I want to fetch the records more than 2000. enter image description here

I want to get more than 2000 records in my gallery but getting this delegation warning and unable to fetch it more than 500 items in gallery.

 SortByColumns(
    Filter(
        [@'Don''t Walk By'],
        StartsWith(
            'Don''t Walk By Type'.Value,
            TextSearchBox1.Text
        ) || StartsWith(
            Category.Value,
            TextSearchBox1.Text
        ) || StartsWith(
            Site.Value,
            TextSearchBox1.Text
        )
    ),
    "Created",
    If(
        SortDescending1,
        Ascending,
        Descending
    )
)

Don't Walk By Type : Choice Category : Lookup Site: Lookup

what changes are required here to get the items in my gallery

2 Answers 2

0

Assuming you have modified the default 500 limit for Data row limit for non-delegable queries, here is an article introducing to break your data in variables by using the ClearCollect function: Best practices for working with data in Microsoft PowerApps.

For example:

ClearCollect(Collection,
    SortByColumns(
        ...
    )
)

And one more reference that might be helpful: Power Apps: Advanced Filtering and Delegation Warnings.

0

In order to avoid the delegation limitation you want to avoid filtering SharePoint lists directly. Instead, you can simply query a variable that contains your entire list in memory.

The only tricky part here is to get the entire SharePoint list first. In order to do it, you want to get the list items in chunks of 200-or-so items. Consider this example:

// loading all meetings to memory:
ClearCollect(InMemoryMeetings,
  Filter('Meetings', ID > 0,  ID <= 200),
  Filter('Meetings', ID > 200, ID <= 400), 
  Filter('Meetings', ID > 400, ID <= 600), 
  Filter('Meetings', ID > 600, ID <= 800),
  Filter('Meetings', ID > 800, ID <= 1000),
  Filter('Meetings', ID > 1000, ID <= 1200),
  Filter('Meetings', ID > 1200, ID <= 1400),
  Filter('Meetings', ID > 1400, ID <= 1600),
  Filter('Meetings', ID > 1600, ID <= 1800),
  Filter('Meetings', ID > 1800, ID <= 2000),
  Filter('Meetings', ID > 2000, ID <= 2200),
  Filter('Meetings', ID > 2200, ID <= 2400),
  Filter('Meetings', ID > 2400, ID <= 2600),
  Filter('Meetings', ID > 2600, ID <= 2800),
  Filter('Meetings', ID > 2800, ID <= 3000),
  Filter('Meetings', ID > 3000, ID <= 3200)
);

Then you can filter against this variable instead. Even using complex quieries like these:

Filter(
    InMemoryMeetings,
    Or(
        StartsWith(TextSearch.Text, Text('Meeting Number')),
        TextSearch.Text in Text('Start Date', "[$-en-US]yyyy-mm-dd"),
        TextSearch.Text in Text('End Date', "[$-en-US]yyyy-mm-dd"),
        ID exactin Filter(InMemoryMotions, TextSearch.Text in 'Motion Text').MeetingID, 
        And(
            ID exactin Filter(InMemoryMotions, FirstWord in 'Motion Text').MeetingID,
            ID exactin Filter(InMemoryMotions, SecondWord in 'Motion Text').MeetingID  
        ),
        And(
            ID exactin Filter(InMemoryMotions, FirstWord in Keywords).MeetingID,
            ID exactin Filter(InMemoryMotions, SecondWord in Keywords).MeetingID  
        )
    )
)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.