Toad World® Forums

Trying to modify RenameRelationshipsMacro .. how do I have the syntax?

Hey there,

I’m trying to modify the RenameRelationshipsMacro so the syntax matches our preferred syntax, but I’m having a very difficult time scripting…

There seems to be zero documentation on how these scripts work, and the script editor does not seem to have any kind of intellisense, so there is no way of knowing how to script anything, which is a little frustrating

This is the complete macro so far:


function Main() {

    var App = System.GetInterface("Application");
    var Model = App.ActiveModel;
    var WS = App.ActiveWorkSpace;
    var Log = System.CreateObject("Log");
    if (Model == null)
        return;
    if (WS == null)
        return;   
        
     var DlgParams = System.CreateObject('DialogParams');
  DlgParams.Caption = 'Rename Relationship Names Info';// Name appears in Settings | Options in section Dialog Boxes.
  DlgParams.DialogIndex = 206;  // Unique number, must be above 200
  DlgParams.Msg = 'This macro allows you to quickly change relationships captions to: <parenttablecaption> - <childtablecaption>';
  DlgParams.Msg +=' and names to: <parenttablename>_<childtablename> ';   
  DlgParams.Msg += 'For more infomation click the Help link at bottom. Do you wish to continue?';
  DlgParams.Buttons = 3;                                              
  DlgParams.DlgType = 2;
  DlgParams.HyperLink = 'http://www.toad-data-modeler.com/help/RenameObjectsPack.aspx';
  DlgParams.HyperLinkCaption = 'Help';
  DlgParams.ScriptName = 'RenameRelationshipsMacro';
 
   if(System.ShowMessageDialogScript(DlgParams) != 6)
   {
      return;
   }

    var SelectedObjects, ObjectsToIterate, OrigRelName, NewRelName, NewRelCaption;
    SelectedObjects = This;
    if ((SelectedObjects == null) || (SelectedObjects.Count == 0)) {
        if (System
                .ShowMessageDialog(
                        1004,
                        'WarningDialog',
                        'No relationship was selected. Do you wish to run the macro for all relationships?',
                        3, 3) == 6) {
            ObjectsToIterate = Model.Relations;
        } else {
            return;
        }
    } else {
        ObjectsToIterate = SelectedObjects;
    }

    var i, SelectedObject, IteratedRelationship;
    // iterate relationships
    Model.Lock();

    for (i = 0; i < ObjectsToIterate.Count; i++) {
        IteratedRelationship = null;
        SelectedObject = ObjectsToIterate.GetObject(i);        
        if (SelectedObject.ObjectType == 2504) // relation line selected on
        // WorkSpace
        {
            IteratedRelationship = SelectedObject.ParentBase;
        } else if (SelectedObject.ObjectType == 2004) // relation selected
        // in Model Explorer
        // or Object Viewer
        {
            IteratedRelationship = SelectedObject;
        }

        if (IteratedRelationship != null) {

            OrigRelName = IteratedRelationship.Name;                       
**NewRelName = "FK_"+IteratedRelationship.ChildEntity.Name+"_"+IteratedRelationship.ParentEntity.Name+"_"+IteratedRelationship.ChildEntity.Target;**
            NewRelCaption = "FK - "+IteratedRelationship.ParentEntity.Caption+" - "+IteratedRelationship.ChildEntity.Caption;
            if(Model.Relations.GetObjectByName(NewRelName))
            {
              NewRelName = NewRelName+"_"+i.toString();
              NewRelCaption = NewRelCaption+" - "+i.toString();
            }
            IteratedRelationship.Name = NewRelName;
            IteratedRelationship.Caption = NewRelCaption;
            Log.Information("Relation "+OrigRelName+" was renamed to "+NewRelName);                             
        }                   
    }
    Model.UnLock();
    Model.RefreshModel();
}

This is basically the line I need corrected:

The syntax I want would be FK_ChildEntityName_ParentEntityName_ChildEntityTargetField

So in SQL terms, this:

ALTER TABLE applications
ADD CONSTRAINT FK_applications_applicationtypes_applicationtypeid
FOREIGN KEY (applicationtypeid) REFERENCES
applicationtypes (id)

Anyone know how to do this?

Hello there,

I assume that by “Child Entity Target Field” you mean the attribute of child entity that is linked to relationship. Try replacing the code of the Rename Relationships macro on lines 67,68 with this:

NewRelName = “FK_”+IteratedRelationship.ChildEntity.Name+""+IteratedRelationship.ParentEntity.Name+""+ChildTarget(IteratedRelationship, 1); //1 - function returns child target name
NewRelCaption = "FK - “+IteratedRelationship.ChildEntity.Caption+” - “+IteratedRelationship.ParentEntity.Caption+” - "+ChildTarget(IteratedRelationship, 2); //2 - function returns child target caption

And also add this to your macro after the Main function:

function ChildTarget(Rel,NameType)
{
var i, fk, result;
result =’’;

for(i=0;i<Rel.ForeignKeys.Count;i++)
{
fk = Rel.ForeignKeys.GetObject(i);
if (result!=’’)
result += ‘_’;
if (NameType == 1)
result += fk.AttrChild.Name;
else if (NameType == 2)
result += fk.AttrChild.Caption;
}
return result;
}

This should create the relationship names and captions you want. Note that in cases when multiple child attributes are linked to one relationship (composite FK), they will all be part of the name/caption (e.g. FK_ChildEntity_ParentEntity_Att1_Att2_Att3)

To learn more about creating scripts/macros, please see the application Help (Projects and Models | Scripting and Customization), the Reference Guide (Expert Mode Menu) or try searching the website. Of course, if you do not find the information you need, you can always ask here.

Regards,

Lukas