Add an FK reference using PD scripting

[Former Member]

I have the columns defined in the (parent/child) tables.  The parent table has a single column PK.  How do I use PD scripting to add a reference from the child to the parent and to identify one of the existing child table columns to be used in the reference?

Thanks in advance for the help.

[former_member200945 · Accepted solution]

As far as I can tell there is not document on teaching how to use Powerdesigner API.

Engineering provides some examples in online document. Excel Import Tool you are using is written in vbscript. It contains a lot of information on how to call an API. Some extensions also contain script. Finally Metamodel Objects Help (In PD go to Help) is very useful.

It'll take a while for you get used to the API.

Here is my latest example.

Create a reference between two tables (parent and child). Also using existing column in

child Table(childAddress) as foreign key.

 

set model = ActiveModel
set references = Model.references

set parent= model.findChildByName("parent", cls_Table)
set Pcolumn= parent.findChildByName("parentAddress", cls_Column)

set child = model.findChildByName("child", cls_Table)
set Ccolumn= child.findChildByName("childAddress", cls_Column)

set newRef = references.CreateNew()
newRef.name ="ABC"
newRef.code ="ABC"


set newref.childTable=child
set newref.ParentTable=parent

set Joins=newRef.Joins
for each j in Joins
  set j.ChildTableColumn=cColumn
next

ActiveDiagram.AttachAllObjects

[Former Member · Accepted solution]

Phillip -- Thanks for the additional sample code.  I read it.  I understood everything.  What I did not get was how one arrives at which API to use and in which order.  I suppose that it goes back to the general question of getting to know the API.

[former_member200945]

Hi Chak,

The following is code sample showing how to create reference between two tables.

Here assuming you have table Parent and Child.

As you can see, the code automatically add Parent PK column as FK in child table.

set model =ActiveModel

set parentTable = model.findChildByName("Parent", cls_Table)
set childTable = model.findChildByName("Child", cls_Table)

set references=Model.references
set ref=references.createNew()
ref.name="child To Parent"
ref.code="childToParent"
set ref.object1=Childtable
set ref.object2=Parenttable

ActiveDiagram.AttachAllObjects

If you have multiple tables to reference to one table, you can modify above code.

set model =ActiveModel

set parentTable = model.findChildByName("Parent", cls_Table)

set tables=model.Tables

for each t in tables

   if  t.name <> parentTable.name then

       set references=Model.references

       set ref=references.createNew()

       ref.name=t.name & " " & parentTable.name

       ref.code=t.code & "_" & parentTable.code

       set ref.object1=t

       set ref.object2=parentTable

   end if

next

ActiveDiagram.AttachAllObjects

You can study code example(which locates at your Powerdesigner folder) to get idea

on how to call API.

[Former Member]

Thanks Phillip.  What would the code be like if I was to use an existing column in the child table to form the reference?

I did look at some sample code.  It is likely that I have yet to figure how to use the documentation.  But the metamodel class "Model" shows the following operations:

Where would I find the method findChildByName that you used in your code above?

And then looking up Reference and ReferenceJoin showed the following operations:

From my reply to an earlier posting, you can see that I have moved forward using Excel import.  But I would like to learn to use scripting to achieve the same.  More importantly, I would like to use the documentation properly.  There must have been obvious that I am not seeing.

Thanks again for your help.

[GeorgeMcGeachie]

Do you want to create many references at once? Rather than scripting, you could use Excel import to create references and select Join attributes.

[Former Member]

I have 70+ tables needing the same reference.  Each table references the same parent twice.  Hence the request for scripting.

I am not married to scripting, if Excel import can do the same.  Does using Excel need me to enter each and every reference?

[GeorgeMcGeachie]

Each row in an Excel import creates or updates an object - 70 references means you need 70 rows. The simplest sheet would only need two columns - Parent Table and Child Table. If you import from this, the FK columns will probably all have the same name as the parent column, unless you have >1 reference between the two tables, or your rolenaming policy is not the standard one (see model options).

You ought to also add a 'Name' column to Excel, using a function to create something like

"Parent_table <--- Child_table" - these names will appear in the PD browser - beware duplicates, though.

[Former Member]

Thanks George.  I got it working, mostly.  Additional PD wholesale change will bring everything to the level I wanted.  But I thought that I would find out exactly how to get it right 100% as part of the import.

Here is a sample scenario.

1. Two tables: Parent with column Parent Key (int) as the PK.  Child with columns Father Key (int) and Mother Key (int) and Child Name (varchar(50)).
2. Here are my Excel sheets:

The import did create references F1 and F2.  "Father Key" at the Child was renamed "Parent Key" and "Mother Key" was renamed "Par_Parent Key".

The child column datatypes remain as "int".  My real data model uses domains.  The resulting domains after the import became "null".

Again, I could use a PD wholesale action and the column names and to change the domains.  But what did I do wrong?

Here are the some of the output lines:

Importing data from table Reference

  2 object(s) created

Importing data from table Reference.Reference Join

Error: Could not set attribute ParentTableColumn for Reference Join 'F1.' with value 'Column 'Parent.Parent Key'' (Cannot set value 'Column 'Parent.Parent Key'' for attribute Parent Table Column of Reference Join 'F1.': Parent Table Column cannot be null.)

Error: Reference Join 'F1.' will not be imported due to missing link extremity

Error: Could not set attribute ParentTableColumn for Reference Join 'F2.' with value 'Column 'Parent.Parent Key'' (Cannot set value 'Column 'Parent.Parent Key'' for attribute Parent Table Column of Reference Join 'F2.': Parent Table Column cannot be null.)

Error: Reference Join 'F2.' will not be imported due to missing link extremity

  no object created or updated

[Former Member]

This scenario that I reported looked identical to that in the thread http://scn.sap.com/thread/3774419.  I wonder if it is caused by a bug in PD or some additional Excel tabs or columns are need to control the import.

Thanks.