Figure 1: Value list – It's essential that your value list use the newly created "Customer to Order to Products by Unordered Products" relationship and that it does so from the context of Customer to Order.

A: There are several ways to do what you're trying to do here. In our opinion, the simpler the technique, the better. Also, if you can do it without the use of a script, that technique has more finesse than brute force scripting.

Let's start by reviewing the structure of the system. You have an Order table, which is related to an Order Item table by Order ID. Order Item is related to Product by Product ID. For simplicity, we're going to consider your case which allows for creation of related Order Item records through the portal. The goal is to get a value list that displays all products that aren't on the current order.

If you try to construct a calculation in the Product table to display or not display the product ID based on related Order Item records, that calculation will be unstored and so won't be usable for purposes of creating a value list (which requires index-able values). If you try to create a script that takes the current order ID, switches to the Product table, then replaces a stored Product ID Display field with the Product ID based on whether related Order Item records match the current Order ID, you'll get values you can index, but the script will run slowly if you have lots of product records, and it won't work in a multi-user environment because different users running the script at the same time will overwrite each other's replaced values. Therefore, you need a solution not based on calculated values in the Product table.

What you can do is create a calculation in the Order table that gives a return-delimited list of the Product IDs currently on the order. There are a couple of different approaches, but the simplest is to use the List function. You'll get a list of the IDs you're looking for if you add a calculation field called z_Prod_IDs_On_Order with this formula:

List ( Customer to Order to OrderItem to Product::_Product_ID )

If you then create a new relationship from Order to Product matching on this field to the Product ID field and setting your join operator to ?, you can create a value list of related values starting from the Order table (figure 1).

This technique works fine if you have at least one item on your order, but it doesn't work for orders without any order items -- no items will appear in your value list. You can fix this issue by changing the calculation slightly:

Case ( Length ( List ( Customer to Order to OrderItem to Product::_Product_ID ) ); List ( Customer to Order to OrderItem to Product::_Product_ID ) ; "0" )

Now the field returns 0 when no items are on the order, and presuming no products have 0 as their Product ID, the resulting value list displays all products.

But does this approach work as expected? If you add a product to the order, then try to add another, you still see the added product in the value list, yet the calculation field shows the product ID as one of the products on the order. If you open the Manage Database dialog, close it, then try the value list again, it works properly. What's happening is that FileMaker Pro caches the join results, which is a fancy way of saying it remembers the related portal rows for longer than you'd like in this case.

You can overcome this last difficulty with a simple script we'll call Add Product that contains the following steps:

Refresh Window [Flush cached join results]
Go to Field [Customer to Order to OrderItem::_Product_ID]

The Refresh Window step is key because it forces FileMaker Pro to re-evaluate the portal and yield the correct values in the value list. The Go To Field step is there because this script gets attached to the _Product_ID field in the portal, and you have to put the user into the field to trigger the value list. After you've defined the field to be a button that performs this script, all will be well. Subscribers can download an example file demonstrating this technique.