Sorry, I wasn't clear in my response. I think that last exists() portion you added can replace the initial :USER = :assignment.laborcode portion of the condition.
The new exists() function should only resolve to true if the currently logged in user, :USER, is assigned to the current work order, :WONUM and :SITEID, via an assignment/labor record association. The existing GROUPUSER condition will resolve to true if the current user is not in the FORSC security group. So something like this could work as a condition:
exists(select 1 from assignment a inner join labor l on a.laborcode = l.laborcode and a.orgid = l.orgid inner join maxuser m on l.personid = m.personid where a.wonum = :wonum and a.siteid = :siteid and m.userid = :USER) and not exists (select 1 from groupuser where userid = :USER and groupname = 'FORSC')
I don't have the ability to test it myself against your environment, but something like that should suffice those requirements.
------------------------------
Alex Walter
A3J Group, LLC
------------------------------
Original Message:
Sent: 07-11-2023 13:22
From: Glen Holt
Subject: Query to allow only members of a specific security group to comp work in workflow
Thanks much for the reply Alex! More than likely, I've added this incorrectly into the query. The way I have it added it allows
anybody to route to COMP status. Let me know if this needs to be entered differently into the equation.
Thanks for your assistance with this.
:USER = :assignment.laborcode and not(exists (select 1 from dbo.groupuser where ((userid = :USER)) and (groupname = 'FORSC'))) and exists(select 1 from assignment a inner join labor l on a.laborcode = l.laborcode and a.orgid = l.orgid inner join maxuser m on l.personid = m.personid where a.wonum = :wonum and a.siteid = :siteid and m.userid = :USER)
------------------------------
Glen Holt
King County
Original Message:
Sent: 07-10-2023 22:30
From: Alex Walter
Subject: Query to allow only members of a specific security group to comp work in workflow
Glen,
You should be able to use a similar exists function on the assignments portion of the where clause as you did with the groupuser portion. Something like:
exists(select 1 from assignment a inner join labor l on a.laborcode = l.laborcode and a.orgid = l.orgid inner join maxuser m on l.personid = m.personid where a.wonum = :wonum and a.siteid = :siteid and m.userid = :USER)
Hope this helps,
------------------------------
Alex Walter
A3J Group, LLC
Original Message:
Sent: 07-08-2023 21:37
From: Glen Holt
Subject: Query to allow only members of a specific security group to comp work in workflow
Maximo 7.6.1.1
I have a condition in workflow with the following query:
:USER = :assignment.laborcode and not(exists (select 1 from dbo.groupuser where ((userid = :USER)) and (groupname = 'FORSC')))
The purpose of the query is to only allow a user who belongs to security group FORSC to route work to COMP status. Users that are not in this group are
re-routed back to their start center with a pop up message. This works well...but only if there is one assigned user line. If there are multiple user assignments
it allows the record to be routed to COMP status. This should be simple but I haven't sorted out yet.
How would I add to this query to look at all assignments to ensure that only an assigned user in the FORSC securtiy group can Comp?
Thanks for any assistance that can be provided!
#EverythingMaximo
#WorkManagement
------------------------------
Glen Holt
King County
------------------------------