Search is not searching all records despite using StartsWith

(2) Share

Report

Posted on by Poweruser32490

130

I have a below gallery filter search code which doesn't work despite using StartsWith function and no usage of in operator. May i know why?

Gallery Items

With(
    {
        _activerequests: SortByColumns(
            Filter(
                'DS',
                (Len('Process Type CB'.Selected.Value) = 0 || ChoiceProcessType.Value = 'Process Type CB'.Selected.Value) && (Len('Employee Type CB'.Selected.Value) = 0 || ChoiceResourceType.Value = 'Employee Type CB'.Selected.Value) && (Len('Region CB'.Selected.Title) = 0 || Region = 'Region CB'.Selected.Title)
            ),
            "Modified",
            SortOrder.Descending
        )
    },
    SortByColumns(
        Filter(
            _activerequests,
            IsBlank(SearchBox.Text) || StartsWith(
                FirstName,
                SearchBox.Text
            ) || StartsWith(
                Title,
                SearchBox.Text
            ) || StartsWith(
                'Employee Id',
                SearchBox.Text
            ) || StartsWith(
                JobTitle_New,
                SearchBox.Text
            ) || StartsWith(
                Region,
                SearchBox.Text
            )
        ),
        varsortcolumn,
        varsortdirection
    )
)

Categories:

AI Builder

Building Power Apps

Microsoft Dataverse with Power Apps

Experimental features

Error handling

GCC, GCCH, DoD - Federal App Makers (FAM)

Connector development

Governance & administering

Power Apps Pro Dev & ISV

Power Query

General topics

I have the same question (0)

All responses (6)

Answers (0)

Sort by

Suggested answer

Haque 3,728 on at

Like
a
(1)

Report
Copy link

Link copied!
Hi @Poweruser32490 ,

Seems like StartsWith is being is correctly just one observation - multiple StartsWith with OR (||) in a single Filter may cause delegation warning.

Some minor areas to consider:

Replace Len('Process Type CB'.Selected.Value)=0 with IsBlank('Process Type CB'.Selected.Value) for safer null checks.

Lowering boths sides [Lower() method actually] will make search case-insensitive.

Finally, if delegation warning shows, please try to limit the search with fewer fields (aka use a single delegable search field if possible).

NOTE: Please check deeply StartsWith is only delegable on Text type and Complex type columns - not Calculated columns.

Avoid delegations - Method-1: We can avoid delegation warnings by rewriting your formula:

Concurrent(
ClearCollect(colResult1, Filter(YourDataSource, StartsWith(ColumnName, "Value1"))),
ClearCollect(colResult2, Filter(YourDataSource, StartsWith(ColumnName, "Value2")))
);
ClearCollect(colAllResults, colResult1, colResult2)

Avoid delegations - Method-2:

If you want a shorter script, you can populate a collection sequentially. Note that this runs linearly, which is slightly slower than Concurrent.

ClearCollect(colAllResults, Filter(YourDataSource, StartsWith(ColumnName, "Value1"))); Collect(colAllResults, Filter(YourDataSource, StartsWith(ColumnName, "Value2")))

References:

SharePoint Delegation Cheat Sheet For Power Apps

Power Apps: How to Bypass the 2000 Record Limit (But Should You?)

I am sure some clues I tried to give. If these clues help to resolve the issue brought you by here, please don't forget to check the box Does this answer your question? At the same time, I am pretty sure you have liked the response!

Was this reply helpful? Yes No
Suggested answer

11manish 3,491 on at

Like
a
(0)

Report
Copy link

Link copied!
try below :

SortByColumns( Filter( 'DS', (IsBlank('Process Type CB'.Selected.Value) || ChoiceProcessType.Value = 'Process Type CB'.Selected.Value) && (IsBlank('Employee Type CB'.Selected.Value) || ChoiceResourceType.Value = 'Employee Type CB'.Selected.Value) && (IsBlank('Region CB'.Selected.Title) || Region = 'Region CB'.Selected.Title) && ( IsBlank(SearchBox.Text) || StartsWith(FirstName, SearchBox.Text) || StartsWith(Title, SearchBox.Text) || StartsWith('Employee Id', SearchBox.Text) || StartsWith(JobTitle_New, SearchBox.Text) || StartsWith(Region, SearchBox.Text) ) ), varsortcolumn, varsortdirection )

The biggest concern in your formula is not StartsWith()—it's the combination of:

Using With() to create an intermediate dataset.

Performing a second Filter() on that dataset.

Multiple OR conditions.

Potentially non-text columns in the StartsWith() expressions.

Was this reply helpful? Yes No
Suggested answer

WarrenBelz 155,940 Most Valuable Professional on at

Like
a
(0)

Report
Copy link

Link copied!
Hi @Poweruser32490,

Firstly, I need to point out the Len() covers both IsBlank and IsEmpty and is actually a better test as it also picks up empty strings "". I have been using it for many years.

For your issue, you need to add the StartsWith to the main filter - I have been involved in a number of your posts on this piece of code and I believe I explained the effect of a With statement on the last one (and also this blog of mine may be of assistance to you). Please take a moment to read that and you may find some useful understanding for the future.

You do however have a problem with the Variable Sort elements at the bottom, which are not Delegable - so they need to be added in a "local" function. Also set the Default of SearchBox to "" (empty string) and you will not need the IsBlank test in the StartsWith sequence.
With( { _activerequests: SortByColumns( Filter( 'DS', ( Len('Process Type CB'.Selected.Value) = 0 || ChoiceProcessType.Value = 'Process Type CB'.Selected.Value ) && ( Len('Employee Type CB'.Selected.Value) = 0 || ChoiceResourceType.Value = 'Employee Type CB'.Selected.Value ) && ( Len('Region CB'.Selected.Title) = 0 || Region = 'Region CB'.Selected.Title ) && ( StartsWith( FirstName, SearchBox.Text ) || StartsWith( Title, SearchBox.Text ) || StartsWith( 'Employee Id', SearchBox.Text ) || StartsWith( JobTitle_New, SearchBox.Text ) || StartsWith( Region, SearchBox.Text ) ), "Modified", SortOrder.Descending ) } }, SortByColumns( _activerequests, varsortcolumn, varsortdirection ) )

If you do the above, the following will happen:-

The filter is totally Delegable, however it must return less than 2,000 records (the list can be of any size), which are then sorted with the non-Delegable variable sort at the bottom. Providing less than this are returned, the code will work exactly as expected

If more than 2,000 are returned, you will get the most lately modified 2,000 matching records, which will then sort with the Variables.

Hopefully this will get your code working as you require.

Please ✅ Does this answer your question 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 answering Yes to Was this reply helpful? or give it a Like ♥
Visit my blog Practical Power Apps LinkedIn

Was this reply helpful? Yes No
MS.Ragavendar 7,466 Super User 2026 Season 1 on at

Like
a
(0)

Report
Copy link

Link copied!

@Poweruser32490, @WarrenBelz has already provide the solution and provided the valid code.

Was this reply helpful? Yes No
Poweruser32490 130 on at

Like
a
(0)

Report
Copy link

Link copied!
Hi @WarrenBelz

I have tried the below code and it works fine. It searches across all 5000 records successfully. However i have a number column called EID in datasource which also needs to be part of search query. If i include that column, i again start facing issue. Rest all single lines of text column works fine like FName, Title, JNew, RNew May i know how to avoid this?

Issue with Number column

StartsWith( EID, SearchBox.Text )

Working Code

With( { _activerequests: SortByColumns( Filter( 'Data Source', (Len('Process Type CB'.Selected.Value) = 0 || ChoiceProcessType.Value = 'Process Type CB'.Selected.Value) && (Len('Employee Type CB'.Selected.Value) = 0 || ChoiceResourceType.Value = 'Employee Type CB'.Selected.Value) && (Len('Region CB'.Selected.Title) = 0 || Region_New = 'Region CB'.Selected.Title) && (StartsWith( FName, SearchBox.Text ) || StartsWith( Title, SearchBox.Text ) || StartsWith( JNew, SearchBox.Text ) || StartsWith( RNew, SearchBox.Text )) ), "Modified", SortOrder.Descending ) }, SortByColumns( _activerequests, varsortcolumn, varsortdirection ) )

Was this reply helpful? Yes No
WarrenBelz 155,940 Most Valuable Professional on at

Like
a
(0)

Report
Copy link

Link copied!
Hi @Poweruser32490,

That is one you cannot do Delegation-wise as StartsWith (as well as Search and in ) are Text functions - a Numeric value is just that (a number - the Number 89 is a completely different value to the Text "89") and does not "start with" anything.

You could include the search in the bottom filter
SortByColumns( Search( _activerequests, SearchBox.Text, Text(EID) ), varsortcolumn, varsortdirection )

but that really would not always work unless the record matching in EID also matched something else returned in the top filter.

A better idea is to either change the field type of EID to Text - it will still store numbers fine or create a new Text field (you can hide it on the Form) with the Default of (assuming a Text input here).
YourEIDInputName.Text

and use this with StartsWith in the main filter. You can also easily back-popluate existing records with a Flow - happy to help here if required.

Please ✅ Does this answer your question 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 answering Yes to Was this reply helpful? or give it a Like ♥
Visit my blog Practical Power Apps LinkedIn

Was this reply helpful? Yes No