Data Table Tools - External Tables Configuration

Data Table Tools - External Tables Configuration

Summary

The External Tables allow for pulling data, and the data is available in all projects without having to manually keep all internal data tables across existing projects in sync.

The External Table Configuration tool is used for defining external data tables based on either Excel files or SQL Server queries.

• eVolve tab ⮞ Integrations panel ⮞ Data Table Tools menu ⮞ External Tables Configuration button

Window Overview

Source from Excel tab

Grid Columns

• Name - displays the label applied to the data source.
• File Path - displays the Source File set in the configuration.
• Cached - displays the status of the Cache Data checkbox set in the configuration.
• Description - displays additional information regarding the data source.

Buttons

• New - creates a new Excel (.xlsx) data source configuration file.
• Edit - modifies an existing Excel data source configuration file.
• Delete - removes an existing Excel data source configuration file.

Edit Source from Excel window

Table Information panel

• Name text box - used to specify a descriptive label for the data source.
• Description text box - provides additional information about the data source.
• Cache Data checkbox - when checked, the information in the data table is stored in memory and only updated when manually queried. Caching data increases performance for data that seldom changes or is expensive to pull. If this is unchecked, a live lookup will be performed for each request.

Source File panel

• Source File text box - specifies the local or network path to the desired Excel (.xlsx) file.
• Browse button - used to manually navigate through Windows Explorer to the desired Excel (.xlsx) file.
• Refresh button - updates the information in the Column Configuration grid.
  NOTE: This does not "refresh" the data source if the Cache Data checkbox is checked.

Column Configuration panel

Grid Column

• Name - displays the column header in the Excel file.
• Data Type - sets the Data Type for the information contained in the selected column in the Excel file.
• Include - when checked, the data in the selected column is contained in the query.
• Exclude - when checked, the data in the selected column is omitted from the query.

Source from SQL Server tab

Grid Columns

• Name - displays the label applied to the data source.
• Cached - displays the status of the Cache Data checkbox set in the configuration.
• Description - displays additional information regarding the data source.

Buttons

• New - creates a new SQL data source configuration file.
• Edit - modifies an existing SQL data source configuration file.
• Delete - removes an existing SQL data source configuration file.

Edit Source from SQL Server window

Table Information panel

• Name text box - used to specify a descriptive label for the data source.
• Description text box - provides additional information about the data source.
• Cache Data checkbox - when checked, the information in the data table is stored in memory and only updated when manually queried. Caching data increases performance for data that seldom changes or is expensive to pull. If this is unchecked, a live lookup will be performed for each request.

SQL Server Connection String panel

• SQL Server Connection String text box - enter an expression containing the server instance, database name, authentication details, and other settings to communicate with the database server. Please see the following link for information on how to format the string - https://www.connectionstrings.com/microsoft-data-sqlclient/

SQL Command panel

• SQL Command text box - (required field) - used to select data from the specified database.

Tips and Tricks

• The configuration of external tables will be stored on each individual user's computer unless they have configured a company standard's location in Workstation Settings in which case it will store them there.
  • If the configuration is local, then the external tables will only be available on that machine.
    • For example, if a Param Sync rule is created that utilizes one of these tables, it will fail on other computers.
  • If the configuration is a computer standard, all users configured to use the same location will have the external tables available to them.
    • The available external tables are read when Revit is started OR when the user opens the External Table Configuration tool and clicks OK/Apply.
• Each external table configures its own caching preferences. Caching is generally preferred (and is the default) for performance reasons, but stale data may be cached if external data is changed frequently.
  • Users can clear the cache to pull fresh data using either View Data Table or the Data Tables Manager.
• Cache Notes - in order to achieve a performance boost, external data tables are cached for fast storage (if the user chooses to option to cache the particular table). However, this could lead to stale data if the cache is never cleared. The user can clear the cache manually at any time (via the Data Tables configuration screen), but an option will be available in Workstation Settings (Misc Settings section) which allows for automatic clearing of the tables cache. When this configured limit is reached, the entirety of the table cache it cleared, and if/when a table is needed again, it is fetched fresh from its source.

Relevant Articles

• Data Table Tools - View Data Tables