SuiteCRM - Issue in Report module - Order/Sort related (field type) columns issue

[trungnguyenhd]

Hi all,

I found an issue in the Report module. It looks incorrect when sorting related fields. For example: I want to simply list all Student records (with Student Name, School (related field)) and sort by School. But the result doesn’t sort by School name, it’s sorted by School ID (record UUID) instead. It works with relationship fields but doesn’t work with related fields. (I mean with relationship fields, it corrects sort by name, but will sort by UUID with related fields).

I debugged and get the report query for this case:

SELECT
    `students`.id AS 'students_id',
    `students`.NAME AS 'Student_name0',
    `students`.school_id AS 'School1' 
FROM `students`
WHERE   students.deleted = 0 
ORDER BY    `students`.school_id ASC

So I think we should add the jion (left jion) here with the school table and sort by school.name instead. The file path: modules/AOR_Reports/AOR_Report.php, line 1369, ( if ($field->sort_by != ‘’) {)

if ($field->sort_by != '') {
   // fixed code here
   if($data['type'] == 'id' && !$field->field_function) {
          foreach ($field_module->field_defs as $def) {
              if(isset($def['id_name']) && $def['id_name'] == $field->field) {
                  $relBean = BeanFactory::newBean($def['module']);
                  $relAlias = "customjt{$i}";
                  $query['join'][] = " LEFT JOIN $relBean->table_name as $relAlias ON $relAlias.id = $select_field AND $relAlias.deleted = 0 ";
                  $query['sort_by'][] = $relAlias.".name " . $field->sort_by; // fixed 'name' here, need to refactor to get the correct field
                  break;
              }
          }
      } else {
     // orgin code here
      }
}

So please check and update it in the next version (SuiteCRM 7.x) More info: I'm using SuiteCRM version 7.11.15, and I also check the latest version but the issue above still not fixed. Thanks

[SuiteBot]

This issue has been mentioned on SuiteCRM. There might be relevant details there:

https://community.suitecrm.com/t/suitecrm-issue-in-report-module-order-sort-related-field-type-columns-issue/86634/1

[GeeteshMehto]

Hello sir, please assign this issue to me as i already worked on this kind of problem in past and has a great experience.

[pgorod]

Hi @GeeteshMehto

We don't normally use the assignments here, people just go ahead and create PR's, so feel free to work on this.

[timo-ecm2]

@GeeteshMehto, feel free to enjoy yourself. I am really interested in this one.