Problems Displaying Last Labor Record in BAQ

[tmcmullen (Travis McMullen)]

I need to modify a dashboard that shows all of our open jobs by adding the Last/Most Recent Labor record for the Job and I’m running into an issue I don’t quite understand.

For testing purposes, I designed a simple query shown below with only the JobHead and LaborDtl tables.

This gives the following (just to show that there are multiple records).

To get the Last Record, I’m creating a calculated field giving the max for LaborDtl.ClockInDate.

Here are the results.

The user ultimately wants the employee name attached to the labor record. However, when I link in the EmpBasic table, I start getting 2 results instead of 1. Even if I just add the EmployeeNum from LaborDtl, I get 2 records, as shown below.

I can’t figure out why this is happening. I’ve tried various methods of joining the LaborDtl table and displaying my results, but the same thing keeps happening when I add the EmployeeNum. I’ve used the displayed method to pull the last record for other items such as the last shipment for a customer and last serial number transaction with success with the only difference being is that I didn’t need to display any other data from those tables.

Is this a bug (doubtful)? Is this a limitation with the BAQ Designer? Or am I not applying the correct criteria to get the last labor record along with the employee information?

[Mark_Wonsil]

To do these kinds of operations (last, first, largest, etc.), use the partition feature of SQL.

Perform a calculation on BAQ Summary Fields

I think you might be able to do this with a window function query. You can see an example with the system BAQ named: zGSTR1-B2B_InvcDtl_IN . If you change the ICE.QueryHdr.CGCode to an empty string you can load the BAQ in the designer and see how Epicor created it. What you want is to sum it over the group something like: SELECT CustNum, OrderNum, PartNum, SUM(UnitPrice*OrderQty) OVER(PARTITION BY PartNum) AS SubTotal FROM ERP.OrderDtl; There is a ton of information on window functions here:…

[tmcmullen (Travis McMullen)]

I feel like I missed a step in reading over and watching tutorials on Window Functions.

I can create the Calculated Field and use LAST_VALUE to display the last value, but it is returning all 4 rows from the initial results set that I showed, just with the Last Value listed for all 4, instead of a single row like I had hoped.

I have since solved my issue using 2 sub-queries, 1 returning the last/most recent labor record, and another bringing in the LaborDtl record again by linking the 2 together by Job Number, and my Last Clock In Date with Clock In Date. I can then display the Employee Name from EmpBasic without extra records appearing.