The “Lookup” custom function script should not appear in the Scripts menu. You invoke it by using the “MACRO” function in a spreadsheet cell or calculated field. The easiest way to do this is to use the “Function...” menu item in the Scripts menu, then choose “Lookup” from the list. It will copy this formula to the clipboard:

MACRO("Lookup",2,"lookup", lookupDB, lookupField, keyField, keyValue)

You can then paste this function into your cell (after an “=” sign), or a database calculation field, and replace the last four parameters with:

Placeholder	Description	Example
lookupDB	Name of the database to lookup.	"Customer"
lookupField	Name of the field to lookup.	"address"
keyField	Name of the field containing the unique identifier (the “key”) in the lookup database.	"ref"
keyValue	Value to match in the key (unique identifier) field in the lookup database. This is usually given as a reference to a field or cell in the local document.	'customer ref'

For example, imagine that you have an Invoice database container the fields: invoice number, date, contact ref, amount, description and so on, and a Contact database containing the fields: ref, full name, address, city, post code etc. Then you can create a calculated field in the Invoice database for each field that you want to lookup in the Contact database.

For example, you can create a “Contact Name” field in the Invoice database as a calculated field, with this formula:

MACRO("Lookup",2,"lookup", "Contact", "full name", "ref", 'contact ref')

AppleWorks will then lookup in Customer the record which has a customer ref matching the ref in the current Invoice record and retrieve the corresponding address value.

A Contact and Invoice database are provided in the Enhancement Pack to demonstrate the above sample. They are in the Scripts Support folder. Here is a snippet:

The “Lookup” function can be placed in any cell or calculated field.

This script only works with AppleWorks 6.

Only registered users are permitted to use the supplied files after 14 days or edit them.

This script is included in the Enhancement Pack for AppleWorks, not supplied separately.

You don’t need to look at or understand the script to use it. This is just for the curious.

To make the script editable, drag and drop it onto the “Show/Hide in Scripts Menu” applet in the Scripts Support folder, then double click to open in your script editor. After closing the script, drop it on the applet again to hide it in the AppleWorks menu.


on lookup(lookupDB, lookupField, keyField, keyValue)
  tell application "AppleWorks 6"
    tell database layer of document lookupDB
      -- set lookupValue to value of field lookupField in first record whose value of field keyField is keyValue
      -- can't simply do above because of AW6 bug returning multiple results in list, so use next two lines:
      set foundRecord to a reference to (first record whose value of field keyField is keyValue)
      set lookupValue to value of field lookupField in foundRecord
      if lookupValue is unavailable then set lookupValue to ""
    end tell
  end tell
  return lookupValue
end lookup

---