PowerApps StartsWith and EndsWith Functions

In this PowerApps Tutorial, we will discuss what is StartsWith and EndsWith functions in PowerApps. Also, by taking some simple scenarios, we will cover these below topics:

• PowerApps StartsWith Function
• PowerApps StartsWith Syntax
• PowerApps EndsWith Function
• PowerApps EndsWith Syntax
• PowerApps StartsWith and EndsWith Examples
• PowerApps StartsWith number
• PowerApps StartsWith multiple
• PowerApps StartsWith blank
• PowerApps StartsWith contains
• PowerApps StartsWith filter
• PowerApps StartsWith delegation
• PowerApps EndsWith delegation
• PowerApps StartsWith multiple columns
• PowerApps StartsWith wildcard
• PowerApps Gallery startswith

PowerApps StartsWith Function

• PowerApps StartsWith function helps to test whether a text string begins with another. The result of this function returns a Boolean value as true or false.
• You can use the StartsWith function with the Filter function to search the data within your app.
• Also, you can use the in operator or Search function within the text strings. You can face some delegation issues if one of these functions can’t be delegated. You may see that a delegation warning message will appear on the screen.

PowerApps StartsWith Syntax

Below represents the syntax of PowerApps StartsWith function as:

StartsWith( Text, StartText )

Where,

• Text = This is Required. Specify the text to test.
• StartText = This is also Required. Enter the text to search for at the beginning of Text. If StartText is an empty string, then the StartsWith returns the boolean value as true.

PowerApps EndsWith Function

• PowerApps EndsWith function helps to test whether a text string ends with another. The result of this function returns a Boolean value as true or false.
• Here also, you can use the EndsWith function with the Filter function to search the data within your app.
• Also, you can use the in operator or Search function within the text strings. You can face some delegation issues if one of these functions can’t be delegated. You may see that a delegation warning message will appear on the screen.

PowerApps EndsWith Syntax

Below represents the syntax of PowerApps EndsWith function as:

EndsWith( Text, EndText )

Where,

• Text = This is Required. Specify the text to test.
• EndText = This is also Required. Enter the text to search for at the end of Text. If EndText is an empty string, then the EndsWith value returns as true.

PowerApps StartsWith and EndsWith Examples

The below table represents some of the formulas, descriptions, and outputs that you can use in some scenarios.

PowerApps StartsWith Example

Formula	Description	Output
StartsWith( “Welcome PowerApps”, “welcome” )	It tests whether “Welcome PowerApps” begins with “welcome“. The test is case insensitive.	true
StartsWith( “Good Day”, “welcome” )	It tests whether “Good Day” begins with “welcome”.	false
StartsWith( “Everyday is a good day”, “day” )	It tests whether “Every day is a good day” begins with “day“. Although “day” appears in the text, it doesn’t appear at the beginning.	false
StartsWith( “Hello hello”, “” )	It tests whether “Hello hello” starts with an empty text string (Len returns 0). Easing its use in Filter expressions, StartsWith is defined to return true in this case.	true

PowerApps EndsWith Example

Formula	Description	Output
EndsWith( “Welcome PowerApps”, ” powerapps” )	It tests whether “Welcome PowerApps” ends with “powerapps”. The test is case insensitive.	true
EndsWith( “Good day”, “good” )	It tests whether “Good day” ends with “good“. The EndText argument (“good“) appears in the text but not at the end.	false
EndsWith( “Everyday is a good day”, “day” )	It tests whether “Every day is a good day” ends with “day”.	true
EndsWith( “Hello hello”, “” )	It tests whether “Hello hello” ends with an empty text string (Len returns 0). Easing its use in Filter expressions, EndsWith is defined to return true in this case.	true

PowerApps StartsWith number

Do you want to work with PowerApps StartsWith with the number field? Let us take from a simple scenario to a complex one. So that you can understand easily.

Example – 1:

• In this example, I have a SharePoint list named Employee Onboarding. This list has an Employee ID column with the Number data type.
• Now I would like to view the employee details when a user will search based upon their ID. This means, the user will search ID and it will filter the ID-specific employee details.

• Let’s say, suppose I will enter the employee ID as “103“, then the specific item is filtering from the SharePoint list and result is appearing in the PowerApps Gallery control as shown below.

• To achieve this, add a Text input control and a Gallery control in the app. Rename the text input control to txtSearch (where it will help you to search the employee ID).
• Then, select the gallery control and apply the below code on its Items property as:

Items = Filter(
    'Employee Onboarding',
    StartsWith(
        Text('Employee ID'),
        txtSearch.Text
    )
)

Where,

• ‘Employee Onboarding‘ = SharePoint List name
• ‘Employee ID‘ = SharePoint list Number field
• txtSearch = Search text input control name

Refer to the below screenshot.

• To perform the operation, just save and publish the app. Preview the app again. When you will enter any employee ID in the search box, the result you will see in the gallery control.

Also, read: PowerApps Search User

Example – 2:

• Next, we will discuss a little bit of a complex scenario as compared to the previous one. The below screenshot represents a SharePoint list named Products. This list has some various columns like:

1. Title = By default, this is a single line of text data type field
2. Vendor = This is a Choice field
3. Customer Name = It is a Single line of text column
4. Quantity = Number Data type field
5. Price = Currency Data type
6. Sales Date = Date type field

• Now in the app, there will be two text input fields where a user will search the product title and its quantity at a time. Once the user will enter the values in both of the search boxes, then the gallery will filter and display the specific product customer name and its vendor as shown below.

• To workaround with this, select the gallery and set the below formula on its Items property as:

Items = SortByColumns(
    Filter(
        Products,
        StartsWith(
            Title,
            TextInput1.Text
        ) && Quantity = Value(TextInput2.Text)
    ),
    "Title",
    Ascending
)

Where,

1. Products = SharePoint list name
2. Title = Specify the SharePoint column name that you want to search
3. TextInput1 = Text input control name where you will search the product title
4. Quantity = SharePoint Numeric column
5. TextInput2 = Text input control name where you will search the product quantity

• Next, you need to select the gallery and specify its label values as:

1. Customer Name = To display its customer name, apply the below code on Label’s Text property as:

Text = "Customer Name: " & ThisItem.'Customer Name'

2. Vendor = To display its vendor name, apply the below code on Label’s Text property as:

Text = "Vendor: " & ThisItem.Vendor.Value

you can refer to the below screenshot.

• Also, if you want to filter PowerApps gallery by number, text, and lookup columns using one search box, then checkout this forum link: PowerApps Filter gallery by number, text, and lookup columns using one searchbox

This is how to work with the PowerApps StartsWith number function.

PowerApps StartsWith multiple

Suppose you may think that, is it possible to search on multiple columns in PowerApps? Yes, you can search multiple columns in the app. To make it possible, you can use the nested filters. Follow the below scenario.

Example:

• Here I have used the same SharePoint List as Products having with the same fields like Title, Vendor, Customer Name, Quantity, Location, Price, etc.
• Also, this list has some records as like the below screenshot.

• Now, I would like to search the record based upon the Product Title (mandatory) + one or two more fields in a single search box. That means, in a single search box, you can search the Customer name and as well as the Location as shown below. Based on the seraches, it will filter the gallery and display the results.
• Suppose I searched the product title as Laptop in the first search box and in the second search box, I searched the Location is Australia (not only you can search the location, but also you can search the customer name). Then the gallery is filtering and it shows only those products whose location is Australia. Refer to the below screenshot.

• To do this, select the gallery and apply the below formula on its Items property as:

Items = Search(
    Filter(
        Products,
        StartsWith(
            Title,
            txtSearch1.Text
        )
    ),
    txtSearch2.Text,
    "CustomerName",
    "Location"
)

Where,

1. Products = SharePoint List name
2. Title = SharePoint Column that you want to search mandatorily
3. txtSearch1 = Text input control name where you will search the product title
4. txtSearch2 = Text input control name where you will search the product customer name or location
5. “CustomerName” = SharePoint Single line of text column
6. “Location” = SharePoint Single line of text column

• Save and Preview the app. Search the gallery based upon title or any other fields (Customer name/Location) as per your choice. The filtered result will appear in the gallery control as shown above screenshot.
• Suppose if you would like each of the fields have a Input control Box, then the formula would be changed into like below:

Items = Filter(
  Filter(
       Filter(
          Filter(Table,
                StartsWith(Title,TextInput1.Text)
                 ), 
          StartsWith(Customer Name,TextInput2.Text)
           ),
       Startswith(Location, TextInput3.Text)
   ),
Startswith(ID, TextInput4.Text)
)

• Also, if you want more details about to search on multiple columns in gallery, then you can follow this forum link: Searching on multiple columns in gallery

This is how to work with PowerApps StartsWith multiple.

PowerApps StartsWith blank

In this scenario, we will discuss what is PowerApps StartsWith blank.

• Here, I would like to filter the PowerApps gallery by dates that are empty or blank. I have taken the data source is a SharePoint list i.e. Products. Apart from all the fields, there is a Date Time field i.e. Sales Date.
• As you can see in the below list, there is having some records without having its Sales Date. Now I would like the list to show only the records with blank dates i.e. present in the “Sales Date” field.

• In the below screen, you can see there is a gallery control that shows only the blank date records.

• When you will search the product title in the text box, then the gallery will filter and display the filtered empty date records as shown below.

• To achieve this, select the gallery and apply the below formula on its Items property as:

Items = SortByColumns(
    Filter(
        Products,
        Value('Sales Date') = 0 && StartsWith(
            Title,
            TextInput3.Text
        )
    ),
    "Title",
    Ascending
)

Where,

1. Products = SharePoint List name
2. ‘Sales Date‘ = SharePoint Date time field name
3. Title = Single line of text data type column
4. TextInput3 = Text input control name where the user will search product title

• Now save and preview the app. When you will search any title in the search box, then the product will filter and display the appropriate result in the gallery control.

This is how to work with PowerApps StartsWith blank.

PowerApps StartsWith contains

In this topic, we will discuss how to work with PowerApps StartsWith contains.

• As you can see in the app, there is a gallery control and it contains only the Product Customer Names (i.e. retrieving from the SharePoint list).
• Suppose a user will search the customer names by their nicknames. That means, instead of John, if I will search Sangrar, then the gallery should filter and display the values related to Sangrar as in the below screenshot.

• To workaround with this, select the gallery control and set the below code on its Items property as:

Items = Search(
    Products,
    txtSearchName.Text,
    "CustomerName"
)

Where,

1. Products = SharePoint List name
2. txtSearchName = Text input control name where a user will search the customer name
3. “CustomerName” = Specify the SharePoint field that you want to search in the search box

NOTE:

This will depend on how many records you have in the SharePoint list (Products), if it is under your record limit in the app and ultimately less than 2000, then you can use the above formula.
You will get a delegation warning on this as it is not delegable to the datasources, but again, if you are less than record limit, then you can ignore the warning.

• Save and Preview (F5) the app. Search any customer name with the nick name in the search box, then the filtered result will appear in the gallery.

This is how to work with PowerApps StartsWith contains.

PowerApps StartsWith filter

Do you want to filter the SharePoint List data using the PowerApps StartsWith function? Refer to the below scenario.

• In the below screenshot, there is a gallery control and a text input control. When a user will search the Product title or Product location in the same search box, then the gallery will filter and display the specific product customer names as shown below.

• To achieve this, you need to specify the or (||) operator. Apply the below formula on gallery’s Items property as:

Items = Filter(
    Products,
    StartsWith(
        Title,
        TextInput5.Text
    ) || StartsWith(
        Location,
        TextInput5.Text
    )
)

Where,

1. Products = SharePoint List Name
2. Title = Specify the SharePoint column name that you want to search in the text box. Its a single line of text field
3. TextInput5 = Specify the text input control name where you will search either product title or product location
4. Location = This is also a single line of text data type field

• Save and preview the app. Search the title or location that you want to display, then accordingly you will get the result in the gallery control.

Also, you can check out this article Filter Choices with StartsWith function to get more information about the PowerApps StartsWith filter.

This is how to work with PowerApps StartsWith filter.

PowerApps StartsWith delegation

Do you want to know what the PowerApps StartsWith Delegation issue is and how to overcome it in the app? Check out the below-detailed scenario.

• Usually what happens is, when your data source is having large items or records, then a warning issue is appearing in the app. It is known as a Delegation warning issue. Follow the below scenario that how the warning issue appears and resolve it.
• In the app, insert a Text input control and a Combo box control and apply the below formula on its Items property as:

Items = Filter(
    Products,
    StartsWith(
        Title,
        TextInput4.Text
    )
)

Where,

1. Products = SharePoint List name
2. Title = Specify the SharePoint field name that you want to search in the text box
3. TextInput4 = Text input control name where the user will search the title

• Once you apply the formula, you will see a delegation warning message on the combo box as shown below. The message shows like “Delegation warning. The “Search” part of this formula might not work correctly on large data sets.“

• At the same time, add a gallery control and apply the same above formula on its Items property. Once you will apply the code, you can observe that it’s not showing any delegation error on the screen. But it’s coming in the Combo box. Refer the below processes to resolve this issue.

• Select the Combo box control and apply the below code on its OnSelect property as:

OnSelect = ClearCollect(
    ColItems,
    Filter(
        Products,
        StartsWith(
            Title,
            TextInput4.Text
        )
    )
)

• Here, I have created a PowerApps Collection (i.e. ColItems) and the filtered data will store within that collection.

• Next, set the collection to the Combo box’s Items property as:

Items = ColItems

• And, also you need to set the field name combo box’s DisplayFields property (in Advanced tab) as:

DisplayFields = ["CustomerName"]

As I want to view all the customer names in the combo box, so I have specified that specific SharePoint field name.

• Save and Preview (F5) the app. Once you will enter any product title in the search box, then all the search box related customer names will appear in the combo box as shown below.

To overcome any type of PowerApps delegation issue, you can follow this article: Power Apps Delegation

This is how to work with PowerApps StartsWith delegation.

PowerApps EndsWith delegation

You can follow up this article Delegation warning when trying to use Filter() to work with PowerApps Delegation in the EndsWith function.

PowerApps StartsWith multiple columns

As we discussed above, we can search multiple Sharepoint columns in a single search box. Also, we can use the StartWith function for multiple columns in a single search box.

• Suppose there is a search text box and a gallery control. Now I would like to search the product title, Customer name, and product location in one single search box. For that, you will use the OR (||) operator.
• When I will search any three of the columns, then the gallery will filter and display the Product Vendor names according to the search name. Refer to the below screenshot.

• To achieve your needs, select the gallery control and apply the below formula on its Items property as:

Items = Filter(
    Products,
    TextInput6.Text in Title || TextInput6.Text in 'Customer Name' || TextInput6.Text in Location
)

Or you can use the below code:

Items = Filter(
    Products,
    Or(
        StartsWith(
            Title,
            TextInput6.Text
        ),
        StartsWith(
            'Customer Name',
            TextInput6.Text
        ),
        StartsWith(
            Location,
            TextInput6.Text
        )
    )
)

Where,

1. Products = SharePoint List Name
2. TextInput6 = Text input control name where a user can search multiple fields
3. Title, ‘Customer Name‘, “Location” = SharePoint Single line of text data type columns. You can specify the columns that you want to search in the search box.

• Save and preview the app. Search either title, customer name or location in the search box, you will get the filtered result in the gallery control.

This is how to work with PowerApps StartsWith multiple columns.

PowerApps StartsWith wildcard

To work with PowerApps StartsWith wildcard, check out this PowerApps forum link: PowerApps Search function by using wildcards

PowerApps Gallery startswith

• Sometimes in some cases what happens is, When you are searching any word in the search box with the first letter, then the gallery is not showing the proper result.
• Suppose, you will type “s” and it will show items that starts with “s” only. Let’s say the first one is “Steward“. If you will type a “t” after the “s“, then all the items in the gallery disappear, even though Steward was just showing.
• So in that case, you might be using the below code (in gallery’s Items property), that’s why the problem may occur.

Items = Filter(DataSource, StartsWith(CustomerName, TextSearchbox.Text))

Where,

1. DataSource = SharePoint List Name
2. CustomerName = SharePoint column name
3. Textbox = Text input control name

• As we know, PowerApps StartsWith() function is used to test whether one text string begins with another. Please make sure the Customer Name column is a Text type column in your Data source.
• So to avoid this above type of search issues, you can consider the below formula in your gallery’s Items property as:

Items = Filter(DataSource, TextSearchbox.Text in CustomerName)

NOTE:

Above formula may cause a Delegation warning issue, if the amount of your data source records is not more than 2000, you could ignore this warning issue. Set the “Data row limit” option to maximum value — 2000 within Advanced settings of App settings of your app, then you could process 2000 records locally at most in your app.

Also, you may like these below PowerApps Tutorials:

• PowerApps Find Function
• PowerApps Mod Function
• Upload PowerApps Attachments to SharePoint Library Folder
• PowerApps Trim Function
• Power BI integration with PowerApps Portals
• Power Apps Azure AD Group
• Power Apps RSS Feed
• PowerApps Weather
• PowerApps Twitter Connector
• Power Apps Calculate + 13 Examples
• Power Apps Rating Control – How to use
• Build a Calculator in Power Apps
• Power Apps Slider Control
• Power Apps Data Table – Complete tutorial
• Migrate PowerApps from one tenant to another
• Power Apps Image Control – How to use
• Power Apps GroupBy Function example
• Power Apps Audio and Video Control [Detailed tutorial]

In this PowerApps Tutorial, we discussed what is StartsWith and EndsWith functions in PowerApps. Also, by taking some simple scenarios, we covered these below topics:

• PowerApps StartsWith Function
• PowerApps StartsWith Syntax
• PowerApps EndsWith Function
• PowerApps EndsWith Syntax
• PowerApps StartsWith and EndsWith Examples
• PowerApps StartsWith number
• PowerApps StartsWith multiple
• PowerApps StartsWith blank
• PowerApps StartsWith contains
• PowerApps StartsWith filter
• PowerApps StartsWith delegation
• PowerApps EndsWith delegation
• PowerApps StartsWith multiple columns
• PowerApps StartsWith wildcard
• PowerApps StndsWith contains
• PowerApps Gallery startswith