Transformation – Creating a Reference Table

I’m going to ignore events for a post due to popular demand, or at least one polite request and talk briefly about transformations.  This isn’t intended to be a detailed discussion of all the wonderful things transforms can do but a quick demo.  A few weeks ago I posted a simple demonstration that automated creating a reference table.  The code I used for that post originally started as a transform, so I’m going to demonstrate how I create lookup tables when generating a PDM from a CDM.

First, the basics.  In order to use transforms, you need 2 things.  An actual transformation, and a transformation profile.  Transformations fall into two categories.  Pre-Generation and Post-Generation.  The documentation has a lot of information on both, but the important thing to remember is that pre-generation transformations are defined on the source model and modifies the source model before the transformation process starts.  Post-generation transformations are defined on the target model and modify the target model after it’s created.

So, for our example, we’ll create a pre-generation transform that will create a generic lookup table.  Our code will be a little different from the event code earlier because we’re working with a CDM instead of a PDM (table becomes entity, etc.).  We’re going to key our transform off the column domain.  So instead of cluttering up your conceptual data model with a lot of lookup tables, we’ll simply define them as being in the “Lookup” domain.

First step, add a transformation to the entity attributes  metaclass and add some code.  I won’t elaborate on the code since we’ve gone through this before.  I’ll call my transform GenerateReferenceTable.

Now the code.  Note that I can define the CDM datatype as “Variable Character” and let PowerDesigner take care of transforming that into the appropriate datatype for the target RDBMS.

Sub %Transformation%(obj, trfm)
   ' Implement your transformation on  here

   dim domainObj
   dim domainName
   dim newAttr
   dim newRefTable
   dim baseTable

   dim newRef

   set domainObj = obj.Domain

   if domainObj is nothing then
      exit sub
   end if

   if domainObj.Name = "Lookup" then

   domainName = obj.name
   output domainName

   ' Step 1 - Create a 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 = getEntity(domainName)

   if newRefTable is nothing then

          set newRefTable = activeModel.Entities.CreateNew()

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

         ' Step 2 - Perhaps some columns would be nice?

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

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

         set newAttr = newRefTable.Attributes.CreateNew()
         with newAttr
            .Name = domainName & " Name"
            .SetNameAndCode .Name, "", true
            .DataType = "Variable Characters(50)"
            .PrimaryIdentifier = false
            .Mandatory = false
         End With

         set newAttr = newRefTable.Attributes.CreateNew()
         with newAttr
            .Name = domainName & " Description"
            .SetNameAndCode .Name, "", true
            .DataType = "Variable Characters(100)"
            .PrimaryIdentifier = 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.Relationships.CreateNew()

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

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

      obj.Delete

      ' Step 5 - Have a beer
   end if
End Sub

Now we have a transform, we need a profile to include it in.  There are only a few options to choose from.  When the model type is Physical Data Model, you can select a Family and Sub-Family which correspond to the target RDBMS and sub-family in the target model.   This tells PowerDesigner which transformations to allow/deny (show/hide) when you’re actually generating a data model.  I’m using Oracle here (mainly to make it easy to find the right box in the picture below), although you can leave them blank and have them apply universally.  I’ve named my profile toPDM.

Once you’ve got a transformation profile, you need to tell it which transformations are in the profile, we’ve only created one, so this should be pretty easy.  Click on the Add Transformations box and check the box next to GenerateReferenceTables.  Click OK.  Your profile should look like the sample below.

To use it you just need to create an entity and assign an attribute in it the Lookup domain.  Each attribute tagged as a lookup should result in a reference table and a foreign key back to the original table.  If multiple attributes have the same name, they should point to the same lookup table.  This script will NOT add the additional tables to the diagram, so you’ll need to add them manually and choose complete links to finish it up.

The only thing left is to generate the PDM.  You won’t need see the transformations by default but you’re just a few clicks away.  On the PDM Generation Options dialog, you’ll need to go to the detail tab and click on the “Enable Transformations” box.

Once that’s done, you’ll have additional tabs for pre-generation and post-generation transformations.  Go to the pre-generation tab and check the box next to our toPDM profile.  Notice that if you have multiple transformations in a single profile, they can be toggled on and off independently.

Now, set the remainder of your generation options as you would normally (that’s a topic for another day) and choose OK.  Next post we’ll be back on events – assuming no more polite requests arrive.

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?

Events – Validate Basics

Part TWO!

I’ve decided to split part two into two parts (maybe part 3 will be split in thirds).  First, a general discussion of validation events, pros, cons and things you should know.  I’ll follow up in another week (or two, or three – my record here isn’t great) with some additional samples of checks and automation done via validation events.

First, the basics.  A validation event fires whenever you modify an object and then hit ‘apply’, ‘ok’, or navigate off the object you’re modifying.  Pretty straightforward.  So, when would we use a validation event?  I see two potential uses for them (please let me know if you’ve got any others).

1. As a real-time substitute for a custom check.  Why wait for someone to request a model check if you can validate it as soon as the object has changed and let them know on the spot.
2. As a way to automate a common task by performing a repetitive action when an object is modified.

This is very appealing but does have a few potential problems.  Why wait for someone to hit the check model (or rely on them actually doing it) if you can ensure they’ll hit the same validation as soon as they make the mistake?  Why let the modeler potentially make the same error 200 times on a model before they bother to run a check and discover they’ve made the same error over and over again?  If you let them know immediately, they’ll learn to do it right immediately.

Well, it turns out there is a downside.  First, validation events allow you to raise an error, and potentially correct it (similar to auto-correct on an event) but makes it much harder to produce a nice concise list of errors.  In addition, if your event forces something to be fixed, the modeler may get annoyed if you’re enforcing rigor they might not be ready for yet.  For example, if they’re trying to lay down a quick model during a working session, being forced to enter a comment on every column could get in the way.  You could have the event write a warning to the output window without forcing the user to correct the issue but that’s easy to overlook and you’ll still need a custom check.  Second, you only get one validation event on each object class.  That can lead to a LOT of code in one big chunk if you’re trying to do multiple checks.  You can split it out into subroutines but there could still be an awful lot going on.  The last problem, related to the second is that there can be a fair amount of overhead involved.  You could be firing a lot more events than you think you are.

For demonstration purposes, I’m going to modify the XEM file I started for initialize events and walk through a little example.  We’ll create two events and see what happens.

First, let’s open our XEM file and edit the global script.  I’m going to add these lines:

dim eventCounter
eventCounter = 0

We’ll use event counter to track how many times we fire a validation event when we test.  Now we’ll create a couple of events.  First, we’ll create a column level validation event.  Here’s the code:

Function %Validate%(obj, ByRef message)

   eventCounter = eventCounter + 1

   output eventCounter & ":  Column Level Event Handler on " & obj.Name

   %Validate% = True
End Function

Next we’ll create a table level validation event

Function %Validate%(obj, ByRef message)

   eventCounter = eventCounter + 1

   output eventCounter & ":  Table Level Event Handler on " & obj.Name

   %Validate% = True

End Function

Before we go ahead and test, there’s one more topic we have to cover.  In the general options for the tool, there’s an option called “Auto Commit”.  Having autocommit on basically pushes the apply button after every change.  I typically have autocommit on, because I’m lazy and don’t make mistakes (at work… people have pointed out plenty on this blog…  you should take my word on this instead of asking my co-workers).

Here’s where the options is.

Now, let’s create a table and see what happens in the output window.  I’ve extended the XEM file I used for the initialization event demo.  This will create a set of audit columns on ever table automatically.  Notice that when we create two columns via script, the event doesn’t fire.

I’ll create a table with the audit columns and add two more on.  I’ll do it once with autocommit enabled and once with it off, resetting the counter in between.

Here’s the results.

Autocommit On:

1:  Table Level Event Handler on Table_1
2:  Column Level Event Handler on Column_3
3:  Table Level Event Handler on Table_1
4:  Table Level Event Handler on Table_1
5:  Table Level Event Handler on Table_1
6:  Table Level Event Handler on Table_1
7:  Table Level Event Handler on Table_1
8:  Table Level Event Handler on Table_1
9:  Column Level Event Handler on Column_4
10:  Table Level Event Handler on Table_1
11:  Table Level Event Handler on Table_1
12:  Table Level Event Handler on Table_1
13:  Table Level Event Handler on Table_1
14:  Table Level Event Handler on Table_1
15:  Table Level Event Handler on Table_1
16:  Table Level Event Handler on Table_1
17:  Table Level Event Handler on Table_1
18:  Table Level Event Handler on Table_1
19:  Table Level Event Handler on Table_1
20:  Table Level Event Handler on Table_1
21:  Table Level Event Handler on Table_1
22:  Table Level Event Handler on Table_1
23:  Table Level Event Handler on Table_1
24:  Table Level Event Handler on Table_1
25:  Table Level Event Handler on Table_1
26:  Table Level Event Handler on Table_1
27:  Table Level Event Handler on Table_1

Autocommit Off:

1:  Table Level Event Handler on Table_2
2:  Column Level Event Handler on Column_3
3:  Column Level Event Handler on Column_4

So, with autocommit on, we get 27 events with autocommit on.  With autocommit off, we get 3.  The model I’m working with has two tables table with 4 columns each.  The validation events are only writing messages to the output window.  If your validation events are complex and you have a lot of functions, you can start incurring a lot of overhead.  If you add validation events to a lot of metaclasses, you could impact performance.  The takeaway –

1. don’t overuse validation events
2. autocommit OFF – if you’re going to use validation events, make sure your modelers know that autocommit may be a problem.
3. write code that will not cause problems if it executes multiple times if you’re going to insist on leaving autocommit enabled (see rule #2).

I’d stick with validation events for items that are difficult to correct after that fact and you’re concerned that people will make repeatedly if they aren’t made aware of the issue immediately.  If your point is to let people know there’s a problem early and “teach” them what they’ve done wrong, you might also consider having the event notify the user of the error along with corrective actions without actually “fixing” the problem.

That’s enough of the basics.  Check back in a week or so for more on validation events.

Events – Initialize

This will be the first of several posts on events.  As many of my friends and colleagues have pointed out, I don’t have a great track record putting out new posts on a regular basis.  So, right now, I’m committing to at least one more post on this topic.  The Sybase documentation lists almost a dozen different events, and I’m fairly certain their list is either incomplete or there’s another list somewhere I haven’t found.  I’m not giving myself and end date (baby steps), but it’s a start.  One of the reasons I started this blog was to force myself to learn a bit more about PowerDesigner and vbScript so this should be interesting.  Some of these events, I haven’t used yet, so honestly I’m not quite sure what I’ll write about but I’m sure I’ll figure something out.

Overview

All metaclasses support 4 events:  Initialize, Can Create, Validate, and On Target Model Reconnection.  There are a few additional methods that apply to specific objects which I may or may not cover.

You add events, just like you add anything else.  By extending a model through an XEM or XDB file.  I’ll use an XEM for my examples.  I’ll assume you know how to get that far (as usual).  For my demonstration, I’m going to create an initialization even on the table metaclass that adds a pair of columns (create date, update date).  I’ve tried to do as much of this as possible to save myself time.  Originally I automated these types of tasks with custom checks and auto-corrects, but initialization events are a much better method.  Why create code to fix something you’ve already done wrong or forgotten to do when you can create code to do it automatically as you create the object.  Faster, better, harder to forget, and usually less code.

Adding our Event

So, you’ve created or opened an XEM file and added the table metaclass.  If you’re not sure how to do this, see several of my prior posts.

Step 1:  Add an event

Right click on the table metaclass, you’ll see a list of 5 events (table has an extra).  Check the box next to Initialize and click ‘OK’
Now, you can open the event and edit the initialize event.  However, as usual, I’ll make things more complex than they have to be (occasionally this pays off in the long run) and put my code in a custom method.  Why?  Because I have some models that need these columns added to existing tables and doing it this way makes it easy to add the columns to existing tables and automatically to new tables with a single piece of code.

Step 2:  Create our Custom Method

Right click on the table metaclass again and create a new method.  Here’s the code I’ll use.  I’m keeping it simple for demonstration purposes.  I called my method addAuditColumns.

Sub %Method%(obj)
   ' Implement your method on <obj> here

   dim newCol

   'Create Date
   set newCol = obj.Columns.CreateNew()

   with newCol
      .Name = "Create Date"
      .SetNameAndCode .Name, "", true
      .DataType = "DATE"
      .Mandatory = TRUE

   end with

   'Update Date
   set newCol = obj.Columns.CreateNew()

   with newCol
      .Name = "Update Date"
      .SetNameAndCode .Name, "", true
      .DataType = "DATE"
      .Mandatory = TRUE
   end with

End Sub

I’ll add a menu item to the table metaclass as well so I can call this method with a couple of clicks on the context menu.  I’ve included it in the demo file.

Step 3:  Call the Custom Method

Now that we’ve done all the hard work, in our method, the event becomes easy.

Here’s the code:

Function %Initialize%(obj)
   ' Implement your initialization on <obj> here
   ' and return True in case of success

   obj.ExecuteCustomMethod("Events.addAuditColumns")   %Initialize% = True
End Function

And we’re done.  Now, anytime you create a table, you’ll automatically start with your two audit columns.

Step 4:  Create Some Tables

Best of luck.

SubObject Formatting using VBScript

A few weeks ago, there was a discussion board post on formatting sub-objects.  I’d tried to accomplish this a few times in the past, and always given up, but I was curious if either things had gotten better with 15.3 or I’d gotten better at scripting.  Turns out the answer is no and maybe.  A little trial and error and I’ve come up with a reasonable method of accomplishing my goals.  I don’t believe the solution I’ve got here is great (maybe not even good), but it’s an interesting topic and perhaps someone will refine it into something more useful.  As a learning exercise, it certainly taught me a lot.

So let’s talk about some of the things I’ve found and some of the things I tried before settling on the solution below.

First, there are no methods for formatting subjects directly (so scratch anything easy off the list).

There is a property on the symbol that controls the formatting of sub-objects (good) but unfortunately, it’s basically a big text string that you have to parse and manipulate.  My first attempt involved parsing that string and manipulating the formatting for particular objects.  That became a bit of a code nightmare and the results during early testing were a bit “erratic” if I didn’t insert my changes into the string perfectly.  Here’s a case where my vbscript skills might have let us down.

So, if there’s no easy methods for manipulating sub-object formatting, and modifying the format is difficult, what’s left?  Well, what I settled on was creating extended attributes for the formatting and then creating a routine to build a new sub-object format string from the ground up based on those properties.  My thought was that by ignoring any existing sub-object formatting, I lessen the chance that my string manipulation creates a garbage format command.  I can write a variety of different routines to set the formatting properties on objects in the model for different purposes … so one routine to make all items modified since the last checkin bold, another to highlight columns that don’t have a definition., etc.  What could possibly go wrong?

So, let’s start building…

Step 1:  Create Custom Attributes

So, it turns out he sub-objects that can be displayed include columns, indexes, keys, etc.  Since I’d rather not have to create the same sets of attributes on them all, we’re going to put them on the extensible object metaclass.  This is a great shortcut when you want to do the same operation on a lot of classes.  So, in case you haven’t done this before, let’s walk through it.

I’m assuming you know how to add an extended attribute, if you don’t you can get the official documentation here or follow Joel’s quick example here.  The only thing you’ll need to do differently is select the PdCommon tab and change the filter to “Show Abstract Modeling Metaclasses”.

Once you’ve done that, ExtensibleObject should appear in the metaclass list.  Check the box and hit ‘Okay’.

Allright, now let’s add attributes.  When you add an attribute, you’ll notice that Sybase has conveniently provided predefined types for Font, Font Size, and Font Name.  They all provide the font dialog box as a handy mechanism for selecting the attributes.  Less convenient , even though all of them show you a dialog that allows you to select the font, size, style and color, none of them return all of those values to the extended attribute.  Font will actually give you name, size and style.  Font Name returns only the name, Font Size returns the size (duh) as a number.  None of them return the color, even though it’s an option on all three dialog boxes.  Downright annoying, the syntax for storing the font for the predefined attributes doesn’t match the formatting used to set the subobject format.

So, if you create custom attributes for Font and Color – the smallest number of attributes necessary, and just concatenate them with a comma in the middle (customFont & “,” & customFontColor) you’d get something like this:

Arial Rounded MT Bold,Bold,8,255 0 0

The subobject string for specifying the same value is

Arial Rounded MT Bold,8,B,255,0,0

Notice a few dfferences.

1. The order of the values is different.  Font size and style are switched
2. The format of the style property is different.  the Font property stores “Bold” while the subobject format is “B”
3. The color property is space separated in the color attribute with spaces and with commas in the subobject format.

Probably a case of two programmers doing their tasks without recognizing there was reason to make them match (or even anything to match to).  Annoying but not the end of the world.  However, if anyone from Sybase reads this, a font property that returns the color value and a subobject format string that matches would be greatly appreciated.

More annoying is that if I use just font and color, I’ve gotta break up the font property (did I mention I dislike string parsing?) and reassemble it in a different order (and with some substitutions to turn Bold into B).

So, here’s what I came up with.  2 extended attributes:

• customFont which is a font data type and gives me the font name, style and size (specified incorrectly for my purposes)
• customFontColor which is a color data type and gives me the custom color
• customFontFormat which I’ve defined as a calculated attribute.  The calculation combines the other two attributes and formats them properly for the subobject format string.  A little divide and conquer that makes the rest of my scripts a little shorter.  You can do this as a subroutine as well, I prefer calculated attributes which makes them “visible” outside of a vbscript and makes debugging a breeze.

Step 2:  Code the Calculated Attribute

Function %Get%(obj)
 ' Implement your getter method on <obj> here
 ' and return the value

 dim customFont, fontName, fontStyle, fontSize, fontColor, strPos

 customFont = obj.getExtendedAttributeText("SubObjDemo.CustomFont")

 strPos = instr(customFont,",")

 'get the name
 fontName = left(customFont,strPos-1)

 'get the style and convert to the allowable values
 customFont = mid(customFont,strPos+1)

 strPos = instr(customFont,",")
 fontStyle = left(left(customFont, strPos-1),1)
 if fontStyle = "" then
 fontStyle = "N"
 end if

 customFont = mid(customFont,strPos+1)

 'now the font size
 fontSize = customFont

 if fontSize = "" then
 fontSize = 10
 end if

 fontColor = obj.getExtendedAttributeText("SubObjDemo.CustomFontColor")

 if fontColor = "" then
 fontColor = "255,255,255"
 else
 fontColor = replace(fontColor," ",",")
 end if

 %Get% = fontName & "," & fontStyle & "," & fontSize & "," & fontColor

End Function

Step 3:  Some Assembly Required

So, now we have all the individual pieces, we just need to assemble them.  I’ve gotten as far as columns and keys.  Obviously there are other properties available, but we’ll leave that as an exercise for the reader.  If anyone would care to finish this, I’d be more than happy to publish the finished product.

So, first create a custom method.  Here’s the logic.  For each table symbol in the active diagram find the object it represents.  Then loop through the columns and keys collection and get the customFontFormat property.  Use these to build a new format string for the symbol.

Here’s my code:

Sub %Method%(obj)
   ' Implement your method on <obj> here

   dim sym
   dim symObj ' object the symbol represents
   dim subObj ' the sub-objects in the objects (columns and keys in this example)
   dim formatStr ' the new sub-object format string we'll assemble
   dim workStr ' a working string that we'll build to replace the format string

   ' now, start by looping through the symbols on the active diagram

   for each sym in activeDiagram.symbols

     if sym.ClassName = "Table Symbol" then'
         'get the object (in this case table)
         set symObj = sym.Object         

         'We're going to build the subobjects string using the formatting values stored in the objects.
         'first the table level entities

         'columns
         for each subObj in symObj.Columns
            if subObj.GetExtendedAttribute("SubObjDemo.CustomFontFormat") <> "" then
               if workStr <> "" then
                  workStr = workStr & vbLf
               end if
               workStr = workStr & subObj.ObjectId & " " & subObj.GetExtendedAttribute("SubObjDemo.CustomFontFormat")
            end if
         next

         if workStr <> "" then
            if formatStr <> "" then
               formatStr = formatStr & vbLf
            end if
            formatStr = formatStr & "Column 0" & vbLf & workStr
         end if

         'now keys
         for each subObj in symObj.Keys
            if subObj.GetExtendedAttribute("SubObjDemo.CustomFontFormat") <> "" then
               if workStr <> "" then
                  workStr = workStr & vbLf
               end if
               workStr = workStr & subObj.ObjectId & " " & subObj.GetExtendedAttribute("SubObjDemo.CustomFontFormat")
            end if
         next

         if workStr <> "" then
            if formatStr <> "" then
               formatStr = formatStr & vbLf
            end if

            formatStr = formatStr & "Key 0" & vbLf & workStr
         end if

         sym.subObjects = formatStr

     end if

   Next

   activeDiagram.RedrawAllViews

End Sub

Create a menu item on the menu item to call or new method and this part is done.

Step 4:  A Method to Set Formats

So now we can format subobjects for a table if there’s a custom format specified – and you can set them manually by editing the custom properties we defined.  Obviously that’s now something we want to do.  What’s left?  A method or methods to set the sub-object format properties on objects based upon some condition.  While I think columns changed or added since the last checkin is the obvious candidate for this, I don’t know if you have a repository and that’s a little hard to test.  So I’m going create my script to identify columns which don’t have a comment.  The thing I like about this particular approach is I can re-use the apply formatting method and create many different methods to set the formatting properties on sub-objects.  So here’s my method:

Sub %Method%(obj)
   ' Implement your method on <obj> here

   Dim mySel
   Dim myObj

   Set mySel = ActiveModel.CreateSelection

   mySel.AddObjects ActiveModel, cls_column, false, true

   For each myObj in mySel.Objects
      With myObj

         if myObj.comment = "" then
            myObj.setExtendedAttribute "CustomFont","Arial,Bold,9"
            myObj.setExtendedAttribute "CustomFontColor","255 0 0"
         else
            myObj.setExtendedAttribute "CustomFont","Arial,Normal,9"
            myObj.setExtendedAttribute "CustomFontColor","0 0 0"
         end if

      end with

   Next

   obj.ExecuteCustomMethod "SubObjDemo.ApplyFormat"End Sub