Show null value

Epicor ERP 10
1

Hi all,

I have a BAQ that shows the bin number and OnHandQty derived from ‘PartBin’ based on user entered part number parameter. Since most parts are located in multiple bins, I have another ‘PartBin2’ table that shows the bin number and OnHandQty of the 2nd bin.
The problem is, if a part exists in only one bin, i.e. if there is no 2nd bin, then the BAQ doesn’t show any results.
I’ve tried left joints and right joints, none work.

I have already applied these filters and failed:
OR where partbin.binnum = ’ ’
OR where partbin.binnum = 0
OR where NOT partbin.binnum ISNULL
OR where NOT partbin.binnum <> ’ ’
OR where NOT partbin.binnum <> 0

I have also tried to use these calculated field and failed:

case when PartBin2.BinNum = ‘0’ then ‘x’ else PartBin2.BinNum end
case when PartBin2.BinNum = NULL then ‘x’ else PartBin2.BinNum end
case when PartBin2.BinNum = ’ ’ then ‘x’ else PartBin2.BinNum end

I would like to see the results with the binnum and qty, and blank results on binnum2 and qty2 if it doesn’t exist.

Any help would be much appreciated.

Thanks
hymal7

2

could you print screen your BAQ table names and links here?

3

Sounds like you need to make the joins to your separate PartBin tables left joins. That shows all rows regardless of whether there’s any match in the second table, whereas the default inner join shows only rows where there’s something in both.

4

OK so without going into details,
When I enter the part number in the parameter field, it shows the bin number under “Bin”, its “OnHand” Qty and the balance after each transaction.

I added Bin2 and OnHand2 based on “PartBin2”. which shows info from other bins.

If other bins dont exist at all, i get no results in the baq.

image
image.png998×432 20.1 KB

image
image.png885×506 24.4 KB

5

don’t you need a brackets to put the first 3 logical statement together against the OR (last one)?

image
image.png1000×524 30.6 KB

6

thanks, tried but didn’t work.

7

More likely brackets around the “Or” conditions?

8

thanks, tried, also failed.

9

I’m a bit puzzled why you need any subquery conditions at all, to be honest. Maybe I’m missing part of what you want to achieve, sorry.

10

ya this BAQ runs around in loops a bit based on what I am looking for, hence the subqueries

11

please copy and paste in the query phrase. I am guessing that you will need to make a subquery for partbin.

That way you can show all bins for the part instead of having to keep adding to the BAQ with criteria’s’.