BAQ help needed

,

Hi I have made a BAQ which is working no problem at all its a simple one but dose the job. Im using PartTran and PORel for a goodsin BAQ Report now here is the Question Finance has seen the BAQ and they like it but have asked for invoice number for out subcons to be added, but i seem to be having a massive issue linking the Invhead, but im not sure if thats is even the right one to use.

Any pointer would be amazing thank you
D

can you show what you have now? Where are you starting from?

Think you would just join APInvHed to PartTran
(Company = Company AND VendorNum = VendorNum AND InvoiceNum = InvoiceNum)

1 Like

… and I’m assuming you want APInvHed (invoices paid to Vendors). InvcHead is the AR side (Invoices sent to customers).

1 Like

this is what i have at current

/*
 * 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 
	(case 
     when PORel.TranType='PUR-STK' then 'Inventory' 
     when PORel.TranType='PUR-SUB' then 'Sub-contract' 
     else 'Other'
 end) as [Calculated_TranType],
	[PartTran].[PartNum] as [PartTran_PartNum],
	[PartTran].[TranDate] as [PartTran_TranDate],
	[PartTran].[PONum] as [PartTran_PONum],
	[PartTran].[PackSlip] as [PartTran_PackSlip],
	[PartTran].[TranQty] as [PartTran_TranQty],
	[PartTran].[LotNum] as [PartTran_LotNum],
	[PartTran].[ExtCost] as [PartTran_ExtCost]
from Erp.PartTran as PartTran
inner join Erp.PORel as PORel on 
	PartTran.PONum = PORel.PONum
	and PartTran.POLine = PORel.POLine
	and PartTran.PORelNum = PORel.PORelNum
where (PartTran.TranType like 'PUR%')
order by PartTran.SysRevID Desc

I have a similar query. The vendor’s invoice number is in the RcvDtl table (I link by Company, PONum, POLine, and PORelNum to the PORel table)

1 Like

You can just bring APInvDtl here. Do another join PORel with APInvDtl using PONum, POLine and PORelNum fields.

1 Like