Create And Edit External Data Queries

External data queries are suitable for standard value lists that reside in existing external (to the Meridian vault) OLE DB data sources such as in Meridian Enterprise Server, SQL Server, Oracle, Excel, or text files. These can be useful for reusing project, customer, or related document data stored in other information systems.

To create or edit an external data query:

1. In Configurator, expand Tables and Queries in the configuration tree to display the existing tables and queries.
2. To delete a query, select it and on the Edit menu, select Delete.
3. To edit an existing query:
   1. Select the query and click the Edit button.
   2. Click the Query tab. The existing query parameters are shown.
   3. If necessary, edit the existing value for Connection string or click the Connection String hyperlink to build a connection string. The Data Link Properties dialog box appears. For assistance in creating a connection, click the Help button.

   4. If necessary, edit the existing value for From:

      • If the Use Enterprise Server lookup table option is selected, select the table from the list.
      • If the VBScript option is enabled, click the Meridian Enterprise Script Editor button to edit the VBScript expression. For information on using VBScript for configuration expressions including their limitations, see the Accruent Meridian Enterprise VBScript API Reference.
      Note:

      For more information on valid values for From, click the From hyperlink, which will display reference information from the Microsoft Developer Network (MSDN) SQL Server Developer Center website.

   5. Click the Test button to preview the results.
4. To create a new query, on the Edit menu, select New Table or Query. The Select Data Source Type dialog box appears.
5. Select Query and click OK. A new query is added to the configuration tree and its property pages appear in the right pane.
6. On the General page, type a name for the query in Display Name. This name will not be visible to users. A default internal name is calculated in Name. Accept the default in most cases.
7. Click the Query page to define the query. The query’s parameter options appear.

8. Type a valid OLE DB connection string in Connection string or click the Connection String hyperlink to build a connection string. The Data Link Properties dialog box appears. For assistance in creating a connection, click the Help button.

   Note:

   The user name and password typed in the Data Link Properties dialog are not shown in the Connection string text box for increased security.

9. Type the name of an existing table or view in the data source in From. A query may also be typed. To calculate the table name, view name, or query from existing vault properties, enable the VBScript option and click the Meridian Enterprise Script Editor button to build a VBScript expression. For information on using VBScript for configuration expressions including their limitations, see the Accruent Meridian Enterprise VBScript API Reference.

   Note:

   For more information on valid values for From, see Using the FROM Clause on the Microsoft Developer Network (MSDN) SQL Server Developer Center website.

10. Click the Test button to preview the results, which will appear in the data grid.
11. Click OK to save the query’s definition.

You may now create a VBScript function to retrieve data from the query, as described in the Accruent Meridian Enterprise VBScript API Reference.