Hi,

I've got a big problem with the LookupExclusion Property of SalesLogix 7.5.4 because it is very very slow.

We have round about 11.000 Contacts at all. About 3.000 Contacs belong to a special Account, so theese Contacts should not be shown in the Lookup.

First we tried to do a Lookup that excludes that Contacts but after that we didn't see any Contacts anymore. So I started searching through the internet and find out that I can do some LookupExclusions to my Lookup.

This is my Code:

Sage.Entity.Interfaces.IAccount acc = Sage.Platform.EntityFactory.GetById<Sage.Entity.Interfaces.IAccount>(strAccountId);<br />    
    lueAddParticipant.LookupExclusions = (ICollection)acc.Contacts;
    lueAddParticipant.LookupPreFilters.Clear();
    lueAddParticipant.InitializeLookup = true;

But this is, as i wrote already, very very ... very slow so I had to remove it.

But where is the problem? Do the LookupExclusions themselfes take so long or is it the Time to convert the generic Collection to the basic ICollection?

What can I do here to get the Contacts belonging to Account "acc" not being shown in the Lookup?

The SECCODEID based security in SLX is DOG slow.....always has been......you'd think it was a simple left join sto SECRIGHTS\SECACCESS whatever that multi join ACCESSID table is) kind of situation but it is not......every row is a separate SQL Transaction.....that is EVERYsingle  row......as in BeginTrans.....EndTrans......

We ended up going to a NATIVE SQL Connection for many of our large scale queries......

Here's a test for you:

Time the ADMIN going after 11,000 Contacts......then Time another User going after the same 11,000 contacts.....we frequently see a slowdown in the neighborhood of 4-8 times ON THE LAN.....probably slower on the Web.....

Still not as slow as MS CRM 2011.....

Hydrating those 3,000 contacts is not going to be fast - no matter what you do.  I would use a lookup pre-filter instead.  You can either hard-code the account ID, or use some other means to identify which contacts should not be added.  You are more or less executing "SELECT * from Contact where Contact ID not in ( <list of 3,000 IDs> ) "

For example, using AA, add a lookup pre-filter for Account.Type != "My Special Account Type".  This results in something more like "SELECT * from Contact where Contact.Account.Type != "My Special Account Type" (much more effecient!)

This will pass the SQL to the DB eliminating the pain and slowness of retrieving 3,000 contacts.   The other problem is that as the number of contacts in the special account grows, you may eventually surpass the maximum string size for a SQL statement causing other problems.

well, this does not work. actually the only way to identify this special account is the id or a business rule called "IsCompanyAccount".

we already tried different things. for example i added a calculated field / codesnippet property to create a pre-filter on it. but every time i use that filter the lookup does not return anything when I click "search".

it would be soooo easy if we could use sql / hql statements to create lookups like the lan client :-/

It seems you need to modify at runtime then.  Here's how to inject your own HQL into a lookup - you can do this in the form load event.  Note the odd format of the string stored in sAcctList.  This is needed because the control will wrap the entire expression in single quotes due to the type being a string.  Essentially, this builds out the following query:

SELECT *
FROM   CONTACT
WHERE Id != 'XXX'
   AND  AccountId = 'My Account ID'
   AND  'A' != 'B'

 

     // 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'"
 
     sAcctList = "xxx' AND (Contact.Account.Id = '" + 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 };
 

 

 

Oh thanks man!!! This does exactly what we were looking for!

The only thing is: With your code I get ONLY the Contacts of my Special Account. To get only the others excluding the ones of my special Account I had to change

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



to


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