Kofax Insight Studio > Records > Create records > Create and use derived fields

Create and use derived fields

A derived field is a calculated field added to a record definition using custom logic, built-in functions, and other field values from the record. The calculation for the derived field can be processed in the source database or the Insight Data database. The Process in source DB check box in the derived field’s definition in the Property Panel enables shifting the load between the Source database server and the Data database server. If the Process in source DB check box is not selected, the derived field calculation is performed in the Data database.

Note For OData-based records, all derived fields are calculated in the Data database without regard to the Process in source DB option: Insight reads data from OData service, inserts data into a temporary table in the Data database, and then calculates the derived fields while adding data to the Main record table. We recommend that you avoid using the OData functions and direct expressions in a derived field.

You can use derived fields for the following purposes:

  • Combine Fields: derived fields are commonly used to combine other fields. For example, you can concatenate the Last and First Name fields; or for dates stored as multiple fields, you can combine the date and time.

  • Applying Custom Logic:

    Another use of a derived field is to apply custom logic to transform the data.

    For example, in the case of different categories of orders (Warehouse, Store, Internet, etc.), a user can use custom logic to mark anything that is Warehouse or Store as under the same category as Brick and Mortar. The logic in this case would be: 

    IF (Order_Category == 'Warehouse' OR Order_Category == 'Store')
THEN
Category = 'Brick and Mortar'

Set up a derived field

  1. In the Fields column, right-click a field name and select Add derived field.

    The Expression Editor appears. See Expression editor.

  2. Select one of the following statement types to create the derived field.

    • Math expression:

      Allows users to mathematically change the record fields using numbers and mathematical operators. When defining a math expression, select data from the different tabs needed to define the derived field: Variables, DB Table Fields, Constants, SQL Functions, R functions or Parameters.

    • Case condition:

      Provides a simple way to create a case statement to break up a continuous value into multiple ranges.

  3. Click OK.

    The statement appears in the main window.

  4. Drag the derived field from the Fields column to the Record column to map it to a record field.
  5. Save the changes.