Creating a DataTools Link

Creating a DataTools Link

Clicking on the New or Edit button brings up the following dialog box, allowing the creation of a new Link, or the editing of and existing selected link.

• Enter the name of your new link in the Name box. This should be a unique name.

• Select your ODBC Driver from the drop-down list box and click on the Setup... button to setup that driver. The driver will walk you through the setup options. If you have difficulties setting up your connection details, contact your database administrator.

  The box underneath will show the connection string.

  Note

  This is editable, though should only be changed with care. Under most circumstances this can be left alone.

  Some examples are:

  • Connection string: Selecting a database with a certain name (test.mdb) that is always next to the model file (as in the resultant NWD/NWF not the original):

    DBQ=%pushpath(%poppath(%currentpath),"test.mdb");DRIVER={Microsoft Access Driver (*.mdb)};

  • Connection string: Selecting a database with the same name as the original model file (e.g. AutoPlant):

    DBQ=%join(%removeext(%removepath(%sourcepath)),".mdb");DRIVER={Microsoft Access Driver (*.mdb)};

• The Hold open for application lifetime check box, when checked, keeps the connection to the dbase open whilst JetStream is open, and only closes it on exit.

• The SQL String must always start with SELECT, and is used to connect to a table within the database. Set up the query for the table by writing the SQL String.

  Some SQL String examples include:

  • SQL String: Selecting all columns from table Test whilst requiring that the column called Entity Handles matches a category/property pair called Entity Handle/Value and the column called File Name matches the original filename of the drawing:

    SELECT * FROM Test WHERE "Entity Handle" = %prop("Entity Handle","Value") AND “File Name” = $removeext(%removepath(%sourcepath));

    Here the path and the extension of the file name are being stripped so a file like c:\'model\'3rdFloorDucts.dwg would come out as 3rdFloorDucts.

  • SQL String: Selecting two columns from table Test whilst requiring that the column called Entity Handle matches a category/property pair called Entity Handle/Value:

    SELECT Name,Part FROM Test WHERE "Entity Handle" = %prop("Entity Handle","Value");

  • SQL String: Selecting all columns from table Test whilst requiring that the column called Value is within a certain range given by two category/property pairs:

    SELECT * FROM Test WHERE Value BETWEEN %prop("Pressure","Minimum") AND %prop("Pressure","Maximum");

  The full Tag list includes:

  Property tags

  • %prop("category","property") - Property of the currently selected object. Category is the name of the tab in the property windows (e.g. Item or Entity Handle) and property is the name of the property in that tab. (e.g. Value or Layer)

  • %intprop("category","property") - Property of the currently selected object. This is the same as the previous tag except instead of using the publicly visible category and property name use the internal JetStream names. The benefit of using internal names is that they are not language dependent. [This is advanced tag that will make more sense to people familiar with the JetStream API]

  File/Path tags

  • %sourcepath – This tag represents the full path and filename that the currently selected object comes from. Even when a collection of model files have been combined into a single NWD file this tag will still remember the path and filename of the original model file.

  • %currentpath - This tag represents the full path and filename of the currently loaded model. If you currently have an NWF or NWD loaded that contains many other models it will just return the path and filename of the top level NWF/NWD.

  File/Path Manipulation Tags

  • %removeext("text") – If the provided text includes a filename with an extension this tag will remove the extension.

  • %removepath("text") – If the provided text includes a path and filename this tag will remove the path and just return the filename.

  • %poppath("text") – If the provided text includes a path this tag will remove the top level. If it the text also includes a filename that will count as the top level and be removed. [e.g. %poppath(“c:\'temp”) becomes c:\' and %popath(c:\'temp\'readme.txt”) becomes c:\'temp]

  • %pushpath("text1","text2") – If text1 is a path and text2 is a file or folder name then text2 will be added onto the path in text1. [e.g. %pushpath(“c:\'test”,”model.nwd”) becomes c:\'test\'model.nwd]

  String Manipulation Tags

  • %join("text","text") – This tag simply joins the two pieces of text together. [For example %join(“c:\'”,”model.nwd”) would return c:\'model.nwd]

  Note

  Tags must not contain white space between the brackets (unless enclosed by quote marks), so

  %prop("EntityHandle","Value")

  works, but

  %prop("EntityHandle", "Value")

  does not.

• Once the string is complete, clicking anywhere in the Fields box brings up a new edit control. Type in the name of a field from the table accessed by the SQL String. Pressing enter automatically copies this name into the display name. This display name is the text that appears in the database tab in the Properties control; click on this display name to alter it. Continue to fill this in until the table contains all the information required to be seen.