cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
simms7400
Level 8

Assistance with Search Function : Need to add in a condition

Hi Folks -

I have the following Function that returns all items in this source for the specified columns:

Search(rdTargetChangeReq,SearchBox.Text,"Alias","Name")

However, I need to add in a condition or filter.

I need to layer in "...where the column "RequestStatus" does not equal "Terminated"...

 

Can someone help me marry that in or explain how?

 

Thank you!

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: Assistance with Search Function : Need to add in a condition

@simms7400 

So you just need to add that condition...

SortByColumns(
Filter(rdTarget, StartsWith(TargetAlias, TextSearchBox4_1.Text) && !(RequestStatus.Value="Terminated")),
"TargetAlias", If(SortDescending1, Descending, Ascending))
_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up. Solved your problem? - Click on Accept as Solution. Others seeking the same answers will be happy you did.

View solution in original post

10 REPLIES 10
Super User
Super User

Re: Assistance with Search Function : Need to add in a condition

@simms7400 

 

Try this in your formula:

Search(Filter(rdTargetChangeReq,!RequestedStatus="Terminated"), SearchBox.Text, "Alias", "Name")

That will filter the source first and then perform the search on the other two fields.

If the RequestedStatus column is a choice column, then you will need to use RequestedStatus.Value in that formula.  But if it is text, then use as it.

 

I hope that is helpful for you.

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up. Solved your problem? - Click on Accept as Solution. Others seeking the same answers will be happy you did.
simms7400
Level 8

Re: Assistance with Search Function : Need to add in a condition

Thank you!  It is a Choice field but when I added .Value, it's saying that is not valid.

 

Any idea?

Super User
Super User

Re: Assistance with Search Function : Need to add in a condition

@simms7400 

When you are typing the RequestedStatus and then the period, do any suggestions come up in the suggestion area?

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up. Solved your problem? - Click on Accept as Solution. Others seeking the same answers will be happy you did.
simms7400
Level 8

Re: Assistance with Search Function : Need to add in a condition

As a quick follow up, this works:

Search(Filter(rdTargetChangeReq,Not(RequestStatus="Terminated")), SearchBox.Text, "Alias", "Name")

BUT, its only returning a handful of rows when it should be returning 162 (all elements that DO NOT equal "Terminated". Do you have an idea on why this may be limiting me more than it should?

Super User
Super User

Re: Assistance with Search Function : Need to add in a condition

@simms7400 

Sorry about the parens...was typing on the fly...should have been:

Search(Filter(rdTargetChangeReq,!(RequestStatus.Value="Terminated")), SearchBox.Text, "Alias", "Name")

If your RequestedStatus column is really a choice column, then you will need to be checking the Value of that column.

In your last formula you posted, you would have been checking the RequestedStatus to no be "Terminated".  It would never be.  However, the RequestedStatus.Value could be "Terminate" or other.

 

Also, what is your data source and how many items are in it?

 

 

 

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up. Solved your problem? - Click on Accept as Solution. Others seeking the same answers will be happy you did.
simms7400
Level 8

Re: Assistance with Search Function : Need to add in a condition

Hi -

Ah I see! That did the trick. RequestStatus<>"Terminated" also works too which I had first.

Thank you for all of your help - greatly appreciated!!!

Super User
Super User

Re: Assistance with Search Function : Need to add in a condition

@simms7400 

I'm highly suspicious of the RequestStatus <> "Terminated" (or anything other than RequestStatus.Value...).

That would not work if it is a Choice column.

But, if you've got things working, then sounds like you're heading in the right direction.

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up. Solved your problem? - Click on Accept as Solution. Others seeking the same answers will be happy you did.
simms7400
Level 8

Re: Assistance with Search Function : Need to add in a condition

@RandyHayes  - me again, I'm sorry!

 

Based on your help, I was able to finish one form and it's working as expected.  However, I built a new form and I believe I got myself into a conundrum.

 

Here is my current code:

SortByColumns(Filter(rdTarget, StartsWith(TargetAlias, TextSearchBox4_1.Text)), "TargetAlias", If(SortDescending1, Descending, Ascending))

However, I need to layer in the same thing as what you helped me yesterday.

 

"where RequestStatus <> Terminated".  I have tried to layer it in but it keeps failing.  Could you assist?

Thank you!

Super User
Super User

Re: Assistance with Search Function : Need to add in a condition

@simms7400 

So you just need to add that condition...

SortByColumns(
Filter(rdTarget, StartsWith(TargetAlias, TextSearchBox4_1.Text) && !(RequestStatus.Value="Terminated")),
"TargetAlias", If(SortDescending1, Descending, Ascending))
_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up. Solved your problem? - Click on Accept as Solution. Others seeking the same answers will be happy you did.

View solution in original post

Helpful resources

Announcements
thirdimage

Power Automate Community User Group Member Badge

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

sixthImage

Power Platform World Tour

Find out where you can attend!

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

fifthimage

Microsoft Learn

Learn how to build the business apps that you need.

Top Kudoed Authors
Users Online
Currently online: 203 members 6,423 guests
Please welcome our newest community members: