cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
DELACRJO
Level: Powered On

Using Filter function for Number and Text data type

Hi,

 

I have a gallery that shows ID(Number type)  and Title(Text type). I got text input for each one to filter the data. I was able to filter the ID but Title didn't work. Here's the  formula in the items property of my gallery.

 

Filter('Observation Reports Test List', ID = Value(SearchId.Text), StartsWith(Title,SearchTitle.Text))

 

Appreciate your advice.

 

Thanks in advance.

 

Joseph

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: Using Filter function for Number and Text data type

 

Ok, I think I'm making an assumption that might be wrong.

 

I'm assuming ID is unique.  Is it?

If it is unique, then searching by Title is only relevant when you don't have an ID, meaning the two filter conditions would be mutually exclusive, not inclusive - i.e.

 

If there's a value in SearchId.Text, then you can ignore the value in SearchTitle.Text.  Effectively you would need to be running two separate filters, not a logical AND or OR within one filter.

 

Try this;

Switch(
    IsBlank(SearchID.Text && SearchTitle.Text);
    true;  //If both inputs are blank, just show the whole list
    ObservationReportsTestList; 
    false;  //if one is blank the result is false, now check which is blank and filter by the other one - ID takes precedence so that's the condition we check
    If(   
        IsBlank(SearchID.Text);
        Filter(
            ObservationReportsTestList;
            StartsWith(
                Title;
                SearchTitle.Text
            )
        );
        Filter(
            ObservationReportsTestList;
            ID = Value(SearchID.Text)
        )
    )
)

If ID is NOT unique, then your current filter should work fine, assuming you have rows with the same ID and different titles. 

 

Please substitute the semi-colons for commas, turns out my region is all whacked for some reason 🙂

 

Hope this helps

 

RT

 

View solution in original post

6 REPLIES 6
Super User
Super User

Re: Using Filter function for Number and Text data type

Hi @DELACRJO ,

 

When you say it doesn't work, do you mean it comes back empty or it gives you an error?


Syntactically, I think functions have been updated to use semi-colons now, but not sure if that's relevant here.  Previously I think we used to use && to join logical conditions, but now it's something like this;

Filter('Observation Reports Test List'; ID = Value(SearchId.Text); StartsWith(Title;SearchTitle.Text))

You may also be getting delegation issues with the formula which might affect the outcome of the filter.

 

Kind regards,

 

RT

DELACRJO
Level: Powered On

Re: Using Filter function for Number and Text data type

Hi RT,

 

It gives an empty result.

 

I tried changing comma to semi-colons but it gave me a syntax error.  I only have less than 30 records, so I think the delegation won't be an issue right now.

 

Thanks

Super User
Super User

Re: Using Filter function for Number and Text data type

Yeah, not sure where the semi-colons entered the picture, I just saw it now when I logged into mine - maybe it's something else that determines the syntax, but it's fairly new to me.  Stick with what it's happy with 🙂

 

Can you try them separately?

Filter('Observation Reports Test List', ID = Value(SearchId.Text))

 and 

Filter('Observation Reports Test List', StartsWith(Title, SearchTitle.Text))

Then, if everything works, try 

Filter('Observation Reports Test List', ID = Value(SearchId.Text) && StartsWith(Title,SearchTitle.Text))

Kind regards,

 

RT

 

DELACRJO
Level: Powered On

Re: Using Filter function for Number and Text data type

The first two works fine but the third formula doesn't give any result when I enter a value in the Title.

I also tried this:  Filter('Observation Reports Test List', ID = Value(SearchId.Text) || StartsWith(Title,SearchTitle.Text))

but I got the same result

Super User
Super User

Re: Using Filter function for Number and Text data type

 

Ok, I think I'm making an assumption that might be wrong.

 

I'm assuming ID is unique.  Is it?

If it is unique, then searching by Title is only relevant when you don't have an ID, meaning the two filter conditions would be mutually exclusive, not inclusive - i.e.

 

If there's a value in SearchId.Text, then you can ignore the value in SearchTitle.Text.  Effectively you would need to be running two separate filters, not a logical AND or OR within one filter.

 

Try this;

Switch(
    IsBlank(SearchID.Text && SearchTitle.Text);
    true;  //If both inputs are blank, just show the whole list
    ObservationReportsTestList; 
    false;  //if one is blank the result is false, now check which is blank and filter by the other one - ID takes precedence so that's the condition we check
    If(   
        IsBlank(SearchID.Text);
        Filter(
            ObservationReportsTestList;
            StartsWith(
                Title;
                SearchTitle.Text
            )
        );
        Filter(
            ObservationReportsTestList;
            ID = Value(SearchID.Text)
        )
    )
)

If ID is NOT unique, then your current filter should work fine, assuming you have rows with the same ID and different titles. 

 

Please substitute the semi-colons for commas, turns out my region is all whacked for some reason 🙂

 

Hope this helps

 

RT

 

View solution in original post

DELACRJO
Level: Powered On

Re: Using Filter function for Number and Text data type

RT,

 

Yes, I'm using the unique ID field. So I need to create a separate filter for a unique value Smiley Happy

 

The formula works fine now. Thanks a lot for all the help.

 

Regards,

 

Joseph

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 (Last 30 Days)
Users online (5,741)