Database Linking

Link database data to drawing entities.

Database linking is a way to use data from external applications. Any record in an external database may be linked to a drawing entity, and a link label showing selected data from the database record can be associated with the linked entity.

The Database Linking window is "non-modal" so you can continue working on your drawing whilst it is open.

See Also Database Linking Example

Procedure

The database linking workflow consists of a few simple steps:

1. Open or create a drawing with the entities you want to link.

2. Create a new database connection, specifying the database you want to use.

3. For the database table you want to link to, specify the field/column which is the “unique id” of the data in that table.

4. Link entities, one by one, to records in the relevant database table.

5. View the data in the database.

6. Run a query.

7. Specify the label format.

Creating a New Database Connection

You need to tell the program where to find the database containing the information you want to link to the drawing entities.

1. In the Database Links window, select Data Connections ► Add Connection.

2. Browse for a database file. This can be an MS Access, MS Excel or any other supported type of database file. Select the database file you want and click Open.

3. Type a name for the new connection and click OK.

4. The new connection displays in the connection tree on the left side of the Database Linking window. The database tables in the database connection are displayed beneath the connection node. Each database table has one or more Label Formats and Queries attached to it.

You cannot create multiple connections to the same database.

5. There is also an Add Connection (advanced) option, which you can use if AllyCAD does not recognise the database type you want to use.

This function uses the Windows OLE DB Data Link settings to allow you to specify/define a database connection.

Renaming a Database Connection

1. Select an existing database connection in the connection tree and then select Data Connections ► Rename Connection.

2. Type in a new name for the connection.

Editing a Database Connection

You may want to edit a database connection if, for example, the location of the database file has changed. In this case, you will get an error message displayed in the connection tree.

1. Select an existing database connection in the connection tree and then select Data Connection ► Edit Connection.

Alternatively, select the connection to edit, right-click and select Edit from the popup menu.

2. The Windows OLE DB Data Link settings are displayed, allowing you to specify the correct location of the database.

If the database location is specified correctly the list of tables in that connection will be displayed in the connection tree.

Removing a Database Connection

1. Select an existing database connection in the connection tree and select Data Connection ► Remove Connection.

Alternatively, select the connection to remove, right-click and select the Remove option from the popup menu.

2. You are asked to confirm the removal. Click Yes to remove the connection.

If you remove a connection, any data links belonging to that connection are lost.

Creating Data Links

How to link rows in database tables to entities in your drawing.

Creating a Link

You need to identify the following:

• The entity you want to link with your data.

• The row of data in a table in a connection that you want to link to your entity.

• The column or field in the table that is a unique identifier for the data rows in the table. This might be a part number, property number, or even a street number.

Once you have this information:

1. Right-click the relevant row of data and choose the Link this row to an entity option.

2. If you have not previously specified the unique identifier, you are prompted to select it from a list of column names.

3. You are then asked to click on the entity you want to link to in the drawing.

4. If you already have an entity selected, the menu option will be Link with selected entity. Using this option will link the selected entity to the record.

5. The row of data in the data grid should now be drawn with a yellow background.

Creating a Link with a Label

A data link can also be created with a text label displaying data from the linked data row. Before creating a label for a link, you need to specify a label format that is used to format the text label created.

To specify a label format:

1. Right-click on the Label Formats item under the table name and select the Add label format option.

2. The Label Format Editor displays.

3. Specify a name for the label format and add at least one column name placeholder to the format text. The format text can also contain other text characters.

4. Click OK. Now you can proceed to create a link and add a label to the drawing.

5. Right-click the relevant row of data and select the Link with label option.

6. If you have not previously specified the unique identifier, you are prompted to select it from a list of column names.

7. If a drawing entity had already been selected, the record would be linked to that entity. If no drawing entity is selected, the Database Links window  minimises and you are prompted to:

Indicate entity to link (Bay = I16)

8. Click on the drawing entity that must be linked with the data record.

9. The link is created. The “data link label” is drawn at the centre position of the linked entity, and the record is highlighted in yellow.

Creating a Label without a Link

You can create a text label in the drawing, displaying data from a specific data row. You need to specify the label format that is used to format the text label created.

1. Right-click the relevant row of data and choose the Label an entity (no link) option.

2. You are prompted to click in the drawing to indicate where you want the label to be added:

Indicate entity to label (Bay = I18)

3. The data link label is drawn where you indicated. It is a text entity that is not linked in any way to the database table.

Editing Data Links

You can edit, update or remove data links and link labels.

Link Label Format

The following rules apply when creating text labels using the database linking functions:

• The text labels are formatted using the “current” text style, UNLESS

• The text labels have been created using a label format where the text style/height/lorg have been set in the “Label Format” window, OR

• The text labels have been created by SHP file import, and the text style/height/lorg values have been set in the additional “TX_HEIGHT”, “TX_LORG” and “TX_STYLE” columns created during import.

Viewing Database Tables

1. Click the table name in the connection tree to view that table’s data in the grid. Rows of data that are already linked to entities in the drawing will be highlighted with a yellow background.

2. To view only the table rows that are linked to entities in the drawing, right-click the table name and select the Show only linked rows option from the popup menu.

3. To identify the rows in the table which are linked to specific entity(s), first select the entity(s) in the drawing, then do one of the following:

3. • In the Database Links window right-click on the relevant table name, and select the Highlight rows linked to the selected entities option.

• • In the drawing window right-click and select the Show Linked Data option.

4. To refresh the data visible in the data grid, right-click on the relevant table name, and select the Refresh option. The latest unfiltered data for that table displays.

Viewing Linked Entities

1. To see which entity is linked to a specific row in the table, right-click on the relevant row in the data grid, and select the Show linked entity option. The drawing window zooms to the extents of the entity  linked to that row.

2. If you enable the Click to Pan option under the Database Links Options menu, the drawing view pans to show the linked entity in the centre of the view when you click on a linked row in the data grid .

3. You can also view the linked data for a specific linked entity in the drawing by selecting that entity in the drawing view and then right-clicking and selecting the Show Linked Data from the popup context menu. The Database Links window displays with the linked row highlighted.

Selecting Linked Entities

1. To select an entity in the drawing which is linked to a specific row in the table, right-click on the relevant row in the data grid, and select the Select linked entity option. The relevant entity in the drawing is selected. The drawing view is not zoomed/panned to show the selected entity.

2. You can also right-click on the table name, and choose the Select all linked entities in this table option to select all the entities in the drawing which are linked to the table.

Queries

A database query allows you to filter the information displayed in the table data grid.

Create a Query

1. Select the table you want to create a query for in the connection tree.

2. Right-click on the table name and select Add Query.

3. The Database Query window appears.

4. Enter a unique name for the new query, e.g. “EmployeesInSalesDepartment”.

5. In the filter grid in the window, specify a filter for the query by selecting a field in the table to filter by, and a condition for that field e.g. “Department = Sales”:

6. Click in the cell just below the “Field” header, and select “Department” from the dropdown list of fields:

7. Click in the cell just below the “Comparison” header, and select “=” from the dropdown list.

8. Click in the cell just below the “Value” header, and type in “Sales”.

9. Click Run Query to test your query. The Database Linking data grid should update to show only the data for the employees in the “Sales” department.

You can specify multiple conditions for the query filter by specifying a field name, comparison operator and value in the second row of the grid, and following rows. The conditions can then be combined by selecting a logical operation (AND or OR) in the first column of the new row.

11. Click OK to exit the Query window and save the query.

Run a Query

1. Navigate to the relevant query node in the connection tree, visible under the “Queries” node which is under a table node.

2. Right-click on the query name in the connection tree, and select the Run Query option in the popup menu.

3. The Database Linking data grid should update to show the results of the query.

Edit a Query

1. Navigate to the relevant query node in the connection tree, visible under the “Queries” node which is under a table node.

2. Right-click on the query name in the connection tree, and select Edit from the popup menu.

3. The Database Query dialog will appear, allowing you to change the name of the query, or change the filter conditions, or select which fields/columns in the table will be displayed in the query results.

See Also Database Linking Example