Getting the SQL for a Group in Infor CRM Web

Groups can be a useful way to allow the user to build conditions to limit the records to perform some action on. Recently, we designed a job that would work on a set of records and we allow the users to define the list of records by adding conditions to a group. The job would get the SQL from the group and then perform some work on them. Of course, all of this depends on the job being able to get the records from the defined group to perform the work. Luckily, getting the SQL for a group turned out to be a pretty task.

Getting the SQL for a Group

To get the group’s SQL, you’ll need the groups’ plugin ID value. Then, you’ll get the values from a GroupInfo object and piece them together as needed.

The following code will do just this:

// first, get the groupInfo for the group 
var groupInfo = new Sage.SalesLogix.Client.GroupBuilder.GroupInfo 
{
    UseAliases = true,
    GroupID = "pMKJ2A100002" // the group's plugin ID value
};

// construct the SQL statement 
var commandText = "SELECT * FROM " + groupInfo.FromSQL;
if (!string.IsNullOrEmpty(groupInfo.WhereSQL)) commandText += " WHERE " + groupInfo.WhereSQL;

// now run the SQL to get the records in the group 
var dataService = Sage.Platform.Application.ApplicationContext.Current.Services.Get<Sage.Platform.Data.IDataService>();
using (var conn = new System.Data.OleDb.OleDbConnection(dataService.GetConnectionString()))
{
    using (var command = new System.Data.OleDb.OleDbCommand(commandText, conn))
    {
        // we need to set any parameter values from the group in our SQL command
        groupInfo.AddCommandParams(groupInfo.LoadGroupXmlDocument(), command);

        conn.Open();
        using (var reader = command.ExecuteReader())
        {
            while (reader.Read())
            {
                // now do whatever we need with the records in the group
                idList.Add(reader[0].ToString());
            }
        }
    }
}

That’s it. Note, in my case, I really don’t care about the “SELECT” fields in the group layout or how the group is sorted. I just want to know what records are in the group, so I am only using the tables from the group in the SQL’s FROM clause and the conditions for the group in the SQL’s WHERE clause. Now, my job can simply run the SQL statement to get the records in the group and do whatever work it needs to do on them.

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.

3 Comments

  1. Hi, Nice post, but why did you use the sql to get the Ids.
    You could also use the GetGroupIDs Method.
    You then can use the GetById Method to get the full NHibernate object.
    I know that sql is sometimes faster, but if you have rules in your system, then they are not fired if you use hql or sql statements.

    Reply
    • Hi Alexander,

      For our case, we created a job that did some processing on records. We created a UI for the users to choose groups that would determine the records the job work perform work on. We also needed to know which records already had been processed by a previous run, so we needed to append SQL conditions to the query. Getting the group ID values and then check each one would have been far too inefficient. Using the method above, we were able to easily append additional conditions to the SQL where clause.

      However, just to point out, getting the group ID values also does use a native SQL query. It does not use nhibernate to retrieve the ID values either which can be seen in Sage.SalesLogix.Client.GroupBuilder.GroupInfo.GetGroupIDs using reflector.

      Either way, when we are talking about reading records, such as in the case of reading the records that are in a group, it does not matter whether it goes through the entity model/nhibernate or direct SQL since the entity events only apply to actions performed on those records (inserts/updates/deletes). The code above it only used for reading the records, for performing any of these actions using the entity model is the way to go to ensure entity rules are enforced.

      Ryan

    • OK, you are right. Just wanted to point out that if you onyl want to get the IDs from a group and not want to append conditions it is less code to use GetGroupIds.
      But yes, in your case appending the where clause is better.

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!