Programmatically Setting Custom Conditions for the Lookup Results Group in Infor CRM (Saleslogix) Web

The Lookup Results Group in Infor CRM displays a list of records matching a particular set of conditions. Typically, the user performs a search and the search conditions set the results of the Lookup Results group. However, it is possible to programmatically set these conditions at runtime.

Kris Halsrud previously wrote about setting the Lookup Results based on a custom condition. This article is going to expound on that idea and look at all the options available.

See Kris Halsrud’s article:
Programmatically Creating a Temporary Lookup Group for a specific entity in the SalesLogix web client

Let’s look at the base code (in this sample, we are on the opportunity entity and setting the condition to display all opportunities for a particular account):

// get group context service
Sage.SalesLogix.Client.GroupBuilder.IGroupContextService groupContextService = Sage.Platform.Application.ApplicationContext.Current.Services.Get<Sage.SalesLogix.Client.GroupBuilder.IGroupContextService>() as Sage.SalesLogix.Client.GroupBuilder.GroupContextService;
if (groupContextService != null)
{
    // set base table
    groupContextService.CurrentTable = "OPPORTUNITY";
        
    // set group info
    Sage.SalesLogix.Client.GroupBuilder.EntityGroupInfo currentGroupInfo = groupContextService.GetGroupContext().CurrentGroupInfo;
    currentGroupInfo.LookupTempGroup.ClearConditions();
    
    // this is the important part, this is where you set the 
    // conditions you want to display in the lookup results.
    // add conditions in form of data path "TABLE:FIELD" and value.
    // other overloads allow operation and conjunction for multiple conditions
    // in this example we will set the lookup results for all opportunities for a particular account
    currentGroupInfo.LookupTempGroup.AddLookupCondition("ACCOUNT:ACCOUNTID", "AGHEA0002669");
        
    // add conditions to group XML & set as lookupresults group
    currentGroupInfo.LookupTempGroup.GroupXML = Sage.SalesLogix.Client.GroupBuilder.GroupInfo.RebuildGroupXML(currentGroupInfo.LookupTempGroup.GroupXML);
    groupContextService.CurrentGroupID = "LOOKUPRESULTS";
}

In the code above, the important part is this line:

currentGroupInfo.LookupTempGroup.AddLookupCondition("ACCOUNT:ACCOUNTID", "AGHEA0002669");

There are some other overloads for AddLookupCondition that allow you to specify the operator (such as equals, starts with, etc) and the conjunction (and, or, etc). One overload allows you to include the operator only (for setting a single condition):

currentGroupInfo.LookupTempGroup.AddLookupCondition("ACCOUNT:ACCOUNTID", "=", "AGHEA0002669");

If no operator is specified, the default is ” STARTS WITH ” (See operator choices below). The other overload that allows you to specify both the operator and the conjunction:

// set the lookup results for all opportunities for two different accounts
currentGroupInfo.LookupTempGroup.AddLookupCondition("ACCOUNT:ACCOUNTID", "=", "AGHEA0002669", "OR");
currentGroupInfo.LookupTempGroup.AddLookupCondition("ACCOUNT:ACCOUNTID", "=", "AA2EK0013031", "OR");

This gives you the ability to add multiple conditions. If you wanted to see all opportunities for a particular account that has a status of open you could add the conditions like this:

// set the lookup results for all opportunities for two different accounts
currentGroupInfo.LookupTempGroup.AddLookupCondition("ACCOUNT:ACCOUNTID", "=", "AGHEA0002669");
currentGroupInfo.LookupTempGroup.AddLookupCondition("OPPORTUNITY:STATUS", "=", "Open", "AND");

So, what are the choices for the operator and conjunction?

Valid operator choices are:

  • ” STARTS WITH “
  • ” LIKE “
  • ” IN “
  • ” IS ” (works great with value ” NULL ” for does not contain data)
  • ” IS NOT ” (works great with value ” NULL ” for does contain data)
  • “=”
  • “>=”
  • “>”
  • “<=”
  • “<“
  • “<>”
  • etc

For the group, this is turning into a SQL query, so basically operators that are valid in SQL should work here (obviously, the “STARTS WITH” here changes to a LIKE with a wildcard after the value)

Valid conjunction/disjunction choices are:

  • AND
  • OR
  • etc

Again, think SQL query for the choices.

If you set the Lookup Result Group on the initial load of a page, the lookup results group will display with the conditions you’ve set. The detail page for the record you navigated to will still display normally, but the lookup results group will be the selected group so the list in the left task pane will show the record matching your conditions. If the page has already loaded and you’re setting the lookup results on a post back, you’ll need to reload the page by redirecting back to itself.

ABOUT THE AUTHOR

Ryan Farley

Ryan Farley is the Director of Development for Customer FX and creator of slxdeveloper.com. He's been blogging regularly about SalesLogix, now Infor CRM, since 2001 and believes in sharing with the community. He loves C#, Javascript, web development, open source, and Linux. He also loves his hobby as an amateur filmmaker.

6 Comments

  1. Hi Ryan,

    Great article!!!

    In your example, opportunity and account are different tables and it’s easy to distinguish them in the fallowing parts.

    groupContextService.CurrentTable = “OPPORTUNITY”;

    currentGroupInfo.LookupTempGroup.AddLookupCondition(“ACCOUNT:ACCOUNTID”, “=”, “AGHEA0002669”);
    currentGroupInfo.LookupTempGroup.AddLookupCondition(“OPPORTUNITY:STATUS”, “=”, “Open”, “AND”);

    If a query needs to use account table with self join:
    select * from ACCOUNT a
    inner join ACCOUNT b on a.ACCOUNTID = b.ACCOUNTID
    where b.PARENTID = ‘xxxxxx’ or a.ACCOUNTID = ‘yyyyyy’

    How do you set the table name in the conditions?

    Thanks!

    Reply
    • Jordan,

      I would suggest building a group with the conditions you want and then use GetGroupContext() to examine what the conditions look like. Additionally, we have a tool that you can use to explore groups that can look at what the group conditions look like: http://customerfx.com/article/saleslogix-group-explorer/

      Ryan

    • Ryan,

      My case is:

      (1) Add a button on the AccountDetails quick form.

      (2) When the button is clicked, a group will be created on the fly based on some values of current account and then listed on the account page.

      select * from ACCOUNT a
      inner join ACCOUNT b on a.ACCOUNTID = b.ACCOUNTID
      where b.PARENTID = current AccountID
      or a.ACCOUNTID = another AccountID related to the current AccountID

      Is it possible to use the same approach in your example? The problem looks like it’s hard to describe the self joined account tables in those parts, right?

      Thanks

      Jordan

      Thanks

      Jordan

    • The key is to build the group manually in the client first, and then see how it stored the conditions so you can duplicate that in code.

      In your example, why the join at all? Why not just:
      select * from account where parentid in (currentAccountId, anotherRelatedAccountId

      OR

      select * from account where parentid = currentAccountId or parentId = anotherRelatedAccountId

    • The self-join query with “on a.ACCOUNTID = b.ACCOUNTID” came from the original requirement I received. I guess they want to reference 2 kinds of accounts.

      I tried to use Query Builder to create a group manually based on the self join query. First, I added a global join (Parent: Account.AccountID -> Child: Account.AccountID), but I couldn’t see the new join in the table Panel in Query Builder. Then I created a similar local join, still couldn’t see it. It seemed that I couldn’t set the self join tables in Query Builder. Any thoughts?

      Note: Our SLX is web 8.2

  2. Ryan,

    Is the solution doable?
    (1) Create a SLX view for the second account table with another name.
    (2) Create a global join for the view and account table.
    (3) Set the conditions with the table and view.

    BTW, need to create an entity for the view? Can create an entity on a view?

    Thanks

    Jordan

    Reply

Submit a Comment

Your email address will not be published. Required fields are marked *

Subscribe To Our Newsletter

Join our mailing list to receive the latest Infor CRM (Saleslogix) news and product updates!

You have Successfully Subscribed!