sort removes null value records

Questions and answers on designing your Servoy solutions, database modelling and other 'how do I do this' that don't fit in any of the other categories

sort removes null value records

Postby rieder » Wed Nov 18, 2015 2:00 pm

Hi

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?

We use:
Servoy

Version: 7.4.4 - build 2039
on Mac OS X 10.10.5

Thanks and regards
Birgit
Birgit Rieder
7r AG, Switzerland
SAN Developer
http://www.seven-r.ch
User avatar
rieder
 
Posts: 82
Joined: Thu Jan 26, 2012 5:18 pm

Re: sort removes null value records

Postby mboegem » Wed Nov 18, 2015 2:17 pm

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
_____________________
Marc Boegem
Solutiative / JBS Group, Partner
• Servoy Certified Developer
• Servoy Valued Professional
• Freelance SAN Developer

Image
User avatar
mboegem
 
Posts: 1395
Joined: Sun Oct 14, 2007 1:34 pm
Location: Hoofddorp, The Netherlands

Re: sort removes null value records

Postby rieder » Wed Nov 18, 2015 2:25 pm

Hi Marc

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.

Best regards
Birgit Rieder
Birgit Rieder
7r AG, Switzerland
SAN Developer
http://www.seven-r.ch
User avatar
rieder
 
Posts: 82
Joined: Thu Jan 26, 2012 5:18 pm

Relation involved in the column to sort?

Postby Bernd.N » Wed Nov 18, 2015 2:27 pm

It may be related to the relation.

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:

Code: Select all
   if (newValue) {
   
      if (foundset.find()  ||  foundset.find()) {
      
         business_partner_id === null;
         foundset.search();
      }
   } else {
   
      foundset.loadAllRecords();
   }


http://www.postgresqltutorial.com/postg ... nner-join/
http://www.postgresqltutorial.com/postgresql-left-join/

Here is a nice German example regarding joins:
http://www.bjoerns-choice.de/archives/20
Attachments
example.png
example.png (9.46 KiB) Viewed 715 times
Bernd Korthaus
LinkedIn
Servoy 7.4.9 SC postgreSQL 9.4.11 Windows 10 Pro
User avatar
Bernd.N
 
Posts: 487
Joined: Mon Oct 21, 2013 5:57 pm
Location: Langenhorn, North Friesland, Germany

Re: sort removes null value records

Postby mboegem » Wed Nov 18, 2015 4:00 pm

Bernd.N wrote: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.
_____________________
Marc Boegem
Solutiative / JBS Group, Partner
• Servoy Certified Developer
• Servoy Valued Professional
• Freelance SAN Developer

Image
User avatar
mboegem
 
Posts: 1395
Joined: Sun Oct 14, 2007 1:34 pm
Location: Hoofddorp, The Netherlands

Re: sort removes null value records

Postby rieder » Wed Nov 18, 2015 4:14 pm

Thank you both.

I agree, the relation is not the reason: I have a second form which has the foundset loaded with sql. Sorting there also removes null value records.

We see the behaviour on columns with dynamic value lists attached. I'll create a case in the support system.

Best regards
Birgit Rieder
Birgit Rieder
7r AG, Switzerland
SAN Developer
http://www.seven-r.ch
User avatar
rieder
 
Posts: 82
Joined: Thu Jan 26, 2012 5:18 pm

Re: sort removes null value records

Postby Bernd.N » Wed Nov 18, 2015 4:29 pm

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.
Bernd Korthaus
LinkedIn
Servoy 7.4.9 SC postgreSQL 9.4.11 Windows 10 Pro
User avatar
Bernd.N
 
Posts: 487
Joined: Mon Oct 21, 2013 5:57 pm
Location: Langenhorn, North Friesland, Germany

Re: sort removes null value records

Postby rieder » Wed Nov 18, 2015 4:43 pm

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
Birgit Rieder
7r AG, Switzerland
SAN Developer
http://www.seven-r.ch
User avatar
rieder
 
Posts: 82
Joined: Thu Jan 26, 2012 5:18 pm


Return to Programming with Servoy

Who is online

Users browsing this forum: No registered users and 4 guests

cron