Search Field not In Values

Only choice is through the SQL tab on the Advanced search.  Using NOT IN (‘GBR’,’FRA’,’DEU’,’ESP’).

Which application version are you using?

In application 10 the first option you had mentioned works fine. 

I don’t think it works to exclude all four region codes.  I agree the search criteria technically works to include any that isn’t GBR plus FRA plus DEU plus ESP.  But that is going to be the same as <>GBR in reality.  What the user wants is <>GBR AND <> FRA AND <> DEU AND <>ESP which is the same as NOT IN (‘GBR’,’FRA’,’DEU’,’ESP’), which you can’t enter into the standard or advanced search fields.

@ShawnBerk  I had checked the SQL statement that would be generating if we used the  1st option mentioned in the post, <>GBR; FRA; DEU
It will output the result for the below query

select <columns> from <table> where <conditions> and (region_code<>GBR and region_code<>FRA and region_code<> DEU)

That’s why I believed it gives the required results. Could you please let me know at what point I’m making the mistake? 

@Avindu Hendawitharana 

OK - then you are saying that the operator ; is completely different in Version 10 than it is in Version 9?  I didn’t go verify in Version 10 because our Race environment was not operational.  I replied on the premise that in Version 9, the semi-colon in the standard or advanced search field is translated literally as an OR condition, not an AND condition.

If I search like this on Advanced:

It appears with SQL like this:

Where the ; is interpreted as OR.

But you are saying in Version 10, that has changed completely and now the ; is an AND operator?  That would be very confusing. In Version 9, if I enter the first condition like this on Advanced

It becomes this on SQL - where the ; is still considered an OR operator, and the not equals operator is only applied to the first term

But you are saying that  in Version 10, ; would be interpreted as AND and the not equals operator is applied to all parameters entered? ….that would seem very illogical.  When I get access to our Race 10 environment again, I will have a look, because yes, that seems wrong to me.  Something that has been that way in multiple previous versions of IFS is now considered opposite behavior….

@Avindu Hendawitharana 

Hi, Avindu, so I agree with your statement it works in V10 - but I still stand by that logic doesn’t make any sense to me.  This has become my first thing I don’t like about V10 which I hadn’t noticed before now.

The interpretation of an operator changing within a very similar search just doesn’t seem right.  Maybe if it started as <>(‘Part A’;’Part B’;’Part C’), I could see that as logical.  But not when it is like this.

@ShawnBerk 
Hi Shawn, I had checked this straightly on the app 10 environment and mentioned that it is working fine. I understand your concerns and they seem to be valid (when comparing the two versions apps 9 and 10).

If we request this as a new feature,  hopefully, the Product development team will consider applying the changes in the next version.

My query was related to Apps9.

Hi Bhavesh,

I’m afraid in In Apps 9 environment there is no way other than using SQL statement NOT IN.