addTableFilterParam for a date range?

Questions, tips and tricks and techniques for scripting in Servoy

addTableFilterParam for a date range?

Postby antonio » Sat Jan 23, 2016 5:24 pm

I'm trying to add a table level filter for a range of dates.

Code: Select all
      var vServerName = controller.getServerName();
      if (vFilterFrom)
      {
         databaseManager.addTableFilterParam(vServerName,  'invoices',  'date_of_service',  '>',  vFilterFrom,  'invoiceFilterFrom')         
      }
      if (vFilterTo)
      {
         databaseManager.addTableFilterParam(vServerName,  'invoices',  'date_of_service',  '<',  vFilterTo,  'invoiceFilterTo')         
      }


It works fine if I only need to set one or other of the dates, but when I try to set both the second addTableFilterParam removes the first.
(I've tried using the same as well as different filter_name parameters)
Is there any way to filter a table on a range of dates?
Tony
Servoy Developer Version 5.2.16
Developer MAC OSX 10.8.4 Java version 1.6.0_51
Production Debian Linux Java version 1.6.0_29
antonio
 
Posts: 622
Joined: Sun Apr 02, 2006 2:14 am
Location: Australia

Re: addTableFilterParam for a date range?

Postby patrick » Sat Jan 23, 2016 8:17 pm

you can use basically any query with the in clause, for example

Code: Select all
var inQuery = 'SELECT invoice_id FROM invoices WHERE date_of_service > x AND date_of_service < y'
databaseManager.addTableFilterParam(vServerName,  'invoices',  ' invoice_id',  'IN', inQuery,  'invoiceFilterFrom')   
Patrick Ruhsert
Servoy DACH
patrick
 
Posts: 3606
Joined: Wed Jun 11, 2003 10:33 am
Location: Munich, Germany

Re: addTableFilterParam for a date range?

Postby antonio » Sun Jan 24, 2016 9:57 am

Escellent, thank you Patrick. That does the job

Code: Select all
if (vFilterFrom && vFilterTo)
{
   var inQuery = 'SELECT invoice_id FROM invoices WHERE date_of_service > "' + utils.dateFormat(vFilterFrom, "yyyy-MM-dd") + '" AND date_of_service < "' + utils.dateFormat(vFilterTo, "yyyy-MM-dd") + '"'
   databaseManager.addTableFilterParam(vServerName,  'invoices',  ' invoice_id',  'IN', inQuery, 'invoiceFilter')   
}
Tony
Servoy Developer Version 5.2.16
Developer MAC OSX 10.8.4 Java version 1.6.0_51
Production Debian Linux Java version 1.6.0_29
antonio
 
Posts: 622
Joined: Sun Apr 02, 2006 2:14 am
Location: Australia

Re: addTableFilterParam for a date range?

Postby rgansevles » Fri Jan 29, 2016 11:12 am

Hi

Alternatively, you can also use the between operator in table filters:

Code: Select all
if (vFilterFrom && vFilterTo)
{
   databaseManager.addTableFilterParam(vServerName,  'invoices',  ' invoice_id',  'BETWEEN', [vFilterFrom, vFilterTo], 'invoiceFilter');
}


Note that between-conditions are defdined as an inclusive range.

Rob
Rob Gansevles
Servoy
User avatar
rgansevles
 
Posts: 1841
Joined: Wed Nov 15, 2006 6:17 pm
Location: Amersfoort, NL


Return to Methods

Who is online

Users browsing this forum: Yahoo [Bot] and 6 guests