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 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
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 
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.
Thank you for trying again!!
