I am trying to create a BAQ which will show employees who are clocked into MES but not clocked into any jobs. I thought that I could filter on LaborHed.ActiveTrans = TRUE because the field description for ActiveTrans is:
Used by Labor collection to indicate that this LaborHed record is currently active. When the employee clocks out it is set to “No”. If record is created by Labor Entry this value is “NO”.
But if I do filter on LaborHed.ActiveTrans = TRUE then it only shows records that are clocked into jobs. That seems to contradict the field’s description.
Any recommendations on how to filter for this? If I filter by ClockInDate = Today and remove the ActiveTrans filter, I get sevveral records for each person (some active and some not) so the data isn’t meaningful.
You filtered on LaborHed.ActiveTrans = true. But if you don’t add LaborDtl, then you can’t tell if a job is clocked. Link the LaborDtl to LaborHed, then use a subquery criteria to show only records where laborDtl.JobNum is blank.
I think this would work - just has to be redone as a BAQ
WITH ActiveLabor as
(SELECT * FROM erp.LaborDtl WHERE ActiveTrans=1)
SELECT lh.*
FROM erp.LaborHed lh
left join ActiveLabor ah on lh.Company=ah.Company and lh.LaborHedSeq=ah.LaborHedSeq and lh.EmployeeNum=ah.EmployeeNum
where lh.ClockInDate>=CAST( GETDATE() AS Date ) and lh.ClockOutTime =0 and ah.EmployeeNum is null
@NateS Filtering in that way won’t work because it would return a bunch of labor records where JobNum is blank even though that person later did clock into a job. For example, here it is without the subquery filter. It would return those blank ones even though there are valid ones too. The results would not be meaningful:
I do have a dummy shop floor account set up for testing. I used it to clock into MES but did not clock into any jobs. This is what it looks like in the same query. I would expect it to show ActiveTrans but it does not:
