Discussion:
PDM: How do I create a reference in VBScript
(too old to reply)
Mark Brady
2010-04-05 17:26:10 UTC
Permalink
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
2010-04-07 14:22:43 UTC
Permalink
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
Post by Mark Brady
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
___________________________________________
Mark Brady
2010-04-13 15:21:05 UTC
Permalink
Thanks for that... I'll try it soon.
t***@gmail.com
2016-11-12 03:09:30 UTC
Permalink
Post by 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
Post by Mark Brady
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
___________________________________________
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

Loading...