cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
vandana
Helper IV
Helper IV

Alternatives of clear collect- clearcollect doesn't support deleagate

is there any alternative of clear collect. clear collect doesn't support delegate and limit of records which will display will be 2000.

 

I am using clear collect to store filter data.


ClearCollect(paymentrequest,'Payment Requests');
ClearCollect(SearchfilterDashBoardData,Filter( paymentrequest,Status.Value <> draft,If(!IsBlank(txtPaymnetRequestId_1.Text),PaymentRequestID=txtPaymnetRequestId_1.Text,true ),
If(!IsBlank(cmb_office.Selected.ID),Office.Id in cmb_office.SelectedItems.ID,true),
If(!IsBlank(cmb_Status.Selected.Value),Status.Value in cmb_Status.SelectedItems.Value,true),
If(!IsBlank(cmb_entity.Selected.EntityName),Entity.Value in cmb_entity.SelectedItems.EntityName,true)));

 

After this i have used this collection in items property of gallery

 

1 ACCEPTED SOLUTION

Accepted Solutions

@vandana ,

The "over 4,000" solution has nothing to do with a new record - that reference to this is only what you have to do to create the Delegable numeric ID "shadow field". The reason for this field is because the built-in ID number is only partially Delegable and can only be used with equals = , not greater then or less than, so you need a "counter" field to do the big collection as described. This is a complete workaround to the Delegation issue inherent in SharePoint and is a completely separate topic to the rest of this discussion and something you really need to understand if you want to use it.

So that we do not keep going around in circles, can I please summarise the problem: -

  • Your can set you Delegation limit up to 2,000  - I will refer to that figure here.
  • You cannot conduct non-Delegable queries such as in on a list and have it work on more than the first (or last) 2,000 items.
  • You can conduct these non-Delegable on a collection, however this is generally is limited to 2,000 items.
  • If you can reduce the collection to under 2,000 items with Delegable filters (such as the Status equals those values), you can then conduct the In filters.
  • I have provided you in the last post with a further workaround to expand this to 4,000 items after filtering on Status.
  • The With() function is nothing more than a temporary collection within the statement.

Please tell me if you understand all of this.

 

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.

 

 

 

View solution in original post

33 REPLIES 33
WarrenBelz
Super User III
Super User III

Hi @vandana ,'

This is nothing to do with ClearCollect, you have several non-Delegable filters in your code

ClearCollect(
   paymentrequest,
   'Payment Requests'
);
ClearCollect(
   SearchfilterDashBoardData,
   Filter(
      paymentrequest,
      Status.Value <> draft,                             //** <> not Delegable
      If(
         !IsBlank(txtPaymnetRequestId_1.Text),
         PaymentRequestID=txtPaymnetRequestId_1.Text,
         true
      ),
      If(
         !IsBlank(cmb_office.Selected.ID),
         Office.Id in cmb_office.SelectedItems.ID,         //** in not Delegable
         true
      ),
      If(
        !IsBlank(cmb_Status.Selected.Value),
        Status.Value in cmb_Status.SelectedItems.Value,    //** in not Delegable   
        true
      ),
      If(
         !IsBlank(cmb_entity.Selected.EntityName),
         Entity.Value in cmb_entity.SelectedItems.EntityName,   //** in not Delegable
         true
      )
   )
)

There may be some workarounds depending on the number of items in your data and how many can be isolated with Delegable filters. I have written a couple of blogs on this - Delegation in general and using the With() filter. Although they are SharePoint related, most will apply to you. Please have a read of them and I will be happy to elaborate further when you work out if they can possibly assist you.

 

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.

https://docs.microsoft.com/en-us/powerapps/maker/canvas-apps/delegation-overview- As per this documentation clear collect is non delegable function.  In and <> can be delegated

 

I am not getting any delegate warning also. why is it so?

@vandana ,

I do not know your data source and was referring to SharePoint, however have another read of my blog on Delegation - there are ways of collecting many more than 2,000 records explained in it.

Also your link to the document does not work.

Thanks Warren,

 

I am using sharepoint list as data source.  I have tried to use "=" operator instead of in, but it's not working. any suggestion on it and what operator i can use as replacement of <>

 

 

please refer this link for documentation - https://docs.microsoft.com/en-us/powerapps/maker/canvas-apps/delegation-overview#delegable-data-sour...

Thanks @vandana ,

I can tell you (despite what that document says) that neither in or <> are Delegable in SharePoint, however looking at it again (I missed this on first response) I see now why you are not getting a Delegation warning as you have tried to collect the list first, then filtered the collection, so you will only be filtering the first 500-2000 (depending on your settings) items from the list.

Firstly,

  • How many items are in your list?
  • How many do not have the Status of Draft and what are the other Status values?
  • Are your combo boxes single or multi-select?

The main issue you have is the true statement when any of the controls are blank, meaning you want all the list.

 

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.

  • If in or <>  are not delegable in sharepoint. what is alternative available?
  • If i have collected records first in list. It will collect only first 2000 records? it means collect is also non delegable?
  • items in list are more than 2000
  • Around 500 items has status Draft.
  • Other Status are like Pending Approval,Pending checks, sent for payment e.t.c
  • We are using multi select combo.

@vandana ,

I am a user like you trying to help you through this. SharePoint is what it is (and so are Collections) and there are things you simply cannot do exactly the way you want to.

How many more than 2,000 are in the list that are not draft?

You will see in the blogs I referenced that there are possible workarounds, but only within certain limits.

Thanks warren

 

 

around 1500 records are not draft

Thanks @vandana ,

You have also marked one of your posts as the solution, but anyway, this will get you up to 2,000 records (providing you have your settings on this) with the top filter. You will have to add in any other Status values you have not mentioned.

 

With(
   {
      wPayReq:
      Filter(
         'Payment Requests'
          Status.Value = "Pending Approval" ||
          Status.Value = "Pending checks" ||
          Status.Value = "Sent for payment"
      )
   },   
   ClearCollect(
      SearchfilterDashBoardData,
      Filter(
         wPayReq,    
         If(
            !IsBlank(txtPaymnetRequestId_1.Text),
            PaymentRequestID=txtPaymnetRequestId_1.Text,
            true
         ),
         If(
            !IsBlank(cmb_office.Selected.ID),
            Office.Id in cmb_office.SelectedItems.ID,        
            true
         ),
         If(
           !IsBlank(cmb_Status.Selected.Value),
           Status.Value in cmb_Status.SelectedItems.Value,       
           true
         ),
         If(
            !IsBlank(cmb_entity.Selected.EntityName),
            Entity.Value in cmb_entity.SelectedItems.EntityName,   
            true
         )
      )
   )
)

 

 

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.

Helpful resources

Announcements
PA_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Power Query PA Forum 768x460.png

Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps

Carousel 2021 Release Wave 2 Plan 768x460.jpg

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

R2 (Green) 768 x 460px.png

Microsoft Dynamics 365 & Power Platform User Professionals

DynamicsCon is a FREE, 4 half-day virtual learning experience for 11,000+ Microsoft Business Application users and professionals.

Users online (3,035)