<div dir="ltr"><div>I would think it's a left join, and the 'is empty' would return non-matching records in the related tables (equivalent to is null)<br></div><div><br></div><div>This gives me one of the datasets I look at (the report in VT6.5 works as desired) -</div><div>select * from vtiger_account a<br> left join vtiger_servicecontracts s on a.accountid = s.sc_related_to<br> join vtiger_accountscf c using (accountid)<br> where c.cf_807 = 'Active'<br> and s.servicecontractsid is null;</div><div><br></div><div>Here's another twist - some modules work! I look for Contacts without Organizations, the list is correct in both 6.5 and 7.5<br></div><div><br></div><div><div dir="ltr" class="gmail_signature" data-smartmail="gmail_signature"><div dir="ltr"><div><div dir="ltr"><div><div dir="ltr"><div><div dir="ltr"><div><div dir="ltr"><div><div dir="ltr"><div><div dir="ltr"><div><i><b>Steve Kenow</b></i><br></div><div>Retail Data Systems of Minnesota<br></div><span>End-User Services & Support Manager</span><br><div>Direct: (952) 392-2686</div><div>Office: (952) 934-4002</div><div><a href="mailto:skenow@rdspos.com" target="_blank">skenow@rdspos.com</a></div><div><br><img src="http://rdspos.com/Portals/0/Web-RDS-Logo.jpg" width="96" height="39"><br><br>Support Questions/Issues? Email <a href="mailto:rdshelp@rdspos.com" target="_blank">rdshelp@rdspos.com</a> (non-emergencies) or call (952) 934-4002 24/7</div></div></div></div></div></div></div></div></div></div></div></div></div></div></div></div><br></div><br><div class="gmail_quote"><div dir="ltr" class="gmail_attr">On Mon, Jun 5, 2023 at 8:22 AM Alan Lord <<a href="mailto:alanslists@gmail.com">alanslists@gmail.com</a>> wrote:<br></div><blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex">I think it's because the Secondary modules are not being joined in the <br>
way you might think. It's like a left join.<br>
<br>
<br>
Try using the Group By "Related To" field of the Service Contract. It <br>
might not remove all the accounts without a service contract but if you <br>
include some columns of the child Service Contracts in the report at <br>
least you can see them.<br>
<br>
<br>
I can't see a "clean" way to do this... @Prasad?<br>
<br>
I think what you really need is something like the mysql: HAVING <br>
COUNT(*) > 0 clause somehow...<br>
<br>
Al<br>
<br>
<br>
On 02/06/2023 21:43, Steve Kenow wrote:<br>
> There are situations where I am trying to determine the absence of more <br>
> than one related module (no projects AND no service contracts). Then I <br>
> put them under All Conditions.<br>
> <br>
> I have also used only 1 condition in v7.5 and no other criteria. Fresh <br>
> install, add 2 organizations, add a service contract to 1 organization, <br>
> build the report with only 1 criteria - 'Service Contract Number is <br>
> empty'. Results include both organizations.<br>
> <br>
> /*Steve Kenow*/<br>
> <br>
> <br>
> On Wed, May 31, 2023 at 12:01 PM Orville Jimenez <br>
> <<a href="mailto:orvillej@blackdiamondlandscape.com" target="_blank">orvillej@blackdiamondlandscape.com</a> <br>
> <mailto:<a href="mailto:orvillej@blackdiamondlandscape.com" target="_blank">orvillej@blackdiamondlandscape.com</a>>> wrote:<br>
> <br>
> Are both Criteria under "All Conditions"?<br>
> <br>
> Cheers!<br>
> <br>
> Orville<br>
> <br>
> <br>
> <br>
> On Wed, May 31, 2023 at 7:06 AM Steve Kenow<br>
> <<a href="mailto:skenow@rdspos.com" target="_blank">skenow@rdspos.com</a><br>
> <mailto:<a href="mailto:skenow@rdspos.com" target="_blank">skenow@rdspos.com</a>>> wrote:<br>
> <br>
> The same thing happens in a fresh install. 😖<br>
> <br>
> /*Steve Kenow*/<br>
> Retail Data Systems of Minnesota<br>
> End-User Services & Support Manager<br>
> Direct: (952) 392-2686<br>
> Office: (952) 934-4002<br>
> <a href="mailto:skenow@rdspos.com" target="_blank">skenow@rdspos.com</a><br>
> <mailto:<a href="mailto:skenow@rdspos.com" target="_blank">skenow@rdspos.com</a>><br>
> <br>
> <br>
> <br>
> Support Questions/Issues? Email<br>
> <a href="mailto:rdshelp@rdspos.com" target="_blank">rdshelp@rdspos.com</a><br>
> <mailto:<a href="mailto:rdshelp@rdspos.com" target="_blank">rdshelp@rdspos.com</a>><br>
> (non-emergencies) or call (952) 934-4002 24/7<br>
> <br>
> <br>
> On Tue, May 30, 2023 at 5:17 PM Steve Kenow <<a href="mailto:skenow@rdspos.com" target="_blank">skenow@rdspos.com</a><br>
> <mailto:<a href="mailto:skenow@rdspos.com" target="_blank">skenow@rdspos.com</a>>> wrote:<br>
> <br>
> Is there something I'm missing? I haven't tried it in a new<br>
> instance yet, just my migrated test version.<br>
> <br>
> For example -<br>
> Primary Module: Organizations<br>
> Secondary Module: Service Contracts (or Projects, or any<br>
> number of modules)<br>
> <br>
> Criteria:<br>
> Organization status: Active<br>
> Service Contract Number: is empty<br>
> <br>
> It returns all active organizations, basically ignoring the<br>
> second criteria.<br>
> <br>
> These work just fine in 6.5<br>
> <br>
> Thanks.<br>
> <br>
> /*Steve*/<br>
> <br>
> _______________________________________________<br>
> <a href="http://www.vtiger.com/" rel="noreferrer" target="_blank">http://www.vtiger.com/</a> <<a href="http://www.vtiger.com/" rel="noreferrer" target="_blank">http://www.vtiger.com/</a>><br>
> <br>
> _______________________________________________<br>
> <a href="http://www.vtiger.com/" rel="noreferrer" target="_blank">http://www.vtiger.com/</a> <<a href="http://www.vtiger.com/" rel="noreferrer" target="_blank">http://www.vtiger.com/</a>><br>
> <br>
> <br>
> _______________________________________________<br>
> <a href="http://www.vtiger.com/" rel="noreferrer" target="_blank">http://www.vtiger.com/</a><br>
<br>
<br>
_______________________________________________<br>
<a href="http://www.vtiger.com/" rel="noreferrer" target="_blank">http://www.vtiger.com/</a></blockquote></div>