by itsapiens

Dynamic Reports and Interactive Charts.
Powerful tool for SuiteCRM report creation and data analytics. Includes a variety of chart types, detailed, summary and pivot tables. This Reporting Tool is for anyone, no need for technical knowledge or SQL. Comes with over a hundred prebuilt reports.

Cancel at any time!
Free Trial

#2363 - values of related Module are not displayed

Open Bug? created by robertlehner Verified Purchase 2 months ago

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 1.jpg

When I want to display parts of the related Module nothing is displayed.
2.jpg

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

  1. robertlehner member avatar

    robertlehner Verified Purchase

    2 months ago

    When I look in devbug logfile I find:
    SELECT t0.name AS 0_Opportunities_name , t0.id AS 0_Opportunities_name_id , 'Opportunities' AS 0_Opportunities_name_setype , t1.name AS 1_itd_test_name , t1.id AS 1_itd_test_name_id , 'itd_test' AS 1_itd_test_name_setype FROM opportunities AS t0

    LEFT JOIN itd_test AS t1 ON t1.id = t0.id

    WHERE (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.name AS 0_Opportunities_name , t0.id AS 0_Opportunities_name_id , 'Opportunities' AS 0_Opportunities_name_setype , t1.name AS 1_itd_test_name , t1.id AS 1_itd_test_name_id , 'itd_test' AS 1_itd_test_name_setype
    FROM
    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, 20

  2. itsapiens member avatar

    itsapiens Provider

    2 months 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

  3. robertlehner member avatar

    robertlehner Verified Purchase

    2 months 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.

This case is public. Please leave out any sensitive information such as URLs, passwords, etc.
Saving Comment Saving Comment...