Unique Accounts

You could try following the out of the box report for "Leads with No Calls, Meetings, Tasks, or Emails" and seeing if you can follow that same logic for Accounts but you will need to go a couple more layers deeper than the Lead report does because the Call/Email could be related to a Contact related to the Account rather than directly to the Account.

And because you don't just want those with NO Calls or Emails but rather no calls or emails within a certain date range it gets much more complicated, and potentially impossible to sort out without an Advanced Custom Query Report

https://support.sugarcrm.com/Documentation/Sugar_Versions/12.3/Sell/Application_Guide/Reports/Advanced_Reports/index.html#Custom_Query_Fields

FrancescaS

Hi Francesca, thanks for this!

We followed this and we were able to get some progress. I have a SQL code for the custom query we built, are you able to assist? We tried this but certain Call Types are still being counted in the report (exclude Text Message and Outbound Attempt).

SELECT
accounts.id AS account_id,
accounts_cstm.practice_ref_c AS Practice_Ref,
accounts.name AS Account_Name,
accounts_cstm.success_manager_c AS Success_Manager,
accounts_cstm.status_c AS Status,
accounts_cstm.potential_c AS Potential,
COUNT(DISTINCT calls.id) AS call_count,
COUNT(DISTINCT emails.id) AS email_count
FROM accounts
LEFT JOIN calls ON accounts.id = calls.parent_id AND calls.parent_type = 'Accounts'
AND calls.deleted = 0 AND calls.date_start BETWEEN '2023-03-01' AND '2023-03-31'
LEFT JOIN calls_cstm ON calls.id = calls_cstm.id_c AND calls_cstm.call_type_c NOT IN ('Call Attempt', 'Text Message')
LEFT JOIN emails ON accounts.id = emails.parent_id AND emails.parent_type = 'Accounts'
AND emails.deleted = 0 AND emails.date_sent BETWEEN '2023-03-01' AND '2023-03-31'
LEFT JOIN accounts_cstm ON accounts.id = accounts_cstm.id_c
WHERE accounts.account_type = 'Customer' AND accounts_cstm.status_c != 'Deactivated' AND accounts.deleted = 0
GROUP BY accounts.id
ORDER BY Account_Name;

Check the dropdown for your status_c in Studio, it is possible that 'Call Attempt' and 'Text Message' are the labels, not the database values.
The query will want the database values in the filter, but the report is likely translating those into labels before it displays.

FrancescaS

I tried both the Item Label and the Display Label names and neither worked. Would those be different from the database values?

You want the database value from the dropdown definition.

Go to the Admin Panel, in Studio, go to the Accounts module.

In the list of fields find your custom fields status_c

Open the field and you will see the Drop Down List definition.

Something like:

Select Edit

It will take you to the dropdown maintenance scree where you will see all the values for the dropdown.

On the LEFT are the database values, on the right in square brackets are the labels that you see in the front end.

Use the values on the left to build your query.

Good luck,

FrancescaS

You want the database value from the dropdown definition.

Go to the Admin Panel, in Studio, go to the Accounts module.

In the list of fields find your custom fields status_c

Open the field and you will see the Drop Down List definition.

Something like:

Select Edit

It will take you to the dropdown maintenance scree where you will see all the values for the dropdown.

On the LEFT are the database values, on the right in square brackets are the labels that you see in the front end.

Use the values on the left to build your query.

Good luck,

FrancescaS

Here's what we have in Studio. I changed the query to match the item name and it still was counting them when I ran the query.