cancel
Showing results for 
Search instead for 
Did you mean: 

Active User Query

Experimenter
Experimenter

I run a report for export control but how do I filter out the inactive users.  So far I have 

Query builder .png

However the query returns both 1 and 0 for status and status 0 is active.  Can you pleasae help me fix this query.

 

8 REPLIES

Re: Active User Query

Valued Contributor
Valued Contributor

I didn't have much time to dig, but we use a similar query with a few extra criteria. I don't see anything in there that is incorrect. Is it returning inactive users in a regular query or is it only in the Report?

activeuserquery.jpg

Re: Active User Query

Experimenter
Experimenter

The error is that both inactive and active users are returned in the query and also in the report.

 

Re: Active User Query

Legend
Legend

Hello,

 

I see the issue is with OR condition in combiation with Not Equal to (!=) Geography and Nationality.

 

If you are looking for all active/inactive users matching a geography/nationality, just change the concatenation operation to OR for both Geography and Nationality.

 

Matching.JPG

 

But if you are looking for all active/inactive users not matching a geography/nationality, change the concatenation operation to AND for both Geography and Nationality. But the results will depend on how the Geography and Nationality are filled for all users in the Organization. e.g., blank or mismatching values would give wrong results

 

 

NotMatching.JPG

 

Hope this helps.

 

Mahendra

Re: Active User Query

Experimenter
Experimenter
Actually the challenge is that using the status =0 gives me 1's also. I need it to properly filter all users that are active then if Nationality != US or Geography != US.
It is the status that is not giving the desired result.


Thanks,

Michael Thuman
Team Center Administrator Sullair
Sullair, LLC
Office: (219) 861-5389
Cell: (219) 214-0324
www.sullair.com
[cid:image002.png@01D1600B.6941ED10]

Re: Active User Query

Experimenter
Experimenter

Hi the issue is I am getting status of 0 and 1 and I only want 0 and geography != US or nationality != US.  The status=0 is being discarded. While the others are being properly evaluated.

 

Re: Active User Query

Experimenter
Experimenter

Using your input Or Nationality and Or Geography gives us with all users including US.

If I make And Nationality and And Geography I will get only users with both != US.

The status is not being applied at all as for with And And or And OR or OR OR I still get status =0 and status=1.  The root of the problem is getting the status=0 to be listened to.

 

Re: Active User Query

Legend
Legend

As I mentioned the query is not executing as expected just because of the OR clause.

OR clause bypasses all AND clauses. So the search result constitues all the results matching the AND clauses along with all results matching the OR clause even if they are not satisfying the AND clauses.

 

As you are generating a report, other workaround could be, just keep the status clause in the query and modify the PLMXML closure report to have a conditional clause to export the users who are matching the criteria (Nationality != US and Geography != US).

 

Mahendra

Re: Active User Query

Experimenter
Experimenter

Thanks for the explanation on the OR bypassing the Ands.  I ended up creating two queries with all ands.

I would like to know of Query Builder is built so that Ands and OR's can be logically stacked?

For example

 

(and status = 0

and Nationality != US)

Or

(and status=0

and Geography !=US)