Category Archives: SQL

How to Convert Between Decimal and Binary in T-SQL

For the past six months, I’ve been teaching night classes in database programming and my students are just about ready to take Microsoft’s 98-364 exam. Now, we’re transitioning over to C# programming for the next phase of the program but I have to keep their SQL skills fresh for the exam.  Tonight, it was time to teach them to understand the binary number system.  I dropped some hints on it a couple of months back to brace them but now it was time to get it done with once and for all.

Then, as I like to do with this class, I came up with a lesson plan that would incorporate the best of both subjects …

Continue reading

Calculated Fields in CiviCRM

One of CiviCRM’s strengths is the ability to add custom fields to hold specific information about your contacts. One thing it doesn’t offer (yet) is a calculated field type that will present the results of calculations of other fields.  While calculated fields are generally discouraged in relational database design, they are sometimes necessary within a user interface. One suggested method is to add custom code hooks within CiviCRM’s PHP code but as a database guy, I decided on a back-end solution.

Continue reading

Importing Case Data into CiviCase

Migrating to CiviCase

On a recent project, I setup CiviCRM and CiviCase to enable a local organization to better manage its client database. The old database had been developed in Borland Paradox and was quickly becoming unusable. As a free and open source solution, CiviCRM turned out to be just the solution needed to accommodate this non-profit.

While CiviCRM itself does allow for the import of data through its API interface, I found that the contacts importer rejected a good portion of this data. The data had been entered by many volunteers into a database which did not impose many constraints on what data was required or how it could be entered. As with any case history, this data was essential to working with clients. With the volume of data involved, manual re-entry was not an option.

Fortunately, while CiviCRM’s data model is sophisticated, it’s relatively easy to decipher and I was eventually able to import the bulk of the data into CiviCase using a series of MySQL queries and MySQL Workbench.

Continue reading