Category Archives: SQL

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