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.

Example

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.

Dynamic Fields Example

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.

  1. 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.
  2. 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.
  3. 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.StreetsLookup Properties
  4. 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
  5. 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
    WHERE pset_name='StreetsLookup'
    /*2. Get the prop_id of the field containing the street values and replace with INSERT_SECOND_VALUE_HERE.*/
    SELECT prop_id from propdef
    WHERE prop_name='e_street'
    /*3. Get the prop_id of the field containing the street values and replace with INSERT_THIRD_VALUE_HERE.*/
    SELECT prop_id from propdef
    WHERE prop_name='estate'
    /*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.*/
    USE [INSERT_DATABASE_NAME_HERE]
    GO
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    CREATE VIEW [dbo].[StreetLookup]
    AS
    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
    WHERE pv1.prop_id=INSERT_THIRD_VALUE_HERE
    and pv2.prop_id=INSERT_SECOND_VALUE_HERE
    and toc.pset_id=INSERT_FIRST_VALUE_HERE
    and (toc.toc_flags & 0x800 = 0)
  6. Now, back in the Administration Console, register the newly created StreetLookup view as an external table.Laserfiche External Table
  7. Add extra values to the Estate list field.Add Extra Values to Dynamic Fields
  8. 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.
  9. 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.Dynamic Fields New Document
  10. 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.Dynamic Field Properties
  11. 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.

Some of the products and services listed on the Laserfiche Solution Exchange were not developed by Laserfiche. The recommendations and opinions expressed on the Laserfiche Solution Exchange are those of the person or persons posting the recommendations only, and they do not necessarily represent Laserfiche's opinion or recommendation of the product or service being reviewed. Laserfiche disclaims all liability resulting from your purchase or use of any non-Laserfiche software product or service listed on the site.