Gallery Items as variable that contains a Filter() formula

Question

I have a gallery that contains data that I want to filter. To circumvent having multiple galleries with each having unique filter formulas in the gallery's Items, I created a table that contains all the filters/roster I want.

ClearCollect(
Roster_Filter_Table,
Table(
    {
        Roster: "All",
        Filter: "Filter(PT_Table,StartsWith(MRN,Pt_Search_ID.Text))"
    },
    {
        Roster: "Prescreen",
        Filter: "Filter(PT_Table,StartsWith(MRN,Pt_Search_ID.Text),Eligibility_Status=Blank())"
    }
));

I have a dropdown on the screen that has the source as this Roster_Filter_Table.

I've tried multiple things such as making the Items as Dropdown_Selection.SelectedText.Filter or having the dropdown set/updatedcontext of a variable that stores the Filter formula and then use that variable name in my gallery's Items, but all of it yields the same error of "Expected a table".

I'm quite lost now in how I can achieve this as I can't find anyone attempting something similar. Is it impossible to do with the limitations of PowerApps?

Answer 1

I think you are facing this issue because you have created formula as string/text. Try below approach once:

Create collection like this:

ClearCollect(
Roster_Filter_Table,
Table(
    {
        Roster: "All",
        Filter: Table(Filter(PT_Table,StartsWith(MRN,Pt_Search_ID.Text)))
    },
    {
        Roster: "Prescreen",
        Filter: Table(Filter(PT_Table,StartsWith(MRN,Pt_Search_ID.Text),Eligibility_Status=Blank()))
    }
));

Then set items property of gallery control to:

Dropdown_Selection.Selected.Filter

---

OR

Create collection like this:

ClearCollect(
Roster_Filter_Table,
Table(
    {
        Roster: "All"
    {
        Roster: "Prescreen"
    }
));

Then set items property of gallery control to:

If(
    Dropdown_Selection.Selected.Roster = "All",
    Filter(PT_Table,StartsWith(MRN,Pt_Search_ID.Text)),
    Filter(PT_Table,StartsWith(MRN,Pt_Search_ID.Text),Eligibility_Status=Blank())
)

You can also use Switch() like:

Switch(
    Dropdown_Selection.Selected.Roster,
    "All",
    Filter(PT_Table,StartsWith(MRN,Pt_Search_ID.Text)),
    "Prescreen",
    Filter(PT_Table,StartsWith(MRN,Pt_Search_ID.Text),Eligibility_Status=Blank()),
    PT_Table
)

Where last parameter (PT_Table) is the Default Result in case there is no matching selection in drop down.