Conditional Popup Menus in a Portal

This article courtesy of ISO Productions. To subscribe to ISO FileMaker Magazine and receive eight informative articles every month, visit www.filemakermagazine.com. To download the file that comes with this article, click on the BONUS link below.

Conditional Popup Menus in a Portal

BONUS: PORTAL.FP5, POPUP.FP5 & ITEMS.FP5

PLATFORM: Mac/Windows
FileMaker Pro 5.0 Required
ISSUE: 33
By: John Mark Osborne

In the last issue, I demonstrated how to implement a basic conditional value list along with a technique for creating preference files. This feature is very easy to use. That is until you decide to place a conditional popup menu in a portal. This article discusses how to add this capability to your own portals as well as an advanced technique for displaying the contents of a portal as a return separated list.

Using A Value List From Another File

You may have noticed the other new 5.0 value list feature in the Define Value Lists dialog. It's called "Use value list from another file". Its primary use is to use the values from one file in any one of the other multiple files in your solution. This certainly saves time and energy designing and updating value lists. This feature enables you to place a conditional popup menu in a portal.

Using the same example of "car makes and models" as in the previous article in this series, the first popup menu will contain car makes such as Acura, Honda, Ford and Chevrolet. When you select a car make, the second popup menu will only display car models from that car manufacturer. For instance, if you select Ford from the first popup menu, you might see Mustang, Bronco, Taurus and Explorer in the second popup menu. If you select Honda, you will see a completely different list of car models. The only difference for this article is that these popup menus will be available from a row in a portal. Unfortunately, the procedure is not as straightforward as it might first appear.

Since multiple car selections can be made from a single portal row in the PORTAL.FP5 file, the entries need to be stored as related records in a line items file. This file is similar to a line items file in an invoicing solution and has been named ITEMS.FP5. The actual popup menu choices will come from a file called POPUP.FP5. It will contain one record per car model. Take a quick look at the bonus files that accompany this article so you can familiarize yourself with the structure of the three databases.

Creating The Menus

Start by creating the relationship and the portal in the PORTAL.FP5 file. The relationship is fairly simple. It relates to the ITEMS.FP5 file using the Serial number field on the left side and a number field with the same name on the right side. The Serial field in the PORTAL.FP5 is set to auto-enter an increasing value. This creates a unique match value for each record in the PORTAL.FP5 file.

Note: If you have existing records in your file, you'll need to use the Replace feature to reserialize if they don't already have a serial number.

Once you've created the relationship, add a portal containing the ::Makes and ::Models fields from the ITEMS.FP5 file. If you've checked the option in Define Relationships to allow "creation of related records", you'll be able to add new portal rows by clicking in the blank portal row.

Now all you need to add is the conditional popup menus. Start by creating another file called POPUP.FP5. Enter one record for each car model. Each car model should also have an associated car make. The easiest approach is to make a clone of the ITEMS.FP5 file minus the Serial field and use it as the POPUP.FP5 file.

Going back to the PORTAL.FP5 file, enter layout mode and select the ::Makes field in the portal. Use the Field Format dialog under the Format menu to change it to a popup menu. Define a value list for the popup menu. This value list will be based on the contents of the Makes field from the POPUP.FP5 file. Even though there are several records with the same car manufacturer on it, the popup menu will only display one of each unique occurrence. That's because value lists based on the contents of a field utilize the index to display the items in the popup menu. Since the FileMaker index is like the index of a book, only one of each item is listed.

To make the second popup menu conditional based on the first popup menu, you will need to create a relationship in the ITEMS.FP5 file. You can't create the relationship in the PORTAL.FP5 because it needs to use the Makes field as the match field on the right side of the relationship. The Makes field does not exist in the PORTAL.FP5 file. It is displayed in a portal from the ITEMS.FP5 file. Therefore, it is necessary to create the conditional value list in the ITEMS.FP5 file.

Choose Define Value Lists from the File menu and create a new value list. Select the second radio button choice for "use values from a field". In the next dialog, select the last radio button option next to "only related values". From the popup menu to the left of this radio button choice, select Define Relationships. Define a relationship from the ITEMS.FP5 file to the POPUP.FP5 file using the Makes field from the ITEMS.FP5 file on the left side and the Makes field from the POPUP.FP5 file on the right side of the relationship. Name the relationship "Car Models". When you are finished defining the relationship, select the ::Models field from the list of fields and exit the Define Value Lists dialogs.

Return to the PORTAL.FP5 file and create another value list. Choose the last radio button option "use value list from another file". When the dialog appears, click the Specify File button to locate the ITEMS.FP5 file. Once you have located the file, you will be able to select the "Car Models" value list you created in the previous paragraph. Attach this value list to the ::Makes field in the portal. Enter browse and test out your conditional popup menu.

Displaying The Contents Of A Portal In A Field

This solution is not as practical as the previous, but I always say it's important to know as much as possible about how FileMaker works. You never know when you'll need a little tidbit of knowledge to solve a nagging FileMaker problem. Maybe you'll find this solution immediately useful, but more than likely you'll want to file it away in your brain for future use.

What Is It Supposed To Do?

The idea is to display the contents of a portal in a field as a return separated list of values. For example, if you have a portal with three car models, the field will display each car model separated by returns. If a new portal row is added, the new car model will automatically appear in the field. The actual example used in the bonus files provided with this solution are tasks and their status. The resulting list of values can be used as a quick reference to see what tasks still need to be completed.

Author's Note: Jim Mastrianni from Happy Software sent me this file shortly after FileMaker Pro 5.0 was released. I think it is really ingenious, but may not be of limited practical use. I would be interested to know if any reader finds a really powerful use for this technique.

Let's Do It!

To create the solution, two relationships, a value list and a calculation will be needed. Start by taking a look at the first relationship. There is nothing fancy about the portal in FIELD.FP5. It uses a serial number match field and allows you to create new portal rows in the CHOICE.FP5 file.

In this example, the portal displays daily tasks. The task is listed in a standard text field and the status of that task is represented by a popup menu. A task can have three states: Not Started, In Process and Finished. The popup menu is a simple static value list containing these three values.

The second relationship requires that you create a calculation in the CHOICE.FP5 file. The calculation is named Serial Trigger:

Case(Status = "Finished", "", Serial)

The job of this calculation is to filter out any tasks that are finished. Only the tasks that are "Not Started" or "In Process" will be displayed in the return separated list.

Go back to the FIELD.FP5 file and create a new relationship titled "Tasks Field". Use the Serial field on the left side of the relationship and the Serial Trigger field from the CHOICE.FP5 file on the right side. This relationship will be used in a value list which will, in turn, be used in a calculation field to display the return separated list of portal items.

Choose Define Value Lists from the File menu and create a new value list. Choose the radio button next to "use values from a field". When the next dialog appears, select the last radio button next to "only related values" and select the "Tasks Field" relationship. The field you want to display in the value list is the ::Task field. When you are done, exit the Define Value Lists dialogs.

Enter Define Fields and create a new calculation field titled "Portal Field". Enter the following formula:

Left("---------NOTE: This calculation must be unstored.----------",0) &

ValueListItems(Status(CurrentFileName), "Tasks") &

Left("---------NOTE: This section forces the field to refresh.----------",0) &

Left(Tasks Portal::Task, 0)

Developer's Note: Most of the above calculation is comments using the Left function. Since the second parameter in the Left function is zero (0), the text in the first parameter will display nothing (""). Therefore, the comments using the Left function will not actually change the result of the formula.

The working portion of the formula is listed below:

ValueListItems(Status(CurrentFileName), "Tasks") &

Left(Tasks Portal::Task, 0)

The ValueListItems function is a Design function more often used for web based FileMaker solutions. It allows you to grab the contents of a value list. Since the value list is conditionally based on a relationship, the values this function grabs are constantly changing to represent the contents of the portal minus any "Finished" tasks.

The Status(CurrentFileName) function is used to create a dynamic function. You could type the name of the file into the first parameter of the ValueListItems function, but any change to the name of the file would require you to modify the calculation. Unfortunately, there is no Status function that points to a value list regardless of the name.

The ValueListItems function is concatenated to the Left function containing a related field as the first parameter and the number zero (0) as the second. The ultimate result is nothing ("') since the number parameter is zero (0) but the affect is to refresh the calculation anytime a change is made to the ::Task field. Without this refresh feature, the calculation would not update when exiting the portal. Even with this refresh feature, an additional Refresh script is needed since the calculation doesn't refresh in every scenario. If you make a change to the ::Task field in the portal and exit the portal, the calculation will update. However, if you tab to the next field in the portal, the calculation will not properly update.

Happy FileMaking!

John Mark has a personal web site with hundreds of free FileMaker tips and tricks Database Pros.