24
Views
20
Comments
Solved
Search Widget - Search for multiple words in a single search bar

Hello again,


Is it possible to search for more than one word in the search bar?

For example:

Diana Paulo Maria


Thanks

Rank: #54
Solution

Hi SpiritFox,


It is possible but not out of the box.

The strategy is to have a delimiter like backspace and when the user searches, you split the search text by the delimiter to get the words (in your example, to get the words "Diana",  "Paulo" and "Maria").

After that, you have to prepare your query (SQL query) to build the WHERE clause to create the command {User}.[Name] LIKE "%Diana%" OR {User}.[Name] LIKE "%Paulo%" OR {User}.[Name] LIKE "%Maria%".


You can do that using a parameter with Expand Inline set to True.


In an OML follows an example of this approach in use and working if you want to take a look on how it is done.



Hope it helps.


Regards,
João

MultipleWordsSearchExample.oml

Rank: #2481

Hi João,

Thanks again for your help

I realized what you said but I have no idea how to do it.

Rank: #54

Hi SpiritFox,


It is a more advanced scenario and it has to be done with a SQL Query (rather than an aggregate), that's why I included a demo OML in the previous answer :)

Have you taken a look at it?


Regards,
João

Rank: #2481

João,,

I didn't see the oml file. :(

Sorry 

Rank: #2481

João,

I can't find this server action


I think I just missed it. I searched the premises ...


Trocasv1.8.oml

Rank: #54

Hi SpiritFox,


You need to add the reference to this method from the Text extension. Make sure that you have the dropdowns set to the option Show All:

 

Regards,
João

Rank: #2481

João,

Do I have to delete the filter (I had for simple search) in my database and the client action I had previously and replace it with a new one? 

I'm missing something

Can you take a look?

Trocasv1.8.1.oml

Rank: #183

Hi Spirit, on your oml looks like you copy Joao Marques actions without change to your use-case, he did an example to present you an solution to your problem, now you need to take that example and change the queries to your user-case 

for eg: in the bellow pic he is using the Espace table as example and he was querying that table, you should create the query for yours case and adapt for the table you are using




Rank: #2481

Carlos,

Thank you for your help.


I thought that the Espace table was part of the solution, wasn't an example.

I already adapted using the João example butI don't know how to make this Assign:


Rank: #54

Hi SpiritFox,


Your assign seems almost right but you're not mentioning which field you will apply the LIKE, it should be {Trocas}.[<your attribute you want to apply the search>], for example {Trocas}.[Name].

Rank: #2481

João,

Assuming that [Name] is an attribute of the {Exchanges} entitie:

I want to search  for the attributes:

Nota_Devolução

Userid_recebe

Userid_envia

Userid_regista

For example: "3" "Diana" "Paulo"


If i only put [Nota_devolução] will it look just for this attribute? 

I have to tell to look at the other attributes, right? How i do that?


Rank: #183

Hi Spirit,  I think you got the point here, but to give you more understanding, in this context everything inside {} is a entity/table eg: {Espace}, everything inside [] is an attribute from that table 

eg: "{Espace}.[Name],

So on construction of your query you need to have as many as attributes you want to search from that table

eg follow the structure created by Joao:


SQLInstruction + "{trocas}.[Nota_Devolução] LIKE '%" + String_Split.List.Current.Text.Value + "%' OR 

{trocas}.[Userid_recebe] LIKE '%" + String_Split.List.Current.Text.Value + "%' OR 


{trocas}.[Userid_envia] LIKE '%" + String_Split.List.Current.Text.Value + "%' OR 


{trocas}.[Userid_regista] LIKE '%" + String_Split.List.Current.Text.Value + "%' OR"


What means at the end of the loop your SQLIntruction would have this sequency of querys X the amount of words on the search.

OBS: On your example For example: "3" "Diana" "Paulo", you are looking for user id 3, what isnt the best approach since usually you dont know this information when you're typing to search, change your query to look for the name of the user


Cheer,

Carlos Lessa

Rank: #2481


Carlos,

"OBS: On your example For example: "3" "Diana" "Paulo", you are looking for user id 3, what isnt the best approach since usually you dont know this information when you're typing to search, change your query to look for the name of the user "

"3" is the number of Nota_Devolução not userID could be "152-Ad565" for example

Rank: #183

ok, wrong assumption from my side but you say that you want to search by this attributes in yellow and they are identifiers, what mean you can type "Diana or paulo" and you are not looking for these guys. 

To look for theses guys you need to join the "User" table  with these 3 and search to {User}.[Name] 

As you see in the pic bellow, these 3 attributes are numbers(identifiers), so "Paulo or Diana" will not find results just the number of your example that will try to find something



Rank: #2481

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

SELECT
    {Trocas}.*{User}.*
FROM
    {Trocas}{User}
INNER JOIN {User} on {Trocas}.USERID_RECEBE={User}.Username
inner join {User} on {Trocas}.USERID_ENVIA={User}.Username

Something like this?


Rank: #2481

Now i have this error:


Rank: #54

Hi SpiritFox,


Your query seems to have syntax errors, it should be something like:

SELECT
    {Trocas}.*,{User}.*, userenvia.*, userregista.*
FROM
    {Trocas}
INNER JOIN {User} on {Trocas}.[USERID_RECEBE]={User}.[Id]
inner join {User} userenvia on {Trocas}.[USERID_ENVIA]= userenvia.[Id]
inner join {User} userregista on {Trocas}.[USERID_REGISTA] = userregista.[Id]
@SQLInstruction


Some things you weren't doing right:

  • When you join a table multiple times like the case of {User} entity in this case you need to define alias to make the distinction. This is what the query above uses with userenvia and userregista;
  • Select outputs must be separated by commas;
  • Your attributes UserId_Envia, UserId_Recebe and User_IdRegista are User Ids  so you want to join them with the Id of the table User, not the attribute Username.

You need to make sure that the output structures of your query are Trocas entity and three User entities to match the output of the query (the first user entity will be referring to UserID_Recebe, the second one will be referring to UserID_Envia, the third one referring to UserID_Regista), as in the image below:

You should rename the automatic names User, User_2 and User_3 to something that makes more sense like User_Envia, User_Recebe and User_Regista, using the right panel after clicking on the output entity, like on the screenshot above.

Additionally, your SQLInstruction parameter should be filled as in the for each loop example but to match your scenario the assign inside the for each should be:


SQLInstruction + "{Trocas}.[Nota_Devolução] LIKE '%" + String_Split.List.Current.Text.Value + "%' OR 

{User}.[Name] LIKE '%" + String_Split.List.Current.Text.Value + "%' OR 

userenvia.[Name] LIKE '%" + String_Split.List.Current.Text.Value + "%' OR 

userregista.[Name] LIKE '%" + String_Split.List.Current.Text.Value + "%' OR


Like this it should work for use case, if you follow the rest of the example.


As a future improvement, you could create a structure with the fields you need and adapt your Query SELECT to that output and your query output structure would be only that structure rather than a set of entities.

Rank: #2481
Trocasv1.8.2.oml

Rank: #54

Hi SpiritFox,


I made the adjustments to the OML so it works.

Take into account that this kind of scenario is an advanced scenario and therefore it requires a "far-fetched" solution.

Note also that expand inline parameters should be avoided when possible (which is not the case here).


Trocasv1.8.2.oml

Rank: #2481