Create And Edit Tables

Standard value tables are suitable for moderate to large lists that you do not want users to be able to edit. Tables may have multiple columns of data, which makes them much more useful for cascading value lists, that is, lists that only show values related to the selection of a specific value in another value list. A single table is also useful for storing multiple columns of related data that can be used as value lists for multiple properties. Table data is presented on a property page by way of a VBScript expression.

Table data is stored outside of the Meridian vault in a Microsoft Access or Microsoft SQL Server Compact Edition database depending on the Meridian version and platform (32-bit or 64-bit). Table data stored in Microsoft Access is not editable with the 64-bit edition of Meridian Configurator and must be edited with Microsoft Access instead. Table data stored in Microsoft SQL Server Compact Edition is not editable with Meridian Configurator running on a workstation and must be edited on the server.

To create a table:

1. In Configurator, expand Tables and Queries in the configuration tree to display the existing tables and queries.
2. On the Edit menu, select New Table or Query. The Select Data Source Type dialog box appears.
3. Select Table and click OK. A new table is added to the configuration tree and its property pages appear in the right pane.
4. On the General page, type a name for the table 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. Select Include values when exporting configuration if you want the table data to be exported along with the table’s definition when the vault configuration is exported. This can be useful if you want to import this table into another vault configuration without having to manually copy the table.

5. Click Apply to save your changes.

   Note:

   If the new table is stored in SQL Server Compact Edition and you do not save your changes before adding columns, your changes will not be saved.

6. Click the Columns tab to add columns to the table.
7. Click Add. The Add New Column dialog box appears.
8. Type a name in Column name, select a data type for the column from Column type, and click OK. The column is added to Available lookup list columns.
Note:

Column names cannot be the same as any Microsoft Jet reserved words.

9. To change the order in which the new column is displayed, select the column and click the arrow buttons   in the upper-right corner of the page.

   Note:

   After you save the table’s definition in step 10, you cannot change the columns’ order using the Configurator. You must use the native database administration tools instead. If the table is stored in Microsoft Access, you can open the table in Microsoft Access using the Open in Microsoft Access button in the lower-left corner of the page when not editing the table in Configurator. Microsoft Access must be installed on the current computer to open the file successfully. If the table is stored in SQL Server Compact Edition, you can open the table in SQL Server Management Studio.

10. Repeat steps 6 to 8 for each column that you want to create.
11. Click OK to save your changes.

To edit an existing table:

1. In Configurator, expand Tables and Queries in the configuration tree to display the existing tables and queries.
2. Select the table that you want to edit and click the Edit button.
3. To edit a column name:
   1. Click the Columns tab. The existing columns are shown.
   2. Click the column name that you want to edit. The column name becomes editable.
   3. Edit the column name and click Apply. The column name is changed.
4. To edit table entries:
   1. Click the Entries tab. The existing data is shown.

   2. Edit the existing values or click the asterisk (*) in the last row of the table to create a new row.

      Note:

      If the table is stored in Microsoft Access, you can edit the table in Microsoft Access using the Open in Microsoft Access button in the lower-left corner of the page when not editing the table in Configurator. Microsoft Access must be installed on the current computer to open the file successfully.

   3. Click Apply to save your changes.
5. To sort the table entries:
   1. Click the Entries tab. The existing entries are shown.
   2. Click Sort On. The Configure Sort Order dialog box appears.
   3. Select the columns upon which you want the entries sorted.
   4. To change the column order of the sorting, select a column name and click Move Up or Move Down accordingly.
   5. Click OK to apply the sort order.
   6. Click Apply to save your changes.
6. To remove a column:
   1. Click the Columns tab. The existing columns are shown.
   2. Select the column that you want to remove and click the Remove button. To remove all of the column definitions, click the Remove All button.
   3. Click Apply to save your changes.
7. When you are finished editing the table, Click OK to save your changes.

By default, all security roles are granted all privileges to new tables and tables that are imported to a vault’s configuration.

To restrict the security privileges for a table:

1. In Configurator, expand Tables and Queries in the configuration tree to display the existing tables and queries.
2. Select the table for which you want to set privileges.
3. Click Privileges. The Roles and Privileges dialog for the table appears.
4. Select the checkbox for each privilege (row) that should be granted for each security role (column) and click OK. Privileges may also be granted together with other objects as described in Create And Edit Security Roles.

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

If the table data is stored in Microsoft Access, all of the tables for the same vault are stored in an Microsoft Access database located in a subfolder of the BC-Meridian Extensions folder on the Meridian server. The name of the subfolder is the same as the name of the vault where it is used. The name of the database is <VaultName>$LL.MDB. For example, the database for a Meridian vault named MyVault would be:

<Drive>:\BC-Meridian Extensions\MyVault\MyVault$LL.MDB