Sublogix

Sublogix for Infor CRM

Sublogix is an entity model and repository for Infor CRM. It allows you to work with entity objects and repository instead of long and difficult to maintain SQL statements. Sublogix makes building stand alone web or desktop applications that work with Infor CRM data a simple task. Sublogix is a .NET assembly that allows you to work with objects instead of SQL queries. Sublogix is not meant to replace the Infor CRM Repository that lives in SalesLogix Web. It’s purpose is to give you the ability to work with Infor CRM entity objects no matter what environment you’re in and allows you to more quickly create outside or stand alone applications that work with the Infor CRM data.

To Install Sublogix, simply download the DLL via NuGet or this page and add it as a reference in your .NET project. By default, Sublogix contains entity definitions for the out of the box Infor CRM schema. However, you can easily create entities that match your Infor CRM database by using the included T4 templates.

Creating Custom Entities To Match Your Database

To create custom entities to match your database, do the following steps (these are also outlined in a video demonstration in the article A Demo of Sublogix – Simple Repository for SalesLogix)

1. Add Sublogix.dll to your project and build your project (so the dll is copied to your project’s build folder)
2. Open the template file “SublogixSettings.ttinclude” in a text editor
3. Change the values for “Server”, “Database”, “UserName”, “Password” to match your database. Optionally, specify a custom namespace for the entities, if desired. Save & close the file.
4. Add a new folder to your project called “Entities” (this can be named whatever you’d like)
5. Add the template files “SublogixEntityObjects.tt” and “SublogixSettings.ttinclude” to this folder in Visual Studio
6. When you add the template files into the folder in Visual Studio, you will be prompted to run the template (or Visual Studio will do it without asking depending on your settings)
7. At any time you can right-click the “SublogixEntityObjects.tt” file and select “Transform Templates” or “Run custom tool” depending on your version of Visual Studio, to rerun the transformation and recreate the entities from your database
8. Add the new namespace, specified in “SublogixSettings.ttinclude” to your code to use the custom entities

Using The Sublogix Repository

To use Sublogix, the first thing you need to do is create a repository object.

// using parameters for the connection info
var repo = new Sublogix.Repository("MyServer", "MyDatabase", "MyUserName", "MyPassword");

// or using a complete Infor CRM connection string
var repo = new Sublogix.Repository(MyConnectionString);

Retrieving Records Using Sublogix

Sublogix provides several ways to retrieve records from the database.

GetById

GetById allows you to select a single record from the database using the table ID value for the record.

var account = repo.GetById<Account>("AGHEA0002669");

All

All will get all records from a table.

var products = repo.All<Product>();

Find

Find will query the database using one or more conditions.

// using simple expressions
var accounts = repo.Find<Account>(x => x.Type == "Customer");

// using WhereConstraint objects for conditions
var accounts = repo.Find<Account>(new WhereConstraint("Type", Operator.Equals, "Customer"));

Select

Select will allow you to chain conditions and sort results.

var accounts = repo.Select<Account>()
                    .Where("Status", Operator.Equals, "Active")
                    .And("Type", Operator.In, new string[]{ "Customer", "Partner" })
                    .OrderBy("Account", OrderByDirection.Ascending)
                    .Execute();

Query

Query is new/experimental and allows you to specify a SQL query to populate results. The SQL query can be a SELECT statement, stored procedure, or view.

// using a SELECT statement 
var accounts = repo.Query<Account>("select * from account acc inner join address adr on acc.addressid = adr.addressid where adr.state = 'MN'");

// using a view
var accounts = repo.Query<Account>("select * from vMyAccountView");

Adding Records Using Sublogix

Create

var account = repo.Create<Account>();
account.AccountName = "Test";
account.Type = "Customer";
account.Status = "Active";
//...
account.Save();

Updating Records Using Sublogix

To update an existing record, simply locate the record using Sublogix and set the entity properties.

var account = repo.GetById<Account>("AA2EK0013096");
account.Type = "Customer";
account.Save();

Deleting Records Using Sublogix

To delete an existing record, simply locate the record using Sublogix and call it’s delete method.

var account = repo.GetById<Account>("AA2EK0013096");
account.Delete();

Miscellaneous Sublogix Helper Methods

Getting the Current User ID

var userId = Sublogix.Helpers.SalesLogixHelper.CurrentUserID;

Getting the Current User Name

var userName = Sublogix.Helpers.SalesLogixHelper.CurrentUserName;

Getting A New Table ID

var id = Sublogix.Helpers.SalesLogixHelper.NewID<Account>();

Getting the Current Database Name

var db = Sublogix.Helpers.SalesLogixHelper.CurrentDatabase;

Creating a Pretty Key (like Ticket Numbers)

var prettyPrefix = Sublogix.Helpers.SalesLogixHelper.GetPrettyPrefix(myId);
var prettySuffix = Sublogix.Helpers.SalesLogixHelper.GetPrettySuffix(myId);
var prettyKey = prettyPrefix + "-" + prettySuffix;

Getting the Current User’s Default SecCode

var secCode = Sublogix.Helpers.SalesLogixHelper.GetSecCode(userId);