Show part in a BAQ even if there is no inventory

Kinetic ERP
1

I made a BAQ that is supposed to show items that we need to keep an eye on to see if we need to buy more. The BAQ works great, except that parts that have no inventory are not shown in the results. I did some testing by adding/removing inventory of a part and confirmed that is the reason why the part does not show up. What can I add to make sure that parts with nothing in our inventory will still be shown in the results?

image

image
image1148×306 12.4 KB

Code:

 * 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  
	[Part].[PartNum] as [Part_PartNum], 
	[Part].[PartDescription] as [Part_PartDescription], 
	[Part].[PUM] as [Part_PUM], 
	[PartBin].[BinNum] as [PartBin_BinNum], 
	[PartBin].[OnhandQty] as [PartBin_OnhandQty], 
	[PartPlant].[MinimumQty] as [PartPlant_MinimumQty], 
	[Vendor].[Name] as [Vendor_Name], 
	[Vendor].[VendorID] as [Vendor_VendorID] 

from Erp.Part as [Part]
inner join Erp.PartBin as [PartBin] on 
	  Part.Company = PartBin.Company
	and  Part.PartNum = PartBin.PartNum
inner join Erp.PartPlant as [PartPlant] on 
	  Part.Company = PartPlant.Company
	and  Part.PartNum = PartPlant.PartNum
inner join Erp.Vendor as [Vendor] on 
	  PartPlant.Company = Vendor.Company
	and  PartPlant.VendorNum = Vendor.VendorNum
where (Part.PartNum = '52300000'  
or Part.PartNum = '30600000'  
etc 200+ parts)  ```
2

You need to use a left outer join from Part table to the rest.

You see you are using an inner join right now:

3 Likes
3

That was it! I never knew you could do other types of joins in the BAQ designer, this will be helpful.

4

There’s not much you can’t do for most common uses of SQL.

2 Likes