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

[Steve Kenow]

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)

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;

Here's another twist - some modules work! I look for Contacts without
Organizations, the list is correct in both 6.5 and 7.5

*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 8:22 AM Alan Lord <alanslists at gmail.com> wrote:

> I think it's because the Secondary modules are not being joined in the
> way you might think. It's like a left join.
>
>
> Try using the Group By "Related To" field of the Service Contract. It
> might not remove all the accounts without a service contract but if you
> include some columns of the child Service Contracts in the report at
> least you can see them.
>
>
> I can't see a "clean" way to do this... @Prasad?
>
> I think what you really need is something like the mysql: HAVING
> COUNT(*) > 0 clause somehow...
>
> Al
>
>
> On 02/06/2023 21:43, Steve Kenow wrote:
> > There are situations where I am trying to determine the absence of more
> > than one related module (no projects AND no service contracts). Then I
> > put them under All Conditions.
> >
> > I have also used only 1 condition in v7.5 and no other criteria. Fresh
> > install, add 2 organizations, add a service contract to 1 organization,
> > build the report with only 1 criteria - 'Service Contract Number is
> > empty'. Results include both organizations.
> >
> > /*Steve Kenow*/
> >
> >
> > On Wed, May 31, 2023 at 12:01 PM Orville Jimenez
> > <orvillej at blackdiamondlandscape.com
> > <mailto:orvillej at blackdiamondlandscape.com>> wrote:
> >
> >     Are both Criteria under "All Conditions"?
> >
> >     Cheers!
> >
> >     Orville
> >
> >
> >
> >     On Wed, May 31, 2023 at 7:06 AM Steve Kenow
> >     <skenow at rdspos.com
> >     <mailto:skenow at rdspos.com>> wrote:
> >
> >         The same thing happens in a fresh install. 😖
> >
> >         /*Steve Kenow*/
> >         Retail Data Systems of Minnesota
> >         End-User Services & Support Manager
> >         Direct: (952) 392-2686
> >         Office: (952) 934-4002
> >         skenow at rdspos.com
> >         <mailto:skenow at rdspos.com>
> >
> >
> >
> >         Support Questions/Issues? Email
> >         rdshelp at rdspos.com
> >         <mailto:rdshelp at rdspos.com>
> >         (non-emergencies) or call (952) 934-4002 24/7
> >
> >
> >         On Tue, May 30, 2023 at 5:17 PM Steve Kenow <skenow at rdspos.com
> >         <mailto:skenow at rdspos.com>> wrote:
> >
> >             Is there something I'm missing? I haven't tried it in a new
> >             instance yet, just my migrated test version.
> >
> >             For example -
> >             Primary Module: Organizations
> >             Secondary Module: Service Contracts (or Projects, or any
> >             number of modules)
> >
> >             Criteria:
> >             Organization status: Active
> >             Service Contract Number: is empty
> >
> >             It returns all active organizations, basically ignoring the
> >             second criteria.
> >
> >             These work just fine in 6.5
> >
> >             Thanks.
> >
> >             /*Steve*/
> >
> >         _______________________________________________
> >         http://www.vtiger.com/ <http://www.vtiger.com/>
> >
> >     _______________________________________________
> >     http://www.vtiger.com/ <http://www.vtiger.com/>
> >
> >
> > _______________________________________________
> > http://www.vtiger.com/
>
>
> _______________________________________________
> http://www.vtiger.com/
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.vtigercrm.com/pipermail/vtigercrm-developers/attachments/20230605/562fae1f/attachment.html>