ZuckerReports Query - Empty Resultset

[IanUGA]

The basics:

Sugar Community Edition v5.0h
ZuckerReports v1.8
MySQL 5.0.45-7
Apache 2.2.3-22
PHP 5.1.6-23
RedHat Enterprise Linux v5

I've got three tables that pertain to this query: contacts, contacts_cstm, and users. Contacts is the contacts module, contacts_cstm is a bunch of custom fields in the contacts module, and users is the table holding data relevant to system users.

In this example, I've got student information populating the contact module. The user that logs into Sugar is a "specialist", assigned to a student's (or students') contact record as the "assigned_user". I'm trying to query through ZuckerReports with a parameter, default name SPECIALIST. Here is the parameter:

SELECT id, concat(last_name, ", ", first_name) AS specialist
FROM users
order by last_name

This should, and does, provide me a list of all system users when I run the query.

The query itself selects all kinds of fields from the Contacts and Contacts_CSTM tables. What I'm trying to do is, when I run the query, select the specialist from the parameters drop-down and have it give me all of the contact records listed that have this specialist as the assigned user. Here's the query:

select c.first_name, c.last_name , c.ssn, MID(c.ssn,8,4) AS last_four, c.birthdate, s.preferred_name_c, s.local_address_c, s.local_address_city_c, s.local_address_state_c, s.local_address_postalcode_c, replace(s.classroom_accom_c,"^,^",CHAR(13)) As ClassroomAccom, replace( s.testing_accom_c,"^,^",CHAR(13)) As TestingAccom, s.disability_charac_c, s.uga_card_id_c, s.dr1_prefix_c, s.dr1_suffix_c, s.dr_first_name_c, s.dr_last_name_c, s.doctor1_address_c, s.doctor1_address_city_c, s.doctor1_address_state_c, s.doctor1_address_postalcode_c, concat( u.first_name, " ", u.last_name) as Specialist, s.contact_status_c, u.title
from
contacts c left join contacts_cstm s on c.id=s.id_c
left join users u on c.assigned_user_id=u.id

where c.assigned_user_id like '$Specialist'

The results of this query give me all of the headers - which means at least I got the syntax correct, but empty results, no matter which specialist I choose. My gut feeling is that I'm not tying the Specialist variable correctly to the assigned_user_id field, but I'm really new to this so any help would be appreciated. Thanks a lot!

[crmsiva]

Why don't you try removing the WHERE part from query and check the result?

[IanUGA]

I attempted that just now. The result set is no longer empty - it now contains every contact record in the system, as the WHERE was (or should) have filtered out the records not assigned to the specialist in the dropdown.

[sugarcane]

Hi Ian,

Instead of

where c.assigned_user_id like '$Specialist'

you need to use

= '$SUGAR_USER_ID'

The where clause is looking at the assigned user id, and the specialist field in essence is the assigned user field renamed.

The '$SUGAR_USER_ID' parameter is built into the system, you see it to the right of the query when you create a new query. When run, the report will auto select and run the report for the current user