Create an ORM Database for your K2 SmartForms Objects

I think it’s fair to say that most of us in this industry at least started out taking over someone else’s project. The original developer left long before you got there, leaving you to figure out what’s what.

Some of us are still in that position, or find ourselves back in that position at some point. So if you can take the time to inventory all of your SmartForms objects at the start, you can let your shiny new object relational management (ORM) database do a lot of your grunt work for you later on.

I recently found myself in a situation where I was going to have to comb through all of the solution’s SmartForms and SmartForm Views looking for rules that broke during the software upgrade. So I figured I’d take advantage of the situation by creating an ORM database to record the relationships each of these objects share.

I used Microsoft SQL Server for this just out of convenience, but the RDBMS really doesn’t matter. I started by creating three database tables — one for object types, one for objects, and one to map the relationships between and among the objects. Here are the highlights, based on trial and error:

  • The ObjectType table simply contains the types “Form”, “View”, and “SmO” (standing for SmartObject).

  • Include in your Object table design the object’s name, its system name, and its category path (e.g., “(project name)\Forms”). The combination of the three will help you differentiate between current and old versions, and will help you distinguish among similarly-named objects in different categories. (This is particularly helpful if subcategories were created for different versions, like “Beta” or “Testing”.)

    — And don’t forget to include a column for the ObjectTypeID.

  • My Relationship table has a series of six columnns, all of type INT. They contain the IDs of the objects from the Object table. I named these columns FormID, ViewID, SmOID, SubformID, SubviewID, and ContentObjectID. The first three seem pretty obvious — the FormID column will hold the ObjectID of an object with the Form ObjectType, and so forth. The next three may not be so obvious: The SubformID column will hold an ObjectID of an object with the Form ObjectType, describing the relationship between some form or view and a form being used as a subform. The same goes with the SubviewID column. This last one — ContentObjectID — describes a form or view being exposed through a Content Control.

  • My only remaining discomfort with my schema is that I don’t have the ability to accurately describe this relationship: Given a form (“F”) with multiple views (“V1” and “V2”), and these views have SmartObject methods; a form rule is implemented on an event for V1 (to override some of the view’s actions). The form action directs V2 to execute a method. Given the relationship structure I described above, I have to make a choice on which view to describe. In this scenario, I chose V1.

With everything I just provided, you ought to be able to blow through creating that schema, as long as you’re comfortable with the flaw I disclosed. The hard part is actually going through every SmartForm and View and mapping them all.

I started at the top of my project category and worked my way down: first recording the metadata of the first form in my Object table, then recording the metadata of the views attached to that form, then recording the relationships. If the form’s ObjectID is 1 and that form has three views with ObjectID’s 2, 3, and 4, then your relationship table ought to look like this:

1 2    
1 3    
1 4    

Next, I looked at the form rules, looking for actions that directly executed SmartObject methods or for actions on view rules that executed either View methods or the methods of SmartObjects not related to the view. Finding them meant creating a record in the Object table and then a record in the Relationship table.

if FormID 1 executes a method on SmOID 5 in the form, the relationship would look like this:

1   5  

So if FormID 1 tells ViewID 2 to execute a method on SmOID 6, the relationship would look like this:

1 2 6  

Next, I look at those views (ViewID 2, 3 and 4), and find their SmartObject associations, starting with the ones I can see in the Category tree and then going into the view rules looking for others.

So let’s say SmoID 6 is attached to ViewID 2, meaning the view is a list view based on that SmartObject. The relationship would look like this:

  2 6  

And now we know that ViewID 2 has its own relationship with SmOID 6, and that FormID 1 directs ViewID 2 to execute a method on SmOID 6.

I think you’re stacking the wood that I’m sawing here.

Putting together an ORM isn’t exactly fun. It took me two weeks to get through everything in my solution — but what is fun is using it to gain valuable insight into an application. With ORM, answers to questions like “How many places is this SmartObject called?” or “How many forms call this view?” are just a query away.