Problem with query to return the values in a relationship between two modules

💬 General Discussion
1

Hello,

I have the following code in the /suitecrm/custom/modules/Opportunities/Ext/Layoutdefs, wich represents the subpanel in a relationship between “Opportunities” and “Invoices”:


<?php
 // created: 2019-02-08 12:00:01
$layout_defs["Opportunities"]["subpanel_setup"]['aos_invoices_opportunities_1'] = array (
  'order' => 100,
  'module' => 'AOS_Invoices',
  'subpanel_name' => 'default',
  'sort_order' => 'asc',
  'sort_by' => 'id',
  'title_key' => 'LBL_AOS_INVOICES_OPPORTUNITIES_1_FROM_AOS_INVOICES_TITLE',
  'get_subpanel_data' => 'function:get_aosInvoices_inoppo_subpanel',
  'function_parameters' => array(

    'import_function_file' => 'custom/modules/Opportunities/am_functions.php',
    'return_as_array' => true

  ),
  'top_buttons' => 
  array (
    0 => 
    array (
      'widget_class' => 'SubPanelTopButtonQuickCreate',
    ),
    1 => 
    array (
      'widget_class' => 'SubPanelTopSelectButton',
      'mode' => 'MultiSelect',
    ),
  ),
);

And the function, which are in the following path suitecrm/custom/modules/Opportunities, is the following:



<?php

function get_aosInvoices_inoppo_subpanel()
{


    return array(
        "select" => "SELECT DISTINCT d.aos_invoices_opportunities_1aos_invoices_ida",
        "from" => "FROM `aos_invoices_opportunities_1_c` d",
    );

}

The function does the following: gets the unique value from the table created when I create the relationship.

When I access the Opportunities, gives me the following error and the subpanel does not appear


Mysqli_query failed.
[FATAL] Error running count query for Opportunity List:  Query Failed: (): 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 ')' at line 1

Can someone help me ?

2

Hi,
Can you please check SUITECRM.LOG file, there will be the full query logged with error. You may get get the error hint from there.
Thnaks

1 Like
3

Change sugar log level to “Debug” , then clean your log file , then refresh your action/pages.

Fresh logs will be appear, this is where your full query will be appreared.

1 Like
4

Hello,

The error that I post before, is from the “suitecrm.log” file.

Thank you.

5

The error thrown is the same even when I change to Debug mode.

Is another way to do this ?

Thank you

6

I am also facing same issue …can someone please help me out from this issue.

When i try to create a new subpanel it is not fetching the data set due to the pagination count query issue.

Query Failed: (): 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 ‘)’

i have commented the respected lines
$final_query and $query = ‘(’ . $query . ‘)’; SugarBean.php file but no use after that change also.