[Tip] Avoiding related searches "ghost" records

by Riccardo Albieri
Morninger Soluzioni

Sometimes you may need to perform searches involving related records. If you are not a SQL expert (just like me), you may be surprised seeing identical instances of the same record listed in the foundset.

I’ll try to be clearer with an example: suppose you want to search all the invoices belonging to customers of a certain State. Nothing difficult, you may think: three globals (the start date, the end date and a combo-box with a State value-list) and a simple search method like this:

var startdate = utils.dateFormat(globals.startdate, 'dd-MM-yyyy');
var enddate = utils.dateFormat(globals.enddate, 'dd-MM-yyyy');

controller.find();

invoice_date = startdate + "..." + enddate + "|dd-MM-yyyy";

if ( globals.gState )  // if the user selects a State
{
    invoices_to_addresses.state = globals.gState;
}

controller.search();
controller.sort('invoice_date asc');

Easy, isn’t it?

But what happens if you have customers with more than one address in the same State?

Simple: you end up with a list of invoices showing twice (or more) because the SQL generated with your method is correct, but the results are repeating the main record (the invoice) for each of the related records called by the search (the addresses, in this case).

There’s nothing wrong in that (is standard SQL behaviour, you can check with a SQL tool), but it can be very confusing: your user expects to see a list of invoices, each one of them shown only once.

A solution that should work under almost all circumstances is to use a specific query to perform the search.

The above example would become:

var startdate = utils.dateFormat(globals.startdate, 'yyyy-MM-dd');
var enddate = utils.dateFormat(globals.enddate, 'yyyy-MM-dd');
    
controller.loadRecords( "SELECT DISTINCT"+
    " invoices.invoicesid from invoices, addresses"+
    " WHERE invoices.invoice_date BETWEEN '" + startdate +"' AND '" + enddate + "'" +
    " and addresses.state = '" + globals.gState + "'" +
    " and  addresses.customerid = invoices.customerid" +
    " order by 1 asc")

In this case, the SELECT DISTINCT instruction will get us rid of the ghost records, leaving only one instance of each invoice, regardless of how many addresses its customer has in the selected State.