pagination problems on v12

Hi Francesca Shiekh,

This is typically happening due to a join in the query. I would check directly the related fields that are in the list. 
Try to reduce one another from the subpanel list view, the issue might resolve. (I mean, you would found a starting point for your debugging) 

Tevfik Tümer
Sr. Developer Support Engineer 

I double checked the list views that are showing this issue and the only relate field is a relate field to another Contacts record. There is no other relate field, other than the implied relationship with email addresses. I also tried defaulting back to the default subpanel and I still have the same issue.

We do have an customization from v6.x that was made by our Partner at the time to allow us to have an M-M relationship between Accounts and Contacts. I am not entirely sure what those changes were at that time to be honest. Could that be causing issues?

It's challenging to elaborate without seeing the application in its entirety. However, my approach would be to enable debug mode and observe the executed queries.

How do the queries look? I would certainly switch to debug mode to identify what is limiting the expected records.

Thanks again,

I don't have actual Contacts queries in the log even with the DEBUG on, but I did find this error/debug line but those three contacts look fine, and are related to the Account in question, It's also strange that it seems to be reporting 4 records that are not in the Bean set, but only 3 IDs.

Thu Oct 10 14:11:30 2024 [22817][1][ERROR] Non-distinct result set detected: sqlRows = 11 vs beanSet = 7

Thu Oct 10 14:11:30 2024 [22817][1][DEBUG] Non-distinct offending record ids: f8ee074a-7630-11e7-a836-001a4a160206, 3ab4a364-e878-dccc-0b4f-4ee36206fb8a, 7a1daa5d-4fda-0bf6-a984-56f564680c7f

I have a v14 upgraded development instance and it has the same error.

Thanks again,

I don't have actual Contacts queries in the log even with the DEBUG on, but I did find this error/debug line but those three contacts look fine, and are related to the Account in question, It's also strange that it seems to be reporting 4 records that are not in the Bean set, but only 3 IDs.

Thu Oct 10 14:11:30 2024 [22817][1][ERROR] Non-distinct result set detected: sqlRows = 11 vs beanSet = 7

Thu Oct 10 14:11:30 2024 [22817][1][DEBUG] Non-distinct offending record ids: f8ee074a-7630-11e7-a836-001a4a160206, 3ab4a364-e878-dccc-0b4f-4ee36206fb8a, 7a1daa5d-4fda-0bf6-a984-56f564680c7f

I have a v14 upgraded development instance and it has the same error.

Lets try this, 

Could you remove all the fields from the subpanel list view except id and name fields (stock ones)?

Let me know if this issue is happening? 

Tev.

In v14.0.0 I removed everything but "Name" and tested adding the fields I had before one at a time.

The Subpanel displayed correctly 10 entries at a time, paginating correctly for each added field except Email.

When I added Email then the counts are off and the scrolling is broken.

I wiped the entire subpanel from Accounts and rebuilt it in Studio and the problem came back with the addition of the Email:

<?php
// created: 2024-10-11 16:03:43
$viewdefs['Contacts']['base']['view']['subpanel-for-accounts-contacts'] = array (
  'type' => 'subpanel-list',
  'panels' =>
  array (
    0 =>
    array (
      'name' => 'panel_header',
      'label' => 'LBL_PANEL_1',
      'fields' =>
      array (
        0 =>
        array (
          'name' => 'name',
          'type' => 'fullname',
          'fields' =>
          array (
            0 => 'salutation',
            1 => 'first_name',
            2 => 'last_name',
          ),
          'link' => true,
          'label' => 'LBL_LIST_NAME',
          'enabled' => true,
          'default' => true,
        ),
        1 =>
        array (
          'name' => 'email',
          'label' => 'LBL_LIST_EMAIL',
          'enabled' => true,
          'default' => true,
        ),
        2 =>
        array (
          'name' => 'phone_work',
          'label' => 'LBL_LIST_PHONE',
          'enabled' => true,
          'default' => true,
        ),
     

Additional oddity: When updating the subpanel from Studio I had to add a field, save-and-deploy. And the field I just added would move back to the Hidden column. The second time I re-added it and save-and-deploy it would stick.

I am going to dig through my customizations and see if we had changes to the email field at any point in time.

Thank you,

FrancescaS

Sounds great. 

As I mentioned previously this is happening due to the relationship. SQL Returns some records but Distinct evaluation wipe some of them and log them aside. 

At this moment, I could blindly guess, somehow an email would be use in multiple same records. You can check the IDs logged in the email addresses to see if it gives you more info. Moreover, the best could be log only the related api end point logs in the sugarcrm.log to get exact query. 

Good luck on your investigations. 

Let me know if you have any further questions.

Tev.

 Tevfik Tümer you hit the nail on the head!

Multiple contacts related to the Account shared a corporate email address as the secondary address. Removing that address fixes the issue.

So it seems that multiple Contacts on an Account CANNOT share an email address or things will get out of whack.

This is going to be a problem for us since it's not an unusual case.

I will be filing a case to see what the developer side of the house thinks.

Note: I also had a problem with the denormalizaiton of Account Name on Contacts, given that our relationship is M-M since v6 the denorm was causing duplicated records (one per account name).

Thank you!