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

[Alan Lord]

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