I have encountered this kind of issue for the last two BAQs i need to make and I really wanted to know how to solve it.
BAQ1 - Raw Material Purchase Info
The table is consist of the following columns: Part# (Raw Material) - PO# - Order Qty - Unit Cost - lot# - Spec Info (Thickness High - Thickness Low - Temper - Width).
Supposedly, each PO# would only be associated with one set of the of the spec info and one lot# is only assigned with one PO#; but the result shows differently, one PO is relate to multiple sets of specs and one lot is with multiple POs.
I attached the screenshot. This flat layer I have tried and found it did not work, so I created a sub query for Partlot, hoping to straighten up the mess. it did not work as well. Clueless…
Please let me know if you need anymore information.
Judy, when I see lines repeating in a way that doesn’t reflect the true database contents, it is usually that the tables are linked incorrectly. You might want to try breaking one of the links and re-linking one of the table combinations so the data structure is like this…
…or like this…
…depending on the relationship between part lots and PO lines.
When you click the table-link that goes into PartLot, what is the list of fields shown there? It may be that in addition to Company, PartNum, etc there could be an additional field or two added to the list (probably lot number, or else PO number and POLine, maybe even PORelNum) that would clear the extra lines being shown. I would try it on my company’s system but we don’t track lots this way.
The field you need is RcvDtl.LotNum. It makes sense that the PO receipt, not the PO records themselves, would have the lot number, because only upon receipt of items could a company be really sure what lot(s) were received. If you’ll include the RcvDtl in your dashboard then you can link that table to PartLot with LotNum as one of the matching fields, and you should see the extra lines go away.
very interesting. yesterday I found that RcvDtl contains the information that i need from PO tables.
is it the table that you think it should do the work?
It still shows the duplicates; did i arrange it wrong?
I think that is definitely the connection you need. When you click the connection between the two tables, is there a connection that says RcvDtl.LotNum = PartLot.LotNum? If not, you’ll need to add it, and I think your duplicates will disappear.
You may want to add the PO Receipt number and receipt line to your display, so when you have more than one receipt line for the same PO line, you won’t see them as duplicates.
it worked out!!! The duplicates are gone! When i only used RcvDtl.partnum = Parlot.partnum, it still had the duplicates.
I appreciate your help, you are awesome.
I thought that BAQ did not make sense sometime when having duplicates like that…lol
For future benefits, it this something that you think i should know of to better use BAQ?
Use the right table to build the reports. “Right” not necessary mean that the table contains the information needed to build the report; but it means the right logic in between the tables.
Find the key joint(s) between the tables and connect the tables through it or them.
I have not systematically learned how to make BAQ; do you have any suggestions for me?
Thanks Judy and glad you got what you need! Yes, the joins between tables will continue to show not just duplicates but inappropriate combinations if you don’t narrow each join down to a unique combination like you did in the last step when you required the lot numbers to match between the two tables. Without that, you’re asking Epicor for a list of all lots received, times the number of entries in the lot table for the given part number, all combinations.
I recommend:
Keep experimenting and building these queries
Use help sites like this one to get help from the community
I thought I could solve the reoccurring issue mysefl for the second BAQ but i failed.
The ultimate goal is to compare the most recent purchase cost against the most recent quoted cost.
However, i found that the table may be too complex to be ran by Epicor. So I split up the BAQ into two:
PO info
Quote info
There are two problems:
I cannot just bring out the most recent PO info by applying the formula in BAQ it would duplicates even if i created a sub-query
Duplicates are all over the place.
I have tried many ways to eliminate the duplicates but failed.
Do you think there is a way to combine the two set of info into one? Anyway,
here is what I have so far
select distinct
[PartTran].[JobNum] as [PartTran_JobNum],
[PartOpr].[PartNum] as [PartOpr_PartNum],
[PartOpr].[OpCode] as [PartOpr_OpCode],
[PartTran].[PONum] as [PartTran_PONum]
from Erp.PartOpr as PartOpr
left outer join Erp.PartTran as PartTran on
PartOpr.Company = PartTran.Company
And
PartOpr.PartNum = PartTran.PartNum
and ( PartTran.TranType = ‘mfg-ven’ )
left outer join Erp.PODetail as PODetail on
PartTran.Company = PODetail.Company
And
PartTran.PONum = PODetail.PONUM
And
PartTran.POLine = PODetail.POLine
And
PartTran.PartNum = PODetail.PartNum
where (PartOpr.OpCode >= ‘200’ and PartOpr.OpCode <= ‘222’)
Try adding the following on your PartOpr to PartTran join.
and PartOpr.OprSeq = PartTran.JobSeq
select distinct
[PartTran].[JobNum] as [PartTran_JobNum],
[PartOpr].[PartNum] as [PartOpr_PartNum],
[PartOpr].[OpCode] as [PartOpr_OpCode], PartOpr.OprSeq, PartTran.JobSeq,
[PartTran].[PONum] as [PartTran_PONum]
from Erp.PartOpr as PartOpr
left outer join Erp.PartTran as PartTran on
PartOpr.Company = PartTran.Company
And
PartOpr.PartNum = PartTran.PartNum
and PartOpr.OprSeq = PartTran.JobSeq
and ( PartTran.TranType = 'mfg-ven' )
left outer join Erp.PODetail as PODetail on
PartTran.Company = PODetail.Company
And
PartTran.PONum = PODetail.PONUM
And
PartTran.POLine = PODetail.POLine
And
PartTran.PartNum = PODetail.PartNum
--where (PartOpr.OpCode >= '200' and PartOpr.OpCode <= '222')
