Results Reporting | Understanding Blank Date Fields
Updated over a week ago

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:

Query Filters 
Check-out Date 787 Less than v 1/1/1900 
Legal Name_151 Different from pattern v %Fake%

How can I be sure my older reports are functioning properly?

First: open the report and access the query here:

apricot Welcome: 
Daily Check-WCheck-out 
Wellness Checks 
Meal TrackerfDietary Need. 

We will need to check the query filters for every query in place on your report. In this example, the report has 6 queries.

Add ue 
Daily Check In/Out Wellness Checks Dietary Needs 
apricot _ 111848 Individuals_14.unx 
Type here to filter the tree 
apricot _ 111848 Individuals_14.unx [unx] 
> Individuals_14 
Rooms & Beds 
Bed Night Date 
Result Objects 
Record Id 
Bed # 6976 
Floor 5902 
Query Filters 
Legal Name_151 
Bed Number 5360 
Room # 6972 
AKA Aliases Nickna... 
Check-ln Date 786 
Room Type_5881 
Record Id 
Service Site 
Check-out Date_787 Is Null v @ 
Legal Name_151 Different from pattern 

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.

Did this answer your question?