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

[nraddatz]

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.

[mlaspina]

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 };

[madlink]

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?

[mlaspina]

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";

[madlink]

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

[mlaspina]

Are you running SQL or Oracle?

[madlink]

I'ts a SQL Database

[mlaspina]

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

[mlaspina]

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

[madlink]

I'm working on  version 8

I'll try that last part.

Thank you