hello developers, i have the list record with data type of text for both column. and i created the search function for the list record

when i search with text "Gender Contains M AND Active Status CONTAINS Active" in the search box, the result not appear. but if i search with "Gender Contains M" or "Active Status CONTAINS Active" the result appear. The problem is that i can't put "AND" in search text.

And i have filter in aggregate

TableName.ColumnName like "%"+Search+"%"

what is wrong with "AND", how can i solve that?


Regards, Phone.


Phone wrote:

hello developers, i have the list record with data type of text for both column. and i created the search function for the list record

when i search with text "Gender Contains M AND Active Status CONTAINS Active" in the search box, the result not appear. but if i search with "Gender Contains M" or "Active Status CONTAINS Active" the result appear. The problem is that i can't put "AND" in search text.

And i have filter in aggregate

TableName.ColumnName like "%"+Search+"%"

what is wrong with "AND", how can i solve that?


Regards, Phone.


Hi,


I don't think any issue what let me guess maybe because AND is reserved keyword in SQL.

I will try this.


Phone wrote:

hello developers, i have the list record with data type of text for both column. and i created the search function for the list record

when i search with text "Gender Contains M AND Active Status CONTAINS Active" in the search box, the result not appear. but if i search with "Gender Contains M" or "Active Status CONTAINS Active" the result appear. The problem is that i can't put "AND" in search text.

And i have filter in aggregate

TableName.ColumnName like "%"+Search+"%"

what is wrong with "AND", how can i solve that?


Regards, Phone.


Hi

I think as per my understanding you need to use advance sql like below.

Select * from Person where Age IN (@Parameter) and Name like '%'@Name'%'


Yeah, i think AND should not be reserved because it is in the text.

Phone wrote:

Yeah, i think AND should not be reserved because it is in the text.

Hey just checked its working for me



Please share the snap of aggregate condition may be a data issue.

JitendraYadav wrote:

Phone wrote:

Yeah, i think AND should not be reserved because it is in the text.

Hey just checked its working for me



Please share the snap of aggregate condition may be a data issue.


here is the snap.

use () braces for every clause before OR.

I mean like below 

(DynamicGroupRec.Title <> "" and DynamicGroupRec.Title like "%"+DynamicGroupRec.Title+"%")

Solution

Hi Phone,

You should be using the Advanced SQL here. Please note that OutSystems is generating the complete query when you use Aggregate and thus, your AND keyword is messing up. Understandably, OutSystems is using the SQL keyword to generate the Query. I hope this clarifies the need.


@Jitendra Yadav, what you are doing is local list filtering not the Aggregate call. Try to manually run the aggregate and check the result.

Solution

Phone wrote:

JitendraYadav wrote:

Phone wrote:

Yeah, i think AND should not be reserved because it is in the text.

Hey just checked its working for me



Please share the snap of aggregate condition may be a data issue.


here is the snap.

On the last line should use OR instead of AND and use () as i said earlier.


Akshay Puri wrote:

Hi Phone,

You should be using the Advanced SQL here. Please note that OutSystems is generating the complete query when you use Aggregate and thus, your AND keyword is messing up. Understandably, OutSystems is using the SQL keyword to generate the Query. I hope this clarifies the need.


@Jitendra Yadav, what you are doing is local list filtering not the Aggregate call. Try to manually run the aggregate and check the result.


I did use aggregate and apply the filter, you can also try that.

Akshay Puri wrote:

Hi Phone,

You should be using the Advanced SQL here. Please note that OutSystems is generating the complete query when you use Aggregate and thus, your AND keyword is messing up. Understandably, OutSystems is using the SQL keyword to generate the Query. I hope this clarifies the need.


@Jitendra Yadav, what you are doing is local list filtering not the Aggregate call. Try to manually run the aggregate and check the result.

Ahhh, now i understand clearly by your explanation, Thank you for your help Akshay.