Using Filter function for Number and Text data type

[DELACRJO]

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

[RusselThomas]

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]

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

[RusselThomas]

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]

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

[RusselThomas · Accepted solution]

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

[DELACRJO]

RT,

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

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

Regards,

Joseph