How do I use a widget parameter in a custom query (widget is based on repeater with custom query)

Mark Fasel asked on September 21, 2017 14:27

Hi there,

I am building a custom widget that is based on the kentico repeater with custom query. I am using the custom query to query my custom table and display a transformation from that table. In my widget I have a custom parameter I have set up that I would like to use in my query for the where clause to make it more user friendly. Any help would be awesome! Thanks

select ##TOPN## * 
from customtable_phone_directory
where Category = '{% DirectoryCategory %}'
order by ItemCreatedWhen asc

This does not seem to work. I even tried this:

select ##TOPN## * 
from customtable_phone_directory
where Category = ##DirectoryCategory##
order by ItemCreatedWhen asc

DirectoryCategory is the custom property I have set up in the widget.

Thanks

widget query repeater

Recent Answers

Mark Fasel answered on September 21, 2017 14:28 (last edited on September 21, 2017 16:02)

Just trying to figure out what I am to use in my query to pull the parameter from the widget

0 votesVote for this answer Mark as a Correct answer

David te Kloese answered on September 22, 2017 10:52

Hi, you can use

GetValue("PropertyName")

As a tip you can just look into the code of other standard Kentico widgets. They are located in the CMSWebParts folder. Widgets are in basically just Web Parts!

0 votesVote for this answer Mark as a Correct answer

Mark Fasel answered on September 25, 2017 13:33

That unfortunately didn't work. I may not have explained it well tho. So basically I have a custom widget I am building (using portal engine) that is based on repeater with custom query. I am using a custom query on a custom table I have and I want to pass in the values for the WHERE clause thru a property on widget. Ex: The property is a dropdown list of categories. So in my custom query on this widget, it would pull the category from the widget's property and use in the repeater's custom query. Does that make more sense?

Thanks for your help!

0 votesVote for this answer Mark as a Correct answer

Peter Mogilnitski answered on September 27, 2017 15:30 (last edited on September 27, 2017 15:35)

First of all you have issue with macros for you query. There are 4 standard ones:

##WHERE## Macro substitutes 'WHERE' condition
##TOPN## Macro substitutes 'TOP n' clause
##ORDERBY## Macro substitutes 'ORDER BY' rule
##COLUMNS## Macro substitutes list of columns

Those 4 go into corresponding properties of your web part/widget:

Image Text

You query should look like

select ##TOPN## *
from customtable_phone_directory
where  ##WHERE## 
order by ItemCreatedWhen asc

Now you can put your where and top in the properties :

Image Text

Assuming your macro {% DirectoryCategory %} resolves correctly it should work

1 votesVote for this answer Mark as a Correct answer