Reports - cannot group by related field

SuiteCRM Forum - English Language SuiteCRM General Discussion
1

Hello.

I am trying to create a report of Contacts grouped by Account Name, but it doesn’t work. I have created a Report with module Contact, added Account Name, First Name and Last Name fields, and set the “Main Group” to “Account Name”. But the report appears as if no Main Group was specified.

However, if I change the “Main Group” to “First Name”, the report displays as I would expect.

Digging through the code, I think the problem is in the build_group_report() method of AOR_Report. Teh query it generates is

SELECT `contacts`.account_id AS 'Business_Name' FROM contacts  WHERE ( `contacts`.account_id IS NOT NULL  AND  contacts.deleted = 0  ) GROUP BY `contacts`.account_id ORDER BY Business_Name ASC

This fails because there is no “account_id” field in Contact, because the relationship between Contact and Account is many-to-many.

Does anyone know if there is a work-around for this?

Regards,

Carl

2

Found a work-around. The way that I added the “Account Name” was to select “Contact” in the “Module Tree”, and then “Account Name” from the “Fields” list, which doesn’t work.

However, if I select “Accounts” in the “Module Tree” and then “Name” from “Fields” list, it does work.

Hope this helps someone else.

Regards,

Carl

2 Likes
3

Thanks! Had a very similar problem and that solved my grouping problem.