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:

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].

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?

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

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

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

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?

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.

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).

Thank you