How to query custom page fields in where clause of a page data source

Eric Conklin asked on September 21, 2020 17:28

I am using an pages data source to grab blog posts from the blog directory. I want to be able to filter by a custom field I added to each blog post, which is called EventDate. I created this in the administrative panel under the development section.

I added a where clause to my data source with the following:

EventDate > getdate()

The idea being that the EventDate should be in the future, in other words only display upcoming events.

I get Invalid column name 'EventDate' error on every attempt to run this where clause.

Any suggestions as to how I can resolve this? I at a bit of a loss here and am not sure if I'm doing this entirely wrong but any feedback is appreciated.

Recent Answers

Eric Conklin answered on September 21, 2020 19:19

Hey, I fixed my query with the following:

'{%EventDate %}'  < getdate()

This code works just fine. I just needed to add the % to and '' around the custom field to get the macro to run correctly within the SQL query itself.

1 votesVote for this answer Mark as a Correct answer

Juraj Ondrus answered on September 23, 2020 07:20

The WHERE clause accepts the field name just fine. There is no need to use macro - macro is just a substitution which is then resolved to something else dynamically while the field name is the same all the time. It does not make sense to use the macro in the place of the field name. I just tried using: EventDate < GetDate() and it is working just fine (screen shot). Isn't it possible that you have filled out the Columns property to load only desired columns and the EventDate was not listed? Or, maybe some other typo was there. But the macro in place of the field name does not sound right. Also, there should not be quotes around field/column name in the SQL syntax.

0 votesVote for this answer Mark as a Correct answer