Toad World® Forums

How to create a relation between two tables via scripts?


#1

Hi,

please could you post here an example how to create a relation between two tables via scripts?

I’ve got a table A with a primary key ID_A and I would like to add a new field ID_A (FK) to some other tables.

Thank you

Lada


#2

Hi Lada,

Please find attached a script that will create a relationship between Entity1 and Entity2 in the model (Videorental in the script).
Please feel free to modify the script at your convenience.
Execute the script in Scripting Window.

In case of any questions, please write us back. Thanks.

Regards,

Vladka + Daril
CreateRelationship.js (542 Bytes)


#3

Hi Vladka and Daril,

thank you for your answer.
I’ve modified your script (see the attached file CreateRelationshipLada.js).

But, when I run it, the script creates more lines between entitites Entity1 and Entity2. It happens only in the workspace that I specify in the code line

var WS = Model.Workspaces.GetObjectByName(‘All Items’);

When I specified another one here, then the problem was again only on the specified workspace, but not on on ‘All Items’. (= There was only one line between Entity1 and Entity2 in ‘All Items’.)

My TDM was very instable at closing of the application.
To be sure I uninstall the version 3.3.7.22 and installed the version 3.3.8.11. I created a new configuration during the installation to stop the problem.

The problems at closing of the application don’t happen any more, but the problem with the twice lines between Entity1 and Entity2 remains.
When I select one of the both lines and delete it (and select option Really remove) then it deletes also the second one.

Please what to do with that?

A second question:
What is the main idea of creating of the relations (foreign keys) between entitites?
We get ID of the Workspace where we want to show the relation, we get ID of the both entitites, we create a link and later we search somehow via Model for the corresponding relation and set the properties of it (e.g. Identifying = false etc)?
Is it so?

A third question:
How can I make an existing relation visible in a workspace? Of course via script.
I plan to prepare a script that goes through all workspaces and shows all invisible relations at each of them.
The problem: I want normally show also all relations among the entities on each Workspace. It is very difficult in TDM to rich it when I add an existing entity on an existing Workspace, because of the existing relations of that entity.

Thank you

Lada
CreateRelationshipProblem.png


#4

Hi Vladka and Daril,

thank you for your answer.
I’ve modified your script (see the attached file CreateRelationshipLada.js).

But, when I run it, the script creates more lines between entitites Entity1 and Entity2. It happens only in the workspace that I specify in the code line

var WS = Model.Workspaces.GetObjectByName(‘All Items’);

When I specified another one here, then the problem was again only on the specified workspace, but not on on ‘All Items’. (= There was only one line between Entity1 and Entity2 in ‘All Items’.)

My TDM was very instable at closing of the application.
To be sure I uninstall the version 3.3.7.22 and installed the version 3.3.8.11. I created a new configuration during the installation to stop the problem.

The problems at closing of the application don’t happen any more, but the problem with the twice lines between Entity1 and Entity2 remains.
When I select one of the both lines and delete it (and select option Really remove) then it deletes also the second one.

Please what to do with that?

A second question:
What is the main idea of creating of the relations (foreign keys) between entitites?
We get ID of the Workspace where we want to show the relation, we get ID of the both entitites, we create a link and later we search somehow via Model for the corresponding relation and set the properties of it (e.g. Identifying = false etc)?
Is it so?

A third question:
How can I make an existing relation visible in a workspace? Of course via script.
I plan to prepare a script that goes through all workspaces and shows all invisible relations at each of them.
The problem: I want normally show also all relations among the entities on each Workspace. It is very difficult in TDM to rich it when I add an existing entity on an existing Workspace, because of the existing relations of that entity.

Thank you

Lada
CreateRelationshipLada.js (677 Bytes)


#5

Hello Lada,

Yes, unfortunately the problem with two shortctuts of relationship occurs on WS where Auto Complete (option in Workspace Format) is on. Please find attached a new script that fixes this problem.
Note: The problem occurred in v. 3.3.7.22, however not in 3.3.8.11. In any case, the modified script is better - it will add the rel. shortcut to all WS where Auto Complete is on and will not add the rel. shortcut where this option is disabled.

My TDM was very instable at closing of the application. To be sure I uninstall the version 3.3.7.22 and installed the version 3.3.8.11. I created a new configuration during the installation to stop the problem.

To be able to simulate the problem, we would need more information. What panes/windows were open during the application close? Was there the Scripting Window? Did any error message occur? What did it say?
In any case, it’s good to hear you solved the problem.

A second question: What is the main idea of creating of the relations (foreign keys) between entitites?..

The script returnes just shortcuts of relationships as only shortcuts are on WS. Relationships (objects) are in Model. It is possible to get to Relationship from its shortcut via property ParentBase (please see the script).

A third question: How can I make an existing relation visible in a workspace? …

Generally, you can use:
either
a) the entity right-click option, Fill Parent and Child Objects (for particular entity)
or
b) the WS right-click option | Add Selected Objects to Workspace, enable only the Links button and select the relationships of which shortcuts you want to add to the WS.

Also, it is possible via script - please see it attached.

If you have any questions, please write us back. Thanks.

Regards,

Vladka + Daril

Message was edited by: vladka - added note
AddRelationshipShortcutWS.js (213 Bytes)


#6

Hello Lada,

Yes, unfortunately the problem with two shortctuts of relationship occurs on WS where Auto Complete (option in Workspace Format) is on. Please find attached a new script that fixes this problem.
Note: The problem occurred in v. 3.3.7.22, however not in 3.3.8.11. In any case, the modified script is better - it will add the rel. shortcut to all WS where Auto Complete is on and will not add the rel. shortcut where this option is disabled.

My TDM was very instable at closing of the application. To be sure I uninstall the version 3.3.7.22 and installed the version 3.3.8.11. I created a new configuration during the installation to stop the problem.

To be able to simulate the problem, we would need more information. What panes/windows were open during the application close? Was there the Scripting Window? Did any error message occur? What did it say?
In any case, it’s good to hear you solved the problem.

A second question: What is the main idea of creating of the relations (foreign keys) between entitites?..

The script returnes just shortcuts of relationships as only shortcuts are on WS. Relationships (objects) are in Model. It is possible to get to Relationship from its shortcut via property ParentBase (please see the script).

A third question: How can I make an existing relation visible in a workspace? …

Generally, you can use:
either
a) the entity right-click option, Fill Parent and Child Objects (for particular entity)
or
b) the WS right-click option | Add Selected Objects to Workspace, enable only the Links button and select the relationships of which shortcuts you want to add to the WS.

Also, it is possible via script - please see it attached.

If you have any questions, please write us back. Thanks.

Regards,

Vladka + Daril

Message was edited by: vladka - added note
ModifiedCreateRelationship.js (867 Bytes)


#7

See my script modification:
Relation = Model.AddLinkObject(2004, TenantEntity, Entity);
Relation.Name = RelationName;
Relation.Caption = RelationName;
Relation.Identifying = false;
Relation.MandatoryParent = false;
Attribute = Relation.ForeignKeys.GetObject(0).AttrChild;
Attribute.Name = AttributeName;
Attribute.Caption = AttributeName;
Attribute.Ordinal = 2;
Attribute.NotNull = false;
Pk = Entity.Keys.GetObjectByName(Prefix+’_PK’);
if (Pk == null) {
System.ShowMessageDialog(1000,‘AdaptEntitiesDialog’,‘Could not find PK ‘+Prefix+’_PK’,3,4);
}
else {
// Remove from Pk.KeyItems
}

  1. How to prevent the new attribute being added to the primary key?

  2. Or as workaround: How to remove an item from the KeyItems list?

  3. Why is the new FK attribute always at the bottom and Attribute.Ordinal=2 ignored? How to move it up to the second position in the script?

Thanx a lot.
Andreas


#8

Hello Andreas,

The attribute is added to primary key automatically. However, as you write, you can remove it - in the following way:


var PKItem = Entity.PK.KeyItems.GetObject(0);//Here it is necessary to select a proper key item, e.g. by attribute. More properties of class PERKeyConstraintItem can be found in Reference Guide.
Log.Information(PKItem.ClassName);
PKItem.Delete();

Unfortunately, it is not possible to use the property Ordinal as you expect. There isn’t a function for ordering of attributes that would be available via scripting. We will look into the matter. CR 76 645.

Thanks for your patience.

Regards,

Vladka + TDM Team

Message was edited by: vladka


#9

Thanx. The delete call did the job.

Andreas