none
Content query, show when no date (or NULL).

    Frage

  • I have a simple list and one of the columns contains "End Date" is only entered when item is no longer valid.

    I have a content query showing this list but struggling to find a way to filter to only show items without an end date.

    I have tried setting filters like:
    End date - is less than Today - (Only shows items with date
    End date -  contains - (Custom) - null
    End date -  is equal to - (Custom) - [NULL]
    End date -  is equal to - (Custom) - [NUL]

    And still trying... but would appreciate some help

    TIA

    Freitag, 13. Juli 2018 09:45

Antworten

  • Hi GTEM,

    We cannot filter the date column without the date directly in the content query web part.

    There is a workaround for the issue.

    We can create a calculated column and use the forum as below, you should replace the “date1” column with your date column.

    =IF(ISBLANK(date1),"Never",TEXT(date1,"mm/dd/yyyy"))

    If we filter the calculated column in the content query web part directly, it will not return the result. Then we should copy the value of the calculated column to a single line of text column.

    In the content query web part, we can filter the single line of text column and check if it is equal to “Nerver”.

    After all, we can get the items which the date column is null or no value.

    For more detailed information, refer to the article below:

    Content Query Web Part Filter on a Date Column with Null Values.

    http://info.summit7systems.com/blog/content-query-web-part-filter-on-a-date-column-with-null-values

    Best regards,

    Sara Fan


    Please remember to mark the replies as answers if they helped. If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com.


    Click here to learn more. Visit the dedicated forum to share, explore and talk to experts about Microsoft Teams.

    • Als Antwort markiert GTEM Montag, 16. Juli 2018 11:10
    Montag, 16. Juli 2018 04:33
    Moderator

Alle Antworten

  • Hi GTEM,

    We cannot filter the date column without the date directly in the content query web part.

    There is a workaround for the issue.

    We can create a calculated column and use the forum as below, you should replace the “date1” column with your date column.

    =IF(ISBLANK(date1),"Never",TEXT(date1,"mm/dd/yyyy"))

    If we filter the calculated column in the content query web part directly, it will not return the result. Then we should copy the value of the calculated column to a single line of text column.

    In the content query web part, we can filter the single line of text column and check if it is equal to “Nerver”.

    After all, we can get the items which the date column is null or no value.

    For more detailed information, refer to the article below:

    Content Query Web Part Filter on a Date Column with Null Values.

    http://info.summit7systems.com/blog/content-query-web-part-filter-on-a-date-column-with-null-values

    Best regards,

    Sara Fan


    Please remember to mark the replies as answers if they helped. If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com.


    Click here to learn more. Visit the dedicated forum to share, explore and talk to experts about Microsoft Teams.

    • Als Antwort markiert GTEM Montag, 16. Juli 2018 11:10
    Montag, 16. Juli 2018 04:33
    Moderator
  • Thank you Sara.In the end I decided to enter dates "1/1/1900" into all blank date fields and then added a filter for anything where date is equal to "1/1/1900"
    I did go down the calculated field route but got stuck with that and decided this was an easier option.

    Montag, 16. Juli 2018 11:10
  • Hi GTEM,

    It is very happy that the issue is resolved.

    Thank you for your sharing and it will help others have the same issue.

    Best regards,

    Sara Fan

    Please remember to mark the replies as answers if they helped. If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com.


    Click here to learn more. Visit the dedicated forum to share, explore and talk to experts about Microsoft Teams.

    Dienstag, 17. Juli 2018 00:45
    Moderator