ERP Link – Why do you hate the smallint?

I discovered another little gem about SalesLogix ERP Link today.  It will not work with SmallInt data types, even though they are a valid SalesLogix data type and can be selected when adding a field to the database in the SalesLogix Workgroup Administrator.  In the Workgroup Administrator they are called (Short Integer).

When you use the ERP Link Entity mapping wizard it does a couple of things upon opening up one of the field mapping templates.

  • Determines the base table of the template
  • Uses the Join data information to then go find all of the associated tables tied to the base table
  • Attempts to use some built in logic to then parse out the tables involved using the sectabledefs (or a straight call to the table) to build out the fields.

The end result is supposed to present you with a dialog where you can see all of the fields you would need to be able to map in your selected ERP Link mapping, like this (show on a Sales Order field mapping screen):

Entity Mapping field screen

The problem comes from the ERPLink field parsing code which is contained in the .NET assembly Sage.SalesLogix.ERPLink.Common.  Within the assembly there is a FieldSchema class with a InitializeSchema method.  If you look at that method it iterates through the SalesLogix Provider’s field type definitions and uses the result to set some attributes, used in the display of the fields shown above.  Here is a little snippet sample:

protected static void InitalizeSchema(FieldSchema schema, DataRow schemaRow)
{
    schema.IsKey = (bool) schemaRow[“IsKey”];
    switch (((int) schemaRow[“ProviderType”]))
    {
        case 200:
            schema.DataType = “String”;
            schema.ImageIndex = 1;
            schema.Length = Convert.ToInt32(schemaRow[“ColumnSize”]);
            if (Convert.ToBoolean(schemaRow[“IsReadOnly”]))
            {
                schema.DataType = “Calculated”;
                schema.Length = Convert.ToInt32(schemaRow[“ColumnSize”]);
                schema.IsReadonly = true;
                schema.ImageIndex = 6;
            }
            return;

        case 0xc9:
        case 0xcd:
            schema.DataType = “Blob”;
            schema.Length = Convert.ToInt32(schemaRow[“ColumnSize”]);
            schema.ImageIndex = 7;
            return;

….
}
    schema.DataType = “String”;
    schema.Length = Convert.ToInt32(schemaRow[“ColumnSize”]);
    schema.ImageIndex = 1;
}

Only problem is that in the case block, it fails to consider the various types that are in use in SalesLogix.  One of these types is the SmallInt.  As you can see by the snippet above, if it does not fall in one of the case statements, it assumes it is a data type of string and tries to determine the length with the column size property.  Another problem is that the case for SamllInt tries to unbox the numeric precision property to an int.  This is apparently the line that is failing as when the unboxing occurs from the reference type to the value type it is failing.  I imagine if the code were:

case 2:

            schema.DataType = “Integer”;
            schema.Length = Convert.ToInt32(schemaRow[“NumericPrecision”]);
            schema.ImageIndex = 2;
            return;

It would probably work?

So when you try to add or edit a field mapping in the ERP Link Entity Mapping Wizard, and you click on the SalesLogix field ellipse:

Entity Mapping add field map

The result will be something like this:

Entity Mapping error map

 

Resolution

Hopefully Sage will correct this problem, for now you are not allowed to use SmallInt data types in any table that has a join to any of the primary mapping tables (Accounts, Contacts, Address, Sales Orders, Contracts).  You can convert your SmallInt fields to Int fields.  Luckily you will be converting them to a larger data type so you wont need to worry about loss of data.

A couple of notes about updating the data types.  The SalesLogix Workgroup Admin will not allow you to change the data types through the SalesLogix database manager.  You will need to modify these field type definitions directly through SQL  However, you must also update the SalesLogix schema definitions which contain the field type attributes.  If you simply update the fields through SQL you will continue to receive the error in ERP Link until the SalesLogix schema is updated.

You can run the following statement to find the SmallInt fields in your database.  Notice that there are some system tables that contain this data type.  These should be OK.  It is just fields in tables that contain user entered data that will be an issue:

select b.name as tablename, a.name as fieldname 
from syscolumns a left join sysobjects b on a.id=b.id
where a.xtype=52 and a.type = 38
order by b.name, a.name

To update your SalesLogix database schema, the easiest thing to do is:

  • Close the SalesLogix Administrator if you have it open.
  • Run the following SQL statement: delete from plugin where type = 26
  • Log into the SalesLogix Administrator as Admin.
  • Go to “Manage…Database…”.  This will cause the schema to be rebuilt with the correct definitions

 

ABOUT THE AUTHOR

Kris Halsrud

Kris Halsrud is a Senior Analyst / Developer for Customer FX Corporation.

1 Comment

  1. I do not believe it has but I haven’t looked in a while.

    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) and Creatio (bpm'online) news and product updates!

You have Successfully Subscribed!