Reports - If the first reported belongs to a Module that is not the "Report Module", this can cause reports to fail on custom fields.

[johnM2401]

Issue

Possibly related to: https://github.com/salesagility/SuiteCRM-Core/issues/613

When creating a report, users are able to select related modules and their fields.

However, if the user creates a report that has the first reportable field as one that is NOT the same module as the "Report Module", it falls over if any subsequent fields are custom fields.

It looks as though it is trying to access the wrong module's custom table.

Using a report set like:

This generates the Query:

SELECT `aos_quotes:accounts`.id AS 'aos_quotes:accounts_id', `aos_quotes:accounts`.name AS 'Name0', `aos_quotes`.id AS 'aos_quotes_id', `aos_quotes`.name AS 'Title1', `aos_quotes:accounts`.testdd_c AS 'testDD2' FROM `aos_quotes` LEFT JOIN accounts `aos_quotes:accounts` ON `aos_quotes`.billing_account_id=`aos_quotes:accounts`.id AND `aos_quotes:accounts`.deleted=0
 WHERE aos_quotes.deleted = 0  GROUP BY `aos_quotes`.name, `aos_quotes:accounts`.id, `aos_quotes:accounts`.name, `aos_quotes`.id, `aos_quotes:accounts`.testdd_c

Generating the error:

#1054 - Unknown column 'aos_quotes:accounts.testdd_c' in 'field list'

Possible Fix

No response

Steps to Reproduce the Issue

1. Create a Report
2. Set the first report field to a related module's CORE field
3. Set a subsequent report field to a custom field in the Report Module
4. Set a subsequent report field to a custom field in the related module
...

Context

No response

Version

8.8.0

What browser are you currently using?

Chrome

Browser Version

No response

Environment Information

MariaDB 10.4.12 / PHP 8.3

Operating System and Version

Ubuntu 20.04

[johnM2401]

Looks like this is the same as (or related to) this in the SuiteCRM7 Repo: https://github.com/salesagility/SuiteCRM/issues/10685

[Sentinel79]

Same here.

If a custom field is selected from a related field, the SQL query is not correct because the field is searched for in the main table, where it does not exist. The _cstm tables are not included in the LEFT JOIN at all.

Unfortunately, the workaround from https://github.com/salesagility/SuiteCRM-Core/issues/613 did not help here

[BastianHammer]

There has been a duplicate ticket - just for keeping the info here #692