FILEMAKER PRO ADVISOR - OCTOBER/NOVEMBER 1998

Simplify Your Solutions with Cascading Pop-ups

By Jonathan Stars

PRODUCT FileMaker Pro 4.0/3.x


My first relational project was putting together a solution for my wife's bakery. People who ordered cakes with two layers could have a different flavor for each layer. At one point we realized that unless a cake had two layers we couldn't allow them to choose a second flavor. Some of the order-taking employees just couldn't seem to grasp the concept. We kept wishing there were a way to make the pop-ups change depending on what was entered in the first field. If the customer chose a single layer cake, the second layer field would show a pop-up that was blank. Thus was born the idea of Cascading pop-ups. (Some people call them Conditional pop-ups. But I really like the word Cascading. It just reminds me of waterfalls which just seems to take some of the drudgery out the day. Why not?!) The idea is this; As you work your way through a set of fields with pop-up menus, the choices you make in one field will change what pops up in the next field. I've heard about developers needing a solution like this for shipping choices. Each carrier has different requirements, delivery times and fees.

As is the case with so many of us developer/inventors, I wasn't the only one looking for an answer to the problem. I've seen a number of interesting solutions out on the web. There are limitations and advantages to each. This is the one I came up with.

For this solution you'll need three files. You'll use the first file to store the information for the changing pop-up lists. The second file is sort of a buffer file. It will only contain one record. The third file is where most of the day to day work will be done. That's where you'll make your item selections. When you choose an item in one of the fields in the work file, lookups are triggered in the buffer file. All of that will become more obvious later, so let's get started.

THE FILES

Open FileMaker and start a new file by selecting File > New... Name this file VALUES.FP3. When the field dialog appears onscreen, create the following two fields;

Field Name	Type
Match	Text
Choices	Text

Double click on the Match field to bring up the Entry Options window. In the upper left corner click on Auto Enter and select Validation. Click the boxes next to Unique, Strict: Do not allow user to override validation and Display custom message if validation fails. In the custom message box type; "This is not a unique name. You might add a letter, a number or another word to make it unique." This is very important because the same Match field is used by multiple pop-ups. If there is a duplicate in the Match field, FileMaker will find the first match and display the list next to it. That just won't do. Click OK followed by Done.

After the screen clears you'll be in Browse mode. Choose Mode > Layout and move the fields and field names until they resemble the layout in Figure 1.

Figure 1: FIELD LAYOUT FOR VALUES.FP3--Layout your fields so they resemble this.

Now enter the data from Table 1. Put a return between each entry in the Choices field. Also be sure you spell everything correctly. Missing one letter will cause a mismatch later on and the lists won't be looked up properly presenting the user with an empty pop-up.

Match (field)	Choices (field)
Cakes	9 x 13 8 inch Single 10 inch Round
9 x 13	Cherry Orange German Chocolate
8 inch Single	White Marble Chocolate
10 inch Round	Carrot Chocolate Raspberry Pineapple
Cookies	Chocolate Chip Sugar Cookies Ginger Snaps
Chocolate Chip	Granulated Sugar Fudge Mini Chocolate Chips
Sugar Cookies	White Icing Chocolate Icing Strawberry Icing
Ginger Snaps	Powdered Sugar Royal Icing Raisins
Bread	White Wheat Cheese
White	Cheese Corn Meal Flour
Wheat	Butter Glaze Wheat Flour Honey
Cheese	Shredded Cheddar Garlic Parsley

Table 1: DATA FOR VALUES.FP3 FIELDS--This is the example data to enter into the fields for the VALUES.FP3 file.

 

Choose File > New... and create the BUFFER.FP3 file with the following fields;

Field Name	Type	Options
Connect	Calculation	= 1
Match	Text
Popups	Text

Click on the Popups field to hi-lite it. Now click on the Options... button. Be sure you're set to Auto Enter (not Validation) and check the box next to Looked-up value. The Lookup dialog window will appear. In the upper right corner you should see <unknown>. Click and pull down to Define Relationships. Click the New... button. Use the dialog to find and open the VALUES.FP3 file. Choose the Match field on both sides of the dialog box and click OK. When you're done it should look like this;

Relationship	Relationship Name	Related File
VALUES	Match = ::Match	VALUES.FP3

Click the Done button to dismiss the Relationships dialog. On the left side of the Lookup dialog you should see the list of fields from the VALUES.FP3 file. Click on the ::Choices field. Now at the bottom left of the dialog uncheck the Don't copy contents if empty box. On the right side of the dialog click the radio button next to Use, but leave the box to the right of the button empty. Click OK, click OK again and finally click Done to close the Define Fields window. Since you won't have any need to look at this file, you don't need to pay any attention to the layout.


THE MAIN FILE

Now we'll create the file where all this cascading stuff will happen. Choose File > New... Call it CASCAD.FP3 and create the following fields;

Field Name	Type	Options
Choice 1	Text
Choice 2	Text
Choice 3	Text
Connect	Calculation	= 1
Order Number	Number

The Connect field is used to build relationships between the files without being concerned about a specific match field.

Double click on the Order Number field to call up the Options... dialog. Be sure you're set to Auto Enter (not Validation) and check the box next to Serial number. Click OK, and then Done to close the Define Fields window.

Choose File > Define > Relationships... Click the New... button. Use the file dialog to find and open the BUFFER.FP3 file. Select the Connect field on both sides of the dialog box, then click OK. The dialog should look like this;

Relationship	Relationship Name	Related File
BUFFER	Connect = ::Connect	BUFFER.FP3

Click the Done button to dismiss the Relationships dialog.

SCRIPTING MAGIC

Under the Scripts menu choose ScriptMaker... and create a script called Enter Choice 1. Click the Clear All button and enter the following steps;

Set Field ["Choice 2", """"]
Set Field ["Choice 3", """"]
Set Field ["BUFFER.FP3::Match",""""]
Set Field ["BUFFER.FP3::Popups",""""]
Go to Field ["Choice 1"]

Click OK and make a script called Enter Choice 2. Clear All of the automatic setps and enter these;

Exit Record/Request
Set Field ["Choice 3", """"]
If ["IsEmpty(Choice 1)"
   Beep
   Show Message ["You can't choose something here until you make a selection in Choice 1"]
   Halt Script
Else
   Set Field ["BUFFER.FP3::Match", "Choice 1"]
 If ["IsEmpty(BUFFER.FP3::Popups)"]
      Beep
        Show Message ["No Choices"]
        Halt Script
    End If
    Go to Field ["Choice 2"]
End If

[NOTE: in this next Script, it calls for a Sub-script that hasn't been created yet. That will Script appears after this one. You'll have to create it first in order to call for it from here.]
Click OK, create the final script and call it Enter Choice 3;

Exit Record/Request
If ["IsEmpty(Choice 1) or IsEmpty(Choice 2)"]
Beep
Show Message ["Can't choose something here until you make selections in Choice 1 and Choice 2."]
Halt Script
Else
Copy ["Choice 2", Select entire contents]
Perform Script [Sub-scripts, "Find Level 2 match", "External: "VALUES.FP3""]
Refresh Window [Bring to front]
If ["IsEmpty(BUFFER.FP3::Popups)"]
Beep
Show Message ["No Choices"]
Halt Script
End If
Go to Field ["Choice 3", Select/perform]
End If

Click OK, then click the Done button to exit the ScriptMaker.

In the VALUES.FP3 you'll have to create the following script. Call it Find Level 2 match;

Set Error Capture [On]
Enter Find Mode []
Set Field ["Level Indent", "2"]
Paste Literal [Select, "Match", "=="]
Paste ["Match"]
Perform Find []
If ["Status(CurrentFoundCount) = 0"]
Find All
Set Field ["BUFFER::Match", """"]
Set Field ["BUFFER::Popups", """"]
Else
Set Field ["BUFFER::Match", "Match"]
End If
Toggle Window [Hide]


THE LAYOUT

Choose Mode > Layout and move your fields and field titles around until they resemble Figure 2.

  Figure 2: FIELD LAYOUT FOR CASCAD.FP3--Layout your fields so they resemble this.

 

Select all four fields and choose the Format > Field Format... menu. Near the bottom of the Field Format dialog uncheck the Allow entry into field box. and click OK. Because you'll be turning the three Choice fields into buttons, it's very important that the fields can only be entered by clicking on them. The scripts trigger the necessary lookups.

This brings up a separate issue. These pop-up choices should be on a layout all by themselves with no other enterable fields anywhere in sight. Otherwise as soon as your user makes a selection, the cursor will jump to the first tab-able field. That would be just too irritating. I'm speaking from experience here because that's how this solution worked when I first tried it.

Click anywhere off the fields to deselect the, then click the Choice 1 field. Select Format > Button... and click on Perform Script in the Specify Button dialog. In the Options area click on <unknown> and pull down to the Enter Choice 1 script then Click OK.

Do the same for the Choice 2 and Choice 3 fields selecting the Enter Choice 2 and Enter Choice 3 scripts respectively.

THE POP-UPS

Click on the Choice 1 field again. Select Format > Field Format... In the dialog window click the radio button next to Pop-up list. Click on <No Lists Defined> and pull down to Define Value Lists... Type Choice 1 and click the Create button. Now type Cakes <return> Cookies <return> and Bread and click the Save, Done and finally the OK buttons.

Click on the Choice 2 field. Select Format > Field Format... In the dialog window click the radio button next to Pop-up list. Click on <unknown> and pull down to Define Value Lists... Type Choice 2 and click the Create button. In the bottom left corner of the dialog click the radio button next to Use values from a field. In the next window click the Specify File... button and find and open the BUFFER.FP3 file. Click on the Popups field to select it and click the OK button. Now click Save and Done and then the OK buttons.

Click on the Choice 3 field. Select Format > Field Format... In the dialog window click the radio button next to Pop-up list. Click on <unknown>, select Choice 2 and click OK.

TEST IT OUT

Now enter browse mode. The first record should already be created. Click on the Choice 1 field and choose one of the items. Click on the Choice 2 field and notice the available selections. Go back and change the first Choice. Notice that Choice 2 is now empty. Click on Choice 2 again and check out the different pop-ups available. Make a selection and move on to the Choice 3 field. Create a new record and make a whole new set of choices.

It is very important that the scripts clear any fields that follow them. When I say "follow" I'm thinking in terms of making selections working left to right in the form. If your users makes a selection and 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 available once the new selection is made.


HOW IT WORKS

When you make a selection in Choice 1 and click on Choice 2, the script attached to Choice 2 sets the selection in Choice 1 into the Match field in the BUFFER.FP3 file. That triggers a Lookup from the VALUES.FP3 file and puts the resulting list in BUFFER.FP3's Choices field. Immediately that list pops up from the Choice 2 field in the CASCAD.FP3 file. After you make your selection in Choice 2 and click on Choice 3, the same thing happens. Except this time your selection from Choice 2 is set into the Match field.

I'm using the same two fields for all the pop-ups. For that reason, some items that appear as Choices for one of the Match items will later end up in the Match field. That way it can provide a sub-pop-up without the need for more fields. This particular demonstration file only has three fields where choices are made. Theoretically there is no limit to the number of fields that can cascade off one another in this way. The most difficult part will be figuring out how to enter the data in the VALUES.FP3 file.

Go back to that file now. Look at the first record with Cakes in the Match field. The second record's Match is 9 x 13 which came from the Choices field in the first record as does the 8 inch Single in the third record and so on until you get to the next primary choice, Cookies. There is a nice solution to this which is included with the online files provided at our web address.

You can also add another file as a front end and have the records show up as line items in a portal. You can download an example of that from our web site, too.


DISADVANTAGES

You can't control the order of items in the pop-up. They'll appear in alphabetical or numerical order. It's also a hassle that you 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.


BACK TO THE BAKERY

Ah, and now about that problem I talked about in the beginning. Remember the single layer cakes that weren't allowed a second flavor? By unchecking the "Don't copy contents if empty" box in the Lookup Options for the Popups field in the BUFFER.FP3 file, it allows you to include items that won't have any choices in that third Choice (or any other) field. Just for fun, go back into the VALUES.FP3 file and remove the three items from the Choices field for the Match item of Cheese. Go back to the CASCAD.FP3 file. Make a new record, select Bread in the Choice 1 field, select Cheese in the Choice 2 field. When you try to make a selection in the Choice 3 field, it's empty! You might even add the following script steps after the Set Field step in the Enter Choice 3 script so your users can't enter the field and type something by hand;

If ["IsEmpty(BUFFER.FP3::Popups)"]
Beep
Show Message ["No Choices"]
Halt Script
End If

I didn't come up with these answers in time to solve the bakery's immediate problem. But I'll be ready for the next customer who has a similar request. Hope this one gives you what you need in time. Or maybe you'll come up with a better answer.

SIDEBAR Other solutions worth a look. Conditional Value Lists Tami Williams creativecomputing 1303 Lakeside Way Atlanta, GA 30319 770-457-3221 pager 259-6782 creativecomputing@mindspring.com Find it at Chris Moyer's web site; http://www.mindspring.com/~fmpro/templates.html A clever solution that only uses two files. EZ Lookups Life $uccess Institute 356 Salem Tpke Bozrah, CT 06334-1518 806-886-7147 lsisales@ctol.net http://www.lifesuccess.org/products.html#EZ Lists Interesting because it uses a timed pause Context Sensitive Popups Eric Scheid <genius@ironclad.net.au> http://www.ironclad.net.au/ This set of files uses stacked fields and a self-join relationship which allows tabbing into fields. Conditional Value Lists - Revisited Bart Bartholomay HAB Marketing P.O. Box 1209 Lake Geneva, Wisconsin 53147 414-248-3634 bart@habmarketing.com http://www.habmarketing.com/Downloads.htm#down8 Check this out out for the complexity of calculations.

 

 

© 1998 Jonathan Stars

** Please write to us - JStarsNearDataDesignPros.com ** That's not a real Email address! What Gives?

< Back to In the News