BAQ Issues

Epicor ERP 10
1

Im hoping someone can help i have made a BAQ code below.

 */select 
	[SerialNo].[PartNum] as [SerialNo_PartNum],
	[SerialNo].[SerialNumber] as [SerialNo_SerialNumber],
	[SerialNo].[SNStatus] as [SerialNo_SNStatus],
	[SerialNo].[JobNum] as [SerialNo_JobNum],
	[SerialNo].[PackNum] as [SerialNo_PackNum],
	[SerialNo].[PackLine] as [SerialNo_PackLine],
	[PartTran].[MtlUnitCost] as [PartTran_MtlUnitCost],
	[PartTran].[TranDate] as [PartTran_TranDate]
from Erp.PartTran as PartTran
inner join Erp.SerialNo as SerialNo on 
	PartTran.PartNum = SerialNo.PartNum
	and PartTran.Company = SerialNo.Company
	and PartTran.JobNum = SerialNo.JobNum
	and ( SerialNo.SNStatus = 'SHIPPED'  )

where (PartTran.TranDate >= dateadd (year, -1, Constants.Today))

and it shows the data im after, it just gives me a load of duplicates in the serial numbers when i copy the data to excel and check for dupes.

Thank you for any help
Cheers
D

2

Assuming it works the same as our Kinetic version - you need to bring in table PartTranSNTran as well…PartTran >> PartTranSNTran >> SerialNo

3 Likes
3

With only that criteria and those joins you will as you can have multiple transactions against the PartTran table. What are you expecting and what are you attempting to do here? What is the goal and or business objective? Please provide more details so someone might assist you better.

5 Likes
4

Again - assuming E10 is the same as Kinetic - I think the missing link is adding a table into the BAQ: PartTran >> PartTranSNTran >> SerialNo

EDIT: I think these joins would get you there

PartTranSNTran.Company = PartTran.Company
PartTranSNTran.PartTranTranNum = PartTran.TranNum
PartTranSNTran.PartNum = PartTran.PartNum

Then
SerialNo.Company = PartTranSNTran.Company
SerialNo.PartNum = PartTranSNTran.PartNum
SerialNo.SerialNumber = PartTranSNTran.SerialNumber

3 Likes
5

Thank you it was a day yesterday and reading this, this morning and looking what i had done ive sorted it out now and got it working thank you so much for the help. Think i had been looking at it for to long and went blind.

Cheers
D

3 Likes
6

Excellent…glad to help.

And I know the feeling of problem-induced myopia. Only treatment for it - set the problem aside for a while and come back to it…and focus usually returns. :eyes: :glasses:

3 Likes