cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Frequent Visitor

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
Highlighted
Microsoft
Microsoft

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
Highlighted
Microsoft
Microsoft

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

Highlighted
Frequent Visitor

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

Highlighted
Microsoft
Microsoft

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

 

Highlighted
Frequent Visitor

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

Highlighted
Microsoft
Microsoft

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

Highlighted
Frequent Visitor

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
secondImage

New Return to Workplace

Reopen responsibly, monitor intelligently, and protect continuously with solutions for a safer work environment.

August 2020 CYST Challenge

Check out the winners of the recent 'Can You Solve These?' community challenge!

Experience what’s next for Power Apps

Join us for an in-depth look at the new Power Apps features and capabilities at the free Microsoft Business Applications Launch Event.

Check this Out

Helpful information

Featuring samples like Return to the Workplace and Emergency Response Applications

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Users online (5,421)