Can't add custom fields to reports

hserour · 13 August 2025 08:02

we just upgraded from 8.7 to 8.8 reports were working fine now whenever we add a custom field report doesn’t load any data and i checked log and found out that the query fails as it isn’t getting the custom field from the cstm table in database

Wed Aug 13 2025 at 07:53:26 [3232271][seed_max_id][FATAL] Mysqli_query failed.
Wed Aug 13 2025 at 07:53:26 [3232271][seed_max_id][INFO] Query Execution Time:0.0009620189666748
Wed Aug 13 2025 at 07:53:26 [3232271][seed_max_id][FATAL]  Query Failed: SELECT count(*) c FROM (SELECT `opportunities`.name AS 'Opportunity_Name0', `opportunities`.assigned_user_id AS 'Assigned_to1', `accounts`.id AS 'Customer2', `opportunities`.opportunityid_c AS 'Opportunity_Id3', `opportunities`.id AS 'opportunities_id', `accounts`.id AS 'accounts_id' FROM `opportunities` LEFT JOIN accounts_opportunities `opportunities|accounts` ON `opportunities`.id=`opportunities|accounts`.opportunity_id AND `opportunities|accounts`.deleted=0

BastianHammer · 13 August 2025 08:11

Hello Heba,

it’s not this one here?

github.com/SuiteCRM/SuiteCRM-Core

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

opened 02:00PM - 02 May 25 UTC

johnM2401

Type: Bug Priority:Important Area: Reports Severity: Major

### Issue Possibly related to: https://github.com/salesagility/SuiteCRM-Core/is…sues/613 <br> 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. <br> Using a report set like: ![Image](https://github.com/user-attachments/assets/a86c5afb-ebf1-4f45-ab56-2ca45704b9d5) 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 ```bash 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

Your first field in the report is a standard field?
How does your screen look like, when you execute the report?
And your using aggregates as well as it seems?
If it’s not the linked issue, can you find it in the Github tickets?

hserour · 13 August 2025 08:21

no there are fields that belong to report module
my first field is opportunity name

when i remove opportunity id data appears normally

hserour · 13 August 2025 08:22

and no filters or charts or any grouped fields

BastianHammer · 13 August 2025 08:37

Hmm … I’ve just tried it in the online version which worked well.

Did you use the same setup?
Customer is the account underneath the Opp?

hserour · 13 August 2025 09:27

not this id it is another custom field
and yes customer is account

BastianHammer · 13 August 2025 09:57

Ah, yes - I’ve been able to replicate the issue on a local system:

The issue is:

 MySQL error 1054: Unknown column 'opportunities.custom_opp_id_c' in 'field list'

and that is correct - it would need to look inside the cstm table for that field.

I haven’t found this exact issue in the Suite8 queue. Maybe is the issue exists in Suite7 as well.
Do you want to create a ticket?
Otherwise I’d do it later on.

hserour · 13 August 2025 12:53

no i can wait for you to create it
thank you

rsp · 13 August 2025 13:28

Do you have values/records for custom field?

BastianHammer · 13 August 2025 14:30

Yes, I’ve added some - still doesn’t show.
It seems like the report is simply checking the wrong table - at least if the logged query is the one that’s actually being used for the report.

Actually @hserour
I’ve tested around a bit further.
Can you move your custom field to the 2nd position of the report?

standard fields
custom fields
related fields

Then it seems to be working.

I’ve created an issue:

github.com/SuiteCRM/SuiteCRM-Core

Custom fields are breaking reports - depending on the order of fields

opened 03:11PM - 13 Aug 25 UTC

BastianHammer

Type: Bug

### Issue If there are custom fields inside reports, the reports won't load any…more / won't show any data, if the custom field is placed behind related fields. I've tested it with opportunities and added a text field. This order of fields is working: <img width="2073" height="711" alt="Image" src="https://github.com/user-attachments/assets/aa1d60a1-e669-4bec-b619-e08fb3fc9552" /> 1. standard fields 2. custom fields 3. related fields If I flip it to: 1. standard fields 2. related fields 3. custom fields it's not working anymore: <img width="2697" height="723" alt="Image" src="https://github.com/user-attachments/assets/71725604-c6d3-417a-b3e9-1658add66031" /> <img width="3803" height="493" alt="Image" src="https://github.com/user-attachments/assets/2ef5cf7d-4a5f-4810-8ff1-2ee8280549eb" /> In my suitecrm.log I can see this bit here: `MySQL error 1054: Unknown column 'opportunities.custom_opp_id_c' in 'field list'` when its not working. ### Possible Fix _No response_ ### Steps to Reproduce the Issue ```bash 1. Add a custom field to a module 2. Create a report with name + custom field ``` ### Context Can't build reports as I see fit. Initially discovered via a forum topic: https://community.suitecrm.com/t/cant-add-custom-fields-to-reports/99920 ### Version 8.9 beta ### What browser are you currently using? Firefox ### Browser Version _No response_ ### Environment Information PHP 8.3 ### Operating System and Version Ubuntu