Lookup Tables

Lookup Tables allow you to directly upload a spreadsheet of tabular data for use throughout Laserfiche Process Automation. See the spreadsheet format below for details on what file formats are supported.

To create a lookup table

  1. On the Data Management page, click Lookup Tables in the navigation bar.
  2. Click New.
  3. Enter a name and description.
  4. Click Choose File and select the appropriate Excel document or CSV file to use as the source of the lookup table.

    5. Note: Upload speed will decrease as imported data sets and their file size increase.

  5. Optional: Select the Enable for Dynamic Fields option to allow the lookup table to be used as a data source for Laserfiche templates and fields when configuring dynamic fields.
  6. Optional: Select the Read-Only access to data option to prevent modification of lookup table values through query rules. Recommended for cases where the lookup table data will not be updated or will only be updated through the Replace Lookup Table Data workflow activity.
  7. Optional: Select the Preserve Whitespace option to save text as is. When not selected, Laserfiche will trim leading and trailing whitespace and normalize internal whitespace to a single space character.
  8. Click Next to proceed.
  9. Choose which columns to include as part of the upload. When applicable, you will also be presented with an option to control the type of values. For example, if Laserfiche detects numerical values, you will have the option to choose whether to save the values as text or as a number.

    Note: Click the Revert button to return to their default values any changes made to column types and selected columns.

  10. Click Create to complete the upload process.

To update a lookup table

There are three ways to update the data in a lookup table: through the Data Management page, through a workflow using the Replace Lookup Table Data activity, and using Lookup Table Query Rules to insert, update, or delete rows in a lookup table.

Note: If data in a lookup table is to be updated regularly, choose whether updates are to be done on a row-by-row basis or by replacing the spreadsheet. Mixing these two methods can lead to data inconsistency.

To update a lookup table through Data Management

  1. On the Data Management page, click Lookup Tables in the navigation bar.
  2. Click on the name of the lookup table.
  3. Click Update () to display the Update Lookup Table dialog. This provides the ability to:
    1. Edit the name and description of the lookup table.
    2. Change the Enable for Dynamic Fields and Read-Only Access to Data selections.
    3. Upload a new Excel document or CSV file. Be aware that this will overwrite the existing lookup table data. When uploading a new file, the included columns and their types can be changed.

Note: Use the download button () to download a copy of the data in the lookup table as a CSV file compressed as ZIP. This option can be useful when you want to bulk update the lookup table, but no longer have the original source Excel document or CSV file used to create the lookup table

Using a query rule to retrieve or update values

Within Rules, create a Query to retrieve, insert, update or delete data from a lookup table. Using the Rows to Return field to configure the desired number of rows for the query to return can increase performance.

  1. Within a workflow, use the Run Rule activity to run the lookup table query rule as part of a workflow.
  2. Within a business process, use Lookup Rules to run a Select-type query rule to pre-populate a form with values from the lookup table.

Spreadsheet format

The spreadsheet file can be either an Excel Workbook (.xlsx) or a CSV text file.

Performance of the imported file is best as a CSV, followed by XLSX, and lastly as a ZIP.

The first row of the spreadsheet should be the column names.

If there is more than one worksheet in an Excel workbook, only the first worksheet can be uploaded to the lookup table.

CSV files can be delimited by commas, semi-colons or tabs. For international compatibility, numbers in CSV files should use a period (“.”) as the decimal separator with no digit grouping (thousands) separator. Dates in CSV files should use the ISO format (YYYY-MM-DD).

Note: Because of the way Excel workbooks store dates and numbers, the date format and decimal separator should not be a concern for spreadsheets in the XLSX format.

Sample Excel file:

Sample CSV file:

ApplicationType,Name,TotalDownloads,CreatedDate,Weight
Internal,My Sample Application,37,2019-12-12,643.2
External,December Resources,95,2020-01-01,1827.3
External,November Resources,33,2019-12-01,613.5
Internal,Notes,12,2019-04-30,271.1

Related Links:

For an example that uses this feature across process automation, see the Migrating Laserfiche Form Tables into Microsoft Word Documents whitepaper.