Gallery not displaying all data

[Mike_N]

I have a SharePoint list with over 2000 items.  In my app, I'm using a gallery to display items filtered by year so that I'm not trying to pull all 2000+ items.  So when I start the app, I create a collection and put the current year items in it using the below:

ClearCollect(colEMSstatRecords, Filter(AddColumns('EMS STAT (AAR)',"Year",Year('Start Date')), 'Year' = Year(Now())));

If it were to work correctly, I should get 794 items.  But it doesn't, which is obviously why I'm here.  I'm only getting 705 items.  It starts at January 1 but stops at August 10.  There's 89 more items it's not grabbing from August 10 to present.  My gallery is items property is the following:

SortByColumns(
        If(
            DdDivision.Selected.Value = "All",
            Filter(
                colEMSstatRecords,
                Year('Start Date') = DdYear.Selected.Value,
                StartsWith(
                    'Call-out Location',
                    TextSearchBox.Text
                ) || StartsWith(
                    'Substantive Case File Number',
                    TextSearchBox.Text
                ) || StartsWith(
                    'Bureau Name'.DisplayName,
                    TextSearchBox.Text
                )
            ),
            Filter(
                colEMSstatRecords,
                Year('Start Date') = DdYear.Selected.Value,
                'Field Office'.Value = DdDivision.Selected.Value,
                StartsWith(
                    'Call-out Location',
                    TextSearchBox.Text
                ) || StartsWith(
                    'Substantive Case File Number',
                    TextSearchBox.Text
                ) || StartsWith(
                    'Bureau Name'.DisplayName,
                    TextSearchBox.Text
                ) || StartsWith(
                    'Field Office'.Value,
                    TextSearchBox.Text
                )
            )
        )
    ,
    "Start_x0020_Date",
    If(
        SortDescending1,
        Ascending,
        Descending
    )
)

I've tried it without the StartsWith parts of the filter and get the same results.  

I have a drop down that allows the user to change the year they want to view.  This calls ClearCollect on the same collection (colEMSstatRecords) and filters the list with the selected year.  If I select 2021 it gives me the correct number of records (1007).  This really confuses me because it's not the amount it is trying to pull that's an issue since 2021 has more items than 2022 currently (794).

Does anyone have a clue what my issue is and how i might be able to fix it?

Thanks

Mike

[Drrickryp]

@Mike_N 

The reason your formula fails is that ClearCollect() is not delegatable and is limited to 2k records.  You can work around this limit by collecting the first 2k and then re-sorting the list by descending ID and get the last 2k.  Then combining the collections and eliminating the duplicates.  This formula will work for lists <4k but will be inaccurate if the list exceeds 4k.  Use the following to create a collection that holds all of your records. 

With(
   {wLow:                         //Collects the first 2k records
      Sort(
         'EMS STAT (AAR),
         ID
      ),
    wHigh:                        //Collects the last 2k records including duplicates
      Sort(
         'EMS STAT (AAR),
         ID,
         Descending
      )
   },
   ClearCollect(                     //Combines the two collections and removes duplicates
      colEMSstatRecords,
      wLow,
      Filter(
         wHigh,
         !(ID in wLow.ID)
      )
   )
)

You can use Filter(), Search() and the 'in' operator on it without having to worry about delegation issues.  Something like:

With({fltrd1:SortByColumns(
                Search(
                     Filter(colEMSstatRecords, Year('Start Date') = DdYear.Selected.Value), 
                       TextSearchBox.Text, 
                         "Call-out_x0020_Location",
                         "Substantive_x0020_Case_x0020_File_x0020_Case_x0020_Number",
                         "Bureau_x0020_ Name".DisplayName
                 ),"Start_x0020_Date",If(SortDescending1,Ascending,Descending)
               ),
     fltrd2:SortByColumns(
                 Search(
                     Filter(colEMSstatRecords,  Year('Start Date') = DdYear.Selected.Value),
                        TextSearchBox.Text,  
                            "Call-out_x0020_Location",
                            "Substantive_x0020_Case_x0020_ File_x0020_Case_x0020_Number",
                            "Bureau_x0020_ Name".DisplayName,
                            "Field_x0020_ Office".Value
                    ), "Start_x0020_Date",If(SortDescending1, Ascending,Descending)
             )                            
       },
          If(DdDivision.Selected.Value = "All", fltrd1,fltrd2)

)

[RandyHayes]

@Mike_N 

Actually, it's not so much that collect is not delegable as that is not relevant in this case, the problem is that AddColumns will only add columns to the first 2000 records (assuming 2000 is your record limit).  So, you will never get more than 2000 records.

Changing your collection formula to the following would resolve that:

ClearCollect(colEMSstatRecords, 
    Filter('EMS STAT (AAR)',
        'Start Date' >= Date(Year(Today()), 1, 1) &&
        'Start Date' < Date(Year(Today()) + 1, 1, 1)
    )
)

You can also avoid duplicating all the data in your app by replacing the Items property formula of the gallery to:

SortByColumns(
    Filter('EMS STAT (AAR)',
        'Start Date' >= Date(DdYear.Selected.Value, 1, 1),
        'Start Date' < Date(DdYear.Selected.Value + 1, 1, 1),
        DdDivision.Selected.Value = "All" || ('Field Office'.Value = DdDivision.Selected.Value && StartsWith('Field Office'.Value, TextSearchBox.Text)),
                
        StartsWith('Call-out Location', TextSearchBox.Text) || 
        StartsWith('Substantive Case File Number', TextSearchBox.Text) || 
        StartsWith('Bureau Name'.DisplayName, TextSearchBox.Text)
    ),
    "Start_x0020_Date",
    If(SortDescending1, Ascending, Descending)
)

I hope this is helpful for you.

[Mike_N]

@RandyHayes, so I made both of your changes and other than a delegation warning, both are working.  But I'm still only getting 705 of the 794 records for this year.  When I select 2021 or 2020, I get everything but there is something strange going on with 2022.

[RandyHayes · Accepted solution]

@Mike_N 

Try "hard writing" the values into the formula.

ex:

SortByColumns(
    Filter('EMS STAT (AAR)',
        'Start Date' >= Date(2022, 1, 1),
        'Start Date' < Date(2023, 1, 1),
        DdDivision.Selected.Value = "All" || ('Field Office'.Value = DdDivision.Selected.Value && StartsWith('Field Office'.Value, TextSearchBox.Text)),
                
        StartsWith('Call-out Location', TextSearchBox.Text) || 
        StartsWith('Substantive Case File Number', TextSearchBox.Text) || 
        StartsWith('Bureau Name'.DisplayName, TextSearchBox.Text)
    ),
    "Start_x0020_Date",
    If(SortDescending1, Ascending, Descending)
)

Then, if no joy, minimize the filter criteria:

SortByColumns(
    Filter('EMS STAT (AAR)',
        'Start Date' >= Date(2022, 1, 1),
        'Start Date' < Date(2023, 1, 1),
    ),
    "Start_x0020_Date",
    If(SortDescending1, Ascending, Descending)
)

See if you get closer to what you are expecting and that will provide a clue as to where the problem is.

[Mike_N · Accepted solution]

@RandyHayes, thanks!  So what worked was taking out the search box stuff.  So right now this gives me everything as expected.  Here is what I have in there now:

SortByColumns(
    Filter('EMS STAT (AAR)',
        'Start Date' >= Date(DdYear.Selected.Value, 1, 1),
        'Start Date' < Date(DdYear.Selected.Value + 1, 1, 1)
    ),
    "Start_x0020_Date",
    If(SortDescending1, Ascending, Descending)
)

So it seems like the search stuff was causing my issue.  Is there a better way to implement the search box?  This is what the box looks like and would filter the list as they typed based off those three fields

Thanks again for all the help so far.