+1-888-365-2779
Try Now
More in this section

Forums / Developing with Sitefinity / Filter Expression with (x=1 OR x is null) format?

Filter Expression with (x=1 OR x is null) format?

2 posts, 0 answered
  1. Jonathan
    Jonathan avatar
    1 posts
    Registered:
    22 Sep 2009
    11 Apr 2011
    Link to this post
    I've added these metaFields to my a Documents & Images (v3.7 build 2096) module:
    FromDate (DateTime)
    ToDate (DateTime)

    These fields are for storing effective dates for a document (i.e. "This document is valid from 1/1/2011 - 12/31/2011")  The fields are intended to be optional: A document with no ToDate is always valid.

    When displaying information from this module on my site I'd like to only show entries where "#Now" is between FromDate and ToDate OR where ToDate IS NULL OR where ToDate = "".  If I were constructing a SQL statement to achieve this, it would be: " ... WHERE isnull(FromDate, getdate()) >= getdate() AND isnull(ToDate, getdate() <= getdate())"

    I've got this FilterExpression: ParentID IN (4e4e997a-43ad-4e39-9aca-43c8c80b540a) AND FromDate <= "#Now" AND ToDate >= "#Now"
    It works, other than the case where FromDate or ToDate is not populated.

    How can I achieve the "or is null" logic I'm after?  SiteFinity seems to not like parenthesis in a FilterExpression and I can't find adequate documentation on this in the User Manual, Forums, Blog posts, etc.

    Further question: Is "ToDate IS NULL OR ToDate = """ redundant? Does blank = NULL?

    Thanks


  2. Ivan Dimitrov
    Ivan Dimitrov avatar
    16072 posts
    Registered:
    25 Nov 2016
    11 Apr 2011
    Link to this post
    Hello Jonathan,

    "OR" is not supported and it is not possible to use in with FilterExpression. The solution requires a custom control that inherits from the content view based control you use and there you can query the data with LINQ.

    Regards,
    Ivan Dimitrov
    the Telerik team

2 posts, 0 answered