Funny Little Issue with Sort

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?

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

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. :)