Database Reference
In-Depth Information
But what if you don't want to use a portal for creating records? For example, even though
each invoice needs to be attached to a Job record, it doesn't make sense to create an Invoice
in a portal on the Jobs layout. Although you could create an
Invoice
record this way, there's
no good way to create the proper
Line Item
record for the new invoice using that same portal.
You really need to be viewing the Invoice record on a layout that shows Invoice records
when you create the invoice's Line Item records.
Here's the solution: On the Invoices layout, create a new record and then use a value list to
enter the proper Job ID. The value list ensures that only valid values (those from existing Job
records) are entered into the foreign key field in the Invoices table.
Here's how to create the value list you need:
1.
Choose File
→
Manage
→
Value Lists and then click New
.
Name your new value list
All Job IDs
. Since you're likely to have lots of value lists in
a finished database, descriptive names help you keep things straight.
2.
Select “Use values from field.”
The “Specify Fields for Value List 'All Job IDs'” window appears (
Figure 5-18
).
3.
In the pop-up menu under “Use values from first field,” choose Jobs
.
This selects the table that holds the Job ID field.
4.
In the list of fields, select Job ID
.
Job ID is the key field that relates an Invoice record to a specific Job record, so you'll
use that field to make sure the information entered is accurate.
5.
Turn on the “Also display values from second field” checkbox. Then, from the
right-hand field list, choose Name
.
To avoid having to remember Job IDs, display the job's Name, too. That way, you can
easily find the right Job ID. The second field's data won't be entered into the field,
though—it's just for show.
TIP
The “Show values only from second field” option is really handy when you're using the
value list to enter key field values and you don't want people to be confused by a number
that might not mean much to them. They see only the value in the second field, even though
the field really stores the key value.
