Project search by assigned user fails - Unknown column 'u.assigned_user_name'

[tolat]

Using Sugar CE 5.2.0c, we get the following when we search Projects by assigned user:

Query: SELECT project.id ,project_cstm.project_id_c,project_cstm.account_id _c,project_cstm.proj_type_c,project_cstm.proj_regi on_c, project.name , project.status , project.date_modified , jt0.user_name assigned_user_name , jt0.created_by assigned_user_name_owner , 'Users' assigned_user_name_mod, jt1.name primary_acct_c , project.assigned_user_id FROM project LEFT JOIN project_cstm ON project.id = project_cstm.id_c LEFT JOIN users jt0 ON
jt0.id= project.assigned_user_id AND jt0.deleted=0
AND jt0.deleted=0 LEFT JOIN accounts jt1 ON project_cstm.account_id_c = jt1.id AND jt1.deleted=0 where ((u.assigned_user_name like 'nitins%')) AND project.deleted=0 ORDER BY project.name ASC LIMIT 0,21
Sun Mar 22 21:47:33 2009 [16130][34e09b69-e0b2-d73e-b2d3-498845d59c6c][INFO] Query Execution Time:0.00054621696472168


Sun Mar 22 21:47:33 2009 [16130][34e09b69-e0b2-d73e-b2d3-498845d59c6c][ERROR] MySQL error 1054: Unknown column 'u.assigned_user_name' in 'where clause'

Is this a bug in Sugar or a bad deployment of the Accounts module? Any ideas how this might be fixed?

Thanks.

[andopes]

Hi tolat

It seems you had create custom fields for the Project module.
Attach in your next message the file cache/modules/Project/Projectvardefs.php.

Cheers

[tolat]

Thanks for your help. I've attached the Projectvardefs.php as requested.

[tolat]

One other related question is that I would like to search on assigned_user_id but it doesn't show up in the list of fields in Studio that I search on. I only see assigned_user_name.

Thanks

[tmamayek]

I'm having the same issue. I have it fixed in Accounts, but I need to be able to search by "assigned user ID" in the Leads as well as Accounts. In accounts the only option I get is "Assigned user name," but that doesn't seem to work. How can we get that fixed so I ca select assigned user ID in leads. When i click on fields in the studio for leads, I see both options, however under the search categories, I just see "assigned user name"

[tolat]

I actually was able to hack this in by editing searchdefs.php in custom/modules/Project/metadata and added:

'assigned_user_id' =>
array (
'name' => 'assigned_user_id',
'type' => 'enum',
'label' => 'LBL_ASSIGNED_USER_ID',
'function' =>
array (
'name' => 'get_user_array',
'params' =>
array (
0 => false,
),
),
'default' => true,
'width' => '10%',
),


I edited the file and then rebuilt the search view in Studio by just changing one field and then Save/Deploy.

I don't know if I followed any wrong protocol in doing this but it worked.

Hope this works for you.

[leemoney24]

This worked for me! Thanks for posting!

Observation:
The non-working field type was a single select field (auto-searches database).
The working field type is a multi-select.
Working now!

[tmamayek]

Tolat,

I don't see the searchdefs.php in custom/modules/Project/metadaat. I do however see it in custom/modules/Accounts/metadata. Is this correct, or should I see the file under projects? I'm confused since I never got the error in the projects module, just in the accounts module is where I'm looking to see this field added.

Please let me know when you get a chance. I really appreciate your help.

-Ted