Database systems often call for value lists that only show the “Active” or “Current” records in a table. In this post we will explore how we can forever wave goodbye to context-dependent value lists for “Active” records in FileMaker 12 using ExecuteSQL and a few other tricky techniques. In addition, we will cover how and when to use context-independent and context-dependent value lists in FileMaker 11 and prior versions in case you have not yet upgraded to 12.
First let’s define the terms we are working with:
Context-Dependent Value List ~ a value list that can only be applied to a layout based on a particular table occurrence.
Context-Independent (or Global) Value List ~ a value list that can be applied to any layout.
It’s important to note that all static value lists where you just type in a list of values into the value list definition are context-independent. The real beauty of this idea emerges when making a dynamic value list context-independent. My colleague Martha Zink has posted some helpful videos on a similar topic on our SoliantTV YouTube Channel here. In her post she makes use of global fields. My post will explore other methods. And I’m sure there are other ways of producing the same result. Feel free to share your favorite method with me in the comments section below!
Here’s the scenario – picture it: you’re designing a system for a footwear manufacturer. You’ve built four tables: Products, Orders, Customers, and Projects. The business rules require that you need to give customer service representatives the ability to assign Products to Orders so you create a value list of all Products in your system. However, you realize during the course of gathering requirements that users should not be able to place Orders for footwear that is no longer in production. That means you need to refine your list to only show currently available, or “Active”, Products.
There are a number of ways to do this in FileMaker 11 that we will talk about later in the post but the burning question on my mind is: how can we do this using ExecuteSQL? John Ahn presented a neat way of doing it at a FileMaker DevCon 2012 Unconference session, and his demo file can be found on the very cool FileMaker Hacks site here – he makes FileMaker perform awesome acrobatic feats. For our purposes, we won’t need to build out to the level of complexity featured in his file so I’ve prepared a simpler exercise inspired by his work. The basic concept is that you create a Global or Utility table with calculations based on global variables into which you throw your list of values by using an ExecuteSQL function on a script trigger that is activated upon entering the field attached to the value list. The global variables parse out the list into records in the Global table and the value list uses those records. Clear as mud? I’ve created a file that you can download and explore: Shoe_Tracks.fmp12 And we can step through the basics together. First, create a new table called Global. In this table create the following fields:
- _pk_utility_ID (number, auto-enter serial, starting at 1)
- Active_Products_NameList_ct set to equal $$z_valuelist_text
- Active_Products_IDList_cn set to equal $$z_valuelist_num
- Active_Products_Name_ct set to equal GetValue ( $$z_valuelist_text; _pk_utility_ID)
- Active_Products_ID_cn set to equal GetValue ( $$z_valuelist_num; _pk_utility_ID)
(Really, we could name these fields without the “Products” descriptor in it because they are dynamic and can be used for other means, but I think it provides more clarity when trying to understand the concept initially)
Now navigate to the Global layout that FM created automatically. View it in Table View. You will need to create as many records in this table as you think you might one day have as “Active” Products (or Customers – as you can use this table for any number of contexts). For some systems this number may be only 10. For other systems it might reach 100,000. I haven’t tested this out on a large-scale system but since the calcs are all based on variables set by ExecuteSQL it seems like it should fare well. For this example let’s create 100 records.
The last thing we need to set up for the Global table is a Cartesian, self-join relationship from the Global table back to itself. You can call the second Global table occurrence Global_Global_All. Next, go to the Orders layout. Pull in the __kp_Product field, set it to be controlled by a Drop-Down list and then create the value list like so (you will receive warning messages that the value list won’t work because neither of the fields can be indexed, we can ignore the messages because a script trigger will set the values every time a user enters the field – you can read more about that here):
The next step is to attach a script trigger to the Order::_kf_Product field. The trigger should be activated OnObjectEnter and it should run a simple script you will need to create called ExitScript. The only step in this script is the ExitScript step. Below is what the parameter on the script trigger should look like, this will throw our list of Active Products into the global variables that feed the Global table calculations of our value list:
Let ( [
$z_valuelist_num = ExecuteSQL( "SELECT " & Quote("__kp_Product") & "FROM Products WHERE Status = 'Active' "; ""; ""; "" );
$z_valuelist_text = ExecuteSQL( "SELECT Name FROM Products WHERE Status = 'Active' "; ""; ""; "" )
]
; ""
)Notice in the above code that we had to put quotes around the __kp_Product ID field in the SQL statement because SQL does not play nice with field or table names that begin with the underscore character (“_”). There is one more layout trick we are going to put in place. I am in awe of John’s creativity with this feature. The basic concept is to use an ExecuteSQL function in conditional formatting to update a variable that will display the text Name of the Product that you have chosen. Check the file I’ve provided for download to see how this works (Shoe_Tracks.fmp12.zip).
Et voila! That is the last step to setting up your completely context-independent value list to show only “Active” records in FM 12 using ExecuteSQL! I will be using this a great deal in the future.
In previous versions of Filemaker, without the power of the ExecuteSQL function, the methodology is a bit different. Let’s first take a look at how, when, and why to set up a context-independent value list. The first step to refining your list of Products to only include “Active” records is to create a calculation field in your Products table named: Name_Full_Active_ct and set it to be equal to: If(Status = “Active”; Name_Full_ct; “”). This field will only show the Name of the Product if the Product is marked as “Active”. Then set up your value list like so:
We will be using the primary ID of the Product table to pop into the Order::_kf_Product field when a user selects a Product from the list but the user will only see the Product Name and not the ID. To give the users that polished interface we must choose to “show only values from the second field”, which disallows us from sorting on the first field, which is our unique ID field. Important: We can only “sort on the second field” using this method if we know for certain that the data in the second field will always be unique across records. In the system we are discussing, we know that we will never name two shoes with the exact same name. That is a business rule that we can rely on. Therefore, we can use this context-independent method to create a dynamic value list that we can apply on any layout in our system.
Oftentimes, we can’t rely on data in a field to be unique across records. With the Customers table, for instance, we know that many people have the same name. Thus we must set up the Active Customers value list to be context-dependent. This difference hinges on the way that the “Sort values using:” radio box works in conjunction with the “Include only related values starting from” selection. If we choose to “Include only related values” from a table then we can display the second field even if it does not contain unique data across records. Why is it important to have unique data across records when sorting on a field in a value list? If you don’t then you won’t see each individual record in the table. For example, if we have 2 Customers in the database named Samantha Jones then our value list would not show a value for each of the different Samanthas it would only show the value for the first Samantha entered into the system. Obviously, this is a problem if we want to place an order for the Samantha Jones that lives in New York, not the one in Chicago.
The context-dependent method requires a few more steps than the previous method. First, you will have to create a calculation field in the Orders table that is set to always result with the text string “Active” (Let’s assume you name the field zConstantActive_ct.) Then you would build a relationship from Orders to Customers based on zConstantActive_ct and the Status field on the Customers table. Next you would set up a dynamic value list based on those components, like so:
Notice that this value list only includes related values starting from the Orders table occurrence pointing to the Orders_Customers table occurrence. Also note that we have selected the box to Show Only Values from the second field. Because this value list is dependent on context it is limited in that we can only apply it to layouts based on the Orders table occurrence. We cannot use it anywhere else in the system. If we need to use a list of Active Customers somewhere else in the system, such as on the Projects table, we would have to replicate the steps we just took for Orders context to make it work. Specifically, you would need to add a constant field to the Projects table, build another relationship from Projects to Products and add an additional value list based on those components and so on and so forth for any other context as your system grows. Unfortunately, this practice can clutter up your relationships graph, introduce more risk, and make your system harder and more expensive to maintain. To get around that you might also consider tacking the city name into the Active Name calculation field and in so doing transform this into a brand-new, sparkly, context-independent value list!
I hope this post has helped you more clearly define the ins and outs of dynamic value lists. Thanks to all those in the community whose thoughts and work helped me to come to my own understanding. And, if I’ve butchered anything, please do let me know!
More Reading: I kept the ExecuteSQL statements pretty simple in this example but you may want to read up on making them less breakable. Kevin Frank wrote a good piece on that here.
