# Report for Accounts with No Contacts is incorrectly pulling Accounts WITH Contacts **URL:** \ **Category:** KReporter\ **Created:** [November 7, 2018, 4:50pm UTC](https://community.spicecrm.io/t/report-for-accounts-with-no-contacts-is-incorrectly-pulling-accounts-with-contacts/376 "2018-11-07T16:50:10Z")\ **Posts on this page:** 20\ **Page:** 1
**Author:** ![tessupport](https://avatars.discourse-cdn.com/v4/letter/t/b2d939/32.png) [@tessupport](https://community.spicecrm.io/u/tessupport)\ **Post date:** [November 7, 2018, 4:50pm UTC](https://community.spicecrm.io/t/report-for-accounts-with-no-contacts-is-incorrectly-pulling-accounts-with-contacts/376/1 "2018-11-07T16:50:10Z")
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. ---
**Author:** ![tessupport](https://avatars.discourse-cdn.com/v4/letter/t/b2d939/32.png) [@tessupport](https://community.spicecrm.io/u/tessupport)\ **Post date:** [November 7, 2018, 4:53pm UTC](https://community.spicecrm.io/t/report-for-accounts-with-no-contacts-is-incorrectly-pulling-accounts-with-contacts/376/3 "2018-11-07T16:53:58Z")
![Manipulate%20Tab%20No%20Contacts](https://canada1.discourse-cdn.com/flex030/uploads/spicecrm/original/1X/607c6dde7a2f711307f20f60f87d2aab1c0ecc35.png) ![Present%20Tab%20No%20Contacts](https://canada1.discourse-cdn.com/flex030/uploads/spicecrm/original/1X/bc410251beac7fc0b62ff4c1f48a6b553dcd0281.png) ![SELECT%20No%20Contacts](https://canada1.discourse-cdn.com/flex030/uploads/spicecrm/original/1X/69e047321dfc12782b4f2b792746f861b216158c.png) ---
**Author:** ![maretval](https://avatars.discourse-cdn.com/v4/letter/m/b38774/32.png) [@maretval](https://community.spicecrm.io/u/maretval)\ **Post date:** [November 8, 2018, 8:32am UTC](https://community.spicecrm.io/t/report-for-accounts-with-no-contacts-is-incorrectly-pulling-accounts-with-contacts/376/4 "2018-11-08T08:32:33Z")
In manipulate you have contact fields as required. It will override the select query. Set optional for Contacts first name, last name and ID ---
**Author:** ![tessupport](https://avatars.discourse-cdn.com/v4/letter/t/b2d939/32.png) [@tessupport](https://community.spicecrm.io/u/tessupport)\ **Post date:** [November 8, 2018, 2:28pm UTC](https://community.spicecrm.io/t/report-for-accounts-with-no-contacts-is-incorrectly-pulling-accounts-with-contacts/376/5 "2018-11-08T14:28:25Z")
Thanks! Currently upgrading our SuiteCRM so I’ll check when it’s back online. Will the manipulate tab always take precedence over Select tab? ---
**Author:** ![maretval](https://avatars.discourse-cdn.com/v4/letter/m/b38774/32.png) [@maretval](https://community.spicecrm.io/u/maretval)\ **Post date:** [November 8, 2018, 3:35pm UTC](https://community.spicecrm.io/t/report-for-accounts-with-no-contacts-is-incorrectly-pulling-accounts-with-contacts/376/6 "2018-11-08T15:35:33Z")
Yes. As soon as a a field is required in manipulate tab, the where clause is extended with “thefield is not null” ---
**Author:** ![tessupport](https://avatars.discourse-cdn.com/v4/letter/t/b2d939/32.png) [@tessupport](https://community.spicecrm.io/u/tessupport)\ **Post date:** [November 8, 2018, 3:55pm UTC](https://community.spicecrm.io/t/report-for-accounts-with-no-contacts-is-incorrectly-pulling-accounts-with-contacts/376/7 "2018-11-08T15:55:24Z")
![image](https://canada1.discourse-cdn.com/flex030/uploads/spicecrm/original/1X/466ed230dc9ea7f161c851875079428a653763ae.png) ![image](https://canada1.discourse-cdn.com/flex030/uploads/spicecrm/original/1X/7d38f9acfa3ad21c33983ca03bc0bad591564050.png) I set the Contact fields in Manipulate to optional, but it is still pulling accounts that have contacts. Any ideas? ---
**Author:** ![maretval](https://avatars.discourse-cdn.com/v4/letter/m/b38774/32.png) [@maretval](https://community.spicecrm.io/u/maretval)\ **Post date:** [November 8, 2018, 7:53pm UTC](https://community.spicecrm.io/t/report-for-accounts-with-no-contacts-is-incorrectly-pulling-accounts-with-contacts/376/8 "2018-11-08T19:53:07Z")
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. ---
**Author:** ![tessupport](https://avatars.discourse-cdn.com/v4/letter/t/b2d939/32.png) [@tessupport](https://community.spicecrm.io/u/tessupport)\ **Post date:** [November 8, 2018, 8:04pm UTC](https://community.spicecrm.io/t/report-for-accounts-with-no-contacts-is-incorrectly-pulling-accounts-with-contacts/376/9 "2018-11-08T20:04:01Z")
Sure, one moment please ---
**Author:** ![tessupport](https://avatars.discourse-cdn.com/v4/letter/t/b2d939/32.png) [@tessupport](https://community.spicecrm.io/u/tessupport)\ **Post date:** [November 8, 2018, 8:05pm UTC](https://community.spicecrm.io/t/report-for-accounts-with-no-contacts-is-incorrectly-pulling-accounts-with-contacts/376/10 "2018-11-08T20:05:06Z")
![image](https://canada1.discourse-cdn.com/flex030/uploads/spicecrm/original/1X/15093c4a11af5d1c7094200b85de40116eb7f9f5.png) ---
**Author:** ![maretval](https://avatars.discourse-cdn.com/v4/letter/m/b38774/32.png) [@maretval](https://community.spicecrm.io/u/maretval)\ **Post date:** [November 8, 2018, 9:00pm UTC](https://community.spicecrm.io/t/report-for-accounts-with-no-contacts-is-incorrectly-pulling-accounts-with-contacts/376/11 "2018-11-08T21:00:43Z")
What’s you mysql version? ---
**Author:** ![tessupport](https://avatars.discourse-cdn.com/v4/letter/t/b2d939/32.png) [@tessupport](https://community.spicecrm.io/u/tessupport)\ **Post date:** [November 8, 2018, 9:25pm UTC](https://community.spicecrm.io/t/report-for-accounts-with-no-contacts-is-incorrectly-pulling-accounts-with-contacts/376/12 "2018-11-08T21:25:03Z")
we are currently using mysql Ver 15.1 Distrib 5.5.56-MariaDB, for Linux (x86\_64) using readline 5.1 ---
**Author:** ![maretval](https://avatars.discourse-cdn.com/v4/letter/m/b38774/32.png) [@maretval](https://community.spicecrm.io/u/maretval)\ **Post date:** [November 8, 2018, 9:32pm UTC](https://community.spicecrm.io/t/report-for-accounts-with-no-contacts-is-incorrectly-pulling-accounts-with-contacts/376/13 "2018-11-08T21:32:38Z")
Do you get proper results when you run following query directly in database? `SELECT cfrgmmhqsgq.id as "sugarRecordId", 'Accounts' as "sugarRecordModule" , wsfqxgxefet.id as "wsfqxgxefetid", 'root:Accounts::link:Accounts:contacts' as "wsfqxgxefetpath", cfrgmmhqsgq.id as "cfrgmmhqsgqid", 'root:Accounts' as "cfrgmmhqsgqpath", cfrgmmhqsgq.name 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 cfrgmmhqsgq.id=aswsggkqsdc.account_id AND aswsggkqsdc.deleted=0 LEFT JOIN contacts wsfqxgxefet ON wsfqxgxefet.id=aswsggkqsdc.contact_id AND wsfqxgxefet.deleted=0 WHERE cfrgmmhqsgq.deleted = '0' AND ((cfrgmmhqsgq.account_type IN ('Customer','Cisco_customer')) AND (wsfqxgxefet.id IS NULL)) GROUP BY cfrgmmhqsgq.name ORDER BY cfrgmmhqsgq.id ASC` ---
**Author:** ![tessupport](https://avatars.discourse-cdn.com/v4/letter/t/b2d939/32.png) [@tessupport](https://community.spicecrm.io/u/tessupport)\ **Post date:** [November 19, 2018, 6:45pm UTC](https://community.spicecrm.io/t/report-for-accounts-with-no-contacts-is-incorrectly-pulling-accounts-with-contacts/376/14 "2018-11-19T18:45:33Z")
Hi Val, That query still brings back accounts that have contacts. ---
**Author:** ![maretval](https://avatars.discourse-cdn.com/v4/letter/m/b38774/32.png) [@maretval](https://community.spicecrm.io/u/maretval)\ **Post date:** [November 20, 2018, 4:23pm UTC](https://community.spicecrm.io/t/report-for-accounts-with-no-contacts-is-incorrectly-pulling-accounts-with-contacts/376/15 "2018-11-20T16:23:26Z")
Very strange. I can’t solve this one for now. On my install query works properly ---
**Author:** ![tessupport](https://avatars.discourse-cdn.com/v4/letter/t/b2d939/32.png) [@tessupport](https://community.spicecrm.io/u/tessupport)\ **Post date:** [November 20, 2018, 4:42pm UTC](https://community.spicecrm.io/t/report-for-accounts-with-no-contacts-is-incorrectly-pulling-accounts-with-contacts/376/16 "2018-11-20T16:42:50Z")
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 ---
**Author:** ![tessupport](https://avatars.discourse-cdn.com/v4/letter/t/b2d939/32.png) [@tessupport](https://community.spicecrm.io/u/tessupport)\ **Post date:** [January 28, 2019, 5:35pm UTC](https://community.spicecrm.io/t/report-for-accounts-with-no-contacts-is-incorrectly-pulling-accounts-with-contacts/376/17 "2019-01-28T17:35:01Z")
Still having issues with this report. If you still have it saved by chance, can you please post shots of the manipulate and select tabs when you have a moment so I can compare? ---
**Author:** ![maretval](https://avatars.discourse-cdn.com/v4/letter/m/b38774/32.png) [@maretval](https://community.spicecrm.io/u/maretval)\ **Post date:** [February 6, 2019, 2:03pm UTC](https://community.spicecrm.io/t/report-for-accounts-with-no-contacts-is-incorrectly-pulling-accounts-with-contacts/376/18 "2019-02-06T14:03:52Z")
This is my select tab ![grafik](https://canada1.discourse-cdn.com/flex030/uploads/spicecrm/original/1X/4871de5a726098950299714a95fb5b2ba594fb69.png) My manipulate tab ![grafik](https://canada1.discourse-cdn.com/flex030/uploads/spicecrm/original/1X/26dcdcb51d10ab9195814cf5c2ba4d21ccb02406.png) I don’t have any custom field. Your problem might be the left join on the custom table. Try first without it. ---
**Author:** ![tessupport](https://avatars.discourse-cdn.com/v4/letter/t/b2d939/32.png) [@tessupport](https://community.spicecrm.io/u/tessupport)\ **Post date:** [February 6, 2019, 2:48pm UTC](https://community.spicecrm.io/t/report-for-accounts-with-no-contacts-is-incorrectly-pulling-accounts-with-contacts/376/19 "2019-02-06T14:48:14Z")
It’s still pulling accounts that have Contacts ☹ ---
**Author:** ![maretval](https://avatars.discourse-cdn.com/v4/letter/m/b38774/32.png) [@maretval](https://community.spicecrm.io/u/maretval)\ **Post date:** [February 6, 2019, 4:15pm UTC](https://community.spicecrm.io/t/report-for-accounts-with-no-contacts-is-incorrectly-pulling-accounts-with-contacts/376/20 "2019-02-06T16:15:46Z")
This is the query generated: SELECT rsatzyrwxyb.id AS “sugarRecordId”, ‘Accounts’ AS “sugarRecordModule”, cfkezsaqnnj.id AS “cfkezsaqnnjid”, ‘root:Accounts:🔗Accounts:contacts’ AS “cfkezsaqnnjpath”, rsatzyrwxyb.id AS “rsatzyrwxybid”, ‘root:Accounts’ AS “rsatzyrwxybpath”, rsatzyrwxyb.name AS “k5c39d56721f7b3db22a8f6122480”, ‘1’ AS “k1223c355e5d4d144844c1a49d25f” FROM accounts rsatzyrwxyb LEFT JOIN accounts\_contacts eqymfccqbhk ON rsatzyrwxyb.id=eqymfccqbhk.account\_id AND eqymfccqbhk.deleted=0 LEFT JOIN contacts cfkezsaqnnj ON cfkezsaqnnj.id=eqymfccqbhk.contact\_id AND cfkezsaqnnj.deleted=0 WHERE rsatzyrwxyb.deleted = ‘0’ AND ((cfkezsaqnnj.id IS NULL)) ORDER BY rsatzyrwxyb.id ASC It definitely returns accounts without contacts on my instances. I processed it on two different databases. Results are OK. I am afraid I can’t help you with this one. ---
**Author:** ![tessupport](https://avatars.discourse-cdn.com/v4/letter/t/b2d939/32.png) [@tessupport](https://community.spicecrm.io/u/tessupport)\ **Post date:** [February 6, 2019, 4:38pm UTC](https://community.spicecrm.io/t/report-for-accounts-with-no-contacts-is-incorrectly-pulling-accounts-with-contacts/376/21 "2019-02-06T16:38:09Z")
Thank you for trying again!!