Extend search after invert records

Classic Servoy
1

I want to find all donors who have not given in 2007 but did give in 2006. (This is a standard search for nonprofit organizations. lybunty stands for Last Year But Unfortuneately Not This Year. An arcane bit of knowledge.)

var dateRngThisYr = ‘01-01-2007…12-31-2007|MM-dd-yyyy’
var dateRngLastYr = ‘01-01-2006…12-31-2006|MM-dd-yyyy’
application.output(dateRngLastYr)

forms.rprt_rct_lybunty_tab.controller.find()
forms.rprt_rct_lybunty_tab.rct_amount = ‘>0’
forms.rprt_rct_lybunty_tab.rct_date = dateRngThisYr
controller.search()

//Invert foundset
controller.invertRecords();

//extend the search
forms.rprt_rct_lybunty_tab.controller.find()
forms.rprt_rct_lybunty_tab.rct_amount = ‘>0’
forms.rprt_rct_lybunty_tab.rct_date = dateRngLastYr
forms.rprt_rct_lybunty_tab.controller.search(false,true)

forms.rprt_rct_lybunty_tab.controller.sort(‘receipts_to_contacts.con_name_last asc’)

It works up to and after InvertRecords. Finds all who have donated previously, ever. When I extend the search it finds all records from 2006 but includes those who have given in 2007. Looks like inverting the records breaks the extended search.

How do I fix this???

Thanks. Kurt

2

Lets start with enquiring which version of Servoy you are using…

Paul

3

Without having looked at your code: it sounds easiest to do this with SQL. The query could for example look like this

SELECT ... FROM
WHERE EXISTS (SELECT * FROM donations WHERE date > ? AND date < ?) AND 
NOT EXISTS (SELECT * FROM donations WHERE date > ? AND date < ?)
4

First Paul, we’re on 3.1.6.

Thanks Patrick, I will try the SQL Query approach and let you know.

Best. K

5

Best is to upgrade to 3.5. There are improvements/fixed in this area in 3.5.

Paul

6

Hi Patrick,

Built the SQL query below. Works fine. Then tried to put it into a Servoy method, also below, which doesn’t work. I’m obviously doing something wrong in the method.

Also, running the SQL query in Navicat on local computer the query takes 50 seconds to run with 1750 contact records. Am I going down the wrong track. Thanks for your help. Kurt

/*
//This SQL query works in Navicat
SELECT contacts.con_name_last, contacts.con_name_first, contacts.con_organization, contacts.con_receipts_total_sum FROM contacts
WHERE contacts.fk_client = 3

AND EXISTS (SELECT * FROM receipts WHERE contacts.pk_contact = receipts.fk_contact
AND receipts.rct_date BETWEEN ‘2006-01-01’ AND ‘2006-12-31’ )
AND NOT EXISTS (SELECT * FROM receipts WHERE contacts.pk_contact = receipts.fk_contact
AND receipts.rct_date BETWEEN ‘2007-01-01’ AND ‘2007-12-31’ )
*/

//Get a dataset based on query
var maxReturnedRows = 10000

var query = ‘SELECT con_name_last, con_name_first, con_organization, con_receipts_total_sum FROM contacts’
//query += 'WHERE contacts.fk_client = 3 ’ //TODO: actually, we may need this; I’m not sure if filterparams hold true when you do an sql query
query += 'WHERE EXISTS (SELECT * FROM receipts WHERE contacts.pk_contact = receipts.fk_contact ’
query += ‘AND rct_date BETWEEN “2006-01-01” AND “2006-12-31” )’
query += 'AND NOT EXISTS (SELECT * FROM receipts WHERE contacts.pk_contact = receipts.fk_contact ’
query += ‘AND rct_date BETWEEN “2007-01-01” AND “2007-12-31” )’

var args = new Array();
//args[0] = null //or new Date()
var dataset = databaseManager.getDataSetByQuery(controller.getServerName(), query, null, maxReturnedRows);
forms.rprt_rct_lybunty_tab.controller.show()

7

You are concatenating the strings together without putting spaces inbetween in all the right places (you missed the first line…)

If you output your query string with application.output(query), you will see what I mean.

You could write your code more easily the follwing way:

var query = 'SELECT con_name_last, con_name_first, con_organization, con_receipts_total_sum FROM contacts\
WHERE EXISTS (SELECT * FROM receipts WHERE contacts.pk_contact = receipts.fk_contact\
AND rct_date BETWEEN "2006-01-01" AND "2006-12-31" )\
AND NOT EXISTS (SELECT * FROM receipts WHERE contacts.pk_contact = receipts.fk_contact\
AND rct_date BETWEEN "2007-01-01" AND "2007-12-31" )'
8

Thanks for the help. Works fine.

After indexing the foreign key the query went from 50 seconds to a fraction of a second. What a difference. K