Dashboard not pulling the part in

I created a dashboard to pull in parts on a pricelist that also calculate the current on-hand qty.
I thought I had it beat, seems to be working like they want until they ran across a part missing from it.
If you look at the image below, you will see that both parts are set up in the ‘CURRENT’ price list.

Here is the result of my search on the dashboard. I used a wild card to show you a few parts before it.

I will post my BAQ code below. Does anybody know what I did wrong? Or is it Epicor issue?

select [Part].[PartNum] as [Part_PartNum], [Part].[PartDescription] as [Part_PartDescription], [SubQuery2].[Calculated_Total_OnHand_Qty] as [Calculated_Total_OnHand_Qty], [PriceLstParts].[BasePrice] as [PriceLstParts_BasePrice], [PartSubs].[SubPart] as [PartSubs_SubPart], [PriceLstParts].[ListCode] as [PriceLstParts_ListCode] from Erp.Part as Part left outer join Erp.PriceLstParts as PriceLstParts on Part.Company = PriceLstParts.Company And Part.PartNum = PriceLstParts.PartNum

left outer join Erp.PartSubs as PartSubs on
Part.Company = PartSubs.Company
Part.PartNum = PartSubs.PartNum

inner join (select
[PartQty1].[Company] as [PartQty1_Company],
[PartQty1].[PartNum] as [PartQty1_PartNum],
(sum(PartQty1.OnHandQty - PartQty1.DemandQty)) as [Calculated_Total_OnHand_Qty]
from Erp.PartQty as PartQty1
group by PartQty1.PartNum,PartQty1.Company) as SubQuery2 on
Part.Company = SubQuery2.PartQty1_Company
Part.PartNum = SubQuery2.PartQty1_PartNum

Anybody have any ideas???
All help is appreciated.

Hey Everybody,

Disregard this, I figured out the issue.

The employee has never made a Qty transaction against the part in question and I have the PartQty table set as inner join.

Once I changed to left outer join, it pulled the part in fine and had a null in it’s place.

I tacked down by slowly rebuilding the BAQ a step at a time and tested each step. Once I found the point of failure, I queried the PartQty table and it wasn’t there.

1 Like