User manual

Working with related files 8-13
2. Define a formula for the field that includes an aggregate function
for the type of summary calculation you want to perform. (See
“Aggregate functions” on page 11-7.)
For example, for a Total field, which calculates the total of values in
the related field Price (which is in a portal and uses the relationship
Line Items), define the following formula:
Sum(Line Items::Price)
Defining lookups between files
Important Before you begin, be sure you understand the information
in “Relational databases and lookups: a comparison” on page 8-7.
Defining lookups
1. Define a relationship for the lookup (see “Defining relationships
for relational databases and lookups” on page 8-10).
2. In the master file, choose File menu > Define Fields.
3. In the Define Fields dialog box, double-click the lookup
destination field (the field in the master file to copy data to). Or
create a field, then double-click it.
4. Click the Auto-Enter tab, then select Looked-up value.
5. In the Lookup dialog box, choose the relationship to use from the
relationship list.
6. In the list of field names, select the lookup source field (the field
in the related file to copy data from).
7. Select options for the lookup.
8. Click OK, click OK again, then click Done.
Keep these points in mind:
1 Be sure the lookup source field and lookup destination field are the
same field type; otherwise, you might get unexpected results.
1 To change a lookup, in the Auto-Enter options, be sure Looked-up
value is selected, click Specify, then make your changes. To stop or
suspend using a lookup, clear Looked-up value in the Auto-Enter
options.
1 When data in the related file changes and you want the master file
to contain the new values, you can look up the data again. For more
information, choose Help menu > Contents and Index, click the Index
tab, and type updating data.
To Do this
Determine what to do when
values in the match fields do not
match exactly
For If no exact match, then, select an option to
copy no value, copy the next lower or next
higher value that’s in the lookup source field,
or display a custom message or data. (In the
text box, enter the message or data, up to
255 characters.)
Prevent null (empty) data in the
lookup source field from being
copied to the lookup destination
field
Select Don’t copy contents if empty. (Clear this
option to allow empty data to be copied.)
To Do this
Select an option
for non-equal data
in match fields
Select the
lookup
source field
Choose the
relationship to
use, or define a
new one
When selected, prevents null
data from being copied