Developer forum

Hi Peter

Could you please give us information on you crash? What exception do you get?

Thanks, Nicolai

Hi Nicolai,

When using the .op a System.Reflection.TargetInvocationException is thrown with inner exception:

I have an item type (for event like objects) which have two datetime fields (StartDateTime, EndDateTime) of which the last one is optional.
I would like to filter my renderitemlist to only show currently active and future events.
I'm not able to use a conventional item publisher and need the rendered one.

I can't seem to find a way to filter on datetimes other than comparing with the exact date, let alone checking for null value OR greater than
Can you provide me with a working example and make me feel stupid again, please?

With kind regards,

Peter

PS, I'm trying to get this working in a ParagraphViewModel template in DW core 9.5

Hm - can you check the field types of those to date fields?

And can you check your culture info of the database. You probably need to use dates in querystring that works with the SQL-Server culture.

BR Nicolai

Data fields are of type datetime.
Display of the values is in "yyyy-MM-dd" format, but no idea what the database culture is.
It is a database hosted in Azure, so I asume it is in en-Us format (how can I check?).
Collation is SQL_Latin1_General_CP1_CI_AS

I included print screens.

My current filter is:
 

var filter = string.Format("StartDateTime={0}&StartDateTime.op=gt", DateTime.Today.ToString("yyyy-MM-dd"))

var myList = RenderItemList(new
{
  ...,
  ...,
  ...,
  Filter = filter
});

I've tried all kinds of date formats, nothing seems to work ...

Can you confirm if my filter string is in a correct format at least?
I've no idea, the documentation is not as clear as you might think it is.
There is just one code sample of a filter, which is the simple "contains".

Perhaps the code samples can be extended with samples of each data type?
And if culture settings are of any influence to the filters, perhaps explain this in how to implement?

And can you tell me / add an example on how I can compare with NULL also?
I actually need a "WHERE FieldValue1 > GETDATE() OR (NOT FieldValue2 IS NULL AND FieldValue2 > GETDATE())" filter ...

Hi Peter

I think your date format in the querystring is wrong compared to what your database wants - the exception your receive is from SQL-Server and it clearly states that formats are off. So look at the profiler, see what the SQL looks like, find the error and change the dateformat in your Querystring.

That said, I am not sure you can test for null like you describe... Then it might be better with a default value of 100 years in the future...

BR Nicolai

The .op part doesn't seem to get converted to SQL at all ... (see Profiler.PNG)
When I changed "StartDateTime=2018/9/18&StartDateTime.op=gt" to "StartDateTime >= 2018/9/18" the query changed to StartDateTime LIKE N'2018/9/18' ...
Please provide me with working samples of filter strings since your documentation seems to be incorrect.

I actually need to do filter grouping as well, is this possible?
For example: WHERE X=1 AND (Y=2 OR Z=2)

 

Ah, now I understand what happens. You are using the querystring syntax on a method call in razor.

You need to use a filter parameter in another format - see this thread: https://doc.dynamicweb.com/forum/templates/templates/renderitemlist-and-filter

Granted, the docs is missing here...!

Still can't get things to work ...
I've simplified my code to some samples, perhaps you can tell me where I'm f-ing things up ....
Sample 5 is what I actually need, but the troubles start whenever I'm combining or use a date ... and I need to do both ...

SAMPLE 1: Filter on just checkbox list values with an OR, THIS WORKS

@inherits Dynamicweb.Rendering.ViewModelTemplate<Dynamicweb.Frontend.ParagraphViewModel>
@{
    var selectedCategoryId = Model.Item.GetInt32("CategoryId");
    var listTake = Model.Item.GetInt32("AmountShown") + 1;
    var listFilter = "";
    var listOrderBy = "Id";
    var listOrderByDirection = "Descending";
    
    // Filter by categoryId
    if (selectedCategoryId > 0) {
        listFilter += string.Format("({1} contains '{0}' or {2} contains '{0}' or {3} contains '{0}' or {4} contains '{0}')",
            selectedCategoryId,
            "Field1",                    // checkbox list value, can contain 1 or 1,2,3
            "Field2",                    // checkbox list value, can contain 1 or 1,2,3
            "Field3",                    // checkbox list value, can contain 1 or 1,2,3
            "Field4");                    // checkbox list value, can contain 1 or 1,2,3
    }
    
    var myList = RenderItemList(new
        {
            ItemType = "MyItem",
            ListTemplate = "../ItemPublisher/List/List.cshtml",
            ListSourceType = "Area",
            ListSourceArea = areaId,
            ItemFieldsList = "*",
            ListPageSize = listTake,
            ListViewMode = "Partial",
            ListShowFrom = 1,
            ListShowTo = listTake,
            IncludeParagraphItems = true,
            ListOrderBy = listOrderBy,
            ListOrderByDirection = listOrderByDirection,
            Filter = listFilter
        });
        
    @myList
}

SAMPLE 2: Filter on a text value, THIS WORKS

@inherits Dynamicweb.Rendering.ViewModelTemplate<Dynamicweb.Frontend.ParagraphViewModel>
@{
    var titleFilterTex = Model.Item.GetString("Title");
    var listTake = Model.Item.GetInt32("AmountShown") + 1;
    var listFilter = "";
    var listOrderBy = "Id";
    var listOrderByDirection = "Descending";
    
    // Filter by categoryId
    if (!string.IsNullOrWhiteSpace(titleFilterTex)) {
        listFilter += string.Format("Title contains '{0}'", titleFilterTex);
    }
    
    var myList = RenderItemList(new
        {
            ItemType = "MyItem",
            ListTemplate = "../ItemPublisher/List/List.cshtml",
            ListSourceType = "Area",
            ListSourceArea = areaId,
            ItemFieldsList = "*",
            ListPageSize = listTake,
            ListViewMode = "Partial",
            ListShowFrom = 1,
            ListShowTo = listTake,
            IncludeParagraphItems = true,
            ListOrderBy = listOrderBy,
            ListOrderByDirection = listOrderByDirection,
            Filter = listFilter
        });
        
    @myList
}

SAMPLE 3: COMBINING the above together, THIS DOES NOT WORK

@inherits Dynamicweb.Rendering.ViewModelTemplate<Dynamicweb.Frontend.ParagraphViewModel>
@{
    var titleFilterTex = Model.Item.GetString("Title");
    var selectedCategoryId = Model.Item.GetInt32("CategoryId");
    var listTake = Model.Item.GetInt32("AmountShown") + 1;
    var listFilter = "";
    var listOrderBy = "Id";
    var listOrderByDirection = "Descending";
    
    // Filter by categoryId
    if (selectedCategoryId > 0) {
        listFilter += string.Format("({1} contains '{0}' or {2} contains '{0}' or {3} contains '{0}' or {4} contains '{0}')",
            selectedCategoryId,
            "Field1",                    // checkbox list value, can contain 1 or 1,2,3
            "Field2",                    // checkbox list value, can contain 1 or 1,2,3
            "Field3",                    // checkbox list value, can contain 1 or 1,2,3
            "Field4");                    // checkbox list value, can contain 1 or 1,2,3
    }
    
    // Filter by categoryId
    if (!string.IsNullOrWhiteSpace(titleFilterTex)) {
        if (!string.IsNullOrWhiteSpace(listFilter)) {
                listFilter += " and ";
        }
        listFilter += string.Format("Title contains '{0}'", titleFilterTex);
    }
    
    
    var myList = RenderItemList(new
        {
            ItemType = "MyItem",
            ListTemplate = "../ItemPublisher/List/List.cshtml",
            ListSourceType = "Area",
            ListSourceArea = areaId,
            ItemFieldsList = "*",
            ListPageSize = listTake,
            ListViewMode = "Partial",
            ListShowFrom = 1,
            ListShowTo = listTake,
            IncludeParagraphItems = true,
            ListOrderBy = listOrderBy,
            ListOrderByDirection = listOrderByDirection,
            Filter = listFilter
        });
        
    @myList
}

What am I doing wrong here?

SAMPLE 4: FILTERING ON ONE DATE, THIS DOES NOT WORK

@inherits Dynamicweb.Rendering.ViewModelTemplate<Dynamicweb.Frontend.ParagraphViewModel>
@{
    var titleFilterTex = Model.Item.GetString("Title");
    var listTake = Model.Item.GetInt32("AmountShown") + 1;
    var listFilter = "";
    var listOrderBy = "Id";
    var listOrderByDirection = "Descending";
    
    // Filter by start date (start date is in the future)
    listFilter += "StartDateTime > \"" + DateTime.Today.ToString("yyyy/MM/dd") + "\"";    // how does this differ from: listFilter += string.Format("StartDateTime > {0}", DateTime.Today.ToString("yyyy/MM/dd"))
    
    var myList = RenderItemList(new
        {
            ItemType = "MyItem",
            ListTemplate = "../ItemPublisher/List/List.cshtml",
            ListSourceType = "Area",
            ListSourceArea = areaId,
            ItemFieldsList = "*",
            ListPageSize = listTake,
            ListViewMode = "Partial",
            ListShowFrom = 1,
            ListShowTo = listTake,
            IncludeParagraphItems = true,
            ListOrderBy = listOrderBy,
            ListOrderByDirection = listOrderByDirection,
            Filter = listFilter
        });
        
    @myList
}

SAMPLE 5: WHAT I ACTUALLY NEED !

@inherits Dynamicweb.Rendering.ViewModelTemplate<Dynamicweb.Frontend.ParagraphViewModel>
@{
    var selectedCategoryId = Model.Item.GetInt32("CategoryId");
    var listTake = Model.Item.GetInt32("AmountShown") + 1;
    var listFilter = "";
    var listOrderBy = "Id";
    var listOrderByDirection = "Descending";

    // Filter by categoryId
    if (selectedCategoryId > 0)
    {
        listFilter += string.Format("({1} contains '{0}' or {2} contains '{0}' or {3} contains '{0}' or {4} contains '{0}')",
            selectedCategoryId,
            "Field1",                   // checkbox list value, can contain 1 or 1,2,3
            "Field2",                   // checkbox list value, can contain 1 or 1,2,3
            "Field3",                   // checkbox list value, can contain 1 or 1,2,3
            "Field4");                  // checkbox list value, can contain 1 or 1,2,3
    }

    // Filter by dates (start date is in the future or end date is not null and in the future
    if (!string.IsNullOrWhiteSpace(listFilter))
    {
        listFilter += " and ";
    }
    listFilter += "(StartDateTime > \"" + DateTime.Today.ToString("yyyy/MM/dd") + "\" or (EndDateTime != \"" + null + "\" and EndDateTime > \"" + DateTime.Today.ToString("yyyy/MM/dd") + "\" ))";

    var myList = RenderItemList(new
    {
        ItemType = "MyItem",
        ListTemplate = "../ItemPublisher/List/List.cshtml",
        ListSourceType = "Area",
        ListSourceArea = areaId,
        ItemFieldsList = "*",
        ListPageSize = listTake,
        ListViewMode = "Partial",
        ListShowFrom = 1,
        ListShowTo = listTake,
        IncludeParagraphItems = true,
        ListOrderBy = listOrderBy,
        ListOrderByDirection = listOrderByDirection,
        Filter = listFilter
    });

    @myList
}

Hi Peter and Nicolai,

Did you ever find a solution for this. I'm in the same situation here right now. I need to use @RenderItemList with a filter, that only shows items older than 90 days (date is chosen in a datefield - Date - on the item) and I can't get the "Date < " filter working in the RenderItemList.

That was my conclusion as well.

Hi Oleg,

In this case I just want to use "RenderItemList" and not the Item Publisher. But sounds great that it works on the ItemPublisher - then we'll be able to use that in other scenarios.

Hi,

RenderItemList is just method allows to render Item Publisher module via appropriate tags in template code. Yup, try it.

BR, Oleg QA

This is great Oleg! Works like a charm. Thank you :-)