ER/Studio Data Architect provides an important navigation feature called Submodels.
Submodels and nested submodels are designed to break down large, complicated views of a data model in order to focus on a specific area. An important aspect of Submodels to understand is that any changes made in the submodel, other than layout, color, display settings, notation or similar items which can be unique to the submodel, will occur automatically in the Main Model view. The Main Model view always includes all the objects (entities, relationships, …).
When we create a submodel, we can automatically add the existing relationships between the different entities|tables:
However, if we’ve introduced new relationships in the Main Model or other submodels, they won’t automatically be reflected in our pre-existing submodels. Similarly, when creating a new submodel and integrating entities through a custom macro, it’s essential to note that the macro must explicitly include the relationships; it doesn’t happen automatically.
In this post, I’ll share a macro which can be used to add|remove the relationships to|from the submodels.
First, we’ll need to create the macro within our ER/Studio Data Architect. If we’re unsure how to do this, a tutorial video is provided at the end of this post, demonstrating the process.
Then, we select the model containing the submodels we want to update (e.g.: Logical model, any Physical one).
Finally we run the macro and we use the provided UI:
'#Language "WWB-COM"
'MACRO TITLE: ADD|REMOVE RELATIONSHIPS INTO|FROM SUBMODELS
' This macro will add or remove the relationships between
' the different entities|tables for the current model
'
' Relationships on the Main Model are not added|deleted.
' It only applies to all the other submodels.
' - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
Option Explicit
Const TITLE = "Add|Remove Relationships Into|From Submodels"
' dim dialog variables
Dim submodel_UIchoice%
Dim submodel_txtchoice$
Dim submodel_choice%
Dim action_choice%
' Dim ER/Studio variables.
Dim diag As Diagram
Dim mdl As Model
Dim submdl As SubModel
Dim ent As Entity
Dim rel As Relationship
Dim reld As RelationshipDisplay
Sub Main
' Init the ER/Studio variables.
Set diag = DiagramManager.ActiveDiagram
'Get the current model.
Set mdl = diag.ActiveModel
'fill array for model drop down in the dialog
Dim rscount%, smcount%
rscount = mdl.Relationships.Count
smcount = mdl.SubModels.Count - 1
If rscount = 0 Then
MsgBox("There is no Relationship in this model!", vbExclamation, TITLE)
ElseIf smcount = 0 Then
MsgBox("There is no additional submodel in this model!", vbExclamation, TITLE)
Else
ReDim SMListArray (0 To smcount) As String
Dim i As Integer
i = 0
For Each submdl In mdl.SubModels
If submdl.Name <> "Main Model" Then
SMListArray(i) = submdl.Name
i = i + 1
End If
Next
Debug.Clear
' Prompt the user.
Begin Dialog UserDialog 390,287,TITLE,.ExportHandler ' %GRID:10,7,1,1
GroupBox 20,7,350,154,"Choose Scope",.GroupBox3
Text 30,28,330,14,"",.tModel
OptionGroup .object_sel
OptionButton 60,56,200,14,"Active Submodel",.OptionButton0
OptionButton 60,84,140,14,"All Submodels",.OptionButton1
OptionButton 60,112,220,14,"Choose Submodel:",.OptionButton2
OKButton 20,238,160,35
CancelButton 220,238,150,35
DropListBox 90,133,260,147,SMListArray(),.SMList,2
GroupBox 20,175,350,49,"Choose Action",.gbAction
OptionGroup .ogAction
OptionButton 30,196,160,14,"Add Relationships",.aAdd
OptionButton 200,196,160,14,"Remove relationships",.aRemove
End Dialog
Dim dlg As UserDialog
If Dialog(dlg) = -1 Then
Debug.Print "SMC: " & submodel_choice
Debug.Print "AC: " & action_choice
If action_choice = 0 Then
Select Case (submodel_choice)
Case 0
Set submdl = mdl.ActiveSubModel
AddRelationships(submdl)
Case 1
For Each submdl In mdl.SubModels
AddRelationships(submdl)
Next
Case 2
Set submdl = mdl.SubModels.Item(submodel_txtchoice)
AddRelationships(submdl)
End Select
Else
Select Case (submodel_choice)
Case 0
Set submdl = mdl.ActiveSubModel
RemoveRelationships(submdl)
Case 1
For Each submdl In mdl.SubModels
RemoveRelationships(submdl)
Next
Case 2
Set submdl = mdl.SubModels.Item(submodel_txtchoice)
RemoveRelationships(submdl)
End Select
End If
MsgBox("Operation complete.", vbInformation, TITLE)
End If 'dialog
End If
End Sub
Sub AddRelationships( submdl As SubModel )
' Add all RS: useless will be automatically discarded
For Each rel In mdl.Relationships
submdl.RelationshipDisplays.Add(rel.ID)
Next
Debug.Print "Number of relationships in submodel """ & submdl.Name & """: " & submdl.RelationshipDisplays.Count
End Sub
Sub RemoveRelationships( submdl As SubModel )
' Remove all RS
For Each reld In submdl.RelationshipDisplays
Debug.Print reld.ParentRelationship.ID
submdl.RelationshipDisplays.Remove(reld.ID)
Next
Debug.Print "Number of relationships remaining in submodel """ & submdl.Name & """: " & submdl.RelationshipDisplays.Count
End Sub
Rem See DialogFunc help topic for more information.
Private Function ExportHandler(DlgItem$, Action%, SuppValue&) As Boolean
Select Case Action%
Case 1 ' Dialog box initialization
DlgEnable "SMList", False
submodel_txtchoice = DlgText("SMList")
DlgText("tModel", "Current Model: " & mdl.Name)
Case 2 ' Value changing or button pressed
If DlgItem = "OptionButton2" Then
DlgEnable "SMList", True
submodel_choice = DlgValue("object_sel")
submodel_txtchoice = DlgText ("SMList")
submodel_UIchoice = DlgValue("SMList")
End If
If DlgItem = "object_sel" Then
submodel_choice = DlgValue("object_sel")
Select Case submodel_choice
Case 0
DlgEnable "SMList", False
Case 1
DlgEnable "SMList", False
Case 2
DlgEnable "SMList", True
End Select
submodel_UIchoice = DlgValue("object_sel")
Debug.Print DlgValue("object_sel")
Debug.Print "submodel_choice = " & submodel_choice
Debug.Print "submodel_txtchoice = " & submodel_txtchoice
Debug.Print "submodel_UIchoice = " & submodel_UIchoice
End If
If DlgItem = "ogAction" Then
action_choice = DlgValue("ogAction")
Select Case action_choice
Case 0
Debug.Print "Option: Add"
Case 1
Debug.Print "Option: Remove"
End Select
End If
If DlgItem = "SMList" Then
submodel_txtchoice = DlgText ("SMList")
submodel_UIchoice = DlgValue("SMList")
Debug.Print "submodel_choice = " & DlgValue("object_sel")
Debug.Print "submodel_txtchoice = " & submodel_txtchoice
Debug.Print "submodel_UIchoice = " & submodel_UIchoice
End If
Rem ExportHandler = True ' Prevent button press from closing the dialog box
Case 3 ' TextBox or ComboBox text changed
Case 4 ' Focus changed
Case 5 ' Idle
Rem ExportHandler = True ' Continue getting idle actions
Case 6 ' Function key
End Select
End Function
As usual, feel free to modify the script so that it perfectly meets your expectations.
A short video which shows how to create a macro from a script in ER/Studio Data Architect:
