27
Views
5
Comments
Solved
Create logic using OR

Hello!

I am looking to create a query logic in which it is possible for the user to be able to search using the expression and OR logic.

For example an expression used by the user: "The cat OR The ball OR Too long".

This would return in the table all the records that contain that expression.

Is it possible to use aggregate or only with advanced query?

I would like an idea to solve this problem. If anyone can show me a way.


Thanks!

mvp_badge
MVP
Rank: #2
Solution

Hi Danilo,

As I understand it (and I think the other responders didn't get this), you want the user to be able to specify multiple conditions, right? So the actual user input may be "cat OR dog" and then everything containing either the word "cat" or the word "dog" (or both) is selected?

If that's the case, then you could take two routes. The first is using an Aggregate. Aggregates are powerful, but they can't deal with dynamic conditions, so if you walk this route, you need to limit the amount of different search terms a user can enter. Say a user may enter three search terms, and you store these in Local Variables "Term1", "Term2" and "Term3", and you have an attribute "Name" that you want to filter on, you need to add three Filters to the Aggregate, all looking like this (but with the right Local Variable names):

	Term1 = "" or Name like "%" + Term1 + "%"

Secondly, you could use a SQL query, and add a dynamic WHERE clause. You pass it as a Query Parameter, with its Expand Inline Property set to Yes. However, be very wary of SQL injections in this case, as you put user input directly in the query. See e.g. this post on how to prevent that.

Champion
Rank: #99

Hi,


You can use it inside the aggregates, in the filters:

Hope this can help.


Best regards,

Ricardo M Pereira

mvp_badge
MVP
Rank: #131

Hello Danilo,

You can also watch this video from the course Aggregates 101 with an example of filters.

Hope this can help you.

BR,

Luís

mvp_badge
MVP
Rank: #2
Solution

Hi Danilo,

As I understand it (and I think the other responders didn't get this), you want the user to be able to specify multiple conditions, right? So the actual user input may be "cat OR dog" and then everything containing either the word "cat" or the word "dog" (or both) is selected?

If that's the case, then you could take two routes. The first is using an Aggregate. Aggregates are powerful, but they can't deal with dynamic conditions, so if you walk this route, you need to limit the amount of different search terms a user can enter. Say a user may enter three search terms, and you store these in Local Variables "Term1", "Term2" and "Term3", and you have an attribute "Name" that you want to filter on, you need to add three Filters to the Aggregate, all looking like this (but with the right Local Variable names):

	Term1 = "" or Name like "%" + Term1 + "%"

Secondly, you could use a SQL query, and add a dynamic WHERE clause. You pass it as a Query Parameter, with its Expand Inline Property set to Yes. However, be very wary of SQL injections in this case, as you put user input directly in the query. See e.g. this post on how to prevent that.