Kentico - WHERE condition for custom Page Types page

Question

I have a custom page type for employees, and one of the fields is Location. I want to show/filter only employees in "San Jose" or "San Francisco" and used this WHERE condition below but it didn't work. Apparently, I missed something very basic. Could you help?

Location LIKE '%San%';

I did another test, where instead of page type, I used custom table with the exact field names and was able to filter using the same statement. On a related note, I'm new to Kentico and exploring which is more suitable for creating/maintaining a list of about 100 employees - Page Types or Custom Tables - with the ability to filter by department, location etc. Appreciated your input here as well. Best!

Answer 1

If you're adding the WHERE condition into a standard Kentico repeater or other data source, the syntax looks right except you do not need the semicolon ";".

You'll also want to double check the field name, and if you are limiting your query to certain columns (as is best practice especially for larger data sets) and be sure the field you are filtering on is being selected.

Regarding the management of your employee list, either method you've described will work. In that scenario it typically depends on who will be editing the content, and how frequently. It is more editor-friendly, in my opinion, to add those documents into the content tree. This also gives you quicker control over the order, and keeps it similar to how other content on the site is maintained. I also like to set up folders or other parent page types as categories if needed, so the documents can be dragged and dropped between them and it sets up a visual taxonomy that isn't possible if it's all stored in a table. Storing items in the tree also allows for workflow and versioning, as well as more granular control over permissions/access, if this is important to you.

Answer 2

It's awesome that you are thinking about how to best store your data in advance. There many factors to consider such as overall number of records, number of columns, the fact whether you need to use workflow, versioning, preview etc..

The best source of information regarding this would be this article which summarizes all options you have and gives clear explanations of which to use in which scenario.

And to your original question - What components are you using to display the data? Is the repeater? If so, can you make sure to set the Page types property to match the page type you are displaying? If the page types is not configured, Kentico will not load any custom fields because it doesn't know from which table it should load the data from.

Additionally make sure to either include the "Location" field in the Columns property or leave the columns blank (not recommended because then Kentico loads all columns which is like 200. when you count all from CMS_Document, CMS_Tree etc..)

Answer 3

Below is the framework that I use to debug whenever I wish to add a repeater and is facing some problem.

1. First get all the columns instead of accessing limited columns. Fetching all columns will make sure that I don't have any problem retrieving data.

2. If I am missing any particular column information name, then I would double check the column name.

3. I verify this by firing up SQL server management studio and access data from page type table or custom table.

4. If access to SQL server is not available(generally in Azure hosted solutions with restrictive access to DB), I would enable SQL debug from the settings and see what query repeater is generating to see if it is correct.