search form

[muscariello]

Hi to all,
I work with Sugar 5.2.0f.
I've created two custom modules "Pratiche" and "Bandi", I've created a relationship one-to-many between them (one Bandi many Pratiche).
So in module "Pratiche" I've a Relate field "Bando".
When I try to use this field for search, search fail.

Any dea?
I hope somebody can help me.

Reguards,
Martina

[salesagility]

did you modify the search panel for Pratiche?

[muscariello]

Hi salesagility,
thanks for response.
I wan't modify search panel.
When I attemp to perform a search using relate field (Bando), search fail.

It's similar to bug 21968 http://www.sugarcrm.com/crm/support/...a-4821992d4c67

It's clear?
Reguards,
Martina

[andopes]

Did you try quick repair and rebuild?
Try to implement my debugging tips at this thread. and let us know the result.

Cheers

[muscariello]

Did you try quick repair and rebuild?
Try to implement my debugging tips at this thread. and let us know the result.

Cheers

Hi andopes,
thank you for replace,
so this is my main_query ,

SELECT prtch_pratiche.id ,prtch_pratiche_cstm.fase_c,prtch_pratiche_cstm.st ato_c,prtch_pratiche_cstm.foglio_c,prtch_pratiche_ cstm.particella_c,prtch_pratiche_cstm.catasto_c,pr tch_pratiche_cstm.pod_c,prtch_pratiche_cstm.nome_c ,prtch_pratiche_cstm.cognome_c,prtch_pratiche_cstm .pratica_c,prtch_pratiche_cstm.ubicazione_city_c,p rtch_pratiche_cstm.residenza_city_c,prtch_pratiche _cstm.ubicazione_state_c,prtch_pratiche_cstm.ubica zione_postalcode_c,prtch_pratiche_cstm.ubicazione_ country_c,prtch_pratiche_cstm.residenza_c,prtch_pr atiche_cstm.residenza_state_c,prtch_pratiche_cstm. residenza_postalcode_c,prtch_pratiche_cstm.residen za_country_c,prtch_pratiche_cstm.ubicazione_c,prtc h_pratiche_cstm.presentazione_dia_c,prtch_pratiche _cstm.ritiro_dia_c,prtch_pratiche_cstm.intestatari o_c,prtch_pratiche_cstm.regione_c,prtch_pratiche_c stm.provincia_c,prtch_pratiche_cstm.comune_c,prtch _pratiche_cstm.regione1_c,prtch_pratiche_cstm.prov incia1_c,prtch_pratiche_cstm.comune1_c,prtch_prati che_cstm.localita_c,prtch_pratiche_cstm.frazione_c ,prtch_pratiche_cstm.esposizione_c,prtch_pratiche_ cstm.tetto_c,prtch_pratiche_cstm.inclinazione_c,pr tch_pratiche_cstm.tipo_impianto_c,prtch_pratiche_c stm.valuta1_c,prtch_pratiche_cstm.valuta2_c,prtch_ pratiche_cstm.valuta3_c,prtch_pratiche_cstm.codice _fiscale_c,prtch_pratiche_cstm.codice_cliente_c,pr tch_pratiche_cstm.tariffa_c,prtch_pratiche_cstm.te nsione_c,prtch_pratiche_cstm.potenza_impegnata_c,p rtch_pratiche_cstm.potenza_disponibile_c,prtch_pra tiche_cstm.tipologia_mercato_c,prtch_pratiche_cstm .ragione_sociale_fornitore_c,prtch_pratiche_cstm.a ltro_fornitore_c,prtch_pratiche_cstm.dati_fornitor e_c,prtch_pratiche_cstm.vincoli_c,prtch_pratiche_c stm.campo_aggiuntivo1_c,prtch_pratiche_cstm.campo_ aggiuntivo2_c,prtch_pratiche_cstm.tipo_certificato _comunale_c,prtch_pratiche_cstm.data_richiesta_cer tif_comun_c,prtch_pratiche_cstm.protocollo_accett_ c,prtch_pratiche_cstm.data_rilascio_certif_c,prtch _pratiche_cstm.protocollo_rilascio_c,prtch_pratich e_cstm.titolo_disponibilita_immobile_c,prtch_prati che_cstm.nome_proprietario_c,prtch_pratiche_cstm.c ognome_proprietario_c,prtch_pratiche_cstm.codice_f iscale_propr_c,prtch_pratiche_cstm.residenza_propr _c,prtch_pratiche_cstm.cap_propr_c,prtch_pratiche_ cstm.regione_propr_c,prtch_pratiche_cstm.provincia _propr_c,prtch_pratiche_cstm.comune_propr_c,prtch_ pratiche_cstm.note_c,prtch_pratiche_cstm.autorizza zione_c , jt0.name bandi_bandi_prtch_pratiche_name, jtl0.bandi_band966ei_bandi_ida bandi_band966ei_bandi_ida , jt1.user_name assigned_user_name , jt1.created_by assigned_user_name_owner , 'Users' assigned_user_name_mod, prtch_pratiche.date_entered , prtch_pratiche.assigned_user_id FROM prtch_pratiche LEFT JOIN prtch_pratiche_cstm ON prtch_pratiche.id = prtch_pratiche_cstm.id_c LEFT JOIN bandi_banditch_pratiche_c jtl0 ON prtch_pratiche.id=jtl0.bandi_band7b0bratiche_idb AND jtl0.deleted=0 LEFT JOIN bandi_bandi jt0 ON jt0.id=jtl0.bandi_band966ei_bandi_ida AND jt0.deleted=0 AND jt0.deleted=0 LEFT JOIN users jt1 ON jt1.id= prtch_pratiche.assigned_user_id AND jt1.deleted=0 AND jt1.deleted=0 where ((bandi_bandi_prtch_pratiche_name like 'ENERGIA CON IL SOLE%')) AND prtch_pratiche.deleted=0 ORDER BY pod_c ASC

and this is the error in phpMyAdmin

#1054 - Unknown column 'bandi_bandi_prtch_pratiche_name' in 'where clause'

Any idea?
Thank you so much,
reguards,
Martina

[andopes]

Hi Martina

It seems Sugar is creating an alias for field name on table bandi_bandi: bandi_bandi_prtch_pratiche_name.
And that is so weird.
Let us know the extended vardefs (custom/modules/prtch_pratiche/Ext/Vardefs/vardefs.ext.php) and custom listviewdefs (custom/modules/prtch_pratiche/metadata/listviewdefs.php) for module prtch_pratiche.

Regards

[muscariello]

Hi andopes,
thank you so much for replace,
so this is vardefs.ext.php file for module prtch_pratiche:

<?php
$dictionary["prtch_Pratiche"]["fields"]["contacts_prtch_pratiche"] = array (
'name' => 'contacts_prtch_pratiche',
'type' => 'link',
'relationship' => 'contacts_prtch_pratiche',
'source' => 'non-db',
);
$dictionary["prtch_Pratiche"]["fields"]["accounts_prtch_pratiche"] = array (
'name' => 'accounts_prtch_pratiche',
'type' => 'link',
'relationship' => 'accounts_prtch_pratiche',
'source' => 'non-db',
'side' => 'right',
);
$dictionary["prtch_Pratiche"]["fields"]["accounts_prtch_pratiche_name"] = array (
'name' => 'accounts_prtch_pratiche_name',
'type' => 'relate',
'source' => 'non-db',
'vname' => 'LBL_ACCOUNTS_PRTCH_PRATICHE_FROM_ACCOUNTS_TITLE',
'save' => true,
'id_name' => 'accounts_pf089ccounts_ida',
'link' => 'accounts_prtch_pratiche',
'table' => 'accounts',
'module' => 'Accounts',
'rname' => 'name',
);
$dictionary["prtch_Pratiche"]["fields"]["accounts_pf089ccounts_ida"] = array (
'name' => 'accounts_pf089ccounts_ida',
'type' => 'link',
'relationship' => 'accounts_prtch_pratiche',
'source' => 'non-db',
'side' => 'right',
);
$dictionary["prtch_Pratiche"]["fields"]["contacts_prtch_pratiche"] = array (
'name' => 'contacts_prtch_pratiche',
'type' => 'link',
'relationship' => 'contacts_prtch_pratiche',
'source' => 'non-db',
);
$dictionary["prtch_Pratiche"]["fields"]["accounts_prtch_pratiche"] = array (
'name' => 'accounts_prtch_pratiche',
'type' => 'link',
'relationship' => 'accounts_prtch_pratiche',
'source' => 'non-db',
'side' => 'right',
);
$dictionary["prtch_Pratiche"]["fields"]["accounts_prtch_pratiche_name"] = array (
'name' => 'accounts_prtch_pratiche_name',
'type' => 'relate',
'source' => 'non-db',
'vname' => 'LBL_ACCOUNTS_PRTCH_PRATICHE_FROM_ACCOUNTS_TITLE',
'save' => true,
'id_name' => 'accounts_pf089ccounts_ida',
'link' => 'accounts_prtch_pratiche',
'table' => 'accounts',
'module' => 'Accounts',
'rname' => 'name',
);
$dictionary["prtch_Pratiche"]["fields"]["accounts_pf089ccounts_ida"] = array (
'name' => 'accounts_pf089ccounts_ida',
'type' => 'link',
'relationship' => 'accounts_prtch_pratiche',
'source' => 'non-db',
'side' => 'right',
);
$dictionary["prtch_Pratiche"]["fields"]["prtch_pratiche_securitygroups"] = array (
'name' => 'prtch_pratiche_securitygroups',
'type' => 'link',
'relationship' => 'prtch_pratiche_securitygroups',
'source' => 'non-db',
);
$dictionary["prtch_Pratiche"]["fields"]["prtch_pratiche_documents"] = array (
'name' => 'prtch_pratiche_documents',
'type' => 'link',
'relationship' => 'prtch_pratiche_documents',
'source' => 'non-db',
);
$dictionary["prtch_Pratiche"]["fields"]["bandi_bandi_prtch_pratiche"] = array (
'name' => 'bandi_bandi_prtch_pratiche',
'type' => 'link',
'relationship' => 'bandi_bandi_prtch_pratiche',
'source' => 'non-db',
'side' => 'right',
);
$dictionary["prtch_Pratiche"]["fields"]["bandi_bandi_prtch_pratiche_name"] = array (
'name' => 'bandi_bandi_prtch_pratiche_name',
'type' => 'relate',
'source' => 'non-db',
'vname' => 'LBL_BANDI_BANDI_PRTCH_PRATICHE_FROM_BANDI_BANDI_T ITLE',
'save' => true,
'id_name' => 'bandi_band966ei_bandi_ida',
'link' => 'bandi_bandi_prtch_pratiche',
'table' => 'bandi_bandi',
'module' => 'bandi_Bandi',
'rname' => 'name',
);
$dictionary["prtch_Pratiche"]["fields"]["bandi_band966ei_bandi_ida"] = array (
'name' => 'bandi_band966ei_bandi_ida',
'type' => 'link',
'relationship' => 'bandi_bandi_prtch_pratiche',
'source' => 'non-db',
'side' => 'right',
);
$dictionary["prtch_Pratiche"]["fields"]["contacts_prtch_pratiche"] = array (
'name' => 'contacts_prtch_pratiche',
'type' => 'link',
'relationship' => 'contacts_prtch_pratiche',
'source' => 'non-db',
);
$dictionary["prtch_Pratiche"]["fields"]["contacts_prtch_pratiche"] = array (
'name' => 'contacts_prtch_pratiche',
'type' => 'link',
'relationship' => 'contacts_prtch_pratiche',
'source' => 'non-db',
);
$dictionary["prtch_Pratiche"]["fields"]["accounts_prtch_pratiche"] = array (
'name' => 'accounts_prtch_pratiche',
'type' => 'link',
'relationship' => 'accounts_prtch_pratiche',
'source' => 'non-db',
'side' => 'right',
);
$dictionary["prtch_Pratiche"]["fields"]["accounts_prtch_pratiche_name"] = array (
'name' => 'accounts_prtch_pratiche_name',
'type' => 'relate',
'source' => 'non-db',
'vname' => 'LBL_ACCOUNTS_PRTCH_PRATICHE_FROM_ACCOUNTS_TITLE',
'save' => true,
'id_name' => 'accounts_pf089ccounts_ida',
'link' => 'accounts_prtch_pratiche',
'table' => 'accounts',
'module' => 'Accounts',
'rname' => 'name',
);
$dictionary["prtch_Pratiche"]["fields"]["accounts_pf089ccounts_ida"] = array (
'name' => 'accounts_pf089ccounts_ida',
'type' => 'link',
'relationship' => 'accounts_prtch_pratiche',
'source' => 'non-db',
'side' => 'right',
);
$dictionary["prtch_Pratiche"]["fields"]["prtch_pratiche_securitygroups"] = array (
'name' => 'prtch_pratiche_securitygroups',
'type' => 'link',
'relationship' => 'prtch_pratiche_securitygroups',
'source' => 'non-db',
);
$dictionary["prtch_Pratiche"]["fields"]["prtch_pratiche_documents"] = array (
'name' => 'prtch_pratiche_documents',
'type' => 'link',
'relationship' => 'prtch_pratiche_documents',
'source' => 'non-db',
);
$dictionary["prtch_Pratiche"]["fields"]["bandi_bandi_prtch_pratiche"] = array (
'name' => 'bandi_bandi_prtch_pratiche',
'type' => 'link',
'relationship' => 'bandi_bandi_prtch_pratiche',
'source' => 'non-db',
'side' => 'right',
);
$dictionary["prtch_Pratiche"]["fields"]["bandi_bandi_prtch_pratiche_name"] = array (
'name' => 'bandi_bandi_prtch_pratiche_name',
'type' => 'relate',
'source' => 'non-db',
'vname' => 'LBL_BANDI_BANDI_PRTCH_PRATICHE_FROM_BANDI_BANDI_T ITLE',
'save' => true,
'id_name' => 'bandi_band966ei_bandi_ida',
'link' => 'bandi_bandi_prtch_pratiche',
'table' => 'bandi_bandi',
'module' => 'bandi_Bandi',
'rname' => 'name',
);
$dictionary["prtch_Pratiche"]["fields"]["bandi_band966ei_bandi_ida"] = array (
'name' => 'bandi_band966ei_bandi_ida',
'type' => 'link',
'relationship' => 'bandi_bandi_prtch_pratiche',
'source' => 'non-db',
'side' => 'right',
);
$dictionary["prtch_Pratiche"]["fields"]["mpnt_impianti_prtch_pratiche"] = array (
'name' => 'mpnt_impianti_prtch_pratiche',
'type' => 'link',
'relationship' => 'mpnt_impianti_prtch_pratiche',
'source' => 'non-db',
);
$dictionary["prtch_Pratiche"]["fields"]["mpnt_impianti_prtch_pratiche_pod_c"] = array (
'name' => 'mpnt_impianti_prtch_pratiche_name',
'type' => 'relate',
'source' => 'non-db',
'vname' => 'LBL_MPNT_IMPIANTI_PRTCH_PRATICHE_FROM_MPNT_IMPIAN TI_TITLE',
'save' => true,
'id_name' => 'mpnt_impiaf0d0mpianti_ida',
'link' => 'mpnt_impianti_prtch_pratiche',
'table' => 'mpnt_impianti',
'module' => 'mpnt_Impianti',
'rname' => 'name',
);
$dictionary["prtch_Pratiche"]["fields"]["mpnt_impiaf0d0mpianti_ida"] = array (
'name' => 'mpnt_impiaf0d0mpianti_ida',
'type' => 'link',
'relationship' => 'mpnt_impianti_prtch_pratiche',
'source' => 'non-db',
);
$dictionary["prtch_Pratiche"]["fields"]["contacts_prtch_pratiche"] = array (
'name' => 'contacts_prtch_pratiche',
'type' => 'link',
'relationship' => 'contacts_prtch_pratiche',
'source' => 'non-db',
);
$dictionary["prtch_Pratiche"]["fields"]["accounts_prtch_pratiche"] = array (
'name' => 'accounts_prtch_pratiche',
'type' => 'link',
'relationship' => 'accounts_prtch_pratiche',
'source' => 'non-db',
'side' => 'right',
);
$dictionary["prtch_Pratiche"]["fields"]["accounts_prtch_pratiche_name"] = array (
'name' => 'accounts_prtch_pratiche_name',
'type' => 'relate',
'source' => 'non-db',
'vname' => 'LBL_ACCOUNTS_PRTCH_PRATICHE_FROM_ACCOUNTS_TITLE',
'save' => true,
'id_name' => 'accounts_pf089ccounts_ida',
'link' => 'accounts_prtch_pratiche',
'table' => 'accounts',
'module' => 'Accounts',
'rname' => 'name',
);
$dictionary["prtch_Pratiche"]["fields"]["accounts_pf089ccounts_ida"] = array (
'name' => 'accounts_pf089ccounts_ida',
'type' => 'link',
'relationship' => 'accounts_prtch_pratiche',
'source' => 'non-db',
'side' => 'right',
);
$dictionary["prtch_Pratiche"]["fields"]["prtch_pratiche_securitygroups"] = array (
'name' => 'prtch_pratiche_securitygroups',
'type' => 'link',
'relationship' => 'prtch_pratiche_securitygroups',
'source' => 'non-db',
);
$dictionary["prtch_Pratiche"]["fields"]["prtch_pratiche_documents"] = array (
'name' => 'prtch_pratiche_documents',
'type' => 'link',
'relationship' => 'prtch_pratiche_documents',
'source' => 'non-db',
);
$dictionary["prtch_Pratiche"]["fields"]["contacts_prtch_pratiche"] = array (
'name' => 'contacts_prtch_pratiche',
'type' => 'link',
'relationship' => 'contacts_prtch_pratiche',
'source' => 'non-db',
);
$dictionary["prtch_Pratiche"]["fields"]["accounts_prtch_pratiche"] = array (
'name' => 'accounts_prtch_pratiche',
'type' => 'link',
'relationship' => 'accounts_prtch_pratiche',
'source' => 'non-db',
'side' => 'right',
);
.... continue...

[muscariello]

....continue....

$dictionary["prtch_Pratiche"]["fields"]["accounts_prtch_pratiche_name"] = array (
'name' => 'accounts_prtch_pratiche_name',
'type' => 'relate',
'source' => 'non-db',
'vname' => 'LBL_ACCOUNTS_PRTCH_PRATICHE_FROM_ACCOUNTS_TITLE',
'save' => true,
'id_name' => 'accounts_pf089ccounts_ida',
'link' => 'accounts_prtch_pratiche',
'table' => 'accounts',
'module' => 'Accounts',
'rname' => 'name',
);
$dictionary["prtch_Pratiche"]["fields"]["accounts_pf089ccounts_ida"] = array (
'name' => 'accounts_pf089ccounts_ida',
'type' => 'link',
'relationship' => 'accounts_prtch_pratiche',
'source' => 'non-db',
'side' => 'right',
);
$dictionary["prtch_Pratiche"]["fields"]["prtch_pratiche_securitygroups"] = array (
'name' => 'prtch_pratiche_securitygroups',
'type' => 'link',
'relationship' => 'prtch_pratiche_securitygroups',
'source' => 'non-db',
);
?>


and following the listviewdefs.php for module prtch_pratiche:

<?php
$module_name = 'prtch_Pratiche';
$listViewDefs [$module_name] =
array (
'POD_C' =>
array (
'width' => '10%',
'label' => 'LBL_POD',
'default' => true,
'link' => true,
),
'FASE_C' =>
array (
'width' => '10%',
'label' => 'LBL_FASE',
'default' => true,
),
'STATO_C' =>
array (
'width' => '10%',
'label' => 'LBL_STATO',
'default' => true,
),
'BANDI_BANDI_PRTCH_PRATICHE_NAME' =>
array (
'width' => '10%',
'label' => 'LBL_BANDI_BANDI_PRTCH_PRATICHE_FROM_BANDI_BANDI_T ITLE',
'default' => true,
),
'ASSIGNED_USER_NAME' =>
array (
'width' => '10%',
'label' => 'LBL_ASSIGNED_TO_NAME',
'default' => true,
),
'DATE_ENTERED' =>
array (
'width' => '10%',
'label' => 'LBL_DATE_ENTERED',
'default' => true,
),
);
?>


It's clear?
Thanks in advance,
Martina

[andopes]

Actually both files seem to be fine.
Try to Quick Repair and Rebuild and let us know if the issue is still there.

Regards

[muscariello]

I've tried to Quick Repair and Rebuild but the issue is still there.

It seems phpMyAdmin don't accept alias in where clause ,
if I write query changing where clause as follow (without alias)

SELECT prtch_pratiche.id ,prtch_pratiche_cstm.fase_c,
.....,
jt0.name bandi_bandi_prtch_pratiche_name, ....
FROM prtch_pratiche LEFT JOIN prtch_pratiche_cstm ON prtch_pratiche.id = prtch_pratiche_cstm.id_c
LEFT JOIN bandi_banditch_pratiche_c jtl0 ON prtch_pratiche.id=jtl0.bandi_band7b0bratiche_idb
AND jtl0.deleted=0
LEFT JOIN bandi_bandi jt0 ON jt0.id=jtl0.bandi_band966ei_bandi_ida AND jt0.deleted=0 AND jt0.deleted=0
LEFT JOIN users jt1 ON jt1.id= prtch_pratiche.assigned_user_id AND jt1.deleted=0 AND jt1.deleted=0
WHERE ((jt0.name like 'ENERGIA CON IL SOLE%')) AND prtch_pratiche.deleted=0 ORDER BY pod_c ASC

query work fine in phpMyAdmin.

I don't understand what is the problem...

Any idea?
Thank you so much,
Martina