28
Views
4
Comments
How can I concatenate a string and integer in aggregate filter condition.
Application Type
Traditional Web

Hi,

I have a filter search condition ( while working on aggregate feature )  where the string is concatenation of string and integer.

The value looks like in the table like this - ABC-123, BCD-456... XYZ-123.
Here is my filer condition.

SearchString = ""

 or 

Name like "%" + SearchString + "%"

or

IntegerToText(Number) like "%" + SearchString + "%"

or

(Name+ "-" +  IntegerToText(Number)) like "%" + SearchString + "%"

By this filter condition if I search by 'ABC' or 'BCD' or 'XYZ'.. I get proper result but I dont get search result by 123 or 456 ..

Even If I search by 'ABC-123' or any other string I dont get any result where as I do have the same value in the table.

It would be really appreciated if some one can help in this kind of situation of aggregate filter.

Thanks

Sam


Rank: #128

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

rwCombinedSearchString.oml

Rank: #35084

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

Rank: #437

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