Notes and Zucker Reports

[gkeyes]

SugarCRM 5c and Zucker Reports 1.8g

Current Code to Produce Output
SELECT notes.date_entered, notes.date_modified, notes.name, notes.description, notes.parent_type, notes.parent_id
FROM notes
WHERE notes.deleted = 0
AND notes.created_by = '$SUGAR_USER_ID'
ORDER BY notes.date_entered;

Header Output
date_entered date_modified name description parent_type parent_id

parent_type contains WHAT it is related to such as CONTACTS or LEADS OR ACCOUNTS, parent_id CONTAINS the id number of the CONTACTS or LEADS OR ACCOUNTS.

All I want to do is ADD the LEAD or ACCOUNT or CONTACTS name to my output. I know can JOIN but I do not need all that information, just ONE, maybe two fields. I have tried IF() and IF..ELSEIF but I get errors, I assume because I am not formatting the code correclty.

[mikesolomon]

Try something like this

SELECT
if (parent_type = 'accounts', `accounts`.name, contacts.first_name) as aname,

notes.date_entered, notes.date_modified, notes.name, notes.description, notes.parent_type, notes.parent_id
FROM notes
LEFT JOIN accounts on notes.parent_id = accounts.id
LEFT JOIN contacts on `notes`.`parent_id` = contacts.id
WHERE notes.deleted = 0
AND notes.created_by = '$SUGAR_USER_ID'
ORDER BY notes.date_entered;

[gkeyes]

Ok, for the most that worked fine, it pulled for CONTACTS and ACCOUNTS, but not LEADS. This seems to be where I get stuck. The THIRD option.

[mikesolomon]

Ok, for the most that worked fine, it pulled for CONTACTS and ACCOUNTS, but not LEADS. This seems to be where I get stuck. The THIRD option.

That was just an example

I have modified it to include leads

SELECT
if (parent_type = 'accounts', `accounts`.name, if (parent_type = 'contacts', contacts.first_name, `leads`.`first_name`)) as aname,
notes.date_entered, notes.date_modified, notes.name, notes.description, notes.parent_type, notes.parent_id
FROM notes
LEFT JOIN accounts on notes.parent_id = accounts.id
LEFT JOIN contacts on `notes`.`parent_id` = contacts.id
LEFT JOIN `leads` on `notes`.`parent_id` = `leads`.id
WHERE notes.deleted = 0
AND notes.created_by = '$SUGAR_USER_ID'
ORDER BY notes.date_entered;

obviously notes can also be added against tasks, calls, meeting ect so you need to exclude anything that is not in your if statement at the top

[gkeyes]

Thanks, I was being a newbie programmer (because I am) when I asked that last question, but your code is better then what I came up with.

Thanks.