Dynamic Fields

With a set of dynamic fields, the value selected in one field determines the values that can be selected in the next field. For example, once a user has selected Wisconsin for the State field, the City field would only display cities in Wisconsin.

Dynamic fields can contain multiple levels (for instance, only displaying the available model numbers for a particular model once the Model field has been filled), or can depend on more than one parent field (for instance, only displaying years relevant to a particular combination of manufacturer and model).

Planning

Before you create your dynamic fields, you should plan what values you want to allow users to set dynamically. You may manually create this list or pull it from another list or 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. See Using Microsoft Excel to Create an External Table for more information.

Any collection of information where one value is associated with another can be turned into a dynamic field. You can also create dynamic fields in which one piece of information controls more than one other field. Below are some examples of this kind of information, and how you could set up your tables to reflect it.

Example: You may want to use a dynamic field that filters a list of employees by department, so that once a department has been chosen, only those employees that are part of that department can be selected. You would begin with a list like the one below. If the following list is used in a dynamic field, for instance, when the HR department is selected, Tanya Wu and Pat Tomson would be available in the employee field, but the other employees would not be.

Example: In some cases, you might want to link more than two pieces of information in a dynamic field. For instance, you might create a set of dynamic fields to track car manufacturers, models, and years, such that if you selected, say, Dodge as the manufacturer, only Dodge models would appear in the models list; once you'd selected Dodge and Caravan, you would then be able to select only years relevant to Dodge Caravan automobiles. You could do so with a list like the one below.

Creating dynamic fields

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 on the same server as your Laserfiche repository database.

Important: The Laserfiche Server service must be able to access the table or schema you are using. For Microsoft SQL Server, ensure that the user the Laserfiche Server service logs in as can access the table you have created.

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.

Creating an external table in Microsoft SQL Server Management Studio

Launch Microsoft SQL Server Management Studio.
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.
Expand your SQL Server.
Expand Databases.
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.
Select Tables.
Right-click and select New Table.... This will open a new table definition.
For each column, type a column name representing that data that will be in the column.
For each column, select a relevant data type from the list.
For each column, determine whether the column should be able to be saved with a null value.
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.
In the File menu, select Save Table_1 and provide a name for the table.
Close the table definition.
In the Tables node, select your new table. Right-click and select Edit Top 200 Rows. This will open your new table.
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.
When you have input all the values you need, save and close your table.

Creating a Microsoft Excel spreadsheet to use with your dynamic fields

Open Microsoft Excel and create a new workbook.
Optional: Double-click the name of the sheet at the bottom of the screen and type a name for the sheet. This name can be automatically used to name the table.
For each field you want to handle dynamically, type a column name representing that data that will be in the column.

Example: A user who works at a pet store wants to create dynamic fields corresponding to types of animals, so that users can select first a category of animal (such as mammals, birds, reptiles or fish), then a type of animal (such as cats or dogs), and then a breed (such as German Shepherd or Labrador Retriever). She creates three columns: Category, Type and Breed

Fill in the values you want to make available in your dynamic fields.
Example: Once the user in the above example has created the columns, she can begin filling in the columns. The spreadsheet would look like the following example.
Optional: If you want to create additional information that will be imported into in other tables, you can do so by creating additional sheets in the workbook.
Open the File menu and select Save. Provide a save name and location for the spreadsheet.
Close Microsoft Excel.

Importing a Microsoft Excel spreadsheet into Microsoft SQL Server Management Studio

Important: To import a Microsoft Excel spreadsheet into your SQL database, you must use the 32-bit version of the Laserfiche Sql Server Import and Export wizard, even if you are using a 64-bit computer. Be sure to select the 32-bit version of the wizard for the import.

Launch the SQL Server Import and Export Wizard in one of the following ways:

In the Start menu, expand the Microsoft SQL Server folder and select Import and Export Data (32-bit).
In SQL Server Management Studio, expand the server tree and select your database, then right-click and point to Tasks. Select Import Data....

In the Welcome step, click Next.
In the Choose a Data Source step, open the Data source option and select Microsoft Excel.
Click the Browse... button and browse to the Excel spreadsheet file you want to import.
The wizard will attempt to automatically detect the version of your Excel spreadsheet in the Excel version option. If it is unable to detect the version correctly, you can select another version from the list.
If your spreadsheet was created with the column names in the first row, select First row has column names. If the first row contains data instead of column names, clear this option. Click Next to continue.
In the Choose a Destination step, open the Data source option and select SQL Server Native Client 10.0.
In the Server name option, select the SQL Server that hosts your Laserfiche database. (Data for dynamic fields must be stored on the same server as your Laserfiche database.)
In the Authentication option, select one of the following:

Use Windows Authentication: Select this option to authenticate to the SQL Server using your current Windows credentials.
Use SQL Server Authentication: Select this option to authenticate to the SQL Server using a username and password, and then provide your username and password.

In the Database option, select a database. You can use the same database as is used with your Laserfiche repository, but you do not have to. Click Next to continue.

Note: If you want to create a new database to host your table, you can do so by clicking New.

In the Specify Table Copy or Query step, select one of the following:

Copy data from one or more tables or views: This option will copy all the data from one or more of the sheets in your Excel spreadsheet workbook. You can select which sheet or sheets to import, but all the data in the specified sheets will be included. In most cases, you will want to select this option.
Write a query to specify the data to transfer: This option allows you to write a SQL query to import only selected data from your spreadsheet. You should select this option if your spreadsheet contains more information than you want to import. If you select this option, you will need to be able to write a SQL query. See SQL Query Language on MSDN for more information.

Click Next to continue.
Make sure that Run immediately is selected, and click Finish to complete the import.

Registering an external table

Once you have created your external table, you will need to register it with your repository. This will make the information stored within that table available for creating dynamic fields.

To register an external table, a user must have the Manage External Tables privilege.

Important: The Laserfiche Server service must be able to access the table or schema you are using. Ensure that the user the Laserfiche Server service logs in as can access the table you have created.

Desktop Admin
Web Admin

To register an external table

Open the Laserfiche Administration Console and navigate to your repository.
Expand the Metadata Management node.
Select the External Tables node.
Right-click, or open the Action menu, and select Register New External Table.... This will open the Register New External Table dialog box.
In the Alias option, type a reference name for this external table. This does not need to match the name of the table or database in SQL. It can be any name consisting of letters, numbers, and/or underscores and must be 63 characters or fewer.
In the Database name option, type the name of the database that hosts your external table.
In the Table Name option, type the name in SQL of the table you want to register.
Note: The table must be on the same SQL Server as your repository, but it does not need to be the same database.
Note: If the database or table name contains special characters, or is a key name in SQL, the name must be enclosed in quotes. It is also a best practice to match the casing when inputting database and table names.
Example: An administrator wants to register his employee list so that he can use it to create dynamic fields. The table is in his MyDatabase database, and the table is called employee_list. The following example demonstrates the settings he could use to register this external table.
Click View to preview the values in the table. (This will require you to save the external table.)
Click OK to save and close.

Note: Certain data types are not supported by external tables. The data types xml and sql_variant are not supported, and will be ignored by Laserfiche when the table is registered. Tables containing the data types "interval year to month," "interval day to second," "timestamp with time zone," and"timestamp with local time zone" cannot be registered with Laserfiche. Binary data will not be dropped, but will be displayed as [data] and cannot be assigned to Laserfiche dynamic fields.

To register an external table

Open the Laserfiche web client management page.
Under Metadata, click External Tables
Click the Add button. This will open the Add External Table dialog box.
In the Alias option, type a reference name for this external table. This can be any name you want, and will be the name you use to refer to the table when creating dynamic fields.
In the Database name option, type the name of the database that hosts your external table.
Enter the schema for the table.
In the Table Name option, type the name in SQL of the table you want to register.
Note: The table must be on the same SQL Server as your repository, but it does not need to be the same database.
Note: If the database or table name contains special characters, or is a key name in SQL, the name must be enclosed in quotes. It is also a best practice to match the casing when inputting database and table names.
Example: An administrator wants to register his employee list so that he can use it to create dynamic fields. The table is in his MyDatabase database, and the table is called employee_list. The following example demonstrates the settings he could use to register this external table.
Click OK to save and close.

Note: Certain SQL data types are not supported by external tables. The data types xml and sql_variant are not supported, and will be ignored by Laserfiche when the table is registered. Tables containing the data types "interval year to month," "interval day to second," "timestamp with time zone," and"timestamp with local time zone" cannot be registered with Laserfiche. Binary data will not be dropped, but will be displayed as [data] and cannot be assigned to Laserfiche dynamic fields.

Viewing and editing external tables

Once you have registered a table, you can view or edit its properties.

To modify an external table, a user must have the Manage External Tables privilege.

Desktop Admin
Web Admin

To view or edit an external table

Open the Laserfiche Administration Console and navigate to your repository.
Expand the Metadata Management node.
Expand the External Tables node and select the table you want to view or modify.
Right-click, or open the Action menu, and select Properties. This will open the Table Properties dialog.
Review or modify the settings:

Alias: A reference name for this external table. This can be any name you want, and will be the name you use to refer to the table when creating dynamic fields
Database: The name of the database that hosts your external table.
Table Name: The name in SQL of the table you want to register

Click View to preview the values in the table. (This will require you to save the external table.)
Click OK to save and close.

To view or edit an external table

Open the web client management page.
Under Metadata, click External Tables.
Click on the table you want to view or modify, and the Edit External Table dialog box will appear.
Review or modify the settings:

Alias: A reference name for this external table. This can be any name you want, and will be the name you use to refer to the table when creating dynamic fields
Database name: The name of the database that hosts your external table.
Schema: The schema for your table.
Table Name: The name in SQL of the table you want to register.

Click OK to save and close.

Adding dynamic fields to a template

Once you have created an external table, and registered that table with your repository, you can begin adding dynamic fields to your templates.

There are three basic steps for adding dynamic fields to a template. First, you must add the fields you want to handle dynamically to the template. Second, you must associate the fields with columns in your external table. Finally, you must specify which fields depend on which other fields.

Desktop Admin
Web Admin

To add dynamic fields to a template

Start the Laserfiche Administration Console.
In the console tree, expand the desired Laserfiche Server item.
Select the desired Laserfiche repository.
If security has been enabled on that repository, log in as any user who has been granted the Create Templates and Fields or Manage Templates and Fields privilege for the specified repository.
Expand the Metadata Management item.
Select the Templates node and either create a new template or select an existing template.
Create or add the fields that you want to access dynamically.
Example: An administrator wants to set up a template so that users can select a department, then select from a list of employees in that department, then select the email address and employee ID for the selected employee. He adds the Department, Employee, E-mail and Employee ID fields to the template.
Open the Dynamic Fields tab.
In the Dynamic Fields tab, add the first field that you want to handle dynamically.

In the Field Name option, select the field you want to manage dynamically.
Example: In the above example, the administrator first selects the "Department" field, which will control the available options in the other fields.
In the External Table option, select the external table in which the information is stored.
Example: The administrator selects the "employee" table that he already registered with the repository.
In the External Column option, select the column that corresponds to the field you selected.
Important: If the column in SQL is wider than the field in Laserfiche, data may be truncated. To avoid this, ensure that your Laserfiche field is wide enough to hold the information stored in the associated column.
Note: The Laserfiche field and its equivalent column in SQL field must be of compatible types. For example, if you created an Employee ID column in SQL as an int field, the corresponding Laserfiche field must be a number, long integer or integer field.
Example: The administrator selects the "Department" external column.
In the Parent Fields option, select the field that will determine the possible contents of this field. (If this is the field that will determine the possible contents of other fields, and is not itself bound to any other fields, leave this option blank.)
Example: Since the Department field will control the possible contents of other fields, but is not itself controlled by another field, the administrator leaves this option blank. If the template contained a Country field and the administrator wanted the Department field to be limited by the selected country, the Country field would be selected.
In the Sort Column field, select the column that will control the ordering of this field.
Example: The administrator selects "Department," indicating that the field will be sorted alphabetically by its own contents.
In the Sort Order field, select Ascending or Descending.
Example: The administrator wants this list to be sorted alphabetically, so he selects Ascending.
Optional: The Restrict Values option determines the behavior of the field when accessed using the SDK or another application; it is not relevant if you are accessing the values through version 8.2 or later of the Laserfiche web or Windows client. If you select False, external applications will be able to save values to that field even if they do not exactly match the values in the external table. For example, if you have a Quick Fields session reading an employee name from OCR, it would be able to save that value even if it contained a small mismatch due to OCR. If you select True, the value must match the value in the external table exactly, or it cannot be saved. This value is recommended if you are using dynamic fields for data verification. If you are unsure which option to select, we recommend leaving it set to the default, False.

Repeat step 9 for each additional field you want to handle dynamically.
Tip: One field can be bound to more than one parent field. For example, if you are using dynamic fields to allow users to specify car manufacturers, models, and years, you might bind the Year field to both Manufacturer and Model. Once you selected a manufacturer and model, you would be presented with a list of the years that were relevant to the specified make and model.
Example: The administrator adds the other fields. First he adds the Employee field and binds it to the Department field so that only employees in a particular department will be available when the department is selected. Then he adds the E-mail and Employee ID fields and binds them to the Employee field, so that only the appropriate email address and ID can be selected once the employee is selected.
Click OK to save.