I’m testing the reporting module in version 7.2.1 suiteCRM see that adding custom fields in a report it stops working.
Example : ready Accounts related to the Opportunities by name and works well. If this same report will add a custom field for example Ready accounts related to the Opportunities by name and date of sale of the opportunity ( custom field ) fails to bring this data.
Seeing the querys in the log I see that not used properly aliases ;
for example:
SELECT count(*) c FROM (SELECT accounts.id AS ‘accounts_id’, accounts.name AS ‘Nombre0’, accounts.billing_address_street AS ‘Calle_de_facturación1’, accounts.billing_address_postalcode AS ‘CP_de_facturación2’, accounts.billing_address_city AS ‘Ciudad_de_facturación3’, accounts.billing_address_state AS ‘Estado/provincia_de_facturación4’, accounts:opportunities.id AS ‘accounts:opportunities_id’, accounts:opportunities.name AS ‘Nombre_Oportunidad5’, accounts:opportunities_cstm.r_rodados_id_c AS ‘Rodado6’ FROM accounts LEFT JOIN accounts_opportunities accounts|accounts:opportunities ON accounts.id=accounts|accounts:opportunities.account_id AND accounts|accounts:opportunities.deleted=0
LEFT JOIN opportunities accounts:opportunities ON accounts:opportunities.id=accounts|accounts:opportunities.opportunity_id AND accounts:opportunities.deleted=0
LEFT JOIN opportunities_cstm accounts:opportunities_cstm ON opportunities.id = accounts:opportunities_cstm.id_c WHERE accounts:opportunities.sales_stage = ‘Vendida’ AND accounts:opportunities.name != ‘MaquinaUsada’ AND accounts.deleted = 0 GROUP BY accounts.name) as n
This opportunities_cstm declaring for table alias accounts: opportunities_cstm and then being used as follows: accounts: opportunities_cstm.r_rodados_id_c ie without the quotes with which he defined.
Opportunities for table defines the alias’ accounts: opportunities` and then when does the inner with opportunities_cstm not use this alias and use the table name directly.
Modify the query and use the alias as it is defined
Modified query:
SELECT count(*) c FROM (SELECT accounts.id AS ‘accounts_id’, accounts.name AS ‘Nombre0’, accounts.billing_address_street AS ‘Calle_de_facturación1’, accounts.billing_address_postalcode AS ‘CP_de_facturación2’, accounts.billing_address_city AS ‘Ciudad_de_facturación3’, accounts.billing_address_state AS ‘Estado/provincia_de_facturación4’, accounts:opportunities.id AS ‘accounts:opportunities_id’, accounts:opportunities.name AS ‘Nombre_Oportunidad5’, accounts:opportunities_cstm.r_rodados_id_c AS ‘Rodado6’ FROM accounts LEFT JOIN accounts_opportunities accounts|accounts:opportunities ON accounts.id=accounts|accounts:opportunities.account_id AND accounts|accounts:opportunities.deleted=0
LEFT JOIN opportunities accounts:opportunities ON accounts:opportunities.id=accounts|accounts:opportunities.opportunity_id AND accounts:opportunities.deleted=0
LEFT JOIN opportunities_cstm accounts:opportunities_cstm ON accounts:opportunities.id = accounts:opportunities_cstm.id_c WHERE accounts:opportunities.sales_stage = ‘Vendida’ AND accounts:opportunities.name != ‘MaquinaUsada’ AND accounts.deleted = 0 GROUP BY accounts.name) as n
This query in phpmyadmin tried it and it works fine .
Is this a bug in version 7.2.1 ?
I tried this and other reports with custom fields in version 7.1.2 and function . To try in version 7.2.1 do not work and they all have the same problem with aliases to assemble the query .
in version 7.2.5beta also fails the query