LOOKUP (Field you want to see, primary keys to that field's record, …)

Select the field you want to see from the additional record where the field exists. Select the primary key fields to that additional record from the processing record. For more information about finding the primary keys for a record, see the attachment below.

Use Example 1 for an example of a Property Management lookup, use Example 2 for an example of an Accounts Payable lookup, or use the attachment "Examples of lookup formulas to use in Payroll" for Payroll examples.

Example 1

Suppose you have a report processing off the PM Unit record that displays the current tenant, and you want to add the current tenant's address information. You cannot directly add the tenant address fields to the report because the PM Unit record cannot automatically access the PM Tenant Address record. Use a LOOKUP function to tell the report how to find the tenant address information. See the example LOOKUP function that you could use in a formula below:

LOOKUP (Address line 1 [PM Tenant Address], Current Tenant [PM Unit], "Billing")

The first field in the LOOKUP function is the Address line 1 field from the additional record (PM Tenant Address). This is the field that the function returns. The primary key values for the PM Tenant Address record are Tenant, Tenant Address Type. You can use the Current Tenant field from the processing record (PM Unit) for the Tenant key value. Since the processing record does not have a field for the address type, you can enter a constant for the Tenant Address Type key value. In this example, we used the billing address.

Example 2

Suppose you have a report processing off the AP Transaction record and you want to add the Job to the report. You've decided to use a lookup rather than simply added the Job from the AP Distribution record. For this example your formula is:

LOOKUP (Job [AP Distribution], Vendor [AP Transaction], Invoice [AP Transaction], Dist Seq [AP Transaction])

The first field in the LOOKUP function is the Job from the additional record (AP Distribution). This is the field that the function returns. The primary key values for the AP Distribution record are Vendor, Invoice, Distribution Sequence.