Sorting of custom subpanel not working according to custom query

[Piyush1]

I’ve added a custom subpanel for the service module in the accounts module, using a custom query to fetch data. However, sorting in the subpanel isn’t working correctly; when I click on a column to sort, it loads all service module data instead of sorting the existing data. How can I troubleshoot and fix this sorting issue?

public/custom/Extension/modules/Accounts/Ext/Layoutdefs/accounts_j_services_1_Accounts.php

<?php

$layout_defs["Accounts"]["subpanel_setup"]['accounts_j_services_1'] = array(
  'order' => 100,
  'module' => 'j_Services',
  'subpanel_name' => 'default',
  'sort_order' => 'asc',
  'sort_by' => 'id',
  'generate_select' => true,
  'title_key' => 'LBL_ACCOUNTS_J_SERVICES_1_FROM_J_SERVICES_TITLE',
  'get_subpanel_data' => 'function:getRelatedServiceQueryData',
  'top_buttons' => array(),
  'function_parameters' => array(
    'import_function_file' => 'custom/modules/Accounts/GetRelatedServiceQueryData.php',
    'accountId' => $this->_focus->id
  ),
);

public/custom/modules/Accounts/GetRelatedServiceQueryData.php

<?php

function getRelatedServiceQueryData($param)
{
    $accountId = $param['accountId'] ?? '';
    $return_array['select'] = " SELECT j_services.id ,j_services.name,round(j_services.minimum_fee,2) AS 'minimum_fee',j_services.date_entered,j_services.date_modified, j_services_cstm.agency_c, j_services_cstm.years_c,j_services_cstm.service_category_c,leads.id AS 'LeadID',j_services_cstm.status_c,j_services_cstm.date_completed_c,j_services_cstm.date_cancelled_c ";
    $return_array['from'] = " FROM j_services ";
    $return_array['where'] = " j_services.deleted=0 AND leads.account_id='" . $accountId . "' ";
    $return_array['join'] = " INNER JOIN leads_j_services_1_c ON leads_j_services_1_c.leads_j_services_1j_services_idb = j_services.id AND leads_j_services_1_c.deleted=0
    LEFT JOIN leads ON leads.id = leads_j_services_1_c.leads_j_services_1leads_ida AND leads.deleted=0 ";
    $return_array['join_tables'] = '';
    return $return_arr

Is there any way to override sorting data in the subpanel, similar to how we override fetching data?

[Pablonr]

I’m guessing that when you try to sort the query is being called again and since you don’t have an order clause it is not being applied.

I’m not sure about all the data your receive in the $params variable but maybe there is an order by key that you can use to add an order by clause to your custom query.

I would recommend using xdebug for debugging the function to look at all the options that are available in the code execution.

[Piyush1]

@Piyush1

accounts_j_services_1

@Pablonr
Thank you for the reply. I will check that. Also, I have one more question: Is it necessary for the service module to have a relationship with accounts to add its custom subpanel to the accounts module?

[pgr]

Have a look at this example:

Subpanels Cases

Let me try giving you a copy of a different example. My advice is to first try to get my example working as it is, without changing anything; only after it’s working you try and adapt it to your requirement, ok? Create this file custom/Extension/modules/Accounts/Ext/Layoutdefs/Leads.php with these contents: <?php $layout_defs["Accounts"]["subpanel_setup"]['leads'] = array( 'order' => 80, 'module' => 'Leads', 'sort_order' => 'asc', 'sort_by' => 'first_name, last_name', 's…

It’s a slightly different way of customizing, and you just put in the whole query. Try using an
ORDER BY clause there, I expect it should work.

[dhuntress]

// Add the ORDER BY clause here

<?php

function getRelatedServiceQueryData($param)
{
    $accountId = $param['accountId'] ?? '';
    $return_array['select'] = " SELECT j_services.id ,j_services.name,round(j_services.minimum_fee,2) AS 'minimum_fee',j_services.date_entered,j_services.date_modified, j_services_cstm.agency_c, j_services_cstm.years_c,j_services_cstm.service_category_c,leads.id AS 'LeadID',j_services_cstm.status_c,j_services_cstm.date_completed_c,j_services_cstm.date_cancelled_c ";
    $return_array['from'] = " FROM j_services ";
    $return_array['where'] = " j_services.deleted=0 AND leads.account_id='" . $accountId . "' ";
    $return_array['join'] = " INNER JOIN leads_j_services_1_c ON leads_j_services_1_c.leads_j_services_1j_services_idb = j_services.id AND leads_j_services_1_c.deleted=0
    LEFT JOIN leads ON leads.id = leads_j_services_1_c.leads_j_services_1leads_ida AND leads.deleted=0 ";
    $return_array['join_tables'] = '';
 >   $return_array['order_by'] = "ORDER BY j_services.id DESC ";
    return $return_arr

That will in essence format the information before it’s displayed to you - so you’ll not have to worry about sorting when on the subpanel.

[Piyush1]

@Subpanels Cases

param

@pgr @Pablonr Thank you, The issue is resolved. Initially, I had created a relationship for adding the subpanel, which was unnecessary. I made changes in these three files.
created new file:
public/custom/Extension/modules/Accounts/Ext/Layoutdefs/services_custom_subpanel.php

<?php
// created: 2024-02-15 08:34:23
$layout_defs["Accounts"]["subpanel_setup"]['custom_service_subpanel'] = array(
    'order' => 100,
    'module' => 'j_Services',
    'subpanel_name' => 'ForAccounts',
    'title_key' => 'LBL_SERVICES_CUSTOM_SUBPANEL_FOR_ACCOUNTS',
    'get_subpanel_data' => 'function:getRelatedServiceQueryData',
    'generate_select' => true,
    'top_buttons' => array(),
    'function_parameters' => array(
        'import_function_file' => 'custom/modules/Accounts/GetRelatedServiceQueryData.php',
        'accountId' => $this->_focus->id,
        'return_as_array' => 'true'
    ),
);

public/custom/modules/Accounts/GetRelatedServiceQueryData.php

<?php

/**
 * Function : getRelatedServiceQueryData
 * This function is for creating the query for fetching data in service subpannel in accounts
 * @return <Array>
 */
function getRelatedServiceQueryData($param)
{
    global $app;
    $controller = $app->controller;
    $bean = $controller->bean;
    $return_array['select'] = " SELECT j_services.id ,j_services.name,round(j_services.minimum_fee,2) AS 'minimum_fee',j_services.date_entered,j_services.date_modified, j_services_cstm.agency_c, j_services_cstm.years_c,j_services_cstm.service_category_c,leads.id AS 'LeadID',j_services_cstm.status_c,j_services_cstm.date_completed_c,j_services_cstm.date_cancelled_c ";
    $return_array['from'] = " FROM j_services ";
    $return_array['where'] = " j_services.deleted=0 AND leads.account_id='" . $bean->id . "' ";
    $return_array['join'] = " INNER JOIN leads_j_services_1_c ON leads_j_services_1_c.leads_j_services_1j_services_idb = j_services.id AND leads_j_services_1_c.deleted=0
    LEFT JOIN leads ON leads.id = leads_j_services_1_c.leads_j_services_1leads_ida AND leads.deleted=0 ";
    $return_array['join_tables'] = '';
    return $return_array;
}

public/custom/modules/j_Services/metadata/subpanels/ForAccounts.php

<?php
// created: 2024-02-15 09:08:26

if (!defined('sugarEntry') || !sugarEntry) {
  die('Not A Valid Entry Point');
}

$module_name = 'j_Services';
$subpanel_layout = array(
  'top_buttons' => array(
    array('widget_class' => 'SubPanelTopCreateButton'),
    array('widget_class' => 'SubPanelTopSelectButton', 'popup_module' => $module_name),
  ),

  'where' => '',

  'list_fields' => array of list fields
);

[Piyush1]

Thank you for reply @dhuntress , yes that I will do, but I wanted to sort on the basis of column name when user click on header(column name) of subpanel, that now is fixed