Creating an External Table

The first step in creating a dynamic field is to create an external table. The external table defines the hierarchy of values that will then be used by the dynamic field. The external table must be in a database hosted on the same Microsoft SQL Server as your Laserfiche repository database.

Note: You can use Microsoft Excel to create a spreadsheet laying out your field information, and then import that Excel spreadsheet directly into your SQL Server.

Important: The Laserfiche Server service must be able to access the table or schema you are using.

Before you begin creating your external table, you should plan what information it will contain. See Planning Dynamic Fields for more information.

The following set of instructions explains how to create an external table in Microsoft SQL Server Management Studio. It is intended as a model set of instructions only. If you are using another database management system or database management tool, the instructions may differ.

To create an external table in Microsoft SQL Server Management Studio

1. Launch Microsoft SQL Server Management Studio.
2. Log in to the same SQL Server you use to host your Laserfiche database as a user with the rights to create and modify tables.

   Important: If you select another SQL Server, your external table will not work with your repository.

3. Expand your SQL Server.
4. Expand Databases.
5. Expand an existing database or create a new database.

   Note: You can use your Laserfiche repository database, but it isn't necessary to do so.

6. Select Tables.
7. Right-click and select New Table.... This will open a new table definition.
8. For each column, type a column name representing that data that will be in the column.
9. For each column, select a relevant data type from the list.
10. For each column, determine whether the column should be able to be saved with a null value.
11. Repeat steps 8-10 for each column you will be using.

    Example: An administrator is creating a table of employees for a Human Resources template. He creates four columns.  The first has the column name "Employee Name," the type "nvarchar(64)", and does not allow nulls. The second has the column name "Department," the type "nvarchar(32)", and does not allow nulls. The third has the column name "E-mail Address," the type "nvarchar(64)", and allows nulls as some employees do not have listed email addresses. The fourth has the column name "Employee ID," the type "int", and does not allow nulls. The table definition would look like the following example.

12. In the File menu, select Save Table_1 and provide a name for the table.
13. Close the table definition.
14. In the Tables node, select your new table.  Right-click and select Edit Top 200 Rows. This will open your new table.
15. Fill in the values you want to make available in your dynamic fields. Unless a column allows null value, every column must be filled in before another row can be started.

    Example: Once the administrator in the above example has created the table definition, he can begin filling in the columns. The table would look like the following example.

16. When you have input all the values you need, save and close your table.