Introduction
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.
Steps
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:
Script
'#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.
Bonus
A short video which shows how to create a macro from a script in ER/Studio Data Architect: