Dynamic Reports and Interactive Charts. A powerful tool for SuiteCRM report creation and data analytics. Includes a variety of chart types, detailed, summary and pivot tables. This Reporting Tool is very user-friendly, no need for technical knowledge or SQL. The Sapiens.BI tool comes with over 100 pre-built reports that are ready to be used on day one.
#415 - Error in join condition with custom related field
Hi, I'm tryng the basic version (trial period began yesterday), but I found an error in query generated from the tool. I'm bulding report on custom module. In the custom module i have a cutom field of related type, but when the report build a query the join condition of the custom related field is not on custom tabel (_cstm).
Here the query generated by the report
SELECT
t0.name AS 0_Accounts_name,
t0.id AS 0_Accounts_name_id,
'Accounts' AS 0_Accounts_name_setype,
t1.name AS 2_INJ_prodotti_anagrafici_name,
t1.id AS 2_INJ_prodotti_anagrafici_name_id,
'INJ_prodotti_anagrafici' AS 2_INJ_prodotti_anagrafici_name_setype,
t4.j_data_acq_c AS 1_INJ_prodotti_acquistati_j_data_acq_c
FROM
accounts AS t0
LEFT JOIN
inj_prodotti_acquistati_accounts_c AS t3_inj_prodotti_acquistati_accounts_c ON (t3_inj_prodotti_acquistati_accounts_c.inj_prodotti_acquistati_accountsaccounts_ida = t0.id
AND t3_inj_prodotti_acquistati_accounts_c.deleted = '0')
LEFT JOIN
inj_prodotti_acquistati AS t2 ON (t2.id = t3_inj_prodotti_acquistati_accounts_c.inj_prodotti_acquistati_accountsinj_prodotti_acquistati_idb)
** LEFT JOIN
inj_prodotti_anagrafici AS t1 ON t1.id = t2.inj_prodotti_anagrafici_id_c**
LEFT JOIN
inj_prodotti_acquistati_cstm AS t4 ON t4.id_c = t2.id
WHERE
(1 = 1) AND (1 = 1)
LIMIT 0 , 20
Here the right query that should be generated
SELECT
t0.name AS 0_Accounts_name,
t0.id AS 0_Accounts_name_id,
'Accounts' AS 0_Accounts_name_setype,
t1.name AS 2_INJ_prodotti_anagrafici_name,
t1.id AS 2_INJ_prodotti_anagrafici_name_id,
'INJ_prodotti_anagrafici' AS 2_INJ_prodotti_anagrafici_name_setype,
t4.j_data_acq_c AS 1_INJ_prodotti_acquistati_j_data_acq_c
FROM
accounts AS t0
LEFT JOIN
inj_prodotti_acquistati_accounts_c AS t3_inj_prodotti_acquistati_accounts_c ON (t3_inj_prodotti_acquistati_accounts_c.inj_prodotti_acquistati_accountsaccounts_ida = t0.id
AND t3_inj_prodotti_acquistati_accounts_c.deleted = '0')
LEFT JOIN
inj_prodotti_acquistati AS t2 ON (t2.id = t3_inj_prodotti_acquistati_accounts_c.inj_prodotti_acquistati_accountsinj_prodotti_acquistati_idb)
LEFT JOIN
inj_prodotti_acquistati_cstm AS t4 ON t4.id_c = t2.id
** LEFT JOIN
inj_prodotti_anagrafici AS t1 ON t1.id = t4.inj_prodotti_anagrafici_id_c**
WHERE (1 = 1) AND (1 = 1) LIMIT 0 , 20
The tool is very interesting, but I think this is a bug, can you confir? Is it possible to solve this issue?
Thanks a lot
Matteo
palaphon
6 years ago
Hello Matteo,
Thank you for contacting us! We will now take a look into this, we will contact you as soon as fix is ready or if there will be additional questions.
Best Regards, IT Sapiens Team
6 years ago
Hello Matteo,
Latest upgrade with fix of the issue reported is now uploaded to SuiteCRM store. Please update via SuiteCRM Module Loader (no need to uninstall previous. Please let us know, if you can confirm the fix of the issue.
Thank you!
Best Regards, IT Sapiens Team
6 years ago
Hi, I tested the report afer I installed the upgrade and now the problem is fixed. Thanks a lot for the quick reply and solution
Matteo
6 years ago
Thank you for the feedback, Matteo!
Best Regards, IT Sapiens Team