Available from Tables 2027.3.
Not everything you need in a schedule lives in the Revit model. Product data sits in a database, cost rates in an Excel list, and the structural engineer's model arrives as an IFC file. With external datasources you connect such data to your workbook and use it in two ways:
- Extend a table: show columns from the external source next to the Revit parameters of each row, for example the manufacturer that belongs to a type mark.
- Selection list for a column: offer the values of an external source as the value list of a Revit parameter, for example the door finishes from your office standard.
Tables only reads external datasources. Nothing is ever written back to the database, the Excel file or the IFC file.
This article walks through the complete workflow with an SQL database. For Excel files and IFC files only the steps that differ are shown further down.
Walkthrough: product data from an SQL database
The manufacturer, model and fire rating of each door type are kept in your office database, in the table DoorProducts. The door schedule in Tables should show this data next to the Revit parameters. Both sides know the type mark:
| TypeMark | Manufacturer | Model | FireRating |
|---|---|---|---|
| D1 | Nordic Doors | ND 40 Classic | - |
| D2 | Nordic Doors | ND 42 Silent | - |
| D3 | SteelTec | ST 30-1 | T30 |
| D4 | Vitrum | VG Duo | - |
| D5 | Vitrum | VS Slide | - |
1. Connect the database
- On the Data tab, click Data Sources in the Datasource group. The Data Sources dialog opens.
The "Data Sources" command on the "Data" tab
- Click Add Datasource and choose SQL datasource.... The Connection Editor opens.
- Fill in the connection:
- Provider: the type of your database, for example Microsoft SQL Server.
- Server name: the name of the database server.
- Authentication type: Windows authentication uses your Windows login. With server authentication, enter User name and Password.
- Database: the database that contains your data. Once server and login are correct, the list offers the databases of the server.
The Connection Editor for a Microsoft SQL Server database
- Click Finish.
2. Choose the data with the Query Builder
A query defines which data the source delivers. Tables opens the Query Builder right after the connection, so you do not have to write any SQL.
- The list on the left shows the tables of the database. Drag the table you need, here DoorProducts, into the empty area in the middle, or double-click it.
- Tick the columns you need. Each ticked column appears in the grid at the bottom, where you can also give it another name (Alias) or sort the result.
- Optional: Filter... limits the rows, for example to one product group. Preview Results... shows what the query delivers.
- Click OK.
The Query Builder: tables on the left, the chosen table with its columns in the middle, the selected columns below
Include the column you want to match with Revit, here TypeMark. Select only the columns you need; this keeps reloading fast.
3. Name and check the datasource
- Back in the Data Sources dialog the new source is listed with its query below it. The name cell is ready for typing: name the source, for example Product database. Click the name of the query to rename it as well, here Door products. An optional description helps your colleagues.
- Select the query and click Preview... to check the first 50 rows.
Preview of the query "Door products"
- Confirm with OK.
The "Data Sources" dialog: the SQL source "Product database" with its query, next to two Excel sources and an IFC source
A source can have several queries, for example one per product group: select the source and click Add Query. Copy duplicates a source and Remove deletes it. The button in the Edit column reopens the connection of a source or the Query Builder of a query.
Datasources are set up once per workbook and are then available to every table in it. Wherever a datasource is selected in Tables, a Manage... button next to the list opens this dialog as well.
4. Extend the table with the external columns
A joined datasource adds the columns of the query to an existing table. A join rule tells Tables which row of the query belongs to which row of the table.
- Place the cursor in the door schedule and click Edit table.
- On the General tab, click Joined Datasources....
- Click the plus button below the list on the left to add a joined datasource.
- Under Datasource, select Product database / Door products (source and query). The new entry is named after the query. You can rename it at any time.
- Under Join Condition, click the plus button and add the rule: the parameter TypeMark of the query is Equal to the table parameter Type Mark.
The query "Door products" joined to the door schedule by the type mark
- Confirm with OK.
- Switch to the Fields tab. The columns of the query are now listed among the available fields, prefixed with the name of the joined datasource, for example Door products:Manufacturer. Add the ones you need to the scheduled fields.
Columns of joined datasources on the "Fields" tab
- Confirm the table dialog with OK.
The door schedule with manufacturer and fire rating from the database
The external values are read again with every Reload, so changes in the database are reflected in the schedule. Columns from an external source are read-only, but you can use them in calculated parameters, filters, sorting and totals like any other column. A table can have up to 20 joined datasources.
5. Or: use the query as selection list for a column
A selection source turns the values of an external datasource into the value list of a column. Instead of typing a value, you pick it, and it is written to Revit with Apply as usual.
- Place the cursor in the table and click Edit table.
- On the Fields tab, select the parameter in the list of scheduled fields and click Edit Parameter (the pencil button below the list). The parameter must be writable.
- Click Add selection source... at the bottom of the dialog.
The selection source button in the parameter dialog. Once a source exists it reads "Edit selection source..."
- On the General tab, select the Datasource and then the Selection Attribute: the column of the external source that supplies the values.
A selection source for the door finish; here the values come from an Excel sheet
- Optional: on the Join tab, add rules that narrow the list for each row, for example only the finishes that are suitable for the door's type.
- Confirm all dialogs with OK.
To pick a value, select one or more cells of the column and open the value list, either with a right-click or with the Value list command. If you select several cells, Tables offers only the values that are valid for all selected rows.
To change the source later, open Edit Parameter again and click Edit selection source.... Clear the Active checkbox to switch the list off without losing its setup.
Good to know for SQL sources
- The connection, including a user name and password you enter, is saved in the workbook, so colleagues can reload without typing it again. Use a database account that may only read.
- Every colleague who reloads the table needs network access to the database server.
Further example: an Excel file
The unit prices of the door types are maintained in Price list.xlsx on the sheet Doors. The first row holds the column names:
| Type Mark | Description | Unit Price |
|---|---|---|
| D1 | Single door, timber frame | 640 |
| D2 | Single door, sound insulated | 980 |
| D3 | Fire door T30, steel frame | 1450 |
| D4 | Double door, glazed | 2890 |
| D5 | Sliding door | 1720 |
Everything works as in the walkthrough, with these differences:
- Connecting (instead of steps 1 and 2): choose Add Datasource > Excel datasource.... The wizard asks for the Excel workbook or CSV file, then for the import settings (keep the option that the first row contains the column names), and then for the sheet or range to read.
- No query: an Excel source has no Query Builder and no queries. The sheet or range you picked is the data. To read a second sheet of the same file, add a second Excel datasource.
- Naming: the source is named after the file. In datasource lists it appears by this name alone, without a query.
- Joining: select the source Price list and add the rule Type Mark equals Type Mark. The columns appear as Price list:Description and Price list:Unit Price.
- Updating: edit and save the Excel file, then click Reload in Tables.
Preview of the Excel source "Price list"
The same door schedule, now also with description and unit price from the Excel file
Further example: an IFC file
The structural engineer delivers the model as an IFC file, and you want to show data of its elements next to your own, for example the properties of the matching wall.
Everything works as in the walkthrough, with these differences:
- Connecting (instead of step 1): choose Add Datasource > IFC datasource... and pick the IFC file. There is no connection to enter.
- Query (instead of step 2): the IFC Query dialog replaces the Query Builder. Give the query a name, choose whether it reads Instances or Types, and tick the classes you need, such as Wall or Door. Classes shown in bold exist in the file, and the number tells you how many elements it contains. Grey classes do not occur in the file. Include subcategories also reads the classes below a ticked class.
- Columns: you do not select columns. A query offers the name, class and GlobalId of each element, the data of its type and storey, and its property sets and quantities.
- Joining: use a value both models share, for example a mark that is maintained in both models.
- Updating: when you receive a new version of the file, replace the file under the same name and click Reload. If the file was moved, Tables names the datasource and the missing file; correct the path with the button in the Edit column of the Data Sources dialog.
An IFC query: the classes of the file with their element counts
Good to know
- Stored in the workbook. Datasources, queries, joins and selection sources are saved with the workbook. A colleague who opens it needs access to the same database or file path.
- Renaming is safe. If you rename a datasource or query in the Data Sources dialog, every table and selection list that uses it follows automatically.
- Removing shows what is affected. Before a datasource or query that is still in use is removed, Tables lists the tables and columns that use it. If you remove it anyway, those objects keep their setup and stay empty until you select another datasource for them.
- Cancel means cancel. Changes made in the Data Sources dialog, to joined datasources or to a selection source only take effect when you confirm with OK.
- Sources without a query cannot be selected. If an SQL or IFC source is missing from a datasource list, it has no query yet. The hint below the list names it; add a query via Manage....
Comments
0 comments
Please sign in to leave a comment.