Hi Sam,

there's nothing wrong with your filter that would explain why the search returns nothing.  I had a try and it just works as expected, see attached oml.

But you don't need all these conditions, it is sufficient to say : 

SearchKeyword = ""
 or 
SomeEntity.Name + "-" + IntegerToText(SomeEntity.Number) like "%" + SearchKeyword + "%"

On a different note, this type of complex filter conditions come from a discrepancy between how you store data and how you want to represent data to your users.  To my feeling, that might be a bad design decision.  Of course there might be legitimate reasons for it, but if apparantly the alpha part + the dash + the numeric part are seen as one thing by the user (such as maybe a license plate number) why would you store the individual parts in separate attributes in your entity ??  I can see that you might want to enforce certain constraints to input, but that's not the way to do it, i think.  

If you really need these separate attributes in your database, consider reduntantly storing the combined string, and execute queries against that one.

Dorine

Hi Dorine,
Thank you so much for your quick response. Yes, you are right I dont need all these conditions. The below is good enough.

SearchKeyword = ""
 or 
SomeEntity.Name + "-" + IntegerToText(SomeEntity.Number) like "%" + SearchKeyword + "%"

But still the IntegerToText(SomeEntity.Number) is not working for me in filter condition. This is just an example what I am facing and did share here but in our real application we have different attributes ( like serial number, Code Number, name) we are storing differently in db according to our project need, Serial Number is Integer which I need to convert to Text ). All together becomes an unique combination number which we dont store into database but shows to user for reference number as example ABC-123, BCD-456 etc.

We appreciate your suggestions and help. I wish IntegerToText(SomeEntity.Number) works properly in this kind of situation otherwise we have to look for some alternative approach.

Thanks again for your help.

Thanks
Sam

Hello Sam and Dorine,

From what I understood, the SearchString is a previous concatenation of a string and integer. So, maybe,  I guess you may have two input fields. What I mean is that the source comes from two different places.

So it may go like: "xxx-123" or "-123" or "xxx-". The SearchString is already a text attribute, even if you only send a number because it concatenates with the "-".

If, in the Database, the name goes already defined in a single attribute, it should look like this: ABC-123, BCD-456... XYZ-123 

So, when you do the filter, you just need to care about:

SearchString = ""

 or 

Name like "%" + SearchString + "%"

The extra filters will not be necessary in this case (from what I understood). 

(Note that if you send everything "ABC-123" in the String and nothing in the integer field, you might be getting empty results because you will be searching for "ABC-123-" with the extra "-".) 

Also, in your first post, after seeing that Name and Number attributes are smaller than the concatenated string, you might be getting less results because the order would be :

SearchString like "%" + Name  + "%"  instead of 

Name like "%" + SearchString + "%" (this will be false because Name is 'ABC' and SearchString is 'ABC-123')

Second Case: (most likely)

If in the Database, the information is stored differently (across different attributes), my advice would be to create a new attribute in the aggregate, instead of doing functions in the filter. Like this:

The concatenation of attributes will generate the unique combination that you want for each record. And, since you are mixing numbers with strings, this calculated attribute will be a string.

At this point, you can do the search directly to that new attribute:

SearchString = ""

 or 

Attribute1 like "%" + SearchString + "%"

I hope this helps, and that you find the easiest solution for your application.

Cheers,

Rúben