Reports Using OR

[tparson]

I'm wondering if someone could do a comprehensive explanation of how to get reports in Sugar PRO using "OR" rather than "AND" or "ANY". If there is, in fact, an explanation for this it would be a great wiki item.

Specifically: a contact report with related orgs where the field "contact category" (custom) is this
AND
Where the org field X = a specific item from a dropdown
OR
Where the org field Y = a specific item from a dropdown

Query show below that I would like to see

SELECT contacts.id primaryid
,CONCAT(CONCAT(IFNULL(contacts.first_name,''),' '),contacts.last_name) contacts_full_name

FROM contacts
INNER JOIN accounts_contacts l0_l ON contacts.id=l0_l.contact_id AND l0_l.deleted=0
INNER JOIN accounts l0 ON l0.id=l0_l.account_id AND l0.deleted=0
LEFT JOIN contacts_cstm contacts_contacts ON contacts.id = contacts_contacts.id_c
LEFT JOIN accounts_cstm accounts_l0 ON l0.id = accounts_l0.id_c

WHERE (contacts_contacts.category_c like '%SeniorAdministrator%'

AND accounts_l0.x_c IN ('itemA')
)
AND contacts.deleted=0
OR accounts_l0.Y_c IN ('itemC')
)
AND contacts.deleted=0

Thanks!

[tparson]

Further info:

I note that the query is in blob format in the database and when accessed is not particularly readable. Is this query stored elsewhere where it might be changed?

[Rovion]

I'm wondering if someone could do a comprehensive explanation of how to get reports in Sugar PRO using "OR" rather than "AND" or "ANY". If there is, in fact, an explanation for this it would be a great wiki item.

In general terms, boolean logic works exactly the opposite of grammatical speech. And is used to add to a set in 'English' and Or is used to exclude "You may have this OR that" vrs boolean or is to include "Where filter is this OR that' generally makes the result set larger for each OR condition met.


For example gramatically the phrase "I like cars that are convertibles AND luxury cars" would mean that you like convertibles and you also luxury cars.
To state the same thing in boolean logic Select all cars where type = luxury OR type = convertible.
Either case would return both sets of all cars in each category. Think of two overlapping circles, one for luxury cars, one for convertibles. In these examples, all cars in both circles would be returned.

To only get the cars that appear on BOTH circles (luxury cars which are also convertible)
In Boolean: Select all cars where type = luxury AND type = convertible.

more info at http://en.wikipedia.org/wiki/Boolean_logic