BAQ - Get records where one field is unique

Epicor ERP 10
1

I’ve looked for a solution and havent found anything relevant.

My goal is to make a BAQ to show only employess who’ve had labor activity in the last X days

I am using LaborDtl to filter the createdate then trying to link that to EmpBasic to get the name.

It’s effective but I’d prefer to not have loads of duplicate names on the result list. I’ve tried to finagle it with join types and even played with a subquery (which I have no experience with) to no avail.

I think if I had to, I could call that BAQ from code and delete duplicate rows from the results table as a last resort. Any thoughts or advice? I’d mention I don’t have access to the SQL server so creating a view is out of the question.

2

Chris,

If you use a sub-query to return the last labor transaction date, you can then filter that to your criteria. Then you can link that sub-query to your employee record so that you only receive a single line for each employee.

Jim Rogers

1 Like
3

You are in 10 aren’t you?.. Just use Group By SQL is awesome like that

4

Thanks for the replies.

Ok Jose, let’s assume for a minute that I’m an idiot…

How can I do a groupBy? I click GroupBy on the field I want in Display Fields but then I get this error:

Severity: Error, Table: , Field: , RowID: , Text: Column ‘Erp.LaborDtl.EmployeeNum’ is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

Jim, I need more than just the last because multiple people can work on the same job. I did play around with that idea though using SubQueries but I found I lack the training to be effective up to this point. I think I got the gist of it, but ultimately couldn’t sort it out.

5

Quick and dirty. Import and revise as needed.

LaborActivity.baq (22.2 KB)

1 Like
6

Works like a champ! Thanks a ton. Now to figure out what you did. Feel free to break it down.:smile:

I am especially puzzled about where Calculated_LaborDate is defined. I expected I could select it and open it with the Calculated Field Editor to see what was happening but nothing showed.

Okay - I see. I was looking at the wrong sub Q. I guess that field has to be on the other as a place holder. On the proper sub Q (Activity) I can see it’s just the count of the records: Count(LaborDtl.PayrollDate)

7

When you are using Group by you need to Group By ALL non calculated fields.

8

@josecgomez Then how does really know what I want to group by?

9

Damn it @Chris_Conn I am not going to teach you SQL :grin:

Don’t make me usa a let me google that for you

1 Like
10

I’m really regretting not getting you a valentine’s gift now. Better late than never:

1 Like
11

2 Likes
12

Get a room…

(I’m leaving all of my SQL puns on the TABLE…)

2 Likes
13

ok you twisted my arm…here is my take… (no need for subqueries if you use group by… not to say subqueries are wrong… but there are many ways to skin a cat)
LaborActivityThing.baq (16.1 KB)

Add LaborDtl and link it to EmpBasic

Filter the LaborDtl by a Param for Number of days… so you can say Give me Employees with Activity in the last X days

Pasted image1043×217 8.34 KB

Then simply display the fields you want and group by them (to eliminated duplicates)

Then use a calculated field (for the heck of it) to show you how many LaborDtl Entries they created in the given period

Pasted image871×475 26.3 KB

The result is just the Employee Name and Info with a Count of Labor Transactinos

1 Like
14

MIne is not filtering all duplicates:


image.png874×92 3.18 KB

15

Becasue your Create Date is different for Each Employee …
Group By Groups by the Common Fields.

16

So subqueries for my needs in this case?

17

Do you need the actual date? Of the LaborDtl created? If so you’ll always have duplicates no matter what…

18

I don’t suppose I need to but the reason I did is because since we didn’t clock in/out regularly the payroll date was unreliable (at least in MES)

If were using payroll date, couldn’t I still have dupes if the range spanned 2 payroll dates?

19

I thought you said you are trying to get Employees that have activity in the last X Days… is that what you want? If so the query I provide you does that without subqueries… I do use the CreateDate or PayrollDate but only to filter the X days… (if that makes sense)
Or am I misunderstanding?

20

No you got. And I follow using the date for the filtering. But assuming we just used PayrollDate as opposed to the CreateDate, couldn’t there still be duplicates?

EMP, JOB1, PAYDATE1
EMP, JOB2, PAYDATE2