cancel
Showing results for 
Search instead for 
Did you mean: 

EXECUTE QUERY ONLY IF USER IN MY LISTE

mari_outtaleb22
Participant
0 Kudos

Hi, 

HOW CAN I SET UP A QUERY THAT DISPLAYS A RESULT ONLY IF THE USERS I HAVE DEFINED IN THE LIST ARE EXECUTING THE QUERY? 

SELECT distinct  t3.docnum as 'N° Demande', t3.docdate 'Date Demande', T0.[DocNum], T0.[DocDate], T0.[TaxDate], T0.[CardCode], T0.[CardName], T0.[DocType], T1.[ItemCode], T1.[Dscription], T1.[UomCode], T1.[Quantity], T1.[OpenQty], T1.[PriceBefDi], T1.[TotalSumSy], T1.[Currency], T0.[Comments] 
FROM OPOR T0  
INNER JOIN POR1 T1 ON T0.[DocEntry] = T1.[DocEntry]
left JOIN  PRQ1 T2 ON T1.BaseEntry =T2.DocEntry
left join OPRQ t3 on t3.docentry=t2.docentry
 WHERE T0.[CANCELED] = 'N'

LIKE THIS MY QUERY IS PUBLIC, ANY ONE CAN HAVE THE RESULT.

ANY IDEAS ?

 

View Entire Topic
BattleshipCobra
Contributor

Hello Mari!

Assuming you are running this from the Query Manager?  If yes, you can just make a restricted folder that only specific users can access.

Start by opening the Query Manager:

BattleshipCobra_0-1713979259816.png

You want to click "Manage Categories" (#1) then make a new name (#2), then "Add" the new category (#3).  Return to the manage categories, find your new category and then select the "Assign Group" (#4) button.

BattleshipCobra_1-1713979409431.png

Clear all the checkboxes (#1), then select only one of the categories and click "Update".  This assigns that folder to only one specific query group.

Now you have to restrict non-Superusers from accessing that query group in authorizations.  Go to Modules >> Administration >> System Initialization >> Authorizations >> General Authorizations:

BattleshipCobra_2-1713979763397.png

Find your user (#1), search for "query generator" (#2) and scroll down to the last query group (#3).  In this case I used "Saved Queries - Group No.15" but you can restrict whichever one you want (you could also make your own query group).  Then set it to "No Authorization" for that user and then click "Update".

The user will not be able to access that folder of queries so you can put things there you don't want them to access.

If you really want to do the query route you could add a statement into your WHERE clause using the $[User] dynamic syntax.  This will give you the raw integer for your USERID (SELECT * FROM OUSR to see the user ids).:

WHERE T0.[CANCELED] = 'N' AND $[User] IN (1,2,3)

You can manually add the USERIDs to the IN clause and if the user isn't one of those it will not show any data.  This is kind of a hack workaround but I tested it and it should do what you need.

When you save the query with the $[User] dynamic syntax remember it will execute with an error if you run it in the Query Manager / Preview window.  It has to be run as an FMS for it to properly, there is no dynamic syntax when you run from the Query Manager window.

If you have V10 you can use the "Assign to Menu" option to put the query right into the "Main Menu" structure.  This is not only cleaner but also will trigger the $[User] dynamic syntax how you would like.

BattleshipCobra_3-1713980644119.png

I also have to mention that B1 Usability Package makes it much easier to format queries (add your own golden arrows and totals for example), you can also really easily restrict different functions by user (or usergroups).  It's worth looking into.

Hopefully this helps you with a couple of options,
Mike