Unexpected behavior when filtering on a Lookup field containing dates

[Chris_Sanders]

When there are multiple entries in a Lookup result (like my multiple dates in the screen capture), I would expect filtering for a range of values would include results where every Lookup result is in the range, or at least one of the results is in the range. I was surprised when the filter returned an entry that had neither of its Lookup values in the filtered range.

[Douglas_Forster]

Hi @Chris_Sanders. Thanks for letting us know and sorry for the inconvenience. I reproduced the problem locally, and will report back once we’ve rolled out a fix.

[Douglas_Forster]

Hi @Chris_Sanders, sorry for the delay. After further investigation, this is expected, although not obvious, behavior. Each cell in the “Case Referral Date” field can contain multiple values. When a multiple-value cell is compared against a filter condition, the filter condition will match if ANY of values within an individual cell matches.

So the filter criteria can be described as:

#1  "any" "value of Case Referral Date" "is on or before" "exact date" "1/1/2015".
  -- and --
#2  "any" "value of Case Referral Date" "is on or after" "exact date" "3/31/2015"

When the multi-value cell “[5/11/2015], [4/2/2014]” is compared against the the filter criteria, the individual value [5/11/2015] matches condition #2, and [4/2/14] matches condition #1. The row satisfies both criteria, so the the row will be included in the view.

My recommendation would be to uncheck “Allow linking to multiple records” for the linked field, so that the “Case Referral Date” lookup will contain only a single value. Hope that helps!