Trouble getting Search to work with Filter: What's the Syntax?

[ACPBSCi]

Hi.

I have a search box that needs to display the results of the search expression. That expression is in the Items field on the Gallery and looks like this:

Search('SharePoint List Name', TextSearchBox1.Text, "Title")

That works fine. However, I already have a filter on my gallery that filters it based on the user so they can only see their entries, with the exception that anyone on the Governance Team can see all the entries. That expression looks like this:

If(User().Email = LookUp('Governance Team', Title = varUserEmail And Active = "Active").Title,'SharePoint List Name',SortByColumns(Filter([@'SharePoint List Name'],Author.Email=User().Email,StartsWith(Title, TextSearchBox1.Text)), "Title", If(SortDescending1, Descending, Ascending)))

This also works fine. What I can't figure out is how to merge them both together. I want to filter the entries so that any user will see only their own entries and that will allow anyone on the Governance Team to see all entries and be searchable in the search box.

BONUS ISSUE: I also can't figure out how to expand my search expression to also search by the Subtitle field, which displays the status (a choice column in the SharePoint list). I have found a few articles, but they are from 2018 and 2017 and things are a little different now. I can't figure it out.

Any help would be appreciated. I love PowerApps, but it's not hugely intuitive...

[CNT · Accepted solution]

@ACPBSCi Please try this,

SortByColumns(
  Filter(
    [@ 'SharePoint List Name' ], 
    (User().Email = LookUp('Governance Team', Title = varUserEmail And Active= "Active").Title ||
      Author.Email = User().Email
    ) && StartsWith(Title, TextSearchBox1.Text)
  ), 
  "Title", 
  If(
    SortDescending1, Descending, Ascending
  )
)

[ACPBSCi]

Thank you @CNT, that works beautifully! Wow, what a very different formula from the two that work independently.

So if I wanted to add a choice column to my search, what would that look like? I haven't been able to get that to work independently even. It's a SharePoint choice column, external name "Status", internal name "GovStatus". I've tried:

Filter('SharePoint List Name', GovStatus.Value=Status.Selected.Result)

Filter(, GovStatus.Value=Status.Selected.Result)

I want my users to search by either the title of the request or the status. How would I add that in? This doesn't work:

SortByColumns(Filter([@ 'Digital Health Evaluation' ], (User().Email = LookUp('Governance Team', Title = varUserEmail And Active= "Active").Title || Author.Email = User().Email) && StartsWith(Title, TextSearchBox1.Text)), "Title", GovStatus.Value=Status.Selected.Result, If(SortDescending1, Descending, Ascending))

[CNT]

@ACPBSCi Assuming that GovStatus is the Choice column name and Status is the Combobox name the code will be,

Filter('SharePoint List Name', GovStatus.Value=Status.Selected.Value)

[ACPBSCi]

I feel like I understand the code and what is happening, but I don't know why it's written the way it is or how to make changes to it. I don't know where to add this bit of code. This doesn't work:

SortByColumns(
Filter(
[@'Digital Health Evaluation'],
(User().Email = LookUp(
'Governance Team',
Title = varUserEmail And Active = "Active"
).Title || Author.Email = User().Email) && StartsWith(
Title,
TextSearchBox1.Text
)
StartsWith(TextSearchBox1,GovStatus.Value=Status.Selected.Value)
),
"Title",
If(
SortDescending1,
Descending,
Ascending
)
)

I feel like I'm throwing darts at a wall. (If you know of any resources where I can learn more about syntax in formulas for PowerApps, I'd love to hear it.)

[CNT · Accepted solution]

@ACPBSCi 

SortByColumns(
  Filter([@ 'Digital Health Evaluation'], // Filter the SP List
    (User().Email = LookUp('Governance Team', Title = varUserEmail And Active = "Active").Title || // Check if the current user is in the Governance Team and Active or if the current user is the Author
        Author.Email = User().Email
    ) && 
    StartsWith(Title, TextSearchBox1.Text) && // Filter Title based on Text in TextSearchBox1
    GovStatus.Value = Status.Selected.Value // Filter GovStatus based on the selection in the Status combobox
  ), 
  "Title", 
  If(SortDescending1, Descending, Ascending)
)

I've added some comments to help you.

[ACPBSCi · Accepted solution]

FINALLY GOT IT!!!

The Status field I want to search on is built on a SharePoint choice column, but it populates the Subtitle field in the gallery:

When I revised the code to StartsWith(GovStatus.Value,TextSearchBox1.Text), it didn't produce any results. I realized I didn't want to filter by Title AND by Status, I wanted one OR the other. I revised the code as follows:

SortByColumns(
Filter(
[@'SharePoint List Name'],
(User().Email = LookUp(
'Governance Team',
Title = varUserEmail And Active = "Active"
).Title || Author.Email = User().Email) && StartsWith(
Title,
TextSearchBox1.Text
)
Or StartsWith(GovStatus.Value,TextSearchBox1.Text)
),
"Title",
If(
SortDescending1,
Descending,
Ascending
)
)

And it works! Thank you for your help and for your notes.