For example I have 2 files called Department & Employee
Department
dept_id
dept_name
Employee
emp_id
employee_name
dept_id
I have a relation called “dept_id__employee_to_department”
I have a foundset of 5 employee records. 3 of them linked to a department (with dept_id not empty). 2 of them not linked (dept_id empty).
I sort records in the employee List using the related field frmEmployeeList.sort(‘dept_id__employee_to_department.dept_name’)
When the sort is performed the 2 employee records which are not linked to any department are lost from the foundset.
Now I have only 3 records in the foundset instead of 5.
Please let me know if am doing anything wrong or this is a bug?
I think this work differently in Filemaker. In filemaker the foundset would not lose un-related when doing a sort. Can you do a work around to fix this issue ?
Loosing unrelated records when doing a join is default SQL, in your case a join is done todo a related sort.
For the future we consider doing inner/outer joins instead of plain joins.
Do you know when this outer join functionality will be implemented. I need this functionality for our clients especially for our old filemaker clients turned to servoy.
The OUTER JOIN functionality has already been implemented? According to the result I am getting from the search on a related field, I get to know that this is not yet available.
Please let me know when this will be implemented. I am really really desperate to get this functionality. If I do not get this functionality quite quickly then I need to write my own SQL to do the searching (which I really do not want to do). There will be a hell of lot of code I need to write to get this functionality done. It would break the concept of simplicity in my solution which Servoy provides by default.
At the momemt you won’t get very far with your own SQL either. You can’t use controller.loadRecords(query) with outer joins and getDataSetByQuery will return max 1000 records…
Release 3.5 will have support for outer joins.
In the relation dialog you will have the option to select a join type.
If you select ‘left outer join’, related sorts will no longer lose missing main records.
The first public beta will be available in the next few weeks.
patrick:
Rob, will this release also allow any joins in controller.loadRecords(query)?
Patrick,
The new release will treat custom queries in 2 ways:
when it has an ‘order by’ clause (which is required < 3.5)
the same restrictions will apply as before, joins are not supported
there is no ‘order by’ clause
the query will be used ‘as is’, the only restriction (as we currently see) is that it selects the PKs.
That means joins, unions, etc will be allowed.
The result will be sorted by the sorting defined on the controller/form.
Release 3.5 will have support for outer joins.
In the relation dialog you will have the option to select a join type.
If you select ‘left outer join’, related sorts will no longer lose missing main records.
Hi, rob
I’m very happy to hear it.
But my very little knowledge about SQL leads me to this question: since left outer join seems to solve those related values problem, is there any case in which it’s preferrable NOT to use this kind of join or can I decide to use it everywhere?
Riccardino:
But my very little knowledge about SQL leads me to this question: since left outer join seems to solve those related values problem, is there any case in which it’s preferrable NOT to use this kind of join or can I decide to use it everywhere?
Ric,
I think you should only use it when you have a relation that defines an optional relationship and you want records in your main table not to be skipped.
Some example:
orders may have no order details yet (relationship 1-n) and you want to list orders sorted on nr of items → use outer join
order detail will always refer to an existing order → use inner joins (the current join type)