Macros – Add or Remove the Relationships in the Submodels

by Jan 8, 2024

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:

Create Submodel

Create Submodel

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:

User Interface provided by the Macro

User Interface provided by the Macro

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: