starred/SuiteCRM-SuiteCRM

Fork 0

mirror of https://github.com/SuiteCRM/SuiteCRM.git synced 2026-03-02 19:16:58 -05:00

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

New issue

Open

opened 2026-02-20 16:25:28 -05:00 by deekerman · 4 comments

deekerman commented

2026-02-20 16:25:28 -05:00

Owner

Originally created by @trungnguyenhd on GitHub (Oct 18, 2022).

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

Originally created by @trungnguyenhd on GitHub (Oct 18, 2022). 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). ![Edit](https://user-images.githubusercontent.com/33930131/196415988-77dadc19-4e61-416a-98cb-3c9627fcaa6e.png) ![image](https://user-images.githubusercontent.com/33930131/196415949-e3fc3dee-f75a-4377-b3a3-a28c4d4e06a7.png) 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

deekerman added the

labels

2026-02-20 16:25:28 -05:00

deekerman commented

2026-02-20 16:25:29 -05:00

Author

Owner

@SuiteBot commented on GitHub (Oct 18, 2022):

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

@SuiteBot commented on GitHub (Oct 18, 2022): 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

deekerman commented

2026-02-20 16:25:30 -05:00

Author

Owner

@GeeteshMehto commented on GitHub (Oct 19, 2022):

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

@GeeteshMehto commented on GitHub (Oct 19, 2022): Hello sir, please assign this issue to me as i already worked on this kind of problem in past and has a great experience.

deekerman commented

2026-02-20 16:25:30 -05:00

Author

Owner

@pgorod commented on GitHub (Oct 19, 2022):

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.

@pgorod commented on GitHub (Oct 19, 2022): 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.

deekerman commented

2026-02-20 16:25:30 -05:00

Author

Owner

@timo-ecm2 commented on GitHub (Oct 20, 2022):

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

@timo-ecm2 commented on GitHub (Oct 20, 2022): @GeeteshMehto, feel free to enjoy yourself. I am really interested in this one.