addTableFilterParam for a date range?

Classic Servoy
1

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

		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?

2

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

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')
3

Escellent, thank you Patrick. That does the job

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')   
}
4

Hi

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

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

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

Rob