Issue with linking APInvDtl with TranGLC in a BAQ.

I have a table called APInvDtl.    Not sure why you wouldn’t have that, just like you would have a table called APInvHed.     Header file and detail file for AP Invoices.

here is a screen shot showing the tables and the mapping of them.

As you can see Table 1 is APInvDtl.    that I am trying to link to TranGLC.   But not getting any errors, just no data from the TranGLC is being pulled in.

Hi Jenny,

Sorry, I meant there are no records in TranGLC table that meet the criteria TranGLC.RelatedToFile = 'APInvDtl'. We do have the APInvDtl table of course. Have a look at the other conditions like TranGLC.RelatedToFile = 'APInvExp' as @thierry.cools suggested.

Dragos

I have done a BAQ just using the TranGLC table and can see that the data needed is there.   From what I see also in the table I posted is that the APInvExp  Table is for Expensed Invoices,, invoices that are not for a purchased made on a PO.

Okay, I have figured out that the information I was given was not correct and linking the APInvDtl table.

So have tried the use of the APInvExp table.     looks like it is working except every record is duplicated.

Hoping someone would look over what I have and point in the the right direct to correct this error.

Here is the Table mapping:

And the Code:

select 
    [APInvDtl].[InvoiceNum] as [APInvDtl_InvoiceNum],
    [APInvDtl].[InvoiceLine] as [APInvDtl_InvoiceLine],
    [APInvHed].[InvoiceDate] as [APInvHed_InvoiceDate],
    [APTran].[TranDate] as [APTran_TranDate],
    [CheckHed].[CheckNum] as [CheckHed_CheckNum],
    [Vendor].[Name] as [Vendor_Name],
    [Vendor].[VendorID] as [Vendor_VendorID],
    [APInvDtl].[PONum] as [APInvDtl_PONum],
    [APInvDtl].[OurQty] as [APInvDtl_OurQty],
    [APInvDtl].[PUM] as [APInvDtl_PUM],
    [APInvDtl].[UnitCost] as [APInvDtl_UnitCost],
    [APInvDtl].[TotalMiscChrg] as [APInvDtl_TotalMiscChrg],
    [APInvDtl].[ExtCost] as [APInvDtl_ExtCost],
    [PurTerms].[Description] as [PurTerms_Description],
    [APInvDtl].[Description] as [APInvDtl_Description],
    [TranGLC].[GLAccount] as [TranGLC_GLAccount],
    [APInvDtl].[PartNum] as [APInvDtl_PartNum],
    [Vendor].[TaxPayerID] as [Vendor_TaxPayerID],
    [APInvDtl].[POLine] as [APInvDtl_POLine],
    [APInvHed].[FiscalYear] as [APInvHed_FiscalYear],
    [APInvDtl].[PackSlip] as [APInvDtl_PackSlip],
    [APInvDtl].[PackLine] as [APInvDtl_PackLine],
    [RcvHead].[EntryPerson] as [RcvHead_EntryPerson],
    [RcvHead].[ReceiptDate] as [RcvHead_ReceiptDate],
    [APTran].[CheckNum] as [APTran_CheckNum],
    [TranGLC].[TransAmt] as [TranGLC_TransAmt]
from Erp.APInvDtl as APInvDtl
left outer join Erp.APInvHed as APInvHed on 
    APInvDtl.Company = APInvHed.Company
    and APInvDtl.VendorNum = APInvHed.VendorNum
    and APInvDtl.InvoiceNum = APInvHed.InvoiceNum
    and ( APInvHed.FiscalYear = 2020  )

left outer join Erp.APTran as APTran on 
    APInvHed.Company = APTran.Company
    and APInvHed.VendorNum = APTran.VendorNum
    and APInvHed.InvoiceNum = APTran.InvoiceNum
left outer join Erp.CheckHed as CheckHed on 
    APTran.Company = CheckHed.Company
    and APTran.HeadNum = CheckHed.HeadNum
    and APTran.CheckNum = CheckHed.CheckNum
left outer join Erp.Vendor as Vendor on 
    APInvHed.Company = Vendor.Company
    and APInvHed.VendorNum = Vendor.VendorNum
inner join Erp.PurTerms as PurTerms on 
    APInvHed.Company = PurTerms.Company
    and APInvHed.TermsCode = PurTerms.TermsCode
left outer join Erp.RcvHead as RcvHead on 
    APInvDtl.Company = RcvHead.Company
    and APInvDtl.VendorNum = RcvHead.VendorNum
    and APInvDtl.PurPoint = RcvHead.PurPoint
    and APInvDtl.PackSlip = RcvHead.PackSlip
left outer join Erp.APInvExp as APInvExp on 
    APInvDtl.Company = APInvExp.Company
    and APInvDtl.VendorNum = APInvExp.VendorNum
    and APInvDtl.InvoiceNum = APInvExp.InvoiceNum
    and APInvDtl.InvoiceLine = APInvExp.InvoiceLine
left outer join Erp.TranGLC as TranGLC on 
    APInvExp.Company = TranGLC.Company
    and APInvExp.VendorNum = TranGLC.Key1
    and APInvExp.InvoiceNum = TranGLC.Key2
    and APInvExp.InvoiceLine = TranGLC.Key3
    and ( TranGLC.RelatedToFile = 'APInvExp'  )
order by Vendor.Name, APInvHed.InvoiceDate

This is what I have now, but need help getting rid of the duplication of records.   My guess is the joins, thinking I may have an issue there, but not sure what is causing it to duplicate every record, so they show up twice.