Tap Into the New
and
Improved Power of
Value Lists
Let's say you want to limit what appears in a value list based on what has been selected in another field. Here's an easy way to do it in FileMaker Pro 5 (and later).
By Jonathan Stars
PRODUCT: FileMaker Pro 5.0 and later
Early last year, I wrote an article about what I called cascading pop-ups. Little did I know at that time what FileMaker, Inc. had in store for us with the release of FileMaker Pro 5. (This will work with later versions of FMP, too.)
In FileMaker Pro, you can create a value list for text, number, date, or time values. When you attach a value list to a field, you can help save time and prevent incorrect data entry. A value list can appear as check boxes, radio buttons, as a pop-up list, or a pop-up menu.
You use cascading pop-ups or conditional value
lists when you want to limit what appears in a value list based on
what's been selected in another field. In earlier versions of
FileMaker Pro, developers had to employ one of a number of methods
involving complex scripting and/or intermediate files. It was both
interesting and challenging to create these solutions, but it was
also time-consuming.
The new Only Related Values radio button in the
Specify Fields for Value List dialog makes this part of a solution
a great deal easier. But it still may require a bit of
guidance--especially for users who haven't spent much time with
value lists. That's what I show you in this article. You need a
FileMaker Pro 5 to follow along with this project.
GETTING STARTED
I want to review the purpose of the solution
before I take you through the steps of building the files. Here
are a few examples.
At my wife's bakery, if a customer orders a
cake with two layers, that customer can choose two different
flavors. But a single layer cake can only have one flavor. Here's
what must happen to make the order form handle this properly:
After the employee chooses "single layer" under Cake Size, the
pop-up choices in the second flavor field must be empty.
Another example is the use of different
delivery services. The choices and divisions by size and weight
and the shipping zones change for the different carriers. After
you choose UPS as the carrier, you only want the list of UPS zones
to appear in the zone field. Anything else would require employees
get extensive training in how to use the zone and price charts for
the various carriers.
For this solution, you only need two files. The
original solution requires a third buffer file that is unnecessary
this time. You use the first file to store the values for the
changing pop-up lists. The second file is where most of the
day-to-day work will be done. That's where you'll make your item
selections. As you work through a set of fields with pop-up menus,
the choices you make in one field change what pops up in the next
field.
THE FILES
Open FileMaker Pro 5 and start a new file by
selecting File > New Database... Name this file VALUES.FP5 and
click on Save. When the Field dialog appears, create these fields:
|
Field Name
|
Type
|
Options
|
|
Category
|
Text
|
|
|
Item
|
Text
|
|
|
Topping
|
Text
|
|
|
Constant
|
Calculation
|
= 1
|
The Constant field is used to build
relationships between the files without being concerned about a
specific match field. Click on OK followed by Done.
After the screen clears, you'll be in Browse
mode. Select View>Layout Mode, then choose Layouts>New
Layout/ Report... This brings up the new Layout Assistant, which
will give me an opportunity to take you on a brief tour. Call the
layout Value List, Select Columnar list/report as the Layout type,
then click on the Next button. Be sure Columnar List/Report is
selected, then click on the Next button. Click on the Move All
button, remove the Constant field from the Layout fields list by
double-clicking on it, then click on the Next button.
In the Sort dialog, move each of three fields
into the Sort order list by double-clicking on them. Leave them in
Ascending order and click on the Next button. In the Theme dialog,
choose Brick Screen and click on the Next button. In the Header
and Footer Information dialog, look at the Header section. Above
the words "Top center," click on the word None, choose Layout Name
from the pop-up, and click on Next.
Click on the Create a Script radio button, call
the script List, and click on Next. Choose the View the Report in
Layout mode radio button and click on Finish. Of course, the Sort
Order and the Header and Footer information are not required to
build the layout. I wanted to call attention to them during this
limited tour.
This should put you in Layout mode. You might
want to move the Value List text in the Header to the left to
center it. You also should expand the Topping field to accommodate
the longer items you'll enter. When you finish, your layout should
resemble figure 1.
Figure 1:Field Layout For
Value.FP5--Move the fields around
in the VALUES.FP5 file until they resemble this layout.
Select View > Browse Mode and enter the data
from figure 2. After you enter the data in the first record, you
can just choose Records > Duplicate Record and change the
Topping. This helps keep your data entry consistent.
This time, I chose a different way to enter
data in the Values file. In my last article, I tried to be
economical by using just two fields. But some readers were
confused by the arrangement. What's nice about having a separate
field for each pop-up group is you can enter all combinations in
any order you want. Then, when you sort by the fields in order
from left to right, you can see exactly what your pop-ups will
look like at a glance.
The main
file
Now, create the file where the cascading
will happen. Select File > New Database... Call it CASCAD.FP5
and create the following fields:
|
Field Name
|
Type
|
Options
|
|
Order Number
|
Number
|
Auto Enter Serial
|
|
Category
|
Text
|
|
|
Item
|
Text
|
|
|
Topping
|
Text
|
|
|
Constant
|
Calculation
|
=1
|
Double-click on the Order Number field to call
up the Options... dialog. Be sure you're in the Auto Enter tab
(not Validation) and check the box next to Serial number. Click on
OK, then Done to close the Define Fields window.
Choose View > Layout Mode, then choose
Layouts > New Layout/Report... Call the layout Orders, select
Columnar list/report as the Layout type, then click on the Next
button. Be sure Columnar List/Report is selected and click on the
Next button. Click on the Move All button, remove the Constant
field from the Layout fields list by double-clicking on it and
then click on the Next button.
|
Match (field)
|
Item
|
Topping
|
|
Cakes
|
9 x 13
|
Cherry
|
|
Cakes
|
9 x 13
|
Orange
|
|
Cakes
|
8 inch Single
|
White
|
|
Cakes
|
8 inch Single
|
Marble
|
|
Cookies
|
Chocolate Chip
|
Granulated Sugar
|
|
Cookies
|
Chocolate Chips
|
Fudge
|
|
Cookies
|
Sugar Cookies
|
White Icing
|
|
Cookies
|
Sugar Cookies
|
Chocolate Icing
|
|
Bread
|
White
|
Cheese
|
|
Bread
|
White
|
Corn Meal
|
|
Bread
|
Wheat
|
Butter Glaze
|
|
Bread
|
Wheat
|
Wheat Flour
|
Figure 2: Example
data --Enter this data in your
VALUES.FP5 file. These are the values that will appear in the
pop-ups.
In the Sort dialog, just click on the Next
button. In the Theme dialog, choose Blue & Gold Screen and
click on the Next button. In the Header and Footer Information
dialog, look at the Header section. Above the words "Top center,"
click on the word None, then choose Layout Name from the pop-up,
then click on Next. Click on the Create a Script radio button,
call it Orders, and click on Next. Choose the View the Report in
Layout Mode radio button. Then, click on Finish. This should put
you in Layout mode.
You might want to move the Orders text in the
Header to the left to center it. You also should expand the
Topping field to accommodate the longer items you enter. When you
get done, your layout should resemble figure 3.
Figure 3:
Field Layout for
CASCAD.FP5--Layout your fields so
they resemble this.
Value
lists
At this point, you can create the
Relationships. But I want to show you how to do that from within
the field format dialog. If you aren't already there, select View
> Layout Mode. Click on the Category field and select Format
> Field Format... Click on the Pop-Up List radio button. Look
to the right and click on <No Lists Defined>. Pull down to
Define Value Lists... and let go to bring up the Define Value
Lists dialog. Click on the New... button and call it Category.
Click on the Use values from field: radio
button. This brings up a dialog called Specify Fields for Value
List "Category." Click on the new Only related values: radio
button (figure 4). Look to the right and click on <unknown>,
which turns into Define Relationships, and brings up the
Relationships dialog. (This dialog is also available under the
File > Define Relationships... menu.) Click on the New...
button. In the File dialog, work your way through the file
hierarchy until you find the VALUES.FP5 file and open it. In the
Edit Relationship dialog, call this Relationship Category
List.
Figure 4:
Specify Fields For Value List
Dialog--This screen shot shows
the new "Only related values:" radio button in FileMaker Pro 5.
Click to highlight the
Constant field in both columns and click on OK. Click on the
New... button and create the other Item List and Topping List
Relationships until your dialog looks like the figure 5.
When you're finished, click on Done to get back
to the Specify Fields for Value List dialog. Make sure Category
List appears in the Only related values: pop-up and click
::Category in the Use values from this field: column on the left.
Click on OK, click on OK again, click on Done, and click on OK one
final time. As you can see, there are quite a few nested dialogs
to go through to make this work. You could have created the
relationships in advance by choosing File > Define
Relationships. But I wanted you to see this method because
FileMaker allows you to get to various dialogs whereever you need
them.
Figure 5:
Relationships--This
is how the Define Relationships dialog should look in the
CASCAD.FP5 file just before you close it.
Repeat this procedure
for the Item field and call this list Item. When you get to the
Specify Fields for Value List "Item" dialog, choose Item List from
the pop-up menu and Item in the left column.
And, finally, repeat the steps for the Topping
field and call this list Topping. When you get to the Specify
Fields for Value List "Topping" dialog, choose Topping List from
the pop-up and Topping in the left column. Click on all the OKs
and Done buttons until you're back in Layout mode.
Check it
out!
There are a couple of issues yet to deal with,
but you can try it out now. Select View > Browse Mode and click
in the Category field. You should see the list Bread, Cakes,
Cookies pop-up. Notice that even though you've entered each of
these Categories three times in the VALUES.FP5 file, they only
appear once here. Make a choice and go to the Item field. You
should get an appropriate list of three items. Go back and make a
different choice in Category. Then, see how the pop-up changes for
the Item field. Move on to the Topping field. Create a new record
and make a choice there.
Some spit and
polish
After testing the solution as it is so far,
your file might look like figure 6. You may have noticed a few
problems. You don't want anybody accidentally changing or deleting
the Order Number. You can prevent that by making it a
non-enterable field.
Figure
6:ORDERS--Your
CASCADE.FP5 might look like this after entering data into a few
records.
Select View > Layout Mode. Click on the
Order Number field and choose Format > Field Format... Near the
bottom of the dialog, you'll see the Behavior area. Click on the
box next to Allow Entry into Field to remove the check mark. Click
on OK, then choose View > Browse Mode.
This is just one way
to handle it. Another alternative is to go into Define Fields and
check the box next to Prohibit Modification of value in the
Auto-Enter tab. You might also have noticed that as you tab
through the other fields, it could be confusing to the user when
the first list pops up again. More importantly, if the user goes
back and changes the Category or Item, it's possible to have an
improper combination of data if the Topping doesn't change.
Imagine if a user had entered Cookie - Chocolate Chip &endash;
Fudge, then changed Cookie to Bread.
What an order that would make! For that
reason, it's very important to create some scripts and attach them
to the fields. The scripts clear any fields that follow them. When
I say "follow," I'm thinking in terms of making selections working
from left to right in the form. If your users make a selection,
then want to go back (to the left) to make a change, all choices
to the right of the changed field should be cleared because those
options might not be avail-able after they make the new
selection.
Script
fixes
Under the Scripts menu, select ScriptMaker.
Note there's already a script created in the Layout Assistant.
Create a script called Enter Category. Click on the Clear All
button and enter these steps:
Set Field ["Item", """"]
Set Field ["Topping", """"]
Go to Field ["Category"]
Make sure the Select/Perform check box in the Options area is
deselected. Click on OK and make a Script called Enter Item. Clear
All the automatic steps and enter these:
Set Field ["Topping", """"]
Go to Field ["Item"]
Make sure the Select/Perform check box in the Options area is
deselected.
Click on OK and remove the check marks in front
of the two new Script names. Click on the Done button to exit
ScriptMaker. Choose View > Layout Mode. Click on the Category
field and choose Format > Button... In the left column, under
the Control heading, choose Perform Script. In the Options area,
choose Enter Category from the pop-up list next to Specify, then
click on OK. Then, do the same to the Item field, but choose the
Enter Item Script. Go back to Browse mode and try that out. Notice
that if you have all three fields of an order filled in, and click
on either Category or Item, the correct fields empty out to the
right.
Use the tab key to tab through the fields. Uh,
oh! Tabbing defeats the Scripts. Choose View > Layout Mode.
Then, choose Layouts > Set Tab Order. Click on the radio button
next to Create new tab order and click on OK. (If you're working
on a layout with other fields, you would just want to remove the
numbers from the tab order arrows that refer to the fields with
the scripts attached.) In the next dialog, click on Omit and
return to Browse mode. Now there's no way around the scripts.
This particular demonstration file only has
three fields where choices are made. Theoretically, there's no
limit to the number of fields that can cascade off one another in
this way. The most difficult part of the process is entering all
that data in the VALUES.FP5 file.
Meanwhile, back at the
bakery
Ah, and now about that problem I discussed
earlier. Remember the single layer cakes that weren't allowed a
second flavor? I can demonstrate something similar in this example
by including items that have no topping.
Go to the VALUES.FP5 file. Add a new record and
enter Cakes as the Category. Then enter Fruitcake as the Item and
leave the Topping field empty. Go back to the CASCAD.FP5 file and
create a new record. Choose Cake as the Category and Fruitcake as
the Item. You'll notice that the Topping pop-up list is blank.
Perfect!
Except that now, the user can click in the
empty field and enter something by hand. In that case, you can
choose to make it a Pop-up Menu instead of a Pop-up List. When
users click on the list, they'll be presented with <no values
defined>. If you think it would be clearer to your users, you
might go back into the VALUES.FP5 file and add None Available or
NONE to the Topping field. The user could either choose that as a
selection or click off the field and leave it blank. (I don't
think any of the decorators would spend much time looking for a
bucket of NONE topping.)
In fact, you might want to make all three
fields into Pop-Up Menus. It might be a little irritating because
users will have to click on the fields twice--once to activate the
script, and then again to make their choice from the list. Ah, the
price we must pay to control the inconsistencies of data
entry.
Disadvantages
You can't control the order of the items that
appear in the pop-up. They'll be in alphabetical or numerical
order. If you use Pop-up Lists, it's also a hassle that users have
to click to get into the fields. That means tabbing isn't allowed.
Many data-entry people dislike taking their fingers off the
keyboard. On the other hand, how many of these special pop-ups
would you normally need in a data-entry file? Probably not enough
to cause too much stress.
Depending on how often data will be entered in
the VALUES.FP5 field, you might want to add a few pop-ups there as
well. Go to the VALUES.FP5 file and choose View > Layout Mode.
Click on the Category field and choose Format > Field Format.
Click on the radio button next to Pop-up Lists, click on <No
Lists Defined> and drag down to Define Value lists. Click on
the New button, and in the next dialog name the list Category.
Click on the radio button next to Use Values from Field. In the
next dialog, click to highlight Category in the list on the left.
Click on OK, click on OK again, Click on Done, and click on OK
again. Try it out in Browse mode. To add new categories, all you
have to do is click in the field to bypass the pop-up and add a
new item. It will appear in the list from then on.
So there you have it: An interesting new
addition to FileMaker Pro 5.0. It might take a couple times around
the block to get comfortable with this technique. But it should be
a valuable tool in your arsenal.
< Back to In the
News
