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.
Mr. Aaron: Is it possible you have an “Inner Join” to the LaborDtl table? That should be an Outer join.
I think this would work - just has to be redone as a BAQ
WITH ActiveLabor as
(SELECT * FROM erp.LaborDtl WHERE ActiveTrans=1)
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:
So I see no good way to filter this yet.
Is says active is false though. You should have active labor HEAD. You have that same flag on both header and detail.
@Banderson This is what the test MES clock in (without job clock in) looks like for both LaborHed and LaborDtl.
Shop Tracker does show TEST as clocked in, but both ActiveTrans values are false here in the query results.
So what field is Shop Tracker using to determine that TEST is clocked in?
Post your BAQ, something isn’t right here.
ShopActivity.baq (22.8 KB)
Your clock in time should not be 0
ClockIn time for LaborDtl is showing as 0 because there is no LaborDtl record. The ClockIn time for LaborHed is correct/valid.
This is it right here. It should look like this.
I think it’s your join to the laborDtl table. Change it to look like this.
Changing the join to look like that gives me results similar to yours:
That looks better. Now I can filter by LaborHed.ActiveTrans and then look for empty jobs.
When you were joining it to LaborDtl, you weren’t going to get the name when there was no laborDtl record. (which is what you are looking for)