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"
        )
    )
)