Subpanel filter by custom column

[costelc]

I have a custom column "aktiv_c" for Leads (which actually resides in leads_cstm table). I want to filter the Leads subpanel data by this column (when active=1). I've tried adding a where clause in Leads/metadata/subpanels/default.php like:

'where' => "(leads_cstm.aktiv_c <> 0)"
or
'where' => "(leads.aktiv_c <> 0)"

but I get sql error Unknown column. What am I missing? Thanks.

[BiztechDev]

it may help u
http://www.sugarcrm.com/forums/showthread.php?t=52221

[costelc]

I have read that thread before posting, and that's what I was trying to do, using the where clause, as I wrote in the post. I know how to filter by a standard column, like status, but the problem is when I try to filter after a custom column (which is stored in the leads_cstm table) - I get sql errors, the query doesn't "see" that column.

[BiztechDev]

$subpanel_layout = array(
'top_buttons' => array(
array('widget_class' => 'SubPanelTopCreateButton'),
array('widget_class' => 'SubPanelTopSelectButton', 'popup_module' => 'Opportunities'),
),
'where' => 'payment_status="1"',
'list_fields' => array(
'order_no' =>
array (
'type' => 'relate',
'studio' => 'visible',
'vname' => 'LBL_ORDER_NO',
'width' => '10%',
'default' => true,
),
.
.
.

i hope this may help u
it works in my code

[costelc]

I'm sure I'm missing some basic stuff, but that what you wrote I already tried. So I have this:

...
'where' => 'aktiv_c="1"',
'list_fields' => array(
'aktiv_c'=>array(
'vname' => 'LBL_AKTIV',
'width' => '2%',
),
...

and I get this error:
Error running count query for J10_Jobs List: Query Failed SELECT count(*) c FROM leads INNER JOIN j10_jobs_leads_c ON (leads.id=j10_jobs_leads_c.j10_jobs_ldd5fdsleads_i db AND j10_jobs_leads_c.j10_jobs_l838310_jobs_ida= '8d204f1c-355c-de95-9a28-4c48094e348c') where ( j10_jobs_leads_c.deleted=0 AND leads.deleted=0 AND aktiv_c="1" AND leads.assigned_user_id ='beba3546-e421-9c4d-9816-4a6eb180f947' ) AND leads.deleted=0 )::MySQL error 1054: Unknown column 'aktiv_c' in 'where clause'


BUT if I remove the "where" line, the query works fine AND I see my custom column with its values in the table.

[costelc]

Any ideas?

[BiztechDev]

is your first line is
1) $subpanel_layout = ...
or
2) $subpanel_layout[list_fields] = ....

if it is like 2nd then make is like 1st ($subpanel_layout = ...)

and see my earlier reply

[costelc]

I'm afraid I don't understand what you mean by $subpanel_layout[list_fields]. Let me paste the exact code. I'm editing modules/Leads/metadata/subpanels/default.php and it is:

$subpanel_layout = array(
'top_buttons' => array(
array('widget_class' => 'SubPanelTopCreateButton'),
array('widget_class' => 'SubPanelTopSelectButton', 'popup_module' => 'Accounts'),
),

//'where' => "aktiv_c = 1",

'list_fields' => array(
'status'=>array(
'vname' => 'LBL_STATUS',
'width' => '7%',
),
'aktiv_c'=>array(
'vname' => 'LBL_AKTIV',
'width' => '2%',
),
................

This way, the second column is aktiv_c, the values are extracted right, but when I uncomment the //where line, I get the sql error from the previous post.

[BiztechDev]

Yes your code is right

i think 1 modification may help you

'where' => "aktiv_c = '1'",

put 1 in single quote

[costelc]

I've already tried that before, no change:

Error running count query for J10_Jobs List: Query Failed SELECT count(*) c FROM leads INNER JOIN j10_jobs_leads_c ON (leads.id=j10_jobs_leads_c.j10_jobs_ldd5fdsleads_i db AND j10_jobs_leads_c.j10_jobs_l838310_jobs_ida= '8d204f1c-355c-de95-9a28-4c48094e348c') where ( j10_jobs_leads_c.deleted=0 AND leads.deleted=0 AND aktiv_c = '1' AND leads.assigned_user_id ='beba3546-e421-9c4d-9816-4a6eb180f947' ) AND leads.deleted=0 )::MySQL error 1054: Unknown column 'aktiv_c' in 'where clause'