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?

The forums are in read-only mode. In case that you want to directly contact the Progress Sitefinity team use the support center. In our Google Plus group you can find more than one thousand Sitefinity developers discussing different topics. For the Stack Overflow threads don’t forget to use the “Sitefinity” tag.
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:
    12 Sep 2017
    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