I had a question recently from someone, and it seemed a common enough requirement that I thought the solution might be worth sharing.

Let’s say you’ve got a database that has an Integer column which is really used as a Boolean. Zero for False, One for True. Further, for whatever reason, you can’t change the DB structure. How do you display this column to the end user as a boolean, so that they can use a Checkbox column in a grid, a TSwitch control, or similar to edit it?

Note, this could also apply to binding from something other than a

database, such as a list of objects (see my post TAdapterBindSource and binding to Objects), but the solution applies anyway.

You could of course do this at the dataset field level, using the TField.OnGetText and TField.OnSetText fields to convert the values back and forth, but let’s also look at what options the LiveBinding framework gives us.

I actually covered the key parts of the solution in my post LiveBindings in XE3: Formatting your Fields. In that post I used the CustomFormat and CustomParse expression in my links to add Currency formatting before displaying a field, and subsequently remove the Currency formatting on the way back to the field. We can use the same technique to actually change the datatype back and forth.

If you have a look at the sample code, I’ve got a ClientDataset with one field of type TSmallIntField, which I’ve imaginatively titled IntegerField. That is hooked up through a TBindSourceDB to a TGrid. The TLinkGridToDataSource that is connecting the data to the TGrid has two columns. In the first I want to display the field as a Boolean, using a checkbox column, so I set its ColumnStyle property to CheckColumn. In the second I’ll display the raw Integer value from the field so I leave the Column properties as default.

The bindings are pretty straight forward, but note the two columns are both bound to the one field.

We still need to set up the logic to convert between the Integer and Boolean. If we go back to the same column where we set the ColumnStyle to CheckColumn, we can use expressions in the CustomFormat and CustomParse properties to alter the value on the way from the field to the column (CustomFormat) and on the way back from the column to the field (CustomParse). In the screenshot below you can see the expressions I’ve used.

In both cases, %s represents the value prior to any manipulation. I think the expressions are relatively straightforward, although perhaps the IfThen statement needs explanation. It tests whether the value of %s is True. If it is, it sets the field value to 1, otherwise it sets it to 0.

A few things to note:

• In this particular case, you can remove the CustomFormat expression entirely, as the LiveBinding engine will  cope with this datatype conversion automatically. I’ve included it here to show what would be required in the general case.
• This actually treats any non-zero value as True. You can test this by editing the value in the second column and seeing how the Checkbox in the first column responds.

Big thanks to Jim Tierney for patiently listening to my ill-informed ideas for a solution to this problem before gently reminding me of a simpler, easier solution that was staring me in the face.

Update: Mathias makes a great point in the comments below. This solution is tied to the binding link. If you need to display this in multiple places in your UI, you’ll be repeating these expressions every time. Check out the link to his custom TValueRefConverterFactory.