With and Without a Supplier that is filterable

Epicor ERP 10
, ,
1

I have been asked to build a report that is filterable for VendorID, but if the filter isn’t specified, shows all part numbers from Part Bin even if there isn’t a VendorID for that part.

Is this possible? If so, how is it done?

My understanding is that those are competing joins in a baq and there isn’t a way to combine one that is wanting to restrict the whole dataset to one that wants to show everything.

2

I have to run, but this is how I would do it. It’s a hack that I use all the time now. It takes all the guesswork out of filtering.

I’ll try to explain it tomorrow.

temp_FilterTest.baq (34.0 KB)

1 Like
3

I’m running a newer version than you are. Can you send screenshots of the baq and what you did?

6

Hi Will,

Try this out!

Temppp.baq (22.6 KB)

1 Like
7

This should work for you. There’s a hack to downgrade the version.

temp_FilterTest2.baq (40.0 KB)

8

The tables and joins

This is not what you asked about, but I thought I'd explain since there is some wacky stuff here.

image

Joining PartBin to Warehouse:
image

Joining Warehouse to PartPlant
image

Criteria on PartPlant
image

Parameter

image
image1011×322 13.5 KB

The magic

This is how I do filters. I make a calculated field - typically named this obvious - and do all the logic in it.

image
image429×560 13.1 KB

All of that was in a SubQuery.

Then I make a top-level query that filters by my calculated column.

image

1 Like