how to filter powerapp gallery with textinput, dropdown, and datepicker

Question

I've been trying to add another dropdown and a datepicker filter on my gallery

what's the best approach to achieve this?

whenever I add the ticket closed filter, it just doesn't work

here's my code

SortByColumns(
    Filter(
        PortfolioAPP,
        userddown.Selected.Value = CollectorName && StartsWith(
            TitanCompanyName,
            SearchComp_1.Text
        ) && StartsWith(
            TitanCompanyContactName,
            SearchContact_1.Text
        ) && StartsWith(
            Title,
            SearchTicket.Text
        ) && StartsWith(
            ResolutionType,
            ResoTypeDDown_1.Selected.Value
        )
    ),
    "TotalDebtGBP",
    Ascending

carlosfigueira carlosfigueira · Accepted Answer · 2018-09-21T12:56:39

You can pass many conditions to the Filter function and it will work by returning the items from your data source (PortfolioApp) that satisfies all of those conditions. For example, you can rewrite your expression as:

SortByColumns(
    Filter(
        PortfolioAPP,
        userddown.Selected.Value = CollectorName,
        StartsWith(
            TitanCompanyName,
            SearchComp_1.Text),
        StartsWith(
            TitanCompanyContactName,
            SearchContact_1.Text),
        StartsWith(
            Title,
            SearchTicket.Text),
        StartsWith(
            ResolutionType,
            ResoTypeDDown_1.Selected.Value)
    ),
    "TotalDebtGBP",
    Ascending)

If you want to add another two conditions (based on a dropdown and a date picker), then you can add two more expressions:

SortByColumns(
    Filter(
        PortfolioAPP,
        userddown.Selected.Value = CollectorName,
        StartsWith(
            TitanCompanyName,
            SearchComp_1.Text),
        StartsWith(
            TitanCompanyContactName,
            SearchContact_1.Text),
        StartsWith(
            Title,
            SearchTicket.Text),
        StartsWith(
            ResolutionType,
            ResoTypeDDown_1.Selected.Value),
        TicketClosedDate = DatePicker1.SelectedDate,
        AnotherColumn = YourOtherDropdown.Selected.Value
    ),
    "TotalDebtGBP",
    Ascending)

Update after comments: Since your data source is SharePoint, it seems like there is currently a bug in which filtering based on dates with SharePoint lists doesn't work. I don't know when this will be fixed, but there are a couple of workarounds that you can consider.

The first one is to create a calculated column in SharePoint that converts the date column into a text column, with an expression such as =TEXT(PlacementDate,"yyyy-MM-dd"). You can then compare it with the date from the date picker in an expression like

Filter(PortfolioApp, PlacementDateText = Text(DatePicker1.SelectedDate, "yyyy-mm-dd"))

This should work if your list doesn't have a many elements (up to 500-2000 rows) as delegation is not supported for calculated columns.

However, you also mentioned that you have >10k rows, so the previous workaround won't work. One other possible workaround is to have another "regular" text column that represents that same date value. You'll need to use some sort of script to backfill the existing values, and then use a flow to update that value every time a new row is added / modified. Not ideal, but that would work, at least until this bug is fixed.

how to filter powerapp gallery with textinput, dropdown, and datepicker

1 Answers