Report for Accounts with No Contacts is incorrectly pulling Accounts WITH Contacts


We are trying to create a report where Customers with No Contacts will be selected. I have filters set to Account Type is one of Customer OR Cisco Customer AND Contact ID is NULL. It is still pulling accounts with contacts available in the account. Please see attached screenshots.

We are using kReporter V4.2.



In manipulate you have contact fields as required. It will override the select query.
Set optional for Contacts first name, last name and ID


Thanks! Currently upgrading our SuiteCRM so I’ll check when it’s back online.

Will the manipulate tab always take precedence over Select tab?


Yes. As soon as a a field is required in manipulate tab, the where clause is extended with “thefield is not null”


I set the Contact fields in Manipulate to optional, but it is still pulling accounts that have contacts. Any ideas?


Can you turn on integrate > query analyzer?
And post the query created (view report > Tools > Query Analyzer)?
Problem might come from Account Customer field in manipulate. The one you group by. Seeing the query should help.


Sure, one moment please



What’s you mysql version?


we are currently using mysql Ver 15.1 Distrib 5.5.56-MariaDB, for Linux (x86_64) using readline 5.1


Do you get proper results when you run following query directly in database?

SELECT as "sugarRecordId", 'Accounts' as "sugarRecordModule" , as "wsfqxgxefetid", 'root:Accounts::link:Accounts:contacts' as "wsfqxgxefetpath", as "cfrgmmhqsgqid", 'root:Accounts' as "cfrgmmhqsgqpath", as "kc9cf3177937e511221c9409efd35", cfrgmmhqsgq.account_type as "k04e28d8aec65f61a16cd615caa54", wsfqxgxefet.last_name as "k9e51f3d263a9c23aef28d0501a62", wsfqxgxefet.first_name as "kdeb0ff8c84b4da61bf2113c12357" FROM accounts cfrgmmhqsgq LEFT JOIN accounts_contacts aswsggkqsdc ON AND aswsggkqsdc.deleted=0 LEFT JOIN contacts wsfqxgxefet ON AND wsfqxgxefet.deleted=0 WHERE cfrgmmhqsgq.deleted = '0' AND ((cfrgmmhqsgq.account_type IN ('Customer','Cisco_customer')) AND ( IS NULL)) GROUP BY ORDER BY ASC


Hi Val,

That query still brings back accounts that have contacts.


Very strange. I can’t solve this one for now.
On my install query works properly


Thanks for the attempt! I’ll keep at it. I tried the reverse and had to include an ID and set fixed value to 1 and count so maybe there’s something there