I created a custom module (through module builder, modelled after the cases module), for our account-specific year end processes, (like sending out W2s). The module is wonderful, and everybody was excited about it, and I was the hero for a couple of days. Now we are trying to search on the module but we can't.

I've been playing around with the vardefs, but don't know enough to be confident. I am trying to do it through trial and error, but so much is changing (or not changing) each time I change the vardefs that I am having a hard time deducing what is actually affecting the SQL, and in what way.

Here's my current SQL from my sugarcrm.log file:

Fri Jan 9 08:03:23 2009,607 [15894] INFO SugarCRM - List View Where Clause: (yre08_year_end_2008.client_name like 'Beyond 20-20%')
-----------
Fri Jan 9 08:03:23 2009,669 [15894] DEBUG SugarCRM - Limit Query: SELECT yre08_year_end_2008.id , yre08_year_end_2008_cstm.*, yre08_year_end_2008.yre08_year_end_2008_number , yre08_year_end_2008.account_id , yre08_year_end_2008.hold_for_finalizing , yre08_year_end_2008.w2s_printed , yre08_year_end_2008.w2s_sent , yre08_year_end_2008.delivery_method , yre08_year_end_2008.sendcd , yre08_year_end_2008.assigned_user_id FROM yre08_year_end_2008 LEFT JOIN yre08_year_end_2008_cstm ON yre08_year_end_2008.id = yre08_year_end_2008_cstm.id_c where ((yre08_year_end_2008.client_name like 'Beyond 20-20%')) AND yre08_year_end_2008.deleted=0 ORDER BY yre08_year_end_2008.yre08_year_end_2008_number ASC Start: 0 count: 21

---------------------
Fri Jan 9 08:03:23 2009,669 [15894] INFO SugarCRM - Query: SELECT yre08_year_end_2008.id , yre08_year_end_2008_cstm.*, yre08_year_end_2008.yre08_year_end_2008_number , yre08_year_end_2008.account_id , yre08_year_end_2008.hold_for_finalizing , yre08_year_end_2008.w2s_printed , yre08_year_end_2008.w2s_sent , yre08_year_end_2008.delivery_method , yre08_year_end_2008.sendcd , yre08_year_end_2008.assigned_user_id FROM yre08_year_end_2008 LEFT JOIN yre08_year_end_2008_cstm ON yre08_year_end_2008.id = yre08_year_end_2008_cstm.id_c where ((yre08_year_end_2008.client_name like 'Beyond 20-20%')) AND yre08_year_end_2008.deleted=0 ORDER BY yre08_year_end_2008.yre08_year_end_2008_number ASC LIMIT 0,21

At one point I had the accounts table appearing in a join with the table accounts_yre08_year_end_2008. However, I just need a somple one-to-one or one-to-many join. I don't need many-to-many through a join table. I also had it searching on accounts.account_name, rather than yre08_year_end_2008.client_name, but at that time, the accounts table was not in the where clause. However, I have not been able to get both the LEFT JOIN accounts, and the accounts.account_name to appear at the same time.

ANY GUIDANCE IS GREATLY APPRECIATED!