After the transition to MySQL8 in Apricot, dates in the system are no longer stored as null values, but as 00-00-0000. Query filters in ARR do not see this is as a null date, but the variables and filters within the report do.
As a result, "[date field] is null" filters will not work as expected in Apricot Results Reporting, specifically in query filters. To effectively put this kind of filter in place, we recommend using the construction: "[date field] less than 1/1/1900" instead. This will guarantee that the report is filtering for not only 00-00-0000 values, but any other values that might be considered blank or null.
Here is an example of a query filter with the recommended filter in place:
How can I be sure my older reports are functioning properly?
First: open the report and access the query here:
We will need to check the query filters for every query in place on your report. In this example, the report has 6 queries.
In this first query, we can see that there is a "[date field] is null" filter – in this case, the date field in question is Check-Out Date_787.
As stated above, this will need to be updated to ensure it functions properly moving forward. Our recommendation is to change them to "[date field] is less than 1/1/1900", as seen in the first screenshot of this article.
Once the query filters are set up correctly, the data can be handled within the report as normal. Despite the value discrepancy, outside of the query itself, Results is able to discern that these 00-00-0000 values are null values, and formulas like "isnull()" will work as expected.