I’m working on converting a SQL report to a BAQ and need to understand the following and how to convert to BAQ:
If NOT isNull({orderhed.orderdate}) and IsDate({orderhed.orderdate})
then Date({orderhed.orderdate})
the part I am unsure about is the IsDate and the Date functions.
Bing AI is helpful. The AI returned this correct answer to your exact text in less time than it took me to reply! 
The
IsDatefunction in SQL is used to check if an expression is a valid date. [It returns 1 if the expression is a valid date, and 0 if it is not]. In your SQL code,IsDate({orderhed.orderdate})is checking iforderhed.orderdateis a valid date.
The
Datefunction in SQL is used to extract the date part of a date or date/time expression]. In your SQL code,Date({orderhed.orderdate})is extracting the date fromorderhed.orderdate.
When converting this to a BAQ in Epicor, you would need to create a calculated field that checks if
orderhed.orderdateis not null and is a valid date, then returns the date part oforderhed.orderdate. However, Epicor BAQs do not have a direct equivalent to theIsDatefunction in SQL. You might need to use a workaround such as checking if the year of the date is within a valid range.
When you get a BAQ that is close, show us and we can help get you over the finish line.
Thanks for the help. I have the BAQ close (attached). The problem is there are 38 lines returning 01/01/1900 (null date), but clearly have a date on the order on my end.
ON-TIMEDELIVERYTOCUST.baq (50.9 KB)
I don’t know what is going on in your BAQ. It is quite a mix! I had to remove a bunch of criteria and UD fields to get it to run on my end. But it runs ok. I think it could be that you are referencing a calcualted field that may not have a date in it for your start and end dates. I changed this to point to ShipDate, though that may not be what you want. Either way, if a parameter is filtering on that field, it must have a valid value to filter by, and a blank might throw things off.
testestetst.baq (49.4 KB)
I would write your AVGTY as PartWhse.OnHandQty - ISNULL(PartAlloc.PickedQty,0)
For Dates I also struggle with nulls, it may not be the best way but I would:
ONTIME
CASE WHEN (ShipHead.Date01) IS NULL THEN ‘NO’
WHEN ShipHead.Date01 <= NEEDBYDATE THEN ‘YES’
ELSE ‘NO’ END
Change to:
CASE
WHEN ShipHead.Date01 > 01/01/1990
and ORDERREL.NEEDBYDATE > 01/01/1990
and ShipHead.Date01 <= ORDERREL.NEEDBYDATE
THEN ‘Yes’
ELSE ‘No’
End
1 Like
For simplicity, I would suggest creating a bool calc field calling SQL IsDate, then just putting a criteria on that.
3 Likes
Was able to get the BAQ working as intended, however in the SQL report there is a dropdown that states include the parameter with option of yes, no, and only. Only the specified checkbox in true. No the specified check box is false, Yes the specified check box can be either true or false. How can this be done in the parameters of the BAQ menu?
Get the ice tools guide and the section for BAQ Designer should have an example. There are Options and filters you can add.
I don’t have access to epicusers to get the ice guide. Is it somewhere else that can be downloaded without requiring an account?
Also, I can do basic filtering and parameters. The filtering in the picture seems advanced in that depending on the input by the end user of the report, a different set of criteria will be used to retrieve the data. I may be making this more difficult than what it actually is though.
It is on EpicWeb.epicor.com and there is no where else you can get it since it is Epicor property.
You may want to look at another approach since this is not a simple baq report. I would only do reports this way.
They moved a lot of stuff into the help file.
1 Like
Crystal reports?