Alternatives of clear collect- clearcollect doesn't support deleagate

[Anonymous]

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

[Anonymous]

Thanks Warn. I tried the solution you provided. bu getting a lot of errors.

[WarrenBelz]

@Anonymous ,

I was using your field names you gave me. They are not correct - please replace them with the correct field names for the Status values you need to include - you will have to add any others you did not mention.

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.

[Anonymous]

Thanks warren

Fields name are correct. Status is the name of first field and it's type is choice. Thats's why i was using it earlier as 'Status. Value'. Other name are also correct. is it possible to set up call on msteams for you?

[WarrenBelz]

@Anonymous ,

Sorry typing on a tablet. Should be double quotes - corrected on post now.

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.

[Anonymous]

Thanks @WarrenBelz 

After modification . it's not showing any compile time error, but not giving correct results.

with(
{
wPayReq:
Filter( 'Payment Requests',Status.Value = "Pending approval" || Status.Value = "Pending checks" || Status.Value = "Sent for Payment"|| Status.Value = "Rejected" || Status.Value = "Expired" )
},
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
)
)
)
)

it's showing me results only the limit which i set here

[WarrenBelz]

@Anonymous ,

Absolutely correct- you need to set the limit to 2,000 (the maximum allowed).

You cannot avoid this Delegation limit if you want to use the in filter on all of those controls, why I asked how many there were that were not Draft and then said you will get up to 2,000 non-Draft (as the text filter is Delegable) using the syntax.

Please have a read of the blog on Delegation I linked earlier - it may make this easier to understand.

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.

[Anonymous]

Thanks @WarrenBelz

Maximum limit is 2000 and in my list records are increasing. It can go beyond 10000, so i want to fetch all records.

Please suggest solution accordingly. Apology for any confusion

[WarrenBelz]

@Anonymous ,

You said the number on non-draft records were 1,500, so the code I sent will find those as Text equals = filters are Delegable. Once the 1,500 is retrieved in the With() statement, the in filters will work on this data set.

The only way of searching through more than 2,000 records using a non-Delegable filter is the "big collection" mentioned in my blog. There is simply no way of using the in filter on data sets bigger than your limit.

Can you please have a look at this and tell me if it suits your requirements.

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.

[Anonymous]

If we collect data first in collect without any filter and after this, we will apply filter it should work? Is it possible for you to set up call on MSTeams

[WarrenBelz]

@Anonymous ,

You can only collect 2,000 items to start with, even using a Delegable filter. I have done some testing on something I was curious about (you will see a variation of this in the blog) and you can get up to 4,000 records matching your Status criteria with the following: -

Clear(colFiltered);
With(
   {
      wDesc: 
      Filter(
         Sort(
            'Payment Requests',
             ID,
             Descending
         ),
         Status.Value = "Pending approval" || 
         Status.Value = "Pending checks" || 
         Status.Value = "Sent for Payment" || 
         Status.Value = "Rejected" || 
         Status.Value = "Expired" 
      ),
      wAsc: 
      Filter(
         Sort(
            'Payment Requests',
            ID
         ),
         Status.Value = "Pending approval" || 
         Status.Value = "Pending checks" || 
         Status.Value = "Sent for Payment" || 
         Status.Value = "Rejected" || 
         Status.Value = "Expired" 
      )
   },
   Collect(
      colFiltered,
      wAsc,
      Filter(
         wDesc,
         !(ID in wAsc.ID)
      )
   )
)

You are essentially "coming from both ends" of the list looking for matching records and then deleting the duplicates.
If this is not suitable please have a read of my "big filter" comments in the blog as this is all that is left for you if you really need to use all those In filters.
Then this will work

ClearCollect(
   SearchfilterDashBoardData,
   Filter(
      colFiltered,    
      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.