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 Categ
ory 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.

 

© 2000 Jonathan Stars

< Back to In the News

 

 
   
Email us here.