Issues with "in" operator

[AdaEloka]

Hello everyone,

I have an issue that wasn't there before. I'm filtering my gallery with the "in" operator, it's connected to a sharepoint list and I can't see entries that were made from yesterday until now. This issue started yesterday and up until then it had been working perfectly. 

//This is the code that's no longer working
Filter(colVoucherReportTable, SearchUnit in varManyUnits && FirstApprover.Email = User().Email && ApprovalStatus="Pending" && FirstApproval=Blank())

//This works when I change the "in" to "="
Filter(colVoucherReportTable, SearchUnit = varManyUnits && FirstApprover.Email = User().Email && ApprovalStatus="Pending" && FirstApproval=Blank())

//This is the variable i'm trying to check...
Set(varManyUnits, Concat(
                    gal_UnitControls.AllItems,
                    Unit,
                    "; "
                ));

I feel like I need to use the "in" or a similar operator.  "exactin" doesn't work either. What's surprising is that this was working fine and now it's no longer working...It's so annoying...

[Pstork1 · Accepted solution]

This is normally caused by the list growing beyond the size of the data row limit. The in operator is not delegable so using it in a filter will limit the filter to the first 500 records unless the data row limit has been changed to allow up to 2,000.  The fix for this is to break the filter up into operators that are delegable and ones that are not. If you use the delegable operators to pre-filter the list and get it below the data row limit you can then use the in operator on the results.  Something like this

Filter(Filter(colVoucherReportTable, FirstApprover.Email = User().Email && ApprovalStatus="Pending" && FirstApproval=Blank()),SearchUnit in varManyUnits )

Using a function like user().Email is also non-delegable so the value for that function should be saved first and the value used instead of the function. Like this.

set(varemail, User().Email);
Filter(Filter(colVoucherReportTable, FirstApprover.Email = varemail && ApprovalStatus="Pending" && FirstApproval=Blank()),SearchUnit in varManyUnits )

[AdaEloka]

Wow, thank you. This makes sense. I checked my list and it's 519 items long. It probably hit 500 by yesterday. Also, I realised that my collection, colVoucherReportTable might also have the same issue and I've tried your suggestion but I'm not sure of what i'm doing. I thought the "with" function was going to take care of the delegation issue, but apparently it didn't. This is what I have in the collection...

ClearCollect(
    colVoucherReportTable,
    If(
        varEmail in varDeptHead,
        With(
            {
                _VoucherData: Vouchers
                
            },
            Filter(
                _VoucherData,
                SearchDept in varManyDepts
            )
        ),
        varEmail in varUnitHead,
        With(
            {
                _VoucherData: Vouchers
                
            },
            Filter(
                _VoucherData,
                SearchUnit in varManyUnits
            )
        ),
        varEmail in varMemEmails,
        Filter(
            Vouchers,
            SearchUnit = varUnit || 'Created By'.Email = User().Email
        ),
        Vouchers
    )
);

[Fardy1]

Hi @AdaEloka, 

I advise you to increase the total number of items you can fecth to 2000. This won't solve your delegation issue but it will allow you to work with more data.

 

[Pstork1]

With is just a different way to write the code that I provided. The point is to pre-filter the records using delegable functions, in the with clause, then filter the results using non-delegable functions. As @Fardy1 said increasing the data row limit to 2,000 will fix your problem for the moment, but that's not a final solution.

[AdaEloka]

Ok, no problem. Let me think about it and try something.

@Fardy1 thanks for your suggestion, however, like @Pstork1 said, I already know that will not be a permanent solution. It has taken roughly a month for the records to surpass 500...if I just increase the limit to 2000 I'll get into the same fix in about 3/4months... I'll rather fix it permanently now...