No ratings

Known Issue: DateTime values in Live Search or Filtering not returning expected results

 

Known Issue: DateTime values in Live Search or Filtering not returning expected results

Issue

When filtering data on DateTime values using the search popup or the filter properties in the Picker control, or when enabling the Live Search property of the Auto-Complete control and searching by date, control values are not returned as expected. This applies to SQL-based SmartObjects, for example SmartBox SmartObjects.

Cause

The SmartForm controls build a contains text filter which gets applied to a DateTime property type. When applying a text-based filter on server side in SQL, the text representation does not match the expected format.

Example 1

 Data

Date text representation on client side 2021-06-24
Date text representation in SQL 2021-Jun-24

Using the data above, if you have a SmartObject with a DateTime type property and search for the value 6.  The second value (2021-Jun-24) will not be returned as the search is done on the SQL server.

Example 2

The following example is specific to the Auto-Complete control. When using the fields of the control in the Display Items as shown below, for example [Name]-[Surname], the behavior is different when enabling or disabling the Live Search property of the control.

001.png

  • Live Search disabled: All the rows of the SmartObject are retrieved, then for each row the calculated value of the display items is determined, and the search term is matched against this.
  • Live Search enabled: A filter is built for each field used in the display items, and only those rows are retrieved. For example, WHERE Name like "%search%" OR Surname like "%search%". Then these rows are filtered again based on the calculated value of the display items.
Search Expected match Live Search Result
Bob-Maggio Bob-Maggio Disabled Correct
Bob-Maggio Enabled Does not return the expected results
Bob Bob Enabled Correct
Maggio Maggio Enabled Correct
6 2021-06-24 Disabled Correct
2021-06-24
(SQL: 2021-Jun-24)
Enabled Does not return the expected results
6 2021-06-24 Disabled Correct
2021-06-24
(SQL: 2021-06-24)
Enabled Correct

Example 3

DateTime is stored in server time and depending on your timezone, the Day or Time may also not filter as expected. See the example below.

Timezones

Client

Australian Central Standard Time
Adelaide (GMT+9:30)
Server UTC (0:00)

Data

Date on client side 2021-06-24 00:00
Date in SQL 2021-Jun-23 14:30

When searching 24 for example, the expected results are not returned as the search is done on the server (SQL). 

This behavior may affect other controls or searches where a SmartObject filter is built against a DateTime type property.

Resolution

Use the Date or Time type properties in your SmartObject to return the correct results when searching by text or use date range searches instead. One example is to create a filter using rules to set up specific date ranges as shown below.

002.png

Labels: (1)
Version history
Last update:
‎07-29-2021 11:36 PM
Updated by:
Contributors