Gallery Filtering Not Working

(0) Share

Report

Posted on by simms7400

829

Hi Folks -

I have the following formula for my gallery:

SortByColumns(
	AddColumns(
		Filter(rdAssets, 
			StartsWith(Alias, TextSearchBox4.Text) || 
			StartsWith(Name, TextSearchBox4.Text) ||
			StartsWith(Target_x0020_Short_x0020_Name, TextSearchBox4.Text) ||
			StartsWith(Brand_x0020_Name, TextSearchBox4.Text) ||
			StartsWith(PF_Research_Code, TextSearchBox4.Text) ||
			StartsWith(Generic_x0020_Name, TextSearchBox4.Text) ||
			StartsWith(Development_Name, TextSearchBox4.Text) ||
			StartsWith(Alternate_Name, TextSearchBox4.Text) && !(Portfolio_x0020_Status.Value="Terminated")), 
	"txtPortfolioStatus", Portfolio_x0020_Status.Value),
	"txtPortfolioStatus", If(SortDescending1, Descending, Ascending),
"Alias", If(SortDescending1, Descending, Ascending)
)

The issue is that I'm still able to see "Terminated" values and I'm not sure why.

For instance:

Gallery Search:

pfc visible.jpg

Based on my formula, would there be any reason why this is? The field "GovernanceStatus" is a Choice field, if that makes a difference?

Thanks!

Categories:

Building Power Apps

I have the same question (0)

All responses (11)

Answers (1)

yashag2255 24,769 Super User 2024 Season 1 on at

Like (0)

Report

Hi @simms7400

Please refer to:

SortByColumns(
    AddColumns(
        Filter(rdAssets, Or(StartsWith(Alias, TextSearchBox4.Text),StartsWith(Name, TextSearchBox4.Text),StartsWith(Target_x0020_Short_x0020_Name, TextSearchBox4.Text),StartsWith(Brand_x0020_Name, TextSearchBox4.Text),StartsWith(PF_Research_Code, TextSearchBox4.Text),StartsWith(Generic_x0020_Name, TextSearchBox4.Text),StartsWith(Development_Name, TextSearchBox4.Text),StartsWith(Alternate_Name, TextSearchBox4.Text)) && !(Portfolio_x0020_Status.Value="Terminated")),
    "txtPortfolioStatus", Portfolio_x0020_Status.Value),
    "txtPortfolioStatus", If(SortDescending1, Descending, Ascending),
"Alias", If(SortDescending1, Descending, Ascending)
)

This is because whenever one of your || condition satisfies, it shows the data and ignores all the other condition.

Hope this Helps!

If this reply has answered your question or solved your issue, please mark this question as answered. Answered questions helps users in the future who may have the same issue or question quickly find a resolution via search. If you liked my response, please consider giving it a thumbs up. THANKS!

Was this reply helpful? Yes No
v-yutliu-msft on at

Like (1)

Report
Hi @simms7400 ,
Please try this formula:
SortByColumns( AddColumns( Filter(rdAssets, StartsWith(Alias, TextSearchBox4.Text) || StartsWith(Name, TextSearchBox4.Text) || StartsWith(Target_x0020_Short_x0020_Name, TextSearchBox4.Text) || StartsWith(Brand_x0020_Name, TextSearchBox4.Text) || StartsWith(PF_Research_Code, TextSearchBox4.Text) || StartsWith(Generic_x0020_Name, TextSearchBox4.Text) || StartsWith(Development_Name, TextSearchBox4.Text) || StartsWith(Alternate_Name, TextSearchBox4.Text) ,
!(Portfolio_x0020_Status.Value="Terminated")
), "txtPortfolioStatus", Portfolio_x0020_Status.Value
),
"txtPortfolioStatus", If(SortDescending1, SortOrder.Descending, SortOrder.Ascending), "Alias", If(SortDescending1, SortOrder.Descending, SortOrder.Ascending) )
Filter(table,formula1,formula2)
formula1 and formula2 is combined with "And".
Here's doc for your reference:
https://docs.microsoft.com/en-us/powerapps/maker/canvas-apps/functions/function-filter-lookup

Best regards,
Community Support Team _ Phoebe Liu

Was this reply helpful? Yes No
simms7400 829 on at

Like (0)

Report
@v-yutliu-msft

That worked perfectly, thank you so much! I now went through an updated all my galleries with tha logic!
One thing I'm noticing with one particular gallery is that when I add the "Portfolio Status" condition (to exclude), it does not show some data elements even though they are NOT "Terminated".

Here is the formula that works and displays all data elements:
SortByColumns( Filter(rdInvestments, CompoundCode = AssetBrowseGallery.Selected.Name, Or( StartsWith(ProjectAlias, TextSearchBox5.Text), StartsWith(ProjectCode, TextSearchBox5.Text), StartsWith(IND_DESC, TextSearchBox5.Text), StartsWith(IND_CODE, TextSearchBox5.Text))), "ProjectAlias", If(SortDescending1, Descending, Ascending) )
But when I add the exclusion condition, it doesn't display correctly and its excluding data elements even WITH a Portfolio Status of "Active".

SortByColumns( Filter(rdInvestments, CompoundCode = AssetBrowseGallery.Selected.Name, Or( StartsWith(ProjectAlias, TextSearchBox5.Text), StartsWith(ProjectCode, TextSearchBox5.Text), StartsWith(IND_DESC, TextSearchBox5.Text), StartsWith(IND_CODE, TextSearchBox5.Text)) && !(PortfolioStatus.Value="Terminated")), "ProjectAlias", If(SortDescending1, Descending, Ascending) )
What could be the issue?

Thanks!

Was this reply helpful? Yes No
v-yutliu-msft on at

Like (0)

Report
Hi @simms7400 ,
I notice that the choice name is "GovernanceStatus" in Access table.
Could you tell me why you use "PortfolioStatus" in formula?
Try this:
SortByColumns( AddColumns( Filter(rdAssets, StartsWith(Alias, TextSearchBox4.Text) || StartsWith(Name, TextSearchBox4.Text) || StartsWith(Target_x0020_Short_x0020_Name, TextSearchBox4.Text) || StartsWith(Brand_x0020_Name, TextSearchBox4.Text) || StartsWith(PF_Research_Code, TextSearchBox4.Text) || StartsWith(Generic_x0020_Name, TextSearchBox4.Text) || StartsWith(Development_Name, TextSearchBox4.Text) || StartsWith(Alternate_Name, TextSearchBox4.Text) ,
!(GovernanceStatus.Value="Terminated")
), "txtPortfolioStatus", GovernanceStatus.Value
),
"txtPortfolioStatus", If(SortDescending1, SortOrder.Descending, SortOrder.Ascending), "Alias", If(SortDescending1, SortOrder.Descending, SortOrder.Ascending) )

Best regards,
Community Support Team _ Phoebe Liu

Was this reply helpful? Yes No
simms7400 829 on at

Like (0)

Report

@v-yutliu-msft Thanks for your reply.

What happened was in SharePoint, I renamed to "GovernanceStatus" from "PortoflioStatus". Therefore, POwerApps only recognizes the old name unless I completely delete the SP Column and re-add.

See screen shot below:

Was this reply helpful? Yes No
simms7400 829 on at

Like (0)

Report

I have solved this, it was a syntax error on my part.

Thank you!

Was this reply helpful? Yes No
simms7400 829 on at

Like (0)

Report

Nevermind, I was mistaken. Same issue.

Was this reply helpful? Yes No
simms7400 829 on at

Like (0)

Report
This is my formula and for some reason, it's still including data elements that have a value of "Terminated" for "PortfolioStatus"

SortByColumns( Filter(rdInvestments, CompoundCode = AssetBrowseGallery.Selected.Name, Or( StartsWith(ProjectAlias, TextSearchBox5.Text), StartsWith(ProjectCode, TextSearchBox5.Text), StartsWith(IND_DESC, TextSearchBox5.Text), StartsWith(IND_CODE, TextSearchBox5.Text), !(PortfolioStatus.Value="Terminated"))), "ProjectAlias", If(SortDescending1, Descending, Ascending) )

Was this reply helpful? Yes No
Verified answer

yashag2255 24,769 Super User 2024 Season 1 on at

Like (1)

Report
@simms7400

The code is not working properly, maybe because you added the closing bracket at the wrong place, it is considering Status condition inside the Or clause. Please use the following expression and this should resolve your issue:
SortByColumns( Filter(rdInvestments, CompoundCode = AssetBrowseGallery.Selected.Name, Or( StartsWith(ProjectAlias, TextSearchBox5.Text), StartsWith(ProjectCode, TextSearchBox5.Text), StartsWith(IND_DESC, TextSearchBox5.Text), StartsWith(IND_CODE, TextSearchBox5.Text)), !(PortfolioStatus.Value="Terminated")), "ProjectAlias", If(SortDescending1, Descending, Ascending) )
Hope this Helps!

Was this reply helpful? Yes No
simms7400 829 on at

Like (1)

Report

@yashag2255 Ah yes, my bad. It was a syntax issue. Silly mistake on my part.

Thanks again!

Was this reply helpful? Yes No