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.
#2363 - values of related Module are not displayed
Hi.
I've a custom module named TEST which has a m:1 relationship to opportunities. (One opportunity many TEST entries)
I create a report (Standard) with base Opportunity and TEST as Sub 
When I want to display parts of the related Module nothing is displayed.
What do I wrong?
Im working on Ubunto 1604 with php7.0 and Mysql DB with suitecrm 7.11.9.
Also when I open the report I get following in suitecrm.log:
Wed Nov 13 11:50:25 2019 [10464][1][FATAL] Query Failed: SELECT COUNT(*) AS COUNT FROM (SELECT FROM opportunities AS t0 WHERE (1=1 ) AND (1=1 )) AS counterTable: MySQL error 1064: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'FROM opportunities AS t0 WHERE (1=1 ) AND (1=1 )) AS counterTable' at line 1
Wed Nov 13 11:50:25 2019 [10464][1][FATAL] Mysqli_query failed.
Wed Nov 13 11:50:25 2019 [10464][1][FATAL] Query Failed: SELECT FROM opportunities AS t0 WHERE (1=1 ) AND (1=1 ) LIMIT 0, 20: MySQL error 1064: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'FROM opportunities AS t0 WHERE (1=1 ) AND (1=1 ) LIMIT 0, 20' at line 1
reagrds Robert
palaphon
5 years ago
When I look in devbug logfile I find: SELECT t0.
nameAS0_Opportunities_name, t0.idAS0_Opportunities_name_id, 'Opportunities' AS0_Opportunities_name_setype, t1.nameAS1_itd_test_name, t1.idAS1_itd_test_name_id, 'itd_test' AS1_itd_test_name_setypeFROM opportunities AS t0LEFT JOIN
itd_testAS t1 ON t1.id=t0.idWHERE (1=1 ) AND (1=1 ) LIMIT 0, 20
This join doesn't work. Suitecrm is using a mapping table for m:1 and m:m relationships : the right statement should be: SELECT t0.
nameAS0_Opportunities_name, t0.idAS0_Opportunities_name_id, 'Opportunities' AS0_Opportunities_name_setype, t1.nameAS1_itd_test_name, t1.idAS1_itd_test_name_id, 'itd_test' AS1_itd_test_name_setypeFROM opportunities AS t0 INNER JOIN opportunities_itd_test_1_c ON t0.id = opportunities_itd_test_1_c.opportunities_itd_test_1opportunities_ida LEFT JOIN itd_test AS t1 ON t1.id = opportunities_itd_test_1_c.opportunities_itd_test_1itd_test_idb WHERE (1=1 ) AND (1=1 ) LIMIT 0, 205 years ago
Hi Robert,
thanks for the very detailed problem description. It seems the problem is very trivial here. When you created the report with Opportunities related to your Test module the relationship type you selected was "One to many" and for the columns you selected both "id" so it is trying to map entries from opportunities with id equal to Test records id. This of course is not correct as you demonstrated in the first query you posted above.
The fix should be quite easy- change the relationship type from "One to Many" to "Many to many" this will allow you to select a relationship table and set up the join as you demonstrated in your second query.
In SuiteCRM most custom modules or relationships are created through a relational table instead of a direct column inside Test opportunity_id, so even when you have a One to Many Relationship logically, Analytic Reporting interprets all relationships with a relational table as "Many to Many", then you can specify the table and all fields and you should be able to join the modules correctly.
If you are unable to solve the issue or have any other questions regarding our Analytic Reporting tool please do not hesitate to contact us: arturs.priede@itsapiens.eu.
Best wishes Arturs
5 years ago
Hi. Thx this helps. But it is a little bit confusing because "One to Many" and "Many to many" means in CRM a relationship with a relational table. In your term a "One to Many" realtionship is a RelateField in CRM.
Thx for assistance.