PDM: How do I create a reference in VBScript

[Mark Brady]

When I do it via Script I get one of a few outcomes:

1. If the child has a column with the same name as the parent... it
seems to work fine.

2. If not I get a either a new column in the child with the same name

3. Or it will change the name of the column that I want it to use
instead... e.g. I have two FK's to the same table.

______________________________________

Build this Oracle PDM

create table PARENT_T (
PARENT_PK NUMBER not null,
constraint PK_PARENT_T primary key (PARENT_PK)
);

create table CHILD_T (
ALT_PARENT_PK NUMBER
);

___________________________________________

This script will create a JOIN for the reference automagically, and it
will create a brand new column that wasn't there before

set tbl = activemodel.tables.item(0) '--- assumes this is parent if
not make it 1
set tbl2 = activemodel.tables.item(1) '--- assumes this is child if
not make it 0

Set ref = activemodel.references.createnewat( -1 , 0)
'
set ref.parenttable = tbl
set ref.childtable = tbl2

___________________________________________

So I tried to create the JOIN First, so maybe it would leave the child
table alone. But instead of adding a new column it just flat out
changed the name of the column to match the parent table.

set tbl = activemodel.tables.item(0) '--- assumes this is parent if
not make it 1
set tbl2 = activemodel.tables.item(1) '--- assumes this is child if
not make it 0

Set ref = activemodel.references.createnewat( -1 , 0)
'
'
Set jn = ref.joins.createnewat(-1, 0)
set jn.parenttableColumn = tbl.keys.item(0).columns.item(0)
set jn.ChildtableColumn = tbl2.columns.item(0)
set ref.parenttable = tbl
set ref.childtable = tbl2

___________________________________________

[Laura]

Hi Mark,

Here is a subroutine from a .VBS I wrote to copy a table and all of
its linked tables to a new PDM. It creates new references pretty
successfully, so hopefully you can adapt it to your needs. It takes
as its parameters the destination model (cls_BasePhysicalModel) and
all the references (by iterating over the source tables OutReferences
and optionally, the InReferences as well). Let me know if you want me
to send you the entire script, and I would happily share the wealth.
The script was written in PD 12.5 -- haven't upgraded to 15 yet
here...

HTH,
Laura

Sub CopyReference(destMdl, srcRef )
dim bFound
dim ref
dim destRef
dim destParentTable
dim destChildTable
dim refJoin
dim destRefJoin
dim col
dim idx
dim bClearJoins

for each ref in destMdl.References
if LCase(ref.DisplayName) = LCase(srcRef.DisplayName) then
bFound = True
exit for
end if
next ' ref

if not bFound then
set destRef = destMdl.References.CreateNew()
destRef.Name = srcRef.Name
destRef.Code = srcRef.Code
destRef.Comment = srcRef.Comment
destRef.Description = srcRef.Description
if srcRef.Annotated then
destRef.Annotation = srcRef.Annotation
end if

set destParentTable = FindIfExists(destMdl,
srcRef.ParentTable.Name, "")
if destParentTable is nothing then
set destParentTable = CopyTable(srcRef.Parent, destMdl,
srcRef.ParentTable.Name, "", False)
end if

destRef.ParentTable = destParentTable

set destChildTable = FindIfExists(destMdl,
srcRef.ChildTable.Name, "")
if destChildTable is nothing then
set destChildTable = CopyTable(srcMdl, destMdl,
srcRef.ChildTable.Name, "", False)
end if
destRef.ChildTable = destChildTable

'destRef.ParentKey = srcRef.ParentKey
'output srcRef.ParentKey.Name

destRef.MinimumCardinality = srcRef.MinimumCardinality
destRef.MaximumCardinality = srcRef.MaximumCardinality
destRef.Mandatory = srcRef.Mandatory
destRef.ParentRole = srcRef.ParentRole
destRef.ChildRole = srcRef.ChildRole
destRef.ChangeParentAllowed = srcRef.ChangeParentAllowed
destRef.Generated = srcRef.Generated
destRef.ForeignKeyConstraintName =
srcRef.ForeignKeyConstraintName
destRef.CheckOnCommit = srcRef.CheckOnCommit
destRef.AutoArrangeJoinOrder = srcRef.AutoArrangeJoinOrder
destRef.Cardinality = srcRef.Cardinality
destRef.UpdateConstraint = srcRef.UpdateConstraint
destRef.DeleteConstraint = srcRef.DeleteConstraint
destRef.ImplementationType = srcRef.ImplementationType

for idx = 0 to srcRef.Joins.Count - 1
if LCase(srcRef.Joins.Item(idx).ParentTableColumn.Name) <>
LCase(srcRef.Joins.Item(idx).ChildTableColumn.Name) then
bClearJoins = TRUE
exit for
end if
next 'idx

if bClearJoins then
for each refJoin in destRef.Joins
destRef.Joins.Remove refJoin, TRUE
next

for each refJoin in srcRef.Joins
set destRefJoin = destRef.Joins.CreateNew()

for each col in destRef.ParentTable.Columns
if LCase(col.Name) =
LCase(refJoin.ParentTableColumn.Name) then
set destRefJoin.ParentTableColumn = col
exit for
end if
next 'col
for each col in destRef.ChildTable.Columns
if LCase(col.Name) =
LCase(refJoin.ChildTableColumn.Name) then
set destRefJoin.ChildTableColumn = col
exit for
end if
next 'col
next ' refJoin
end if
end if
End Sub

[Mark Brady]

Thanks for that... I'll try it soon.

[tranphu...@gmail.com]

Hi Laura,
I'm working on PowerDesigner 15.3
I created a script that looked like the same as you but it didn't work when I assign the ParentTableColumn by a column in ParentTable. The error was "Cannot set value 'Column 'Case History.case history : iMX case reference'' for attribute Parent Table Column of Reference Join "
Was you success with PD 15.3? If yes, could you please post your script here?
Thank a lot.
Nguyen