We observe records to disappear in table view, when sorting a column. The foundest gets reduced to records with not null values. We have a value list attached, which allows null values. The foundset ist loaded by a relation. When I attach an onSort event to the form and execute the default code (generated), the records are not removed and the behaviour is as expected. Others which observe same behaviour? A bug? Should I make a case?
I believe there was some behaviour change in this area.
Last time I solved it was exactly like you say: attach a function to the onSort event and make sure you do whatever is needed to make it work.
BTW, if you look in the performance data after you do the original sort, you will see the query and why it doesn’t work (maybe clear performance data first to find it easier)
If I’m not mistaken it has something to do with the PK being included where it shouldn’t
Thank you for the answer. I’ll have a look at the performance data and the generated query. But: Is there a reason for this? For me it is just unexpected behaviour. It results in dissatisfied customers. I’d like this to be fixed.
You can test that behaviour best when you have a list form with headers, and the form.view-property is “Table view (locked)”.
(By the way, I prefer a detail view layout also for those forms, as you can move columns around and change their width much easier, see example.)
When you have a normal column with lets say a string with some null values, and you sort that column with a click on the header, the rows with null values stay.
But when you do that on a column that has a related value, in my example it is persons_to_business_partners.bp_displayed_name (field shows business_partner_id as name), the records/rows where that relation does not have a value disappear in case you have defined the relation as an inner join.
In case it is a left outer join, the records/rows stay.
So it seems it is expected behaviour, and you can tweak it by choosing the relation type.
To show/filter null values explicitly, you could add a checkbox in the table header, put a boolean value to it (defined as a form variable) and then run a filter with the onDataChange-Event.
That way your users could always filter those null-value-records easily:
if (newValue) {
if (foundset.find() || foundset.find()) {
business_partner_id === null;
foundset.search();
}
} else {
foundset.loadAllRecords();
}
Bernd.N:
But when you do that on a column that has a related value, in my example it is persons_to_business_partners.bp_displayed_name (field shows business_partner_id as name), the records/rows where that relation does not have a value disappear in case you have defined the relation as an inner join.
In case it is a left outer join, the records/rows stay.
This problem is not the same as described.
I know I came across a similar issue a couple of months ago.
It has something to do with the valuelist and the relation used in that particuar valuelist.
It seems to generate sql that isn’t correct.
So when you take away the attached value list from that column that you sort, the null values stay?
How exactly is the dynamic value list created?
And would it be an option to use a stored calculated field in which you put the needed value from the value list? That could take away the sorting problem and may be at least a short term work around for the problem.
Hi Bernd
Yes, if I take away the value list, the records are not removed while sorting.
The value list is filled with a global relation.
My current work around is the onSort command which executes the default code. Easy.
Kind regards
Birgit Rieder
