Event – Validation Automation

The long awaited (or at least long delayed) part 2.5!

So, today we’re going to talk about automation and a bit of code I’ve been using for a bit to make my life easier.  If you’re like me (lazy) and like to get as much done as possible with the fewest number of mouse clicks or key strokes, you’ve probably thought, isn’t there some way I can do more with less clicks?  Well, validation events might just be the trick you’re looking for.  Sound good?  Okay, here’s my example.

There are a few things I find myself doing a lot.  First on the list is creating lookup tables.  I do this constantly.  You probably do too, and since most tables take on a pretty consistent structure, why not automate it.

So, here’s what I’m going to do.  I’m going to create a validation event on the column metaclass that automatically creates a lookup table for a given column when I end the column name with “+L”.  This way, whenever I want a new reference table on a column, I simply add a switch to the column name and the tool takes care of the rest.  I’m going to reuse some simplified code I’ve already deployed elsewhere to keep this manageable, but here goes.

Step 1:  I need some code to create the reference table.  As usual, I’ll create this as a method, which allows me to call it from inside the event handler, or from a menu on the column object (if you didn’t see this coming, you haven’t read any of my earlier posts).

Step 2:  Create the event handler.  This will simply look for the +L suffix and call the appropriate method, and then remove the switch from the end (I can’t think of a reason I’d actually want a column ending in +L).

So, here’s the code.  The method will actually consist of two parts, a global routine which I’ll use to make sure I reuse an existing lookup table if I’ve already created one.  Remember from my earlier discussions that event handlers should be written to ensure they work correctly if they execute multiple times. First, my getTable global method.  I’m using a selection here to check all the existing tables.  Again, if you aren’t using selections, read my earlier posts.

function getTable(tableName)

   Dim mySel
   Dim myObj
   Set mySel = ActiveModel.CreateSelection

   mySel.AddObjects ActiveModel, cls_table, false, true

   For each myObj in mySel.Objects

      if myObj.Name = tableName then
         set getTable = myObj
         exit function
      else
         set getTable = nothing
      end if  

   Next

   set getTable = nothing

end function

Pretty straightforward, now the method.  Again, this is a simplified version of some existing code.  I’m not going to spend a lot of time walking through it (lazy, remember?).  First, we’re going to check for an existing table by searching on name using the method above.  If we don’t find it, we’ll create it and add a few columns.  Then we’ll create a relationship from our reference table back to the original table.  Finally, we’ll remove the original column.

Sub %Method%(obj)

   dim domainObj
   dim domainName
   dim newAttr
   dim newRefTable
   dim baseTable

   dim newRef

   domainName = obj.name
   output domainName

   ' Step 1 - Create the standard Reference Table

   ' Step 1.5 - Check to see if the table already exists (hey it happens)
   ' create it if you need it, otherwise - reuse it

   set newRefTable = getTable(domainName)

   if newRefTable is nothing then

      set newRefTable = activeModel.Tables.CreateNew()

      with newRefTable
         .Name = domainName
         .SetNameAndCode .Name, "", true
              .Comment        = obj.Comment
      End With  

      ' Step 2 - Perhaps some columns would be nice?

      set newAttr = newRefTable.Columns.CreateNew()
      with newAttr
         .Name = domainName & " Key"
         .SetNameAndCode .Name, "", true
         .DataType = "Integer"
         .Primary = true
      End With  

      set newAttr = newRefTable.Columns.CreateNew()
      with newAttr
         .Name = domainName & " Code"
         .SetNameAndCode .Name, "", true
         .DataType = "Variable Characters(4)"
         .Primary = false
         .Mandatory = false
      End With  

      set newAttr = newRefTable.Columns.CreateNew()
      with newAttr
         .Name = domainName & " Description"
         .SetNameAndCode .Name, "", true
         .DataType = "Variable Characters(100)"
         .Primary = false
         .Mandatory = false
      End With  

   end if

   ' Step 3 - Create a relationship between the new lookup table and the table 
   ' containing the original column

   set baseTable = obj.Parent

   set newRef = activeModel.References.CreateNew()

   with newRef
      .object2 = newRefTable
      .object1 = baseTable
      'Set the optionality using the property from the original column
      '.Mandatory = true
   end with

   ' Step 4 - Remove the original column from the base table

   obj.Delete

   ' Step 5 - Have a beer (suggestions welcome)

End Sub

Finally, we need our event.  Since we’ve done all the hard work already, the event is simple.  We’ll check for the +L suffix (we’re trailing spaces and ignoring case), then calling our method.

Function %Validate%(obj, ByRef message)
   ' Implement your object validation rule on <parent> here
   ' and return True in case of success, False otherwise with a message

   obj.Name = trim(obj.Name)

   if ucase(right(obj.Name,2)) = "+L" then

      output "Lookup table"
      with obj
      .Name = trim(left(.Name,len(.Name)-2))
      .SetNameAndCode .Name, "", false

      .ExecuteCustomMethod("EventAutomation.ConvertToLookup")

      end with 

   end if

   %Validate% = True

End Function

So there you go.  A few lines of vbscript and I don’t have to create a generic reference table anymore (lazy).

Incidentally, I use almost this exact same method (not event) in a transform to automatically create reference tables when I move from a conceptual to a physical model.  This let’s me keep my conceptual models a little less “cluttered” and still get my reference tables in the PDM.  If you’re not working in a conceptual model, this is obviously not an option, but it’s something worth considering.

That’s all for tonight.   Give it a try and let me know what you think.  Good?   Bad?  Any better ideas for automation?