Hi Mike M

For datalist using JDBC queries on using filters, first get the "filterId" or "URL Request Parameter" generated when you drag a field (left column) into the filter area (top right). View the id by clicking on the that date filter's edit  properties button. It should look something like this "d-6125985-fn_c_dateField", assuming your field name is "dateField".

Then create your SQL select statement like the below:

SELECT * FROM app_fd_TableName WHERE c_dateField = '#requestParam.d-6125985-fn_c_dateField#'

or

SELECT * FROM app_fd_TableName WHERE c_dateField <= '#requestParam.d-6125985-fn_c_dateField#'

You can set the filter's default value via the filter properties 2nd tab "General > Configure Text Field Filter" if you like to prevent an empty listing.

Thanks Matthew ... not sure if I fully understand your answer!

the dateField is not in the select clause to drag it into the filter area.

My query looks similar to:

select col1, col2, col3 from table1 where dateField between fromDateVariable1 and toDateVariable2

I would like to filter on the dateField in the Where Clause and please notice that this field is not the select clause

Best,

Hi Mike M

Here's another look, where the fromDate and toDate are filters in your datalist:

select col1, col2, col3 from table1 where c_dateField between '#requestParam.d-6125985-fn_c_fromDate#' and '#requestParam.d-123480-fn_c_toDate#'

Thanks Matthew

Apologies ... I think I understand what you say and did it for fields in the SQL SELECT clause successfully; but, how to set the requestParam per DataList session if the field is not in the filter area

How to drag a field that is not in the SQL SELECT clause into the filter area?  the field is only in the WHERE clause?

Best,

Hi Mike M

An alternative to the filter list is to call your datalist using your own inserted parameters, and this parameters will be passed to the datalist's SQL where clause, this way you don't need to have any filters in the datalist.

Example:

URL Call: https://mike.cloud.joget.com/jw/web/userview/ims/ims/_/invoices?fromDate=16/01/01&toDate=16/01/31

List JDBC SQL: select col1, col2, col3 from table1 where c_dateField between '#requestParam.fromDate#' and '#requestParam.toDate#'

Hope this helps .

cool ... Thanks Matthew

one caveat, what would be the best way to enter the variables and build and submit the query? of course, you did not mean to type the query string every time

I tried a form with 2 date pickers; but, it seems that the form hash variables are not recognized in the JDBC data list query ... 

Best,

Do enclosed your hash variables in single quotes in SQL statement, eg '#requestParam.fromDate#' . The best way to enter the variables is using datalist filters.