Add custom fields to subpanel

[Lawliet]

Hello,
I have created a one-to-many relationship between the parent module cstm_Clienti and the cstm_ClientiProdotti module, so the cstm_Clienti module displays a subpanel for cstm_ClientiProdotti.

Additionally, the cstm_ClientiProdotti module has a many-to-one relationship with the cstm_Prodotti module and includes a quantity field. Currently, I can select a product in the subpanel and edit the record to add quantities.

However, I would like to display additional fields from the cstm_Prodotti module (besides just the product name) as columns in the subpanel.

I’ve edit the file cstm_clientiprodotti_cstm_clienti_cstm_Clienti.php

suitecrm/public/legacy/custom/Extension/modules/cstm_Clienti/Ext/Layoutdefs/

<?php
$layout_defs["cstm_Clienti"]["subpanel_setup"]['cstm_clientiprodotti_cstm_clienti'] = array(
    'order' => 100,
    'module' => 'cstm_ClientiProdotti',
    'subpanel_name' => 'default',
    'title_key' => 'LBL_CSTM_CLIENTIPRODOTTI_CSTM_CLIENTI_FROM_CSTM_CLIENTIPRODOTTI_TITLE',
    'get_subpanel_data' => 'function:getRelatedCallsQueryData',
    'function_parameters' => array(
        'import_function_file' => 'custom/modules/cstm_Clienti/getCallsSubpanelData.php',  
    ),
  
    'top_buttons' => array(
        array('widget_class' => 'SubPanelTopButtonQuickCreate'),
        array('widget_class' => 'SubPanelTopSelectButton', 'mode' => 'MultiSelect'),
    ),
  
    'list_fields' => array(
    
        'name' => array(
            'vname' => 'LBL_NAME',
            'widget_class' => 'SubPanelDetailViewLink',
            'width' => '15%',
        ),
        
        'brand' => array(
            'vname' => 'LBL_BRAND',
            'type' => 'varchar',
            'width' => '10%',
            'source' => 'non-db',          
           
            
        ),
        'category' => array(
            'vname' => 'LBL_CATEGORY',
            'type' => 'varchar',
            'width' => '10%',
            'source' => 'non-db',        
            
        ),
        'quantity' => array(
            'vname' => 'LBL_QUANTITY',
            'type' => 'int',
            'width' => '10%',
           
        ),

    ),
);

To fetch data for the subpanel, I implemented the function getRelatedCallsQueryData:

<?php


function getRelatedCallsQueryData($params){
         $bean = $GLOBALS['app']->controller->bean;

         $query = " SELECT id, name, brand, category, quantity, distributore FROM subpanelClientiProdotti WHERE subpanelClientiProdotti.id_cliente = '{$bean->id}'";
         
         return $query;
}

Here, subpanelClientiProdotti is a database view that performs a more complex query to aggregate all the necessary data. I have tested the query, and it works correctly.

However, after running Repair and Rebuild, the additional fields (brand and category) are still not displayed in the subpanel.

[pgr]

I am not sure, but maybe your function is not supposed to return only the SQL, but the actual data after retrieval.

Did you check examples of this?

[Lawliet]

I attempted to return the query results as an array instead of a SQL object using the following function:

function getRelatedCallsQueryData($params) {
    global $db; // Ensure the database object is available

    $bean = $GLOBALS['app']->controller->bean;

    // Construct the query
    $query = "
        SELECT id, name, brand, category, quantity, distributore 
        FROM subpanelClientiProdotti 
        WHERE subpanelClientiProdotti.id_cliente = '{$bean->id}'
    ";

    // Execute the query
    $result = $db->query($query);

    // Fetch the results into an array
    $records = [];
    while ($row = $db->fetchByAssoc($result)) {
        $records[] = $row;
    }

    // Log the results for debugging
    error_log("Query Results: " . json_encode($records));

    return $records; // Return the array of results
}

The query is executed correctly, and the results are valid. Here’s an example from the logs:

[Fri Jan 24 18:08:08.445060 2025] [php:notice] [pid 248761] [client 127.0.0.1:50174] Query Results: [{“id”:“dda9a528-3661-7d00-8b8d-67928ea8f822”,“name”:“VaR Lt. 1,00”,“brand”:“Smeraldina”,“category”:“Naturale”,“quantity”:“2000”,“distributore”:“Sardinia Wine Ltd.”}], referer: http://localhost/public/

However, at the same time, I receive the following error in the SuiteCRM prod.log file:

[2025-01-24 18:13:31] php.WARNING: Warning: Array to string conversion {“exception”:“[object] (ErrorException(code: 0): Warning: Array to string conversion at /var/www/html/suitecrm/public/legacy/data/SugarBean.php:4509)”}
[2025-01-24 18:13:32] php.WARNING: Warning: Array to string conversion {“exception”:“[object] (ErrorException(code: 0): Warning: Array to string conversion at /var/www/html/suitecrm/public/legacy/data/SugarBean.php:4509)”}
[2025-01-24 18:13:32] app.ERROR: strtolower(): Argument #1 ($string) must be of type string, array given {“exception”:“[object] (TypeError(code: 0): strtolower(): Argument #1 ($string) must be of type string, array given at /var/www/html/suitecrm/public/legacy/data/SugarBean.php:4512)”}

[rsp]

Try this:

function getRelatedCallsQueryData($params) {
    global $db; // Ensure the database object is available

    $bean = $GLOBALS['app']->controller->bean;

    // Construct the query
    $query = "
        SELECT id, name, brand, category, quantity, distributore 
        FROM subpanelClientiProdotti 
        WHERE subpanelClientiProdotti.id_cliente = '{$bean->id}'
    ";

    // Execute the query
    $result = $db->query($query);

    // Fetch the results into an array
    $records = [];
    while ($row = $db->fetchByAssoc($result)) {
        $records[] = $row;
    }

    // Log the results for debugging
    error_log("Query Results: " . json_encode($records));

    return $records; // Return the array of results
}

// Ensure the function calling code correctly handles the returned array
$data = getRelatedCallsQueryData($params);
if (is_array($data)) {
    // Process the array data correctly
    // For example, iterate and log individual records
    foreach ($data as $record) {
        error_log("Record: " . json_encode($record));
    }
} else {
    error_log("Unexpected result type: " . gettype($data));
}

[Harshad]

I think the function should return an array of SQL query parts something like

$return_array = [];
    $return_array['select'] = 'SELECT DISTINCT emails.id ';
    $return_array['from'] = 'FROM emails ';

    $return_array['join'] = array();

return $return_array;

Can you please refer this file: public\legacy\modules\Accounts\metadata\subpaneldefs.php

function_parameters should specify related module name as second element in the array such as 'link' => 'contacts'

[pgr]

@Harshad is right

See one more example here:

Adding records to History Subpanel?

So I’ve got what I thought might be a simple project. I have NOTES that are added to PROJECT TASKS. They show up in the history of the Project Tasks subpanel. Great! What I also want to do is add this to the history subpanel for the related Opportunity to the Project. I’ve got that figured out how to get it and how to add the relationship via an after_relationship_add hook to the Opportunity. However, it REPLACES the relationship to the Project Task. Not what I want. I want to have bot…

[pstevens]

I’m not sure I totally understand what you want to do, but I think you are wanting to display a field or fields from another module in an existing subpanel in a different module? Correct?

Here’s how I have done it:

1. Add a custom field, lets say custom_field_c (you can even make it a non-db field if you like it doesn’ t matter). Then add it to the subpanel. You can do this part in studio.

2. Add a custom logic hook (process record) to display the result of a custom function in the field ie: get the data from another module.

Here’s an example I did. Basically, I wanted to grab the last call/meeting date from the account record and display it in the quote subpanel. I have another function that populates that based on the call/meeting history for the account (that part doesn’t matter for your purpose only that I need the data of that field from the account to the quote subpanel of quotes).

<?php
$hook_array['process_record'][] = array(
    1,
    'Fetch last call/meeting from Account',
    'custom/modules/AOS_Quotes/FetchAccountData.php',
    'FetchAccountData',
    'populateLastCallMeeting',
);

and the function:

<?php
if (!defined('sugarEntry') || !sugarEntry) die('Not A Valid Entry Point');

class FetchAccountData
{
    public function populateLastCallMeeting(&$bean, $event, $arguments)
    {
        // Check if we're in the subpanel context
        if (!empty($bean->parent_id) && $bean->parent_type === 'Accounts') {
            $accountId = $bean->parent_id;
            $accountBean = BeanFactory::getBean('Accounts', $accountId);

            if (!empty($accountBean->last_call_meeting_c)) {
                // Set the value for the subpanel display
                $bean->last_call_meeting_desc_c = $accountBean->last_call_meeting_c;
            }
        }
    }
}

Simple, you don’t have to mess with subpanel queries anything.