Conditional row in subpanel

[fab]

Hi, I have a need to have only records within a subpanel display that have set a flag type field.

Let me explain better: within a module I have various relations (so various subpanels), within one of them there is a list of records, the records of this list should be visible only if the field “verified” is flagged.

How can I implement this situation? Thank you

[pgr]

Subpanels Cases

I just followed your example to add a panel with invoices in AOS_Products, because, in our process the sell isnt done until we send the invoice, the quote is just before the invoice. As I´m not developer (just and enthusiast) I search for this, and didnt found a solution, so, I think is a good way to share with someone thats looking for some similar to have the other steps. First, the PANEL. This is done exactly where @pgr wrote: custom/Extension/modules/Accounts/Ext/Layoutdefs/ANYNAMEYOUWAN…

[abuzarfaris]

You need to locate the Layoutdef (subpanel definition ) of the that subpanel
In the subpanel definition you can add filter in the where of the subpanel
See the example that I am attaching

[fab]

After several attempts, I managed to get the filter to work with “Where” (abuzarfaris’ suggestion), but I didn’t edit the custom/extension/modules/<MODULE>/Ext/layoutdefs/<FILE> file (it doesn’t work), I had to edit the modules/<MODULE>/metadata/subpanels/default.php file.

What if I want this filter to work only for certain categories of users? As admin? it’s possible?

[fab]

Update, since it is necessary that in the subpanel only records with “verified” field are shown and only for users with a certain role, I opted for pgr’s solution:

function getCustomQuery(){

global $mod_strings, $app_strings, $sugar_config, $current_user;
$customQuery='';
$objACLRole = new ACLRole();
$roles = $objACLRole->getUserRoles($current_user->id);

if($roles[0] == "Customer"){
$customQuery = "
SELECT *
FROM MODULE inner join MODULE_cstm on MODULE.id=MODULE_cstm.id_c
WHERE MODULE.deleted = 0
AND MODULE_cstm.verified_c = '1'
";

}else{
$customQuery = "
SELECT *
FROM MODULE inner join MODULE_CSTM on MODULE.id=MODULE_CSTM.id_c
WHERE MODULE.deleted = 0
";
}
    
     return $customQuery;
}

now there are 2 problems:

1. as you can see from the code above, I had to put a second query, for the other roles, because the return of an empty $customQuery fails.

2. more important, is that passing the query directly, it means that the subpanel will be populated with all the data present in the DB, instead it must be populated with the data related to the record of the module it is located in.

How do I use the id value I need in the getCustomQuery() function to add it to the query? "SELECT ... FROM ... WHERE id='".$id."'"

[pgr]

I think you have that made in my 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…

Getting to the bean id through app->controller->bean

[fab]

Great, works perfectly! Thank you! Alternatively I also retrieved it via $_REQUEST['id'].
Unfortunately the associated query is a bit slow, but I don’t think it can make it any faster

[pgr]

I’m not an SQL expert but I believe an SQL expert could surely make your query much faster. Try using an SQL EXPLAIN statement and then adding an index, it’s likely you can make it 10x faster.

[gunnicom]

You can try if putting an index on the column verified_c helps a bit for the performance.