Issue with filtering on date column when data contains some empty values for that column

[Michael Evans]

Hi,

I'm running into an issue when trying to use Query Builder to filter data on a date column when some data in the data set does not have a date in that column i.e. some rows have a date and some rows have blank data. Even when setting the type of the column in query builder to "date", it seems like query builder is treating the data as a string. The operators available default to the string operators (contains, start with, etc.). I have tried manually changing the operators for the column to the date operators (less than, greater than, etc.) but when I use them the filter still operates as though the data was a string. For example, if I pick "less than" the current date, no data rows are returned in the filter. If I pick "not equal to" a specific date, the only data rows that appear are those that are blank.

Any date columns that are fully populated with dates in every row work correctly.

I have tried changing the data for these empty rows from the string "NULL" to the empty string to an actual null, but get the same results every time.

It looks like this had recently been raised as issue here: https://www.syncfusion.com/feedback/17066/null-support-for-date-type-values

However, I am running the version it was fixed in, 18.3.0.35 and these issues still persisted.

Can you give any feedback on how to make query builder properly filter date columns when some columns are empty? My expectation would be that any rows with empty dates would not be included, but those that did have dates would be filtered based on what the user selected.

[Gayathri KarunaiAnandam]

Hi Michael, 

We have resolved the reported issue in the version 18.4.32. So, we request you to upgrade to mentioned version or above to get rid of the reported issue.  

For your reference we have prepared a sample, please find the link below  

Link: https://stackblitz.com/edit/angular-19xg2h-qpnny5 

Kindly check with the above mentioned version. Please get back us if you need further assistance. 

Regards, 

Gayathri K 

[Michael Evans]

Hello and thank you for your response. We have upgraded to the latest version and this does seem to fix the issue with the null dates. However, we were still running into some issues where the dates were not filtering as expected. I finally tracked this down to how the dates are coming across in the JSON data set.

We found that dates which were in the format "yyyy-MM-ddT00:00:00" would work and filter correctly (i.e. 2020-11-24T00:00:00) but if the date format was just "MM/dd/yyyy" (i.e. 11/24/2020) these did not work, even though the grid was able to properly display those dates in the format we wanted.

For now, we have a workaround in ensuring that dates are properly converted before serialized into JSON, but this may be something you want to look into.

Thanks,

Michael

[Gayathri KarunaiAnandam]

Hi Michael, 

We have checked your reported query and we have maintained the date values as string only in rule object and once we create predicate we have converted to date object with the help of format which you have specified. This is working fine if the dataSource have Date object as demonstrated in the below code snippet  

 

Code Example:  

export let hardwareData: Object[] = [ 

  { 

    TaskID: 1, 

    Name: "Lenovo Yoga", 

    Category: "Laptop", 

    SerialNo: "CB27932009", 

    InvoiceNo: "INV-2878", 

    DOP: new Date("02/12/2018"), 

    WEO: new Date("05/01/2021"), 

    Status: "Assigned", 

    AssignedTo: "John Doe", 

    Note: "Remarks are noted" 

  }, 

 

 

API link for format property: 

https://ej2.syncfusion.com/angular/documentation/api/query-builder/formatObject/ 

Documentation link for Date Object: 

https://ej2.syncfusion.com/angular/documentation/grid/columns/#complex-data-binding 

Could you share the code snippet or issue reproduced sample, so we could proceed further? 

Regards, 

Gayathri K 

[Michael Evans]

Hi,

The difference between your code sample and what we are doing is that we are using an API to return a dynamic dataset back to the client so I cannot cast the data to a Date as you are doing in your example. My comments were about the format of the date that seemed to work when the query builder attempted to filter on that data. The grid seemed to display either format correctly as a date but the query builder would only filter strings in the "yyyy-MM-ddT00:00:00" format correctly. It seemed to treat the "MM/dd/yyyy" strings as strings, even if I overrode the data type of the column to be date. Our code is as follows:

    this.queryBuilderObj.columns.map(q => {

      this.selectedViewIds.map(vid => {

        const vw: SnowflakeView = this.allViews.find(av => av.snowflakeViewId === vid);

        const dc: DataColumn = vw.dataColumns.find(d => d.friendlyName === q.field);

        if (dc) {

          q.type = dc.dataType.name.toLowerCase();

          if (q.type === 'date') {

            q.format = this.dateFormat;

            q.operators = this.dateOperators;

          }

        }

      });

    });

Where queryBuilderObj is a reference to the Syncfusion query builder control.

For each column in the query builder's columns property, we are finding the data column and then setting the query builder's "type" to match the data column (dc)'s type. These type names match the type names query builder is expecting. If the data type is "date" we are setting the format we want to see the date in. I'm also setting the operators because for data that has empty dates in a date field, it was defaulting to the string operators like "Contains", "Start With" etc. whereas we want to see operators for dates (Equals, Not Equals, Less Than, Greater Than, etc.)

For now our planned workaround is to use SQL views to ensure that fields which are dates are converted to the format we have found works with the query builder. Unfortunately we have data from multiple source systems and we cannot enforce the format or data type from those systems (some are stored in the database in text columns and not date columns). I just wanted to make your team aware that while the grid seems to recognize strings that can be parsed as a date and treat them as a date, the query builder seems to only filter correctly for some formats and not all. 

[Gayathri KarunaiAnandam]

Hi Michael, 

 

We would like to let you know in Query Builder, the datasource will not be processed. So, we could not be able to change the date type values. However, in grid they have changed the date type values when bound to Grid. So, we suggest to change the date type values before bind the datasource to query builder.  

  

Please let us know, if you need any further assistance. 

 

Regards, 

Gayathri K