Global Lookup Forms

What is a Global Lookup Form?

Forms are used when standard ACE fields do not capture the data you need.

A Global Lookup Form extends this by allowing you to standardize and reuse that data across multiple forms. It stores a centralized list of values that users can select from instead of entering free text.

Overview

There are three components:

• Global Lookup Form

  A standalone form that stores reusable reference data

• Lookup Field (Field Type = LOOKUP)

  A field used in other forms to select data from a Global Lookup Form

• ETL Load Method = Lookup Form

  Used to import data into a Global Lookup Form

Form Requirements

• Global Lookup Forms must be:

  • Standalone

  • Multi-Instance

• Forms that use Lookup Fields:

  • Do not need to be multi-instance

  • Do not need to be standalone (unless required by your use case)

How It Works

Global Lookup Forms follow a simple 3-step process:

1. Create the Global Lookup Form

2. Load lookup data

3. Add a Lookup Field to another form

Step 1: Create a Global Lookup Form

Create the Form

1. Go to Setup → Forms → New

2. Configure:

   1. Form Config Type: Lookup

   2. Location: Standalone

   3. Allow Multiple Instances: Enabled

   4. Assign appropriate roles/permissions

3. Click Apply

Add Form Fields

1. Add the Primary Key field

   1. This is the value users will see in dropdowns

   2. Example: Item Type (String)

2. Add additional fields as needed

   1. Example:

      1. Item Description (Text Area)

      2. Manufacturer (String)

3. Click Save

Verify Setup

1. Open the form

2. Confirm all fields are present

3. Click Manage Lookup Rows

Step 2: Load Lookup Data

Option A: Manual Entry

1. Open the Global Lookup Form

2. Click Manage Lookup Rows

3. Click New

4. Add or edit records

5. Save

Option B: ETL Import Profile

Prepare the File

1. Create a CSV file

2. Include:

   1. Global Lookup Form Identifier or Form ID

   2. Columns for each field

3. Ensure:

   1. Row 1 = headers

   2. Data starts on row 2

Create Import Profile

1. Go to Accounts → ETL Import Profiles

2. Create a new profile

3. Set:

   1. Load Method = Lookup Form

4. Add a name and description

Upload and Map

1. Upload the CSV file

2. Set Start Row = 2

3. Map:

   1. Form Identifier/Form ID

   2. Each column to the correct field

Load and Verify

1. Run the import

2. Return to the form

3. Click Manage Lookup Rows

4. Confirm records loaded successfully

Step 3: Add a Lookup Field to Another Form

Create or Open the Related Form

1. Open or create a Debt, Client, or Debt Trans form

2. Set:

   1. Location: Standalone

   2. Allow Multiple Instances: Enabled (if needed)

Add the Lookup Field

1. Add a new field

2. Set:

   1. Field Type = LOOKUP

   2. Lookup Form = [Your Global Lookup Form]

3. Click Save

Use the Form

1. Open an account, client, or transaction

2. Add a new form record

3. Enter standard field values

4. Select a value from the lookup dropdown

5. Click Save

Please Note: Form Visibility

If the form does not appear immediately on the account, client, or transaction screen:

• Refresh the page or clear cache

• In some cases, a short delay may occur after creating or updating forms

View Lookup Details

1. Open the saved form record

2. Click View Lookup

3. The lookup record opens in a read-only view in a new tab

What Users See

• The Lookup Field appears as a dropdown list

• Values come from the Global Lookup Form

• The displayed value is based on the Primary Key field

• If blank, the system displays the row ID

Reporting

SQL reports that include form data can use joins to resolve lookup fields to their corresponding lookup form values.

In this example:

• Account form table: FORM2

• Lookup field: lookup_to_catalog

• Lookup form table: GLOBAL_LOOKUP_1

• Display field: unique_value

The lookup field stores the selected lookup form record ID. By joining FORM2.lookup_to_catalog to GLOBAL_LOOKUP_1.id, the report can display GLOBAL_LOOKUP_1.unique_value instead of the stored ID.

SELECT
    FORM2.id AS "Id",
    FORM2.debt_id AS "Debt Id",
    FORM2.created_by AS "Created By",
    FORM2.create_date AS "Create Date",
    FORM2.last_updated_by AS "Last Updated By",
    FORM2.last_update AS "Last Update",
    FORM2.form2_field1 AS "Form2 Field1",
    GLOBAL_LOOKUP_1.unique_value AS "Lookup To Catalog"
FROM FORM2
LEFT JOIN GLOBAL_LOOKUP_1
    ON FORM2.lookup_to_catalog = GLOBAL_LOOKUP_1.id
ORDER BY
    FORM2.id;