How to tell index query to only fetch products where publication period validto field is not less than DateTime.now

Hans Ravnsfjall

Posted on 30/05/2017 11:40:08

we have a costumer that uses the functionality of setting publication period on products. The set a valid-to value in the publication period.

I realize that in the index query, it is not enough to only set it to select active products, because in DW a product is still active altough publication period is overdue.

I have tried to set this up in Index Query by setting Campaign End Time (Not sure if it's the right field to use?) to be either larger than DateTime.Now or = NULL.

But it does not seem to work because it only gives 4 products of the about 70-80 products it should give. See my setup https://www.screencast.com/t/Wtyn6Ku31E

I also tried creating a query group and negate it so that it would select everything where the to-date is not less than now. This just gives an Error.

Hope someone can help?

br. Hans

Replies

Steffen Kruse Hansen

Posted on 30/05/2017 14:44:57

Hi Hans,

Which version of Dynamicweb do you use?

'Campaign End Time' is the right field to use, but the problem is the null check in the query, which will never return a result, so my quess is that the 4 products of the 70-80 have an 'Campaign End Time' which is greater than today, and the other 66+ products don't have a 'Publication Period'.

In 9.2 and below we don't have any options for searching for empty strings/null values, but in 9.3 we have introduced an 'IsEmpty' operator, which is able to do what you are trying to do.

The functionality of the 'IsEmpty' operator also exists in the latest version of Dynamicweb 9.2, but it is not visible in the frontend, so if you write it directly in the query file, it is possible to make it work. I can provide you with an example query if you are interested, but it will only work if you are running DW9.2.X

Best regards

Steffen

Hans Ravnsfjall

Posted on 30/05/2017 16:33:04

Hi Steffen

thanx for your reply. I am running 9.2.9

Br.

Hans

Tin Ma Ma

Posted on 21/08/2017 00:58:56

Hi Steffen,

How about in 8.9.2.7?

Best Regards,

Tin Ma Ma

Steffen Kruse Hansen

Posted on 11/09/2017 13:29:37

Hi Tin Ma Ma,

In the DW 8.9.2, we don't have the IsEmpty operator, so that rules out that option.

But I think you should be able to use create a negated group, to obtain the same result. You can see the configuration in the attached image.

I hope this helps you

Best regards

Steffen

You must be logged in to post in the forum

Developer forum

How to tell index query to only fetch products where publication period validto field is not less than DateTime.now

Replies