Lookup lists are used to minimize redundant data in the database; they are also used within an application to limit and display choices to the user. By using the LookupList class, you can easily add new lookup lists to the application cache and user's can modify them using the administrative lookup list editor.

Lookup List Database Table Design

The fields required for a simple lookup list include:

• code -- the unique primary key
• description -- the value to appear in the lookup list
• default_item -- indicates if this value should be selected by default when first displayed
• level -- the order in which the value should be displayed
• enabled -- indicates whether this value should be displayed to the user or not

Notes:

• The sequence name should be specified, instead of using the database's default
• Sequence names can be up to 31 characters

CREATE SEQUENCE lookup_step_actions_code_seq;
CREATE TABLE lookup_step_actions (
  code INTEGER DEFAULT nextval('lookup_step_actions_code_seq') NOT NULL PRIMARY KEY,
  description VARCHAR(300) NOT NULL,
  default_item BOOLEAN DEFAULT false,
  "level" INTEGER DEFAULT 0,
  enabled BOOLEAN DEFAULT true
);

To install your new lookup list into ConcourseSuite CRM, see the section on creating install and upgrade scripts.

Instantiating in an Action Class

Lookup lists can be instantiated and cached by using the systemStatus object to access the list. Once retrieved, the list can be used in the request, but must not be modified because it is shared.

// Retrieve Lookup List using the cache
SystemStatus systemStatus = this.getSystemStatus(context);
LookupList list = systemStatus.getLookupList(db, "lookup_step_actions");
// Add the lookup list to the request to be used by a JSP
context.getRequest().setAttribute("stepActionsLookupList", list);

A lookup list can also be loaded directly from the database and manipulated before being used in a JSP.

LookupList list = new LookupList(db, "lookup_step_actions");
context.getRequest().setAttribute("stepActionsLookupList", list);

Accessing from a JSP

With the LookupList object in the request, the method getHtmlSelect("formFieldName", defaultId) can be used to render an HTML Select field with the options.

<jsp:useBean id="stepActionsLookupList" class="org.aspcfs.utils.web.LookupList" scope="request"/>
<tr class="containerBody">
  <td class="formLabel">
    <dhv:label name="sales.step.action">Step Action</dhv:label>
  </td>
  <td>
    <%= stepActionsLookupList.getHtmlSelect("stepAction", otherBean.getStepActionId()) %>
  </td>
</tr>

The LookupList object contains both enabled and disabled items, however only the enabled items will be shown. The exception is that if the form object is set to a disabled item, the disabled item will be included in the Lookup List with an (X) next to its value to alert the user that a disabled item is being used. The user can then optionally change the value to an enabled value, or leave the existing disabled value.