Simple SQL item - Part & Price list join

Epicor ERP 10
,
1

I would like to create a dashboard that shows the sell price for a part across 4 different customer price list. I am doing a BAQ join across part, pricelstparts and pricelst but if a part doesn’t exist on on a price list the dashboard is blank. I would like info to be shown if the part is not on a price list. Can anyone give me a couple of pointers?

image
image965×372 41.7 KB

2

What are you linking by between
Part → PriceListParts
PriceLstParts → PriceLst
Part → PartWhse

3

Criteria for LeftOuter Joins might also need to allow for “or Null” type situations.

image001.jpg

4

Hi Chance,

All joins are Company & partnum apart from PriceLstParts & PriceLst which is Company & LstCode

Thanks

5

Hi Patrick,

So in a similar vane to this (copied from another query I have):

(ISNULL(Supply.Calculated_totalSupply,0.00))

6

Start deleting features / tables piece by piece to find the culprit.

7

I would start with a Distinct list of PriceLstParts to get you all the parts that you want to see in your dashboard. You dont want to see every part, just all the parts that are on a list, correct?

8

I am pulling in from Part as I need to show if part is inactive or not but part could be on 1 or more price lists.