Hello,
I have a BAQ where I want to add Part and PODetail. I am having trouble connecting these. I know I need Table Relations but can’t find a second one outside of Company that matches.
Add POHeader (which has VendorNum and PONum keys) between Vendor and PODetail.
You might (not necessarily, but it’s worth thinking about) join APInvHed to one of the PO tables to reduce duplication. That’s not an automatic decision since you can have an invoice without a PO, so joining it to the PO instead of the vender will filter out those misc. invoices.
Hello,
Thank you for responding.
This is what I did but it gives me: “Severity: Error, Text: Bad SQL statement.”
It looks like your join from APInvHed to PODetail is your issue.
You have VenPartNum = Vendor number
Switch the position of POHeader and PODetail and then link APInvHed to POHeader with Company and VendorNum
Then you can link Part to PODetail and link Vendor to POHeader
Your joins you will need to figure out but this should link and work everything you are looking for.
1 Like
Alternate option: include AP InvDtl. You might even be able to drop PODetail entirely. Depends on what you’re looking for specifically.
1 Like
Thank you both!
Well I ran into an issue. Apparently it’s doubling up on some of the content. Like repeated lines of the same information.
select
[APInvHed].[VendorNum] as [APInvHed_VendorNum],
[Vendor].[Name] as [Vendor_Name],
[APInvHed].[InvoiceAmt] as [APInvHed_InvoiceAmt],
[APInvHed].[InvoiceDate] as [APInvHed_InvoiceDate],
(datepart(year, APInvHed.InvoiceDate)) as [Calculated_SpendYear],
(datepart(month, APInvHed.InvoiceDate)) as [Calculated_SpendMonth],
[Vendor].[VendorID] as [Vendor_VendorID],
[VendGrup].[GroupCode] as [VendGrup_GroupCode],
[VendGrup].[GroupDesc] as [VendGrup_GroupDesc],
[Part].[PartNum] as [Part_PartNum],
[PODetail].[PONUM] as [PODetail_PONUM]
from Erp.APInvHed as APInvHed
inner join Erp.Vendor as Vendor on
Vendor.Company = APInvHed.Company
and Vendor.VendorNum = APInvHed.VendorNum
inner join Erp.VendGrup as VendGrup on
VendGrup.Company = Vendor.Company
and VendGrup.GroupCode = Vendor.GroupCode
inner join Erp.APInvDtl as APInvDtl on
APInvDtl.Company = APInvHed.Company
and APInvDtl.VendorNum = APInvHed.VendorNum
and APInvDtl.InvoiceNum = APInvHed.InvoiceNum
inner join Erp.PODetail as PODetail on
APInvDtl.Company = PODetail.Company
and APInvDtl.PONum = PODetail.PONUM
and APInvDtl.POLine = PODetail.POLine
inner join Erp.Part as Part on
Part.Company = PODetail.Company
and Part.PartNum = PODetail.PartNum
where (APInvHed.InvoiceDate >= @pStartDate and APInvHed.InvoiceDate <= @pEndDate)
Hello,
Just seeing if anyone has any insight on how to fix the duplicate lines. Thank you!
This happens when there are multiple items on a table that matches your join criteria.
You can create a test baq and start adding tables until you get the duplicate record, then try and figure out a better relationship between the tables.
The original let’s say would bring me 766 rows. When I add one table, APInvDTL it goes up to 1133 rows.
Original:
select
[APInvHed].[VendorNum] as [APInvHed_VendorNum],
[Vendor].[Name] as [Vendor_Name],
[APInvHed].[InvoiceAmt] as [APInvHed_InvoiceAmt],
[APInvHed].[InvoiceDate] as [APInvHed_InvoiceDate],
(datepart(year, APInvHed.InvoiceDate)) as [Calculated_SpendYear],
(datepart(month, APInvHed.InvoiceDate)) as [Calculated_SpendMonth],
[Vendor].[GroupCode] as [Vendor_GroupCode],
[VendGrup].[GroupDesc] as [VendGrup_GroupDesc]
from Erp.APInvHed as APInvHed
inner join Erp.Vendor as Vendor on
APInvHed.Company = Vendor.Company
and APInvHed.VendorNum = Vendor.VendorNum
left outer join Erp.VendGrup as VendGrup on
Vendor.Company = VendGrup.Company
and Vendor.GroupCode = VendGrup.GroupCode
where (APInvHed.InvoiceDate >= @StartDate and APInvHed.InvoiceDate <= @EndDate)
PO and Part Num added from APInvDtl:
select
[APInvHed].[VendorNum] as [APInvHed_VendorNum],
[Vendor].[Name] as [Vendor_Name],
[APInvHed].[InvoiceAmt] as [APInvHed_InvoiceAmt],
[APInvHed].[InvoiceDate] as [APInvHed_InvoiceDate],
(datepart(year, APInvHed.InvoiceDate)) as [Calculated_SpendYear],
(datepart(month, APInvHed.InvoiceDate)) as [Calculated_SpendMonth],
[Vendor].[GroupCode] as [Vendor_GroupCode],
[VendGrup].[GroupDesc] as [VendGrup_GroupDesc],
[APInvDtl].[PONum] as [APInvDtl_PONum],
[APInvDtl].[PartNum] as [APInvDtl_PartNum]
from Erp.APInvHed as APInvHed
inner join Erp.Vendor as Vendor on
APInvHed.Company = Vendor.Company
and APInvHed.VendorNum = Vendor.VendorNum
left outer join Erp.VendGrup as VendGrup on
Vendor.Company = VendGrup.Company
and Vendor.GroupCode = VendGrup.GroupCode
inner join Erp.APInvDtl as APInvDtl on
APInvHed.Company = APInvDtl.Company
and APInvHed.VendorNum = APInvDtl.VendorNum
and APInvHed.InvoiceNum = APInvDtl.InvoiceNum
where (APInvHed.InvoiceDate >= @StartDate and APInvHed.InvoiceDate <= @EndDate)
Which makes sense to me. As there are multiple lines/details for each Invoice Header.
Add a few more columns from the APInvDtl table you will see the differences.
If you don’t want any more details of the APinvDtl table it might a good idea to create a subquery or use window functions to “flatten” the data.
Hello,
Unfortunately, I am still having difficulty figuring out what exactly is wrong and what I need to do to stop seeing duplicate lines:
select
[APInvHed].[VendorNum] as [APInvHed_VendorNum],
[Vendor].[Name] as [Vendor_Name],
[APInvHed].[InvoiceAmt] as [APInvHed_InvoiceAmt],
[APInvHed].[InvoiceDate] as [APInvHed_InvoiceDate],
(datepart(year, APInvHed.InvoiceDate)) as [Calculated_SpendYear],
(datepart(month, APInvHed.InvoiceDate)) as [Calculated_SpendMonth],
[Vendor].[GroupCode] as [Vendor_GroupCode],
[VendGrup].[GroupDesc] as [VendGrup_GroupDesc],
[APInvDtl].[PartNum] as [APInvDtl_PartNum],
[APInvDtl].[PONum] as [APInvDtl_PONum]
from Erp.APInvHed as APInvHed
inner join Erp.Vendor as Vendor on
APInvHed.Company = Vendor.Company
and APInvHed.VendorNum = Vendor.VendorNum
left outer join Erp.VendGrup as VendGrup on
Vendor.Company = VendGrup.Company
and Vendor.GroupCode = VendGrup.GroupCode
inner join Erp.APInvDtl as APInvDtl on
APInvHed.Company = APInvDtl.Company
and APInvHed.VendorNum = APInvDtl.VendorNum
and APInvHed.InvoiceNum = APInvDtl.InvoiceNum
where (APInvHed.InvoiceDate >= @StartDate and APInvHed.InvoiceDate <= @EndDate)
Add more columns of APInvDtl to see more of those records. Guessing you have different PONum, POLines, PORelNum, PackSlip,Packline which are on the same InvoiceNum…
I see it now. Thank you.
Why does adding APInvDtl bring up more lines than without?
I guess my other issue/question is why does it show up like this:
But in the AP Invoice Tracker, it’s 2 lines that are $74.92 and $94.37 each.
Think you are only showing the amount on the header vs the lines.
[APInvHed].[InvoiceAmt]
You will need to add the line $$$ amount.
In theory, you can have partial shipments per PO release, each with their own invoice. So there isn’t a 1:1 relationship between (say) PORel and ApInvDtl. There is also a one-to-many relationship between PODtl and PORel and ApInvHed and ApInvDtl.
How to deal with this, again, depends on exactly what you’re looking to report on.
1 Like
Thank you, this has been resolved!