In this section:
Available Languages: MODIFY
The LOOKUP function retrieves a data value from a cross-referenced FOCUS data source in a MODIFY request. You can retrieve data from a data source cross-referenced statically in a Master File or a data source joined dynamically to another by the JOIN command. LOOKUP retrieves a value, but does not activate the field. LOOKUP is required because a MODIFY request, unlike a TABLE request, cannot read cross-referenced data sources freely.
LOOKUP allows a request to use the retrieved data in a computation or message, but it does not allow you to modify a cross-referenced data source.
To modify more than one data source in one request, use the COMBINE command or the Maintain facility.
LOOKUP can read a cross-referenced segment that is linked directly to a segment in the host data source (the host segment). This means that the cross-referenced segment must have a segment type of KU, KM, DKU, or DKM (but not KL or KLU) or must contain the cross-referenced field specified by the JOIN command. Because LOOKUP retrieves a single cross-referenced value, it is best used with unique cross-referenced segments.
The cross-referenced segment contains two fields used by LOOKUP:
For example, LOOKUP retrieves all the fields from the segment
RTN = LOOKUP(SEG.DATE_ATTEND);
When using LOOKUP, the MODIFY request reads a transaction value for the host field. It then searches the cross-referenced segment for an instance containing this value in the cross-referenced field:
Is the name of the field to retrieve in the cross-referenced file. If the field name also exists in the host data source, you must qualify it here. Do not include a space between LOOKUP and the left parenthesis.
Note: LOOKUP does not use an output argument.
You can use a field value in a host segment instance to search a cross-referenced segment. Do the following:
This request displays the employee ID, date of salary increase, employee name, and the employee position after the raise was granted:
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:
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
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.
If the LOOKUP function cannot locate a value of the host field in the cross-referenced segment, use extended syntax to locate the next highest or lowest cross-referenced field value in the cross-referenced segment.
To use this feature, create the index with the INDEX parameter set to NEW (the binary tree scheme). To determine the type of index used by a data source, enter the FDT command.
COMPUTE LOOKUP(field action);
Is the name of the field in the cross-referenced data source, used in a MODIFY computation. If the field name also exists in the host data source, you must qualify it here.
Specifies the action the request takes. Valid values are:
EQ causes LOOKUP to take no further action if an exact match is not found. If a match is found, the value of rcode is set to 1; otherwise, it is set to 0. This is the default.
GE causes LOOKUP to locate the instance with the next highest value of the cross-referenced field. The value of rcode is set to 2.
LE causes LOOKUP to locate the instance with the next lowest value of the cross-referenced field. The value of rcode is set to -2.
Do not include a space between LOOKUP and the left parenthesis.
The following table shows the value of rcode, depending on which instance LOOKUP locates:
Exact cross-referenced value located.
Next highest cross-referenced value located.
Next lowest cross-referenced value located.
Cross-referenced value not located.