cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
CLS720
Level: Powered On

Applying Search to Gallery/Datatable before/after Two Levels of Filtering (and Sorting)

I have a gallery shaped into a datatable with two filter dropdowns, a sort dropdown, and a search text box (pictured below).

  • When a selection is selected from the facility filter dropdown, a filter variable and a facfilter variable are set to true.
  • When a selection is selected from the department dropdown, a filter variable and a deptfilter variable are set to true.
  • When a selection is made from the sort dropdown, a sort variable is set to true.
  • When text is entered into the search text box, a search variable is set to true.
  • This gallery/datatable is showing data from a datasource called "Requests" and is only showing items with a Status of Approved and a Service Area that is equal to the variable myServiceArea.
  • There's also a sort order dropdown that appears if a selection is made from the sort dropdown.

Table with filter dropdowns and search box

The gallery/datatable below will filter records based on the selections from the dropdown. This gallery/datatable will also filter records based on text entered into the search box, but ONLY when there are no selections made from the facility and department dropdowns. The search box does not work whenever a selection is selected from one of the filter dropdowns, before or after search is entered into the search box. Any suggestions to changes I can make to my code that's applied to the items property of the gallery/datatable (below) to allow for searching and filtering to occur at the same time on this gallery/datatable?

If(
    sort,
    SortByColumns(
 If(
        filter,
        Filter(
            Requests,
            OData__Status = "Approved",
            Service_x0020_Area = MyServiceArea,
            If(
                facfilter,
                Facility = facCombo_2.Selected.Result,
                ol_Department = deptCombo_2.Selected.Result
            ),
            If(
                deptfilter,
                ol_Department = deptCombo_2.Selected.Result,
                Facility = facCombo_2.Selected.Result
            )
        ),
        facfilter && search,
        Search(
            Filter(
                Requests,
                OData__Status = "Approved",
                Service_x0020_Area = MyServiceArea,
                Facility = facCombo_2.Selected.Result
            ),
            searchBox_2.Text,
            "Mnemonic",
            "Reason",
            "Approver_x0020_Comments",
            "CancelPatientsText",
            "OData__Comments",
            "ol_Department"
        ),
        deptfilter && search,
        Search(
            Filter(
                Requests,
                OData__Status = "Approved",
                Service_x0020_Area = MyServiceArea,
                ol_Department = deptCombo_2.Selected.Result
            ),
            searchBox_2.Text,
            "Mnemonic",
            "Reason",
            "Approver_x0020_Comments",
            "CancelPatientsText",
            "OData__Comments",
            "Facility"
        ),
        deptfilter && search && facfilter,
        Search(
            Filter(
                Requests,
                OData__Status = "Approved",
                Service_x0020_Area = MyServiceArea,
                ol_Department = deptCombo_2.Selected.Result,
                Facility = facCombo_2.Selected.Result
            ),
            searchBox_2.Text,
            "Mnemonic",
            "Reason",
            "Approver_x0020_Comments",
            "CancelPatientsText",
            "OData__Comments"
        ),
        search,
        Search(
            Filter(
                Requests,
                OData__Status = "Approved",
                Service_x0020_Area = MyServiceArea
            ),
            searchBox_2.Text,
            "Mnemonic",
            "Facility",
            "Reason",
            "Approver_x0020_Comments",
            "CancelPatientsText",
            "ol_Department",
            "OData__Comments"
        ),
        Filter(
            Requests,
            OData__Status = "Approved",
            Service_x0020_Area = MyServiceArea
        )
    ),
        Text(sortCombo_1.Selected.RealName),
        Text(sortOrder_combo.Selected.Value)
    ),
    If(
        filter,
        Filter(
            Requests,
            OData__Status = "Approved",
            Service_x0020_Area = MyServiceArea,
            If(
                facfilter,
                Facility = facCombo_2.Selected.Result,
                ol_Department = deptCombo_2.Selected.Result
            ),
            If(
                deptfilter,
                ol_Department = deptCombo_2.Selected.Result,
                Facility = facCombo_2.Selected.Result
            )
        ),
        facfilter && search,
        Search(
            Filter(
                Requests,
                OData__Status = "Approved",
                Service_x0020_Area = MyServiceArea,
                Facility = facCombo_2.Selected.Result
            ),
            searchBox_2.Text,
            "Mnemonic",
            "Reason",
            "Approver_x0020_Comments",
            "CancelPatientsText",
            "OData__Comments",
            "ol_Department"
        ),
        deptfilter && search,
        Search(
            Filter(
                Requests,
                OData__Status = "Approved",
                Service_x0020_Area = MyServiceArea,
                ol_Department = deptCombo_2.Selected.Result
            ),
            searchBox_2.Text,
            "Mnemonic",
            "Reason",
            "Approver_x0020_Comments",
            "CancelPatientsText",
            "OData__Comments",
            "Facility"
        ),
        deptfilter && search && facfilter,
        Search(
            Filter(
                Requests,
                OData__Status = "Approved",
                Service_x0020_Area = MyServiceArea,
                ol_Department = deptCombo_2.Selected.Result,
                Facility = facCombo_2.Selected.Result
            ),
            searchBox_2.Text,
            "Mnemonic",
            "Reason",
            "Approver_x0020_Comments",
            "CancelPatientsText",
            "OData__Comments"
        ),
        search,
        Search(
            Filter(
                Requests,
                OData__Status = "Approved",
                Service_x0020_Area = MyServiceArea
            ),
            searchBox_2.Text,
            "Mnemonic",
            "Facility",
            "Reason",
            "Approver_x0020_Comments",
            "CancelPatientsText",
            "ol_Department",
            "OData__Comments"
        ),
        Filter(
            Requests,
            OData__Status = "Approved",
            Service_x0020_Area = MyServiceArea
        )
    )
) 

 

1 ACCEPTED SOLUTION

Accepted Solutions
CLS720
Level: Powered On

Re: Applying Search to Gallery/Datatable before/after Two Levels of Filtering (and Sorting)

Figured it out. Turns out the search variable that is set to true when a change is made on the search box was just overcomplicating things. Here's the code that works:

If(
    sort,
    SortByColumns(
        If(
            filter,
            Search(
                Filter(
                    Requests,
                    OData__Status = "Approved",
                    Service_x0020_Area = MyServiceArea,
                    If(
                        facfilter,
                        Facility = facCombo_2.Selected.Result,
                        ol_Department = deptCombo_2.Selected.Result
                    ),
                    If(
                        deptfilter,
                        ol_Department = deptCombo_2.Selected.Result,
                        Facility = facCombo_2.Selected.Result
                    )
                ),
                searchBox_2.Text,
                "Mnemonic",
                "Reason",
                "Approver_x0020_Comments",
                "Profile_x0020_Code",
                "CancelPatientsText",
                "OData__Comments",
                "ol_Department",
                "Facility"
            ),
            Search(
                Filter(
                    Requests,
                    OData__Status = "Approved",
                    Service_x0020_Area = MyServiceArea
                ),
                searchBox_2.Text,
                "Mnemonic",
                "Reason",
                "Approver_x0020_Comments",
                "Profile_x0020_Code",
                "CancelPatientsText",
                "OData__Comments",
                "ol_Department",
                "Facility"
            )
        ),
        Text(sortCombo.Selected.RealName),
        Text(sortOrder_combo_1.Selected.Value)
    ),
    If(
        filter,
        Search(
            Filter(
                Requests,
                OData__Status = "Approved",
                Service_x0020_Area = MyServiceArea,
                If(
                    facfilter,
                    Facility = facCombo_2.Selected.Result,
                    ol_Department = deptCombo_2.Selected.Result
                ),
                If(
                    deptfilter,
                    ol_Department = deptCombo_2.Selected.Result,
                    Facility = facCombo_2.Selected.Result
                )
            ),
            searchBox_2.Text,
            "Mnemonic",
            "Reason",
            "Approver_x0020_Comments",
            "Profile_x0020_Code",
            "CancelPatientsText",
            "OData__Comments",
            "ol_Department",
            "Facility"
        ),
        Search(
            Filter(
                Requests,
                OData__Status = "Approved",
                Service_x0020_Area = MyServiceArea
            ),
            searchBox_2.Text,
            "Mnemonic",
            "Reason",
            "Approver_x0020_Comments",
            "Profile_x0020_Code",
            "CancelPatientsText",
            "OData__Comments",
            "ol_Department",
            "Facility"
        )
    )
)
1 REPLY 1
CLS720
Level: Powered On

Re: Applying Search to Gallery/Datatable before/after Two Levels of Filtering (and Sorting)

Figured it out. Turns out the search variable that is set to true when a change is made on the search box was just overcomplicating things. Here's the code that works:

If(
    sort,
    SortByColumns(
        If(
            filter,
            Search(
                Filter(
                    Requests,
                    OData__Status = "Approved",
                    Service_x0020_Area = MyServiceArea,
                    If(
                        facfilter,
                        Facility = facCombo_2.Selected.Result,
                        ol_Department = deptCombo_2.Selected.Result
                    ),
                    If(
                        deptfilter,
                        ol_Department = deptCombo_2.Selected.Result,
                        Facility = facCombo_2.Selected.Result
                    )
                ),
                searchBox_2.Text,
                "Mnemonic",
                "Reason",
                "Approver_x0020_Comments",
                "Profile_x0020_Code",
                "CancelPatientsText",
                "OData__Comments",
                "ol_Department",
                "Facility"
            ),
            Search(
                Filter(
                    Requests,
                    OData__Status = "Approved",
                    Service_x0020_Area = MyServiceArea
                ),
                searchBox_2.Text,
                "Mnemonic",
                "Reason",
                "Approver_x0020_Comments",
                "Profile_x0020_Code",
                "CancelPatientsText",
                "OData__Comments",
                "ol_Department",
                "Facility"
            )
        ),
        Text(sortCombo.Selected.RealName),
        Text(sortOrder_combo_1.Selected.Value)
    ),
    If(
        filter,
        Search(
            Filter(
                Requests,
                OData__Status = "Approved",
                Service_x0020_Area = MyServiceArea,
                If(
                    facfilter,
                    Facility = facCombo_2.Selected.Result,
                    ol_Department = deptCombo_2.Selected.Result
                ),
                If(
                    deptfilter,
                    ol_Department = deptCombo_2.Selected.Result,
                    Facility = facCombo_2.Selected.Result
                )
            ),
            searchBox_2.Text,
            "Mnemonic",
            "Reason",
            "Approver_x0020_Comments",
            "Profile_x0020_Code",
            "CancelPatientsText",
            "OData__Comments",
            "ol_Department",
            "Facility"
        ),
        Search(
            Filter(
                Requests,
                OData__Status = "Approved",
                Service_x0020_Area = MyServiceArea
            ),
            searchBox_2.Text,
            "Mnemonic",
            "Reason",
            "Approver_x0020_Comments",
            "Profile_x0020_Code",
            "CancelPatientsText",
            "OData__Comments",
            "ol_Department",
            "Facility"
        )
    )
)

Helpful resources

Announcements
firstImage

Watch Sessions On Demand!

Continue your learning in our online communities.

SecondImage

PowerApps Monthly Community Call

Next Wednesday, August 21st at 8am PDT

Top Community Contributors for July 2019

Top Community Contributors for July 2019

Let's thank our top community contributors

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

FirstImage

Power Platform World Tour

Coming to a city near you

thirdimage

PowerApps Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

FourthImage

Join PowerApps User Group!!

Connect, share, and learn with your peers year-round

FifthImage

Dynamics 365 and Power Platform April 2019 Release notes

Features releasing from April 2019 through September 2019!

SixthImage

Power Summit Australia 2019

August 20-23rd 2019

Users Online
Currently online: 19 members 4,298 guests
Please welcome our newest community members: