This function reads a field value from a remote table and can access any table in the current dictionary. It is used for accessing data in a table which is not hierarchically or relationally linked to the current table.

The most basic use of the function is for ancestral lookups (into grandparent tables etc) using the following format:

Lookup (FieldName)

Where FieldName is a field in an ancestor table. The current record at each hierarchical level is located, until the required field is located. The field value is then returned.

An additional use is for lookups into completely unrelated tables/ databases, using the following format:

Lookup (FieldName, SearchExpression, IndexName)

Again, FieldName is a field in the remote table. IndexName is an index in the remote table and SearchExpression is the index value of the required record, which may be a MakeKey value. Equinox automatically locates the correct table, then uses SearchExpression and IndexName to locate the appropriate record. The value held in FieldName is then returned from that record.

The function is also capable of finding a remote record within a given range, using the following format:

Lookup (FieldName, SearchExpression, IndexName, operator [,LimitfieldName])

In this case, you specify the target record by assigning operator one of the following values, which finds the first remote record which has a value in index IndexName which is:

• LT less than SearchExpression
• LE less than or equal to SearchExpression
• GT greater than SearchExpression
• GE greater than or equal to SearchExpression
• EQ equal to SearchExpression
• QE greater than or equal to SearchExpression, searching through the index in reverse order (ie last matching record).

LimitfieldName specifies a second field in the remote table (or home table, if required). If it is included, then a further test is made between SearchExpression and LimitfieldName, using the reverse procedure specified by operator. If both tests pass, then the value held in FieldName is then returned. The best way to see how this works is to study the following examples. Note that the second test is not made if operator is EQ or QE.

Note that:

• If required, you may use this function to read from a record in the current table.
• You cannot use this function to return a picture field value.

After the function is executed, the SysError system workarea will contain one of the following values:

The first example checks that a product is not being sold back to the supplier. It is used in a hierarchical database containing Customer (parent), Order (child) and Item (grandchild) tables and is attached to the Item table. The Lookup function is used to reference the Customer (grandparent) table.

if Lookup(customer) = Supplier then

Alert "You are selling this item To the supplier!"

end if

The next method (also attached to the Item table) reads the commission out of the rates table. There is no dictionary link, so the method uses the Lookup function to make its own adhoc relational link.

ItemCommission = Lookup(CommRate,ItemCode,iCommission)

The company using this system uses accounting periods which do not match calendar months. The appropriate period number must be stored with each invoice.

The period start and end dates are held in a table. The last method matches an invoice date against this table and returns the number held in the PeriodNo field. The Lookup statement finds the first record where the invoice date is greater than or equal to the period start date and less than the period end date.

period = Lookup(PeriodNo,InvoiceDate,PeriIdx,le,PeriodEnd)

if syserror then

ReportError 35,"Accounting period table out of Date",5

end if