216
Views
13
Comments
Solved
search for tect in any order
Question

I'm trying to see if there is a way to filter on a text in any order. 

I.E. I have done the normal thing and set up a filter that is: 

SyntaxEditor Code Snippet

attributeA like "%" + SearchText + "%"


So lets say attributeA has a really long sting like "38mm product b24 with some kind of ancillary material". If I were a user I could search for key words in this string as long as they were in order by inputting into the search box "38%b24%ancillary" and this would return a value, however if a user put this out of sequence I.E. "ancillary%38%b24" they would get no results. 


Is there anyway to make a filter that returns words in any order as long as the have a "%" between them? Or something similar. 

mvp_badge
MVP
Rank: #62
Solution

Hi Talis,

As was already mentioned in other posts above, if you use an aggregate you have to know the maximum number of tokens. Also mentioned was that you should split the search string and build the WHERE clause with that.

So, see if this works for you:

  • Use an SQL query instead of an aggregate
  • Use String_Split to have a list of tokens
  • Using that list of tokens, build the WHERE clause to use in the SQL query

See attached oml for a possible solution.

Cheers,

José

TestingSearchWords.oml

mvp_badge
MVP
Rank: #15

Hi,

You can split the the input string from the user and iterate the list of strings and for each one do atributeA like "%" + split string element +"%" and concatenate them all with an or.

Hope this helps,

Marcelo

mvp_badge
MVP
Rank: #15

Hi,

There is a extension called Text that have a action called String_Split

Regards,

Marcelo

mvp_badge
MVP
Rank: #15

Hi,

Created an example for you.

Hope this helps.

Regards,

Marcelo

Example.oml

mvp_badge
MVP
Rank: #6

Hello Talis, if you are using SQL and your database is SQL Server, you can use "Contain", like in the example below:

CONTAINS(Column, ' First_term OR Second_term OR Third_Term OR etc ')

In this case, you can pre-process the search string, with the split, make a new string in the correct format and pass as input parameter to the SQL with the ESCAPE set to no (inline parameters) and use it inside the query like this:

WHERE
     CONTAINS(Column, @search)

Cheers,
Eduardo Jauch

mvp_badge
MVP
Rank: #15

Hi,

You can only use CONTAINS if the column is full-text indexed. And for that you need to do a change directly on the database. If anyone decides to use this approach check the link below to create the index.

https://www.outsystems.com/forums/discussion/8934/how-to-install-configure-and-use-full-text-search/#Create2

Best Regards,

Marcelo

mvp_badge
MVP
Rank: #62
Solution

Hi Talis,

As was already mentioned in other posts above, if you use an aggregate you have to know the maximum number of tokens. Also mentioned was that you should split the search string and build the WHERE clause with that.

So, see if this works for you:

  • Use an SQL query instead of an aggregate
  • Use String_Split to have a list of tokens
  • Using that list of tokens, build the WHERE clause to use in the SQL query

See attached oml for a possible solution.

Cheers,

José

TestingSearchWords.oml

mvp_badge
MVP
Rank: #15

You can do it like this

SearchClause + "{Product}.[Name] like '%" + EncodeSql(String_Split.List.Current.Text.Value) + "%' and {Brands}.[Name] like '%" + EncodeSql(String_Split.List.Current.Text.Value) + "%'"

Also were is a good place to learn a bit about SQL:

https://www.w3schools.com/sql/sql_intro.asp

Rank: #30411

thank you.