InforCRM – Chars, Bits and Booleans (oh my!)

For anyone who works with InforCRM SNC, the loose definition of the “boolean” is familiar. Basically, InforCRM treats “Booleans” as single character strings, rather than a bit datatype in SQL, allowing “T” and “F” values, as well as null.

The SNC environment cannot handle bit values, however the Web environment can. This causes a problem if you use a SNC bundle (.sxb) to install bit fields to another system. The database manager will create those values as char(1) rather than their original datatypes, causing potential errors and display issues for that data.

To demonstrate, I created a new table in the SNC Architect named HappyTestTable, and included a standard SLX Boolean field:

We can now see the field as a string with a length of 1.

If we check the table in SQL as well, the field is added as a varchar(1):

Once the table and field is created, it needs to be added to the Entity Model in order to use those objects in the web environment. After adding the entity, this is what we get in the entity model:

As you can see, it added the new field with the datatype of “Text”. This will not work, if the field is meant to be used with a checkbox or radio button, so we need to change the datatype to either TrueFalse or YesNo by clicking on the datatype link:

Changing the Type to True/False results in the entity looking like this:

With this change, all is right with the world. This entity property can be used properly for checkboxes as a “Boolean” value without error.

But… Here’s where the problem comes in.

What if you wanted to create the entity and table all within the web environment? Well, you can! Right click on the Application Entites package, and select new entity. This will open the new Entity wizard:

You have a couple options here: Either you can create an entity from an existing table, or you can create a new table. Let’s create a new table called HappyTestTable2 and put in a boolean field.

From the wizard, after defining the table name, you will get to the point where you can add fields. The Options for datatypes do include the True/False and Yes/No options, but what if you actually select boolean?

With the new table created, we can see this was created with a bit datatype in SQL

With the field created like this, it CAN be used in checkboxes in the web environment, however it will store 1 and 0 values rather than T and F.

This is all well and good, until you get to the point where you want to install this functionality into another database. InforCRM cannot deal with bit values when bundling changes. See what happens when I pull both fields into a bundle manifest:

Using state of the art screen capture technology, I’ve displayed the properties of both fields side-by-side. As you can see, the datatype in both cases is string. If you were to install this bundle on another system, it would create both fields as char(1), however; if the Entity Property for TestBoolean2 is included in the bundle as well, the entity property will have a “boolean” data type, causing a type mismatch error within the web client. This would be the same with SNC bundles (sxb) as well.

You could change the entity property type to True/False and prevent errors, but if the field already contains 0-1 data, then checkboxes mapped to these fields will not display as checked unless the field contains “T” values.

The short of it kiddies, is this: Stay away from the boolean datatype in the web, if you plan on moving those changes to another system via a bundle. Use the True/False or Yes/No types instead… Just make sure to check your entity property types as well.

I would consider this a bit of a bug, so I plan on reporting this as an issue to Infor.