Contents

Introduction
Quick Tip - Designing Intelligent List Implementations
Settings
Quick Tip - Intelligent Assignment Employee Lists
Assuring Consistent Submissions
Quick Tip - Sequenced Data Entry
Implementing Type-down Lists

Introduction

CATSWeb Intelligent Lists are managed from the Field Definition page for individual fields. They allow multiple, alternative selection lists to be bound to a field. At runtime, CATSWeb selects and displays the optimal Intelligent List (or no Intelligent List) based on Intelligent List configuration settings and current data in the form or record. If no Intelligent List matches the selection criteria, the field uses its default configuration as determined by its Field Definition page

The list selection process is performed when the form loads. It is repeated each time the user clicks a Reload button on the form, thereby allowing new lists to be selected based on user entries in the form. Intelligent Lists are managed from the Field Definitions page for each field.

Back to Top

Back to Top

Settings

The settings on the Intelligent List page include:

• List or Data Link - The CATSWeb List or Data Link that provides values for the Intelligent List. When Stored Procedure or ActiveX DLL data links are specified, they receive input data from the current data in the form. This allows them to respond to new data entered by the user when Refresh is clicked.
• Evaluation Order - When multiple Intelligent Lists exist for a single field, CATSWeb evaluates them one at a time until it finds a suitable list. This parameter determines the order of evaluation. Lower numbers are evaluated before higher numbers. This means that it is generally a good idea to place the more selective lists at lower evaluation orders compared to more general lists.
• Description - A description of the Intelligent List.
• Active on Add - Enables the Intelligent List to be active during add operations (when a user is submitting a new record).
• Active on Edit - Enables the Intelligent List to be active during edit operations.
• Intelligent on Default - Enables the Intelligent List to consider default values for fields as being "Known Fields" (see below) when the form is initially loaded in Add mode. If this box is not checked, default values are ignored. The setting has no effect when the form is loaded in Edit mode or is regenerated as a result of a Refresh.
• Intelligent on Prefill - Enables the Intelligent List to consider prefill values for fields as being "Known Fields" (see below) when the form is initially loaded in Add mode. If this box is not checked, prefill values are ignored. The setting has no effect when the form is loaded in Edit mode or is regenerated as a result of a Refresh.
• Initial Value In List On Edit - This setting may best be understood via an example. Assume that a standard list on a form contains the values "Yes", "No" and "Maybe". A record has been saved with "Maybe" selected. Now the record is edited, the form reloaded, and an Intelligent List containing only the values "Yes", and "No" replaces the standard list.
  
  If the intent is to allow the user to leave the field value unchanged as "Maybe", check this box. This is necessary since the new list does not contain "Maybe". Checking this box causes CATSWeb to include the initial field value ("Maybe") in the list, even if it would not normally appear in the list.
  
  If the intent is to force the user to change the field value to either "Yes" or "No", uncheck the box. The Intelligent List will appear as-is, without any additional values added to it. This setting is only active when the form is in Edit mode.
  
• Initial Value In List On Add - This setting is similar to Initial Value In List On Edit above, except that it is only active when the form is in Add mode. In Add mode, the initial value would be either the default value or prefill value, if either are defined.
• First Value Persistence - This setting determines whether or not the first non-empty value entered in the Intelligent List's parent field (i.e. the field the Intelligent List is defined for) persists as the parameter value to be fed to stored procedure data links that are selected as the List or Data Link for this Intelligent List. If not checked, the current value of the field is used as the parameter value. For most Intelligent List implementations, leave this box unchecked. It is provided as an option for Intelligent Lists that implement type-down functionality.
• Known Field 1-10 - Enter the names of up to 10 fields (table field names) that must have values in them for the Intelligent List to be selected by CATSWeb.
• Condition 1-4 - Enter up to 4 conditional expressions that must be true for the Intelligent List to be selected by CATSWeb. Conditional expressions cannot contain boolean operators such as AND or OR. Conditional expressions related to text values can only use the "= " and"<>" operators. Several examples of conditional expressions are shown in the table below.

Assuring Consistent Submissions

Consider the following scenario: you have established Intelligent Lists that guide the user through selecting a set of consistent entries.  For example, if the user selects "Ford" in the first list (Manufacturer), you have used Intelligent Lists to assure that only Ford models are present in your Model list, and that only the appropriate years for the selected model are present in the Model Year list. The user has no way of submitting a record for a 1998 Chevy Taurus (an inconsistent set of entries since Taurus is a Ford model, not a Chevy model). Or do they...

Unfortunately, if you do nothing else, they do. Here is how it can happen:

• User chooses Ford in the Manufacturer list and clicks the reload button.
• User chooses Taurus in the Model list and clicks the reload button.
• User chooses 1998 in the Model Year list.
• Instead of submitting as you expect them to do, the user goes back to the Manufacturer list, chooses Chevy, and then submits.
• A 1998 Chevy Taurus (which doesn't exist) in the database.

Fortunately, there are two possible ways of preventing this, each with their own advantages and disadvantages:

• Implement a custom validation routine by way of an event hook routine (ActiveX or Stored Procedure) that performs a similar check and assures that the entries are consistent.
• Enable the List Value Validation setting on the Field Definition page for each of the "slave" list fields (i.e. for each field except for the Manufacturer list in this example).  When this setting is enabled, CATSWeb repeats the Intelligent List evaluation process for the field at the time of submission. It determines which list should be in place based on the other data submitted, per your configuration. CATSWeb then checks to see if the value actually submitted (e.g. "Taurus") is in that list. If the submitted value is not in the list, a validation error is returned to the user.
  
  Here is how this works in this example:
  
  
  • CATSWeb sees that Chevy is the value in the "master" list and would load a model list containing only Chevy models.
  • CATSWeb checks to see if Taurus is in that list. It isn't.
  • A validation error is returned.  Submission of the non-existent 1998 Chevy Taurus is thwarted.
    

The advantage of the first technique (custom validation routine) is greater configurability (you design the routine), higher performance, and it can be made to be effective in all usage scenarios. For example, instead of a generic validation error being returned, a specific error such as "Chevy does not manufacture the Taurus model. Did you mean Ford instead?" may be returned by your routine. The disadvantage of this technique is that some type of SQL or other programming skills are required for implementation. Implementation is certainly not as easy as checking a box on a configuration page.

The advantage of the second technique (List Value Validation setting) is that an event hook routine does not need to be implemented. However, your form design must assure that all of the following requirements are met for it to work properly:

• Fields with List Value Validation enabled must be based on Intelligent Lists, and the Intelligent Lists must themselves be based on data links (not standard lists).
• The outputs from the Intelligent List data links must be a function of at least one of the values on the form that consistency is being enforced for. For example, in the case of a stored procedure data link, the procedure must have at least one input parameter, and the parameter value must come from one of these form fields. The internal SQL must use the parameter in its Where clause.
• The user must be forced to reload the form at least once, to ensure that Intelligent List processing has taken place. This can be accomplished by setting one of the fields to be Mandatory, and binding it to an empty standard list. The user will be forced to reload the form at least once to transform the empty standard list to a non-empty Intelligent List before they can choose a value for the mandatory field.

An additional disadvantage of this technique is that it may not be as efficient in terms of performance. For example, if the two slave lists (Model and Model Year) are each coming from a single external system, CATSWeb would need to make two trips to the database to gather the lists for comparison. A custom routine might be able to make a single trip, or invoke a stored procedure to perform the validation, either of which would deliver better performance.

Back to Top

Implementing Type-down Lists

Consider the following scenario: your part number list contains 100,000 entries. You know that providing 100,000 entries in a list will slow page loads and make data entry difficult. Your users typically know at least a few characters of the part number they are interested in.

This is a good scenario for a type-down list, that can be implemented in CATSWeb using Intelligent Lists. Here is how:

• On your form, use a text field for your Part Number field. Do not bind it to a list.
  
  
• Create a stored procedure that implements logic like this:
  
  Select PartNumber From PartNumberTable Where PartNumber Like rtrim(@typeDown) + '%' Order By PartNumber
  
  @typeDown is an input parameter. This SQL statement selects all part numbers that begin with the text passed in @typeDown (% is the SQL wildcard character). For example, if @typeDown were "AB", the output might include AB-1, AB-2 and AB-3. The stored procedure is assumed to have only this single @typeDown parameter (NOTE: This is SQL Server syntax, in Oracle use the appropriate PL/SQL syntax).
  
  
• Define a stored procedure data link for your stored procedure. Select "Field Value" in the Parameter 1 list. This tells the data link to pass the value of the field the data link is associated with to your @typeDown parameter.
  
  
• Create an Intelligent List for your Part Number field. Select your stored procedure data link as the List or Data Link. Check the Intelligent on Add box, and leave all other check boxes unchecked. Enter the name of your field in the Known Field 1 box.

From CATSWeb, open your form in Add mode. Type a few characters in the Part Number box, then click Reload. The field transforms into a list of part numbers that match the few characters you entered. Now select a part number and click Reload again. The list filters down again, this time either containing only the part number you selected (unless it is a substring in other part numbers).
You may or may not want the Part Number list to filter down in this way on subsequent reloads.

The First Value Persistence setting is provided to solve this "problem". If checked, CATSWeb remembers the first characters you typed in the field, and uses that to fix your part number list based on those characters, no matter how many times you may Reload the form.

If the user needs to enter a different set of characters to get a different list, they may use their browser's Back button to return to the version of the form where they entered the characters. They can enter different characters and click Reload to get a different list. If the Intelligent List does not have the First Value Persistence setting checked there is another option: select the blank list entry and click Reload. The Intelligent List will not be enacted, and the field returns to its default text box configuration.