Showing results for 
Search instead for 
Do you mean 
Community Home Request Access Read Blogs Share Your Ideas Search Community View My Settings
Reply
Nickel Contributor
Posts: 47
Registered: ‎05-25-2011

How do i restrict a lookup with an "or" condition?

Hi,

 

I'm trying to solve a problem restricting a Contact lookup based on the following condition:

 

  • Contact belongs to account xyz.
  • (Contact.Status != "No longer employed" OR Contact.Status is null)

I currently have this:

 

LkpAgencyContact.LookupPreFilters.Clear();
Sage.SalesLogix.HighLevelTypes.LookupPreFilter filter = new Sage.SalesLogix.HighLevelTypes.LookupPreFilter();
filter.LookupEntityName = "Contact";
filter.PropertyName = "Account.Id";
filter.OperatorCode = "=";
filter.FilterValue = agencyId;
filter.PropertyType = "System.String";
LkpAgencyContact.LookupPreFilters.Add(filter);

//Contact.Status <> 'No Longer Employed'
filter = new Sage.SalesLogix.HighLevelTypes.LookupPreFilter();
filter.LookupEntityName = "Contact";
filter.PropertyName = "Status";
filter.OperatorCode = "!=";
filter.FilterValue = "No Longer Employed";
filter.PropertyType = "System.String";
LkpAgencyContact.LookupPreFilters.Add(filter);

 

The problem is that i haven't figured out a way to implement the "OR" in the second condition.

 

What would you suggest? Any ideas or workarounds?

 

Thanks.

Bronze Super Contributor
Posts: 236
Registered: ‎03-30-2009

Re: How do i restrict a lookup with an "or" condition?

[ Edited ]

You need to inject the HQL you want into the filter value.  The example shown is an 'in' cluase, but you can easily write your own 'or' clause.  This can be done in 2 ways:

 

Statically:

 

Set the filter value as shown below below(be sure to use a string property) – note that the single quote on the beginning and end is missing – the lookup will add these at runtime:

 

XXX' and Contact.Account.Type IN (‘Account Type 1’, ‘Account Type 2’) and 'A' != 'B

 

This results in a where clause of:

                WHERE account.type != ‘XXX’

                  AND  account.type IN (‘Account Type 1’, ‘Account Type 2’)

                  AND ‘A’ != ‘B’

 

Or, you can do it programmatically – the following example ads an ‘In’ clause to a lookup and is fired in the quickformload:

 

// Inject HQL into a lookup filter:
string sAcctList = string.Empty;
string sId = 'MY ID Here'; // account ID to search for

// Note that the filter builds out the value as the following string "Account.Id != '<value in sAccount List variable>'"
//  So, we need to make sure we account for our HQL being inside of single quotes - thus the unmatched single quotes
//  around "xxx" at the beginning and "B" at the end.  The resulting HQL executed from the lookup will be:
//   "Where Account.Id != 'xxx' AND (Contact.Account.Id = 'MY ID Here' OR Contact.Account.ParentId = 'MY ID Here') AND 'A' != 'B'"
//
// Be sure to add the ‘LookupExclusions’ line at the end – this kills the lookup cache and re-sets it all.  If you 
//   forget this line, the lookup will only work correctly the 1st time.

sAcctList = "xxx' AND (Contact.Account.Id = '" + sId + "' OR Contact.Account.ParentId = '" + sId + "') AND 'A' != 'B";

Sage.SalesLogix.HighLevelTypes.LookupPreFilter actfilter = new Sage.SalesLogix.HighLevelTypes.LookupPreFilter();
actfilter.LookupEntityName = "Sage.Entity.Interfaces.IContact";
actfilter.FilterValue = sAcctList;
actfilter.PropertyName = "Id";
actfilter.OperatorCode = "!=";
actfilter.PropertyType = "System.String";
lupContact.LookupPreFilters.Clear();
lupContact.LookupPreFilters.Add(actfilter);
lupContact.LookupExclusions = new string[] { string.Empty };

 

Mike LaSpina
Professional Services Consultant

Sage
8800 N Gainey Center Dr Ste 200
Scottsdale AZ 85258
Tel: 480-383-5344
Cell: 480-321-6637
Fax: 480-556-4090
mike.laspina@sage.com

Your Business in Mind.
New Member
Posts: 6
Registered: ‎02-05-2013

Re: How do I restrict a lookup with an "or" condition?

Hi Mike, 

 

I'm trying to use your solution with the in clause but on the Account id.

 

Sage.Entity.Interfaces.IAccount account = this.GetParentEntity() as Sage.Entity.Interfaces.IAccount;

string sAcctList = string.Empty;
string sId = string.Join("','", account.GetRelatedAccountsIds()); // accounts IDs to search for
 
sAcctList = "xxx' AND (Id in ('" + sId + "'))";
Sage.SalesLogix.HighLevelTypes.LookupPreFilter actfilter = new Sage.SalesLogix.HighLevelTypes.LookupPreFilter();
actfilter.LookupEntityName = "Sage.Entity.Interfaces.IAccount";
actfilter.FilterValue = sAcctList;
actfilter.PropertyName = "Id";
actfilter.OperatorCode = "!=";
actfilter.PropertyType = "System.String";
lkpIndemnitor.LookupPreFilters.Clear();
lkpIndemnitor.LookupPreFilters.Add(actfilter);
lkpIndemnitor.LookupExclusions = new string[] { string.Empty };

 

But it's not returning the correct information. Using chrome I can get the sdata query 

 

  1. where:
    upper(Id) ne "XXX' AND (ID IN ('A1GT0A10000A','A6UJ9A000K96','A6UJ9A000L09','A6UJ9A000LO4','A6UJ9A000LSX','A6UJ9A000LTA','A6UJ9A000MTL','A6UJ9A000N4F','A6UJ9A000NTQ'))"
  2. select:
    Id,$key,AccountName
  3. orderby:
    AccountName

As you can see the lookup is changing the FilterValue to upper case and that it's breaking the query. How can we fix this?

 

Bronze Super Contributor
Posts: 236
Registered: ‎03-30-2009

Re: How do I restrict a lookup with an "or" condition?

Since ID is technically not a sting type in the ORM, that could be the problem.  Try switching it to a known string property such as Type:

 

actfilter.PropertyName = "Type";
Mike LaSpina
Professional Services Consultant

Sage
8800 N Gainey Center Dr Ste 200
Scottsdale AZ 85258
Tel: 480-383-5344
Cell: 480-321-6637
Fax: 480-556-4090
mike.laspina@sage.com

Your Business in Mind.
Highlighted
New Member
Posts: 6
Registered: ‎02-05-2013

Re: How do I restrict a lookup with an "or" condition?

Mike, I changed the property as you suggested but it keeps creating the where clause with uppercase 

 

sAcctList = "xxx' AND (Id in ('" + sId + "'))";

        Sage.SalesLogix.HighLevelTypes.LookupPreFilter actfilter = new Sage.SalesLogix.HighLevelTypes.LookupPreFilter();
        actfilter.LookupEntityName = "Sage.Entity.Interfaces.IAccount";
        actfilter.FilterValue = sAcctList;
        actfilter.PropertyName = "Type";
        actfilter.OperatorCode = "!=";
        actfilter.PropertyType = "System.String";
        lkpIndemnitor.LookupPreFilters.Clear();
        lkpIndemnitor.LookupPreFilters.Add(actfilter);
        lkpIndemnitor.LookupExclusions = new string[] { string.Empty };

 

 

  1. where:
    upper(Type) ne "XXX' AND (ID IN ('A1GT0A10000A','A6UJ9A000K96','A6UJ9A000L09','A6UJ9A000LO4','A6UJ9A000LSX','A6UJ9A000LTA','A6UJ9A000MTL','A6UJ9A000N4F','A6UJ9A000NTQ'))"
  2. select:
    Id,$key,AccountName,Type
  3. orderby:
    AccountName
Bronze Super Contributor
Posts: 236
Registered: ‎03-30-2009

Re: How do I restrict a lookup with an "or" condition?

Are you running SQL or Oracle?

Mike LaSpina
Professional Services Consultant

Sage
8800 N Gainey Center Dr Ste 200
Scottsdale AZ 85258
Tel: 480-383-5344
Cell: 480-321-6637
Fax: 480-556-4090
mike.laspina@sage.com

Your Business in Mind.
New Member
Posts: 6
Registered: ‎02-05-2013

Re: How do I restrict a lookup with an "or" condition?

I'ts a SQL Database

Bronze Super Contributor
Posts: 236
Registered: ‎03-30-2009

Re: How do I restrict a lookup with an "or" condition?

One more thing - what is your SLX version? Note that I have not tried this in 8 yet.
Mike LaSpina
Professional Services Consultant

Sage
8800 N Gainey Center Dr Ste 200
Scottsdale AZ 85258
Tel: 480-383-5344
Cell: 480-321-6637
Fax: 480-556-4090
mike.laspina@sage.com

Your Business in Mind.
Bronze Super Contributor
Posts: 236
Registered: ‎03-30-2009

Re: How do I restrict a lookup with an "or" condition?

Also note that you are missing the end part.  Your code should read

 

sAcctList = "xxx' AND (Id in ('" + sId + "')) and 'A' != 'B";

  Note the following is added to the end (leave off the single quote):

          and 'A' != 'B

Mike LaSpina
Professional Services Consultant

Sage
8800 N Gainey Center Dr Ste 200
Scottsdale AZ 85258
Tel: 480-383-5344
Cell: 480-321-6637
Fax: 480-556-4090
mike.laspina@sage.com

Your Business in Mind.
New Member
Posts: 6
Registered: ‎02-05-2013

Re: How do I restrict a lookup with an "or" condition?

I'm working on  version 8

 

I'll try that last part.

 

Thank you