Funny Little Issue with Sort

Classic Servoy
1

I have a situation in Servoy 3.5.6 where I have a trans table and a trans_to_addresses_$shipto table.

The shipto table will store zip codes for my trans record in the event that one is entered.

My issue is this. I’ve created a report that breaks down the sales by zip code.

Everything works dandy until I get to the sort… when I sort by trans_to_addresses_$shipto.postal_code… the found set goes away and any subsequent finds on that table fail until I reload the solution.

So what’s with the sort? Can I not sort by a related value? Or is it failing because I have some trans records that do NOT have a related value.

My code:

		//------------------------------------------------------------------------------------------------------------------
		//         Sales by zip 
		//------------------------------------------------------------------------------------------------------------------
	
		else if (searchName == 'Sales by Zip/Postal Code')  
		{
		
			var rptForm = 'rpt_sales_by_zip'
						
			forms[rptForm].controller.loadRecords(found)		
			globals.g_report_title = searchName
			globals.g_report_dates = criteria
				
				// sort
			forms[rptForm].controller.sort('site_id asc, trans_to_addresses_$shipto.postal_code asc')
		}

// enter preview mode
forms[rptForm].controller.showPrintPreview()

Any ideas?

2

Hi chico,

This behaviour may be related to the type of join that you have for the trans_to_addresses…

There have been a couple of threads discussing this if you search the forum for ‘sort related’ which may help

Cheers
Harry

3

Harry!

Thanks so much… that was it… we needed an outerjoin relationship to do the sort.

FileMaker didn’t prepare me for these kinds of things. :)