I’m trying to learn more about BAQs, so I’ve been creating as many as I can. Some of them are working fine, but others—like the one I need help with—are giving me issues.
I’ve built a BAQ that pulls data such as serial number, part number, SN status, invoice number, and shipping quantity. The data looks correct overall, but I’m getting duplicates, which I think might be related to the line details.
It also looks like I forgot to include the invoice date. I now have all the fields I need, but the duplication is still happening. When I export the results to Excel and manipulate the data there, I can get it into a usable form—but I’d like to fix the duplication properly in the BAQ itself.
I don’t know if I need to do a sub query here but has I’ve never done anything like that before if you can point in the right direction to documents to read that would be great.
Pasted code below forgive me if its not built in the right way.
/*
* Disclaimer!!!
* This is not a real query being executed, but a simplified version for general vision.
* Executing it with any other tool may produce a different result.
*/select
[SerialNo].[SerialNumber] as [SerialNo_SerialNumber],
[SerialNo].[PartNum] as [SerialNo_PartNum],
[SerialNo].[SNStatus] as [SerialNo_SNStatus],
[ShipDtl].[PackNum] as [ShipDtl_PackNum],
[InvcDtl].[InvoiceNum] as [InvcDtl_InvoiceNum],
[ShipDtl].[OurInventoryShipQty] as [ShipDtl_OurInventoryShipQty]
from Erp.SerialNo as SerialNo
inner join Erp.ShipHead as ShipHead on
SerialNo.Company = ShipHead.Company
and SerialNo.PackNum = ShipHead.PackNum
inner join Erp.ShipDtl as ShipDtl on
ShipHead.Company = ShipDtl.Company
and ShipHead.PackNum = ShipDtl.PackNum
inner join Erp.InvcDtl as InvcDtl on
ShipDtl.Company = InvcDtl.Company
and ShipDtl.PackNum = InvcDtl.PackNum
and ShipDtl.PackLine = InvcDtl.PackLine
It doesn’t look like you are joining by your serial number. So if there are multiple serial numbers in the system, they will show up on a pack slip, even if it’s not there.
Edit: I think I’m wrong here. I haven’t used serial numbers, and don’t have any to look at in my system, so I can’t really verify.
One trick I like to use to see which table is causing the duplicates is to include the sysrowID for each of your tables, and the one that doens’t have the sysrowID duplicated is the one that has more than one row and causing your duplicates.
Instead of joining first on ShipHead, then on ShipDtl, join ShipDtl to SerialNo on Company, Packnum, Packline. Then join InvcDtl to ShipDtl on Company, PackNum, PackLine. Based on your select statement, it doesn’t look like you need fields from ShipHead at all, but if you do, then join it to ShipDtl on Company, PackNum.
Also, think about what makes a record unique, include those fields, and really interrogate those assumptions. With a little experience and knowing what kind of things can go sideways, even if everything looks fine!
Part, SN, pack, and invoice seem like they’d produce distinct records, but one pack can have multiple invoices. Weird but not impossible.
Pardon my SN ignorance, but shouldn’t the SN be related to the pack line instead of the pack head? Unless serialized parts can only be shipped one-per-pack-head but that doesn’t seem right.
As for learning BAQ, it’s just Microsoft SQL in a trenchcoat made of mouse clicks. All the T-SQL querying tutorials on the internet will be relevant, and there’s a ton of really good stuff out there.
Generally speaking, always start with the thing you want most and find the table that gives you that information in the greatest detail. Some tables give you too much though like the SerialNo vs SerialNoTrans.
If you want to know what parts shipped, then start with ShipDtl. Then connect the rest of the information you want to that table.
Banderson’s trick about using the sysrowID to find the duplicates is genius. It’s way easier than when I told you to go table by table.
I think a couple people have said the same thing but restructure to be something like this. Always use as many key columns as possible to join on a xxxDtl table or you’ll get duplicate rows.
SerialNo to ShipDtl by Company, PackNum, PackLine
ShipHead to ShipDtl by Company, PackNum
ShipDtl to InvcDtl by Company, PackNum, Packline
InvcDtl to InvcHead by Company, InvcNum
