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

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?

This is my select tab

My manipulate tab

I don’t have any custom field. Your problem might be the left join on the custom table.
Try first without it.

It’s still pulling accounts that have Contacts :frowning:

This is the query generated:

SELECT AS “sugarRecordId”, ‘Accounts’ AS “sugarRecordModule”, AS “cfkezsaqnnjid”, ‘root:Accounts::link:Accounts:contacts’ AS “cfkezsaqnnjpath”, AS “rsatzyrwxybid”, ‘root:Accounts’ AS “rsatzyrwxybpath”, AS “k5c39d56721f7b3db22a8f6122480”, ‘1’ AS “k1223c355e5d4d144844c1a49d25f”
FROM accounts rsatzyrwxyb
LEFT JOIN accounts_contacts eqymfccqbhk ON AND eqymfccqbhk.deleted=0
LEFT JOIN contacts cfkezsaqnnj ON AND cfkezsaqnnj.deleted=0
WHERE rsatzyrwxyb.deleted = ‘0’ AND (( IS NULL))

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.

Thank you for trying again!!