03-08-2013 06:01 AM
Hi,
I'm trying to solve a problem restricting a Contact lookup based on the following condition:
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.
03-15-2013 09:53 AM - edited 03-15-2013 09:54 AM
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 };
04-10-2013 08:13 AM
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
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?
04-10-2013 09:46 AM
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";
04-10-2013 11:05 AM
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 };
04-10-2013 11:08 AM
Are you running SQL or Oracle?
04-10-2013 11:10 AM
I'ts a SQL Database
04-10-2013 11:24 AM
04-10-2013 11:32 AM
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
04-10-2013 11:34 AM
I'm working on version 8
I'll try that last part.
Thank you