Data Reoccurring Issue

baq

(Judy Poling) #1

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.

Thank you very much for your help.

BAQ Issues.docx (86.9 KB)


(Monty Wilson) #2

Judy, what is the structure of tables within your query?

Best
…Monty.


(Judy Poling) #3

Thank you for your quick response:grinning:

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.

thank you,

JudyBAQ Issues.docx (95.6 KB)


(Monty Wilson) #4

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…

image

…or like this…

image

…depending on the relationship between part lots and PO lines.

HTH
…Monty.


(Judy Poling) #5

Monty,
I used the second way and a lot of the duplicates are gone.
But when adding in Lot#, the table got messed up again. Please see attachments.

Here is the table without lot#
image

After adding lot#, it went weird.
image

Thank you,

Judy


(Monty Wilson) #6

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.


(Judy Poling) #7

Thanks Monty, i tried to clear up the table by restructure the layer; it did not work.
My brain got smashed.

But I ran PO tables and lot tables separately, and then assemble the columns in Excel to finish this report. That’s the way I could think of.

I will keep working on it in Epicor to see if there is any logic mistakes i made to prevent futures failures.

Again, thank you very much for your help.

Sincerely,

Judy


(Monty Wilson) #8

Dear Judy,

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.

Best
…Monty.


(Judy Poling) #9

Morning Monty,

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?

image

image

image

It still shows the duplicates; did i arrange it wrong?

best,
Judy


(Monty Wilson) #10

Dear Judy,

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.


(Judy Poling) #11

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?

  1. 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.

  2. 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?

Thank you so very much!

Judy


(Monty Wilson) #12

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
  • Epicor tools training

…any and all of the above.

Best
.Monty.


(Judy Poling) #13

Hello Mounty,

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:

  1. PO info
  2. Quote info

There are two problems:

  1. 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
  2. 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

image

image

image

Appreciate your help.

Judy


(Ken Nash) #14

The join you have on PartOpr will return all PartOpr items, even if there is no PO for it. Don’t know if that is intentional.

Can you post the query phrase. That will make troubleshooting the issue quicker.


(Judy Poling) #15

image

yes, this is intentional as there may be some parts that do not have any PO log in the system, which I only need it to show part - opcode

thank you very much.


(Ken Nash) #16

could you copy and paste it in. Thanks,

I am being lazy.


(Judy Poling) #17

Sure, thanks for your help!

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’)


(Ken Nash) #18

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')

(Ken Nash) #19

I think I spoke to soon.

This might not be the best way to accomplish what you are trying to do.

Have you looked at the PartCost table?


(Judy Poling) #20

The information on the table is not accurate. I just tried.