LOOKUP: Retrieving a Value From a Cross-referenced Data Source

LOOKUP(field);

Example: Reading a Value From a Cross-referenced Data Source

You may need to determine if employees were hired before or after a specific date, for example, January 1, 1982. The employee IDs (EMP_ID) and hire date (HIRE_DATE) are located in the host segment. The following diagram shows the file structure:

The request is:

MODIFY FILE EMPLOYEE
PROMPT EMP_ID ED_HRS
COMPUTE  EDTEST = LOOKUP(HIRE_DATE);
   COMPUTE ED_HRS = IF DATE_ENROLL GE 820101 THEN ED_HRS * 1.1
         ELSE ED_HRS;
MATCH EMP_ID
   ON MATCH UPDATE ED_HRS
   ON NOMATCH REJECT
DATA

A sample execution is:

1. The request prompts you for the employee ID and number of class hours. Enter the ID 117593129 and 10 class hours.
2. LOOKUP locates the first instance in the cross-referenced segment containing the employee ID 117593129. Since the instance exists, the function returns a 1 to the EDTEST variable. This instance lists the enroll date as 821028 (October 28, 1982).
3. LOOKUP retrieves the value 821028 for the DATE_ENROLL field.
4. The COMPUTE command tests the value of DATE_ENROLL. Since October 28, 1982 is after January 1, 1982, the ED_HRS are increased from 10 to 11.
5. The request updates the classroom hours for employee 117593129 with the new value.

Example: Using a Value in a Host Segment to Search a Data Source

You can use a field value in a host segment instance to search a cross-referenced segment. Do the following:

• In the MATCH command that selects the host segment instance, activate the host field with the ACTIVATE command.
• In the same MATCH command, code LOOKUP after the ACTIVATE command.

This request displays the employee ID, date of salary increase, employee name, and the employee position after the raise was granted:

• The employee ID and name (EMP_ID) are in the root segment.
• The date of increase (DAT_INC) is in the descendant host segment.
• The job position is in the cross-referenced segment.
• The shared field is JOBCODE. You never enter a job code; the values are stored in the data source.

The request is:

MODIFY FILE EMPLOYEE
PROMPT EMP_ID DAT_INC
MATCH EMP_ID
   ON NOMATCH REJECT
   ON MATCH CONTINUE
MATCH DAT_INC
   ON NOMATCH REJECT
   ON MATCH ACTIVATE JOBCODE
   ON MATCH COMPUTE
      RTN = LOOKUP(JOB_DESC);
   ON MATCH TYPE
      "EMPLOYEE ID:        <EMP_ID"
      "DATE INCREASE:      <DAT_INC"
      "NAME:               <D.FIRST_NAME <D.LAST_NAME"
      "POSITION:           <JOB_DESC"
DATA

A sample execution is:

1. The request prompts you for the employee ID and date of pay increase. Enter the employee ID 071382660 and the date 820101 (January 1, 1982).
2. The request locates the instance containing the ID 071382660, then locates the child instance containing the date of increase 820101.
3. This child instance contains the job code A07. The ACTIVATE command makes this value available to LOOKUP.
4. LOOKUP locates the job code A07 in the cross-referenced segment. It returns a 1 the RTN variable and retrieves the corresponding job description SECRETARY.
5. The TYPE command displays the values:

   EMPLOYEE ID:          071382660
   DATE INCREASE:        82/01/01
   NAME:                 ALFRED STEVENS
   POSITION:             SECRETARY

Fields retrieved by LOOKUP do not require the D. prefix. FOCUS treats the field values as transaction values.

You may also need to activate the host field if you are using LOOKUP within a NEXT command. This request displays the latest position held by an employee:

MODIFY FILE EMPLOYEE
PROMPT EMP_ID
MATCH EMP_ID
   ON NOMATCH REJECT
   ON MATCH CONTINUE
NEXT DAT_INC
   ON NONEXT REJECT
   ON NEXT ACTIVATE JOBCODE
   ON NEXT COMPUTE
      RTN = LOOKUP(JOB_DESC);
   ON MATCH TYPE
      "EMPLOYEE ID:      <EMP_ID"
      "DATE OF POSITION: <DAT_INC"
      "NAME:             <D.FIRST_NAME <D.LAST_NAME"
      "POSITION:         <JOB_DESC"
DATA

Example: Using the LOOKUP Function With a VALIDATE Command

When you use LOOKUP, reject transactions containing values for which there is no corresponding instance in the cross-reference segment. To do this, place the function in a VALIDATE command. If the function cannot locate the instance in the cross-referenced segment, it sets the value of the return variable to 0, causing the request to reject the transaction.

The following request updates an employee's classroom hours (ED_HRS). If the employee enrolled in classes on or after January 1, 1982, the request increases the number of classroom hours by 10%. The enrollment dates are stored in a cross-referenced segment (field DATE_ATTEND). The shared field is the employee ID.

The request is as follows:

MODIFY FILE EMPLOYEE
PROMPT EMP_ID ED_HRS
VALIDATE
   TEST_DATE = LOOKUP(DATE_ENROLL);
COMPUTE
   ED_HRS = IF DATE_ENROLL GE 820101 THEN ED_HRS * 1.1
         ELSE ED_HRS;
MATCH EMP_ID
   ON MATCH UPDATE ED_HRS 
    ON NOMATCH REJECT
DATA

If an employee record is not found in the cross-referenced segment, that employee never enrolled in a class. The transaction is rejected as an error.