BAQ Filter for Null

Epicor ERP 10
1

I am trying to write a BAQ that filters on a Null and I cant seem to figure out how to do this.

I would like to show only the JobOper records where my open jobs do NOT have any JobOpers.

I have a main BAQ that lists all of my Open Jobs. I then added the JobOper table and did a left join.

I see the blank fields from JobOper where there are no operations - However I would like to filter my main BAQ so the only thing showing is the Jobs that have NO JobOper Records.

Am I over thinking this? Any Help greatly appreciated.

Thanks,
DaveO
Ph: 651-246-3281

2

Here is a screenshot:

Pasted image686×665 21.8 KB

Pasted image546×594 8.01 KB

3

In Table Criteria, under the operation where it defaults to ‘=’ there is an option for ‘ISNULL’ that you can then apply to your field, whether it be the Operation or the Opr column.

4

You could add a calculated field to the BAQ that conditionally shows a value and filter on that field

5

Mr. Brian: I see the ISNULL however - when I add that as a table criteria - It does not work correctly.

I should see just 11 records and when I add the ISNULL condition to the JobSeq field on the JobOper Table, I get 559 records. I must be doing this wrong.

6

Mr. Tanner: Thank you for the suggestion.
I was able to create a calculated field that shows the right answer - however I don’t see my calculated field in the list of fields available for table criteria.

I suppose if I was going to use this BAQ in a dashboard I could apply a filter on my calculated field - however I don’t plan on using this BAQ in a dashboard.

Any other suggestions appreciated - maybe use a subreport somehow?
DaveO

7

You can add it to Subquery Criteria, which applies to the returned results. You can’t add it as a Table Criteria because it’s not a table field.

Regards
Mark

8

Dave,

It appears you would need to use a subquery and filter the criteria on the subquery. There is a feature request on the EUG board to add the ability to use SQL in BAQ designer which would make this much simpler.

Thanks,
Tanner

9

What does the query look like on the general tab? That helps with debugging. Then you don’t have to mess with a calculated field.

10

Pasted image751×622 11.5 KB

11

You are close. I only added the following subquery criteria and the left join from the header. Give this a try.

Capture.JPG1486×795 141 KB

Capture2.JPG1005×423 41.6 KB

2 Likes
12

Thank you Mr. Nash: That did the trick.
Use SubQuery condition (even though i technically do NOT have a subquery) and it works perfectly. No Calculated field needed.

You are the BAQ MAN!
Thanks again,
DaveO

13

Just added to this thread as the solution from Ken didn’t work for me. I had to a criteria, and not a sub one (although I’m taking note of that solution for future use), to say FIELD <> constant and then when the pop up box came up saying what’s the constant I didn’t type anything and just pressed ok. The BAQ screen then had “Empty string” as the constant. Then the only result I got back where when we had typed something into that field:

image
image980×159 27.9 KB

I think I remember conversations with DBAs and what really is NULL. I suppose it depends on what solution will work depending on what Epicor state the DB table structure to be.

2 Likes
14

This helped me, thank you!