BAQ - Calculated Field - Dealing with NULL

Epicor ERP 9
1

Hi All,
I struggle with this all the time and I’m hoping someone can help me out and then I will document it so that I never forget again.

I have two scenarios and maybe the solution is the same for both.

  1. I want to create a calculated field in the BAQ where if a date field is empty (e.g. Customer.EstDate) then output some date that I define.
  2. I have an outer join between two tables (e.g. Vendor -> POHeader). If there are no associated POs then output a value I’ve supplied.

In scenario 1, I’ve tried the following with not success:
if Customer.EstDate = ?.. (this throw a syntax error)
if Customer.EstDate = “” …(this compiles fine but does not “work”)

In the end, I found the idea of comparing to an unused user defined field and this works but I feel like there is another way.

In scenario 2, I’ve tried the same two above and also:
if IsNull(POHeader.OrderDate) … (this compiled but did not work)

2

Pretty sure e9 is the same in this way, but wouldn’t ISNULL(yourValue, theOtherValueIfyourValueIsNull) work

1 Like
3

The easiest way would be to use

isnull(Customer.EstDate, '2019-08-21')
4

Darn!

In my aggravation with this, I didn’t closely look to see that I have the conditional expressions in reverse for scenario 2. IsNull works.