Step 1 - Create Stored Procedure Data Link

• From the Manage page, click Data Links in the Connectivity and Escalation section and add a new Stored Procedure Data Link.
• Enter the following information:
  • Data Link Name - Get Supervisor from Disposition Department
  • Procedure Name - sp_GetDepartmentSupervisorEZ
  • Leave all of the Parameter pull-downs blank.
• Submit the Data Link.
• Select the new data link from the list. When the edit form appears, click the Free-form Edit link. A new edit form will appear with the parameter fields shown as text boxes instead of pull-down lists.
• In the Parameter 1 field, enter DispositionAssignmentDept, which is the actual table field name for the disposition department field. Specifying this field for Parameter 1 means that CATSWeb will pass its value to the stored procedure as the first parameter. The stored procedure will use the value for its dept parameter shown in the procedure listing above. Note that the actual table field name can be determined by viewing the CATSWeb field definition form.
• Click Submit Data Link to save this final version of the data link.

Step 2 - Add an Event Hook to the Issue Category

• From the Manage page, click Issue Forms in the Form and Workflow Design section and click on the name of your category. The category edit form will open. Click Add where it appears under Event Hooks.
• Enter the following information:
  • Title - Lookup Supervisor of Disposition Department
  • Data Link - Select [Get Supervisor from Disposition Department]
    (CATSWeb automatically added the brackets to the name)
  • Field Mapping - DispAssignmentEmployee=Supervisor
    
    Here, DispAssignmentEmployee is the actual table field name for the disposition employee field. Supervisor is the name of the field returned by the sp_GetDepartmentSupervisorEZ stored procedure (see listing above).
    
    
  • Check the following fields: Active, Active on Add, Override User Inputs on Add.
• Click Submit to save the event hook.

Step 3 - Test the Automatic Lookup

In CATSWeb, add a new issue in your category. Select a disposition department, and deliberately select a disposition employee that is not the supervisor of the department you selected. Submit the issue, then view it. The selection you made for the disposition employee should have been automatically overriden with the name of the department supervisor.

If this test did not succeed, the following steps can be used to troubleshoot the problem:

• Verify your entries for the Stored Procedure Data Link and the Event Hook.
• From the Manage page, click Departments in the User Management section and view the Department record of the disposition department you selected. Verify that the name entered in the Supervisor field is correct. Verify also that it appears in the Department Employees list at the bottom of the page.

Step 4 - Finishing Touches

Checking the Override User Inputs on Add box in the event hook definition will cause any user selection of disposition employee to always be overwritten. Therefore, it is desirable to either make that field invisible when the user adds a new Issue, or make it non-editable. Either of these settings can be made in the Field Definition page for that field (Visible on Add and Editable on Add settings).

Alternatively, unchecking the Override User Inputs on Add box will give the user the option of selecting a disposition employee (which will not be overriden by the lookup), or leaving it blank. If left blank, the event hook will automatically insert the proper department supervisor.

Additional Information

If the issue category does not contain a disposition step, a similar technique can be used for the corrective action assignment employee and department. The stored procedure is generic and does not need to be changed. What would change in this case is:

• The stored procedure data link definition would need to specify the CAAssignmentDept field instead of the DispositionAssignmentDept field for Parameter 1. Disposition and corrective action assignment information is stored in two separate sets of fields.
• Similarly, the Field Mapping setting for the automatic lookup would have to change from DispAssignmentEmployee=Supervisor to CAAssignmentEmployee=Supervisor. The example could be adapted to Action and Subtask categories as well, with the table field names changed appropriately.

Note that event hooks occur after the user clicks the Submit button and before the submitted data is validated by CATSWeb. This means that if an event hook is designed to provide values for mandatory fields (such as in this example), care must be taken to assure that a value is always provided by the lookup if that field is invisible or non-editable to the user. The values provided should meet all other validation rules as well. In this case, the department supervisor names must be valid and they must be actual members of their department.

Implementation Example 2 - Automatic Assignment based on Product Family Selection

This example assumes familiarity with, and extends, Implementation Example 1 above. Assume that your manufacturing nonconformance Issue category contains a pull-down list for selecting a product family (using a list you created called "Product Families"). Each product family has a designated engineer in charge of all quality issues. Rather than forcing the user to choose the correct engineer when they enter the issue, an Automatic Lookup can make the correct assignment automatically.

Implementing this design requires:

• A means of looking up the correct engineer, and the engineer's department, for any product family.
• A means of automatically inserting the engineer's name and department into the disposition employee and department fields of the issue.

Step 1 - Entering the Product Family/Engineer Information

The unused Item Selector field can be used to record the engineer's name for each product family. And, the unused Extra Data field can be used to record the engineer's department. Entering that data gives this new list item table:

Step 2 - Develop Stored Procedure A stored procedure can now be developed to select the engineer and department given the product family. The ListItems table in CATSWeb is used to store entries in lists. Examining that table shows that the Item Selector information is actually stored in a field named Text1. The Extra Data information is actually stored in a field named Text2. If one were to show the ListItems table contents using the actual field names, it would appear like this (showing only the fields of interest):

Another field in the table called ListName has the same entry for each item, the name of the list (e.g."Product Family List"). With all of this in mind, the stored procedure can now be written:

---------------------------------------------------
CREATE PROCEDURE sp_GetProductFamilyEngineer
/* This procedure returns engineer information for a product family.
**
** prodFamily - The product line.
**
*/

@prodFamily nchar(50)

AS

Select Text1 As 'Engineer' Text2 As 'Department'
From ListItems Where
ListName=N'Product Families' And ListItem=@prodFamily
---------------------------------------------------

Note that in the procedure, the Text1 and Text2 fields have been aliased as Engineer and Department respectively. This is not critical, but aliasing can make using the procedure somewhat easier. See the Using Stored Procedure Data Links application note for additional information on writing stored procedures and loading them into the CATSWeb database.

Step 3 - Create Stored Procedure Data Link

• From the Manage page, click Data Links in the Connectivity and Escalation Section and add a new Stored Procedure Data Link.
• Enter the following information:
  • Data Link Name - Get Engineer from Product Family
  • Procedure Name - sp_GetProductFamilyEngineer
  • Leave all of the Parameter pull-downs blank.
• Submit the Data Link.
• Select the new data link from the list. When the edit form appears, click the Free-form Edit link. A new edit form will appear with the parameter fields shown as text boxes instead of pull-down lists.
• In the Parameter 1 field, enter the actual table field in the Issue form that the Product Family list is bound to (e.g. Text4). Specifying this field for Parameter 1 means that CATSWeb will pass the product family selected by the user to the stored procedure as the first parameter. The stored procedure will use the value for its prodFamily parameter shown in the procedure listing above. Note that the actual table field name can be determined by viewing the CATSWeb field definition form.
• Submit this final version of the data link.

Step 4 - Add an Event Hook to the Issue Category

• From the Manage page, click Issue Forms in the Form and Workflow Design section and click on the name of your category. The category edit form will open. At the bottom of the form, click Add where it appears under Event Hooks.
• Enter the following information:
  • Title - Lookup Engineer from Product Family
  • Data Link - Select [Get Engineer from Product Family]
    (CATSWeb automatically added the brackets to the name)
  • Field Mapping - DispAssignmentEmployee=Engineer|DispositionAssignmentDept=Department
    
    Here, DispAssignmentEmployee and DispositionAssignmentDept are the actual table field names for the disposition assignment fields. Engineer and Department are the names of the fields returned by the sp_GetProductFamilyEngineer stored procedure (see listing above). Note that the aliased names are returned, not the actual table field names. The "|" character is used as the delimiter between multiple lookup field pairs.
    
    
  • Check the following fields: Active, Active on Add, Override User Inputs on Add.
• Submit the event hook.

Step 5 - Test the Event Hook

In CATSWeb, add a new Issue in your category. Select a product family, and deliberately select a disposition employee that is not the engineer responsible for the product family. Submit the issue, then view it. The proper engineer's name and department should appear in the disposition assignment area.

If this test did not succeed, the following steps can be used to troubleshoot the problem:

• Verify your entries for the Stored Procedure Data Link and the Event Hook.
• Verify that the List Items entries for the Product Family list include all necessary information.
• Verify that each engineer is, in fact, in the department shown in Extra Data of the list item.
• Verify that the stored procedure operates correctly by executing it from SQL Server Query Analyzer. Executing a line like the following will run the stored procedure and return the data:

Step 6 - Finishing Touches

Apply finishing touches as described in Implementation Example 1 above.