Contributed by: Ben Birns, Capital Solutions
Dynamic fields allow you to constrain the values in one field based on a value set in another. Usually, administrators must specify which sub-values (child values) are valid for which parent values by populating tables in an external database and then specifying which Laserfiche fields in the template correlate with the external table values. By following the method below, you can configure dynamic fields without needing to create and maintain any external database tables, allowing you to quickly update your field values without having to involve a database administrator.
In this example, we are dealing with a number of estates. Each estate has many streets that belong to it. We are going to set up a template named Housing, which then allows a user to select an Estate from a drop-down menu and a corresponding Street from a dynamic field.
In this example, Estate is the parent field and Street is the child field.
How do I implement this solution?
These instructions continue along the example I have introduced, but you can adapt these steps to fit your specific template and field needs. The embedded hyperlinks will also take you to areas of the Laserfiche Online Help that describe the specific process in more detail.
- Log into the Laserfiche Administration Console and create a list field called Estate. Populate this field with a few estate names of your choosing. Later, you will be able to add more names to the list.
- Create a text field named e_street. Allow this field to store multiple values. Since this field will only be used for adding dynamic field values, the naming doesn’t matter. Make sure that only administrators have edit rights to e_street.
- Create a new template named StreetsLookup and add the Estate and e_street fields to it. Administrators should secure the StreetsLookup template to prevent general users from adding to dynamic field values. Note the width of the new fields and record them as ESTATE_WIDTH and ESTREET_WIDTH respectively.
- Create a text field and call it Street. This is the field that will be displayed on document templates. Do not populate this field yet. Make sure the width is the same as ESTREET_WIDTH
- Use these SQL scripts to create a view named StreetLookup in an external database. These scripts are written to run directly within the Laserfiche database, but they can be easily modified if you would like to create them in another database.
/*1. Get pset_id of template containing level 1 values and replace with INSERT_FIRST_VALUE_HERE*/
SELECT pset_id from propset
/*2. Get the prop_id of the field containing the street values and replace with INSERT_SECOND_VALUE_HERE.*/
SELECT prop_id from propdef
/*3. Get the prop_id of the field containing the street values and replace with INSERT_THIRD_VALUE_HERE.*/
SELECT prop_id from propdef
/*4. Note down the name of the external database and replace with INSERT_DATABASE_NAME_HERE
5. Note down the name of the Laserfiche database and replace with INSERT_LFDATABASE_NAME_HERE
6. Modify and run the script below.*/
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
CREATE VIEW [dbo].[StreetLookup]
SELECT CONVERT(NVARCHAR(ESTATE_WIDTH),pv1.str_val) AS estate, CONVERT(NVARCHAR(ESTREET_WIDTH),pv2.str_val) as street
FROM INSERT_LFDATABASE_NAME_HERE.dbo.propval AS pv1
inner join INSERT_LFDATABASE_NAME_HERE.dbo.propval AS pv2 ON pv1.tocid=pv2.tocid
inner join INSERT_LFDATABASE_NAME_HERE.dbo.toc ON toc.tocid=pv1.tocid
and (toc.toc_flags & 0x800 = 0)
- Now, back in the Administration Console, register the newly created StreetLookup view as an external table.
- Add extra values to the Estate list field.
- In the Laserfiche Client, create a new blank document without any image by right clicking New – Document, and assign the StreetsLookup template to it. You need one document per estate. It does not matter where in the repository these documents are created.
- For each estate, populate the Street field with the related streets. You should end up with as many blank documents as estates, with each document referencing a different estate.
- Now it is time to create the actual document template. The example below is called Housing. Add the Estate and Street fields to the template and set up the dynamic field links.
- Test out your new dynamic template by creating or importing a new document into Laserfiche and assigning the Housing template to it.
Note: This solution has only been tested to work with Laserfiche Client and Server versions 8.2 and 8.3, and SQL Server 2005 and 2008.