Acumatica Find Duplicate Leads

Question

I would like to identify if a lead being created is yet on the database, base on its email. The following sql query is working in SSMS but not in bql :

'''SELECT [CRLead].[ContactID]  FROM [dbo].[CRLead]  INNER JOIN [dbo].[Contact] On ([CRLead].[ContactID]=[Contact].[ContactID])WHERE [CRLead].[Resolution]<> 'DL' AND [Contact].[CreatedDateTime] IS NOT NULL AND [Contact].[Email]='X@X.XX' '''

    '''string email = row.EMail;
int? code = row.ContactID;
string dl = "DL";
CRLead lead0 = SelectFrom<CRLead>.Where<CRLead.eMail.IsEqual<@P.AsString>>.OrderBy<Desc<CRLead.contactID>>.View.SelectWindowed(Base, 0, 1, email); // result OK
CRLead lead1 = SelectFrom<CRLead>.Where<CRLead.createdDateTime.IsNotNull.And<CRLead.eMail.IsEqual<@P.AsString>>>.OrderBy<Desc<CRLead.contactID>>.View.SelectWindowed(Base, 0, 1, email); // result OK
CRLead lead2 = SelectFrom<CRLead>.Where<CRLead.resolution.IsNotEqual<@P.AsString>.And<CRLead.eMail.IsEqual<@P.AsString>>>.OrderBy<Desc<CRLead.contactID>>.View.SelectWindowed(Base, 0, 1, dl, email);// result NOT OK
CRLead lead3 = SelectFrom<CRLead>.Where<CRLead.createdDateTime.IsNotNull.And<CRLead.resolution.IsNotEqual<@P.AsString>.And<CRLead.eMail.IsEqual<@P.AsString>>>>.OrderBy<Desc<CRLead.contactID>>.View.SelectWindowed(Base, 0, 1, dl, email);// result NOT OK
CRLead lead = SelectFrom<CRLead>.Where<CRLead.contactID.IsNotEqual<@P.AsInt>.And<CRLead.resolution.IsNotEqual<PX.Objects.CR.doublClass>.And<CRLead.eMail.IsEqual<@P.AsString>>>>.OrderBy<Desc<CRLead.contactID>>.View.SelectWindowed(Base, 0, 1, code, email);// RESULT NOT OK
public class doublClass : PX.Data.BQL.BqlString.Constant<doublClass>
{
    public doublClass() : base("DL") { }
}'''

Answer 1

Isn't it a NULL handling issue? Try this BQL; I’ve included records with null values.

CRLead lead2 = SelectFrom<CRLead>
.Where<Brackets<CRLead.resolution.IsNotEqual<@P.AsString>.Or<CRLead.resolution.IsNull>>
    .And<CRLead.eMail.IsEqual<@P.AsString>>>
.OrderBy<Desc<CRLead.contactID>>.View.SelectWindowed(Base, 0, 1, dl, email);