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?