[Vtigercrm-developers] "is empty" criteria for related modules - not working in 7.x?

[Steve Kenow]

Contacts is the primary module and Organizations is set as a related
module. Is that how I'll need to adjust?

*Steve Kenow*
Retail Data Systems of Minnesota
End-User Services & Support Manager
Direct: (952) 392-2686
Office: (952) 934-4002
skenow at rdspos.com



Support Questions/Issues? Email rdshelp at rdspos.com (non-emergencies) or
call (952) 934-4002 24/7


On Mon, Jun 5, 2023 at 11:14 AM Alan Lord <alanslists at gmail.com> wrote:

> On 05/06/2023 16:38, Steve Kenow wrote:
> > 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)
>
> Yeah, but the report engine doesn't quite work that way. Take a look at
> the file ReportRun.php (then run away!!!)
>
> > This gives me one of the datasets I look at (the report in VT6.5 works
> > as desired) -
> > select * from vtiger_account a
> > left join vtiger_servicecontracts s on a.accountid = s.sc_related_to
> >      join vtiger_accountscf c using (accountid)
> >      where c.cf_807 = 'Active'
> >      and s.servicecontractsid is null;
>
> If only it were that simple... It can be actually. We have an extension
> that lets admin users write their own SQL Report queries.
>
> https://libertus.co.uk/sql-reports/
>
> > Here's another twist - some modules work! I look for Contacts without
> > Organizations, the list is correct in both 6.5 and 7.5
>
> With the Organisation as the Primary module again?
>
> Al
>
> _______________________________________________
> http://www.vtiger.com/
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.vtigercrm.com/pipermail/vtigercrm-developers/attachments/20230605/58f505c8/attachment.html>