In July and August we explored several esoteric value list techniques. This time around, and over the next few postings, we’re going to step back from the cutting edge, identify some common value list challenges, and propose some solutions to those challenges. A few thoughts before we begin:
- Some of the material in this series will be beginner-level; some will be either intermediate or advanced, depending on your point of view
- Value lists are subtle and multifaceted; to get them to do what we want, we sometimes have to move beyond the obvious
- As often happens in FileMaker, there are many ways to skin the cat
- I plan to explore only a few of these ways
- But will do so in microscopic detail
Now on to our first demo (Thinking About Value Lists, part 1 demo 1), which contains a table of employees with office sizes. Here it is in layout mode…
…and here it is in browse mode, and you’ll note that the office_size field is populated via a pop-up menu…
…which is tied to a value list based on entries in a Values table.
A Digression or Two…
Of course out in the real world, one probably wouldn’t dedicate a table called “Values” to a single-purpose value list. However, in this demo it helps make the point clear that the values come from records in a table created specifically to store value list values.
But, why use field values at all? Why not use a custom value list instead?
That’s a fair question, because custom value lists do have certain advantages, including:
- they are easy to set up
- they allow you to easily specify the order of list items, without having to resort to heroic measures or trickery
…but custom value lists also have some drawbacks, the biggest being that they are stored as schema rather than as data, so when you update a client’s database by importing their data into your new improved schema, you will lose any changes the client may have made to their custom value lists, unless you take special steps to prevent this from happening. E.g., you could manually update custom value lists in the new file, or you could attempt to use FMrobot, but unfortunately it has not been updated since FM 10. You could also employ a trick I picked up from Lynn Allen back in the 1990s, and use a separate file for custom value lists… perhaps that will be the subject of a future article.
At the risk of digressing from what was already a digression, you will note that accounts/passwords, and “next serial number” values are also stored in schema, but at least FileMaker offers script steps to facilitate the transfer of these items from the client’s source files into your shiny new schema (and for the sake of completeness, I should mention that custom functions live in schema also, and while there are no script steps to assist in their transfer, at least they can be copied and pasted).
Bottom line: there is no built-in or obvious way to automate the transfer of custom value list contents from one file to another.
And with that, we’re done digressing, and ready to move on to…
Challenge #1
Here are all the records in the Employees table:
Word has come down from corporate HQ that “Enormous” is not an approved office size designation; the proper term is “Gigantic”. So, you make the change in your Values table…
…but of course modifying a record in Values doesn’t magically update corresponding existing values in Employees, does it? You’ve still got “Enormous” where you should have “Gigantic”.
Perhaps it’s worth mentioning that if your system uses radio buttons instead of pop-up menus, at this point Office Size will appear to be empty for the first three rows, since “Enormous” is no longer in the Values table.
At any rate, you are going to need to fix it. So, you manually update the three records…
…and then you notice that the modification fields have updated to reflect this. While technically correct (the records did change after all), from a business perspective, it may not have been desirable for modification data to change under such trivial circumstances.
So, what can be done to prevent this from happening? Well, you could…
- Implement Nick Orr’s amazing “selective modification” technique on both the timestamp and account modification fields, and then use a script to update the office size replacement, or…
- Export the original data to another file, modify it in the other file, and then reimport it, telling FileMaker to not perform “auto-enter” operations when the dialog appears asking you if you wish to do so, or…
- Manually turn off auto-entry on the timestamp and account modification fields, make the changes to office_size, then re-enable auto-entry…
Or you could have designed the system differently in the first place, to use a two-column value list, as per today’s second demo file (Thinking About Value Lists, part 1 demo 2).
In this demo, as you might expect, the ID field in Values is a standard auto-enter serial number, and the “office size” value list is defined like so:
As a reminder, in demo #1, there was an “office_size” field in the Employees table…
…but now, in demo #2, we’re going to instead store the ID of the value, rather than the value itself, so the field is “id_office_size”.
Since the value list is set to only display the second column, and since our field is formatted as a pop-up menu, our users will be none the wiser…
…but let’s be clear that the visible text is an illusion, and what we’re really storing are foreign keys (which correspond to the primary keys in the Values table).
So, getting back to our challenge (change “Enormous” to “Gigantic”), this time when we edit the value in the Values table…
…we return to Employees to discover that nothing else needs to be done, and since no data has been modified in the Employees table, the modification fields are undisturbed.
Challenge #2
Another decree from on high: as a cost-saving measure, “Gigantic” (formerly known as “Enormous”) offices are being phased out… all employees are being downgraded one size, with those currently occupying “Small” offices to be moved into new “Tiny” ones. This is an across-the-board mandate that affects all employees, no exceptions.
So, in demo #1, we would first update all four records in the Values table like so:
…and then manually change office_size for every record in Employees, inadvertently updating all the modification fields in the process.
But in demo #2, guess what? We simply update the values in the Values table…
…and that’s all there is to it. It’s way less work, and the modification fields are untouched.
Closing Thoughts
In closing I want to point out that this “store the first column, but display the corresponding value from the second column” behavior works with pop-up menus, radio buttons and check boxes. It does not work with drop-down lists, which are happy to show you the second column while dropped, but reveal the ugly truth when they are at rest.
And one thing I particularly like about pop-up menus under this scenario is that if one of the records is somehow deleted from the Values table, it becomes immediately apparent in Employees.
In part 2 we’ll look at some of the nuances associated with filtered value lists.
I have a “lists issue” on which I would like your opinion. I just don’t know what’s available as I’m still mostly 8.5 with my main files.
I have a transactions file with type listed as
Physical
Emotional
Mental
Family
Social
Work
Professional
Buy
Sell
Transfer
When you define a physical transaction you only get the choices that are physical, emotional, the same, etc.
What I’d like to see is when the physical choices are chosen they present in the most numerically present order, most first.
I’m sure this is possible but don’t really know how to concept it, and I’m sure there’s better ways than I would understand in newer versions of FMP.
Right now I’m just using a list’s file with all the transactions in there, but this would need to be reflected in the main file I assume, or else sorted somehow in the list’s file. Not sure how to approach it.
Thanks,
John
Hi John,
I don’t have an immediate answer for you; perhaps someone else will chime in. I am contemplating a future posting re: this age old dilemma: how can we apply custom sorts to field based value lists?
In the short term, I would suggest that a path of least resistance would be to not use a value list at all, but rather some other selection mechanism, for example, a sorted portal or a popup picker.
Hope This Helps,
Kevin
Call me slow but I don’t feel like I’m using a value list. I’m picking from a list of choices that is activated by the key Physical, which provides the data in the category field from the other file. So, maybe I’m not up on the right terminology, It’s just key/portal to me, but like I said. I know there’s a lot of list functions in 9+ and all the supposed FM 12 functions that you’ve been writing about.
At this point it does what I want, but it’s not very sexy.
Interesting subject (well covered) that point out my recurrent dilemma: using a pop-up or a drop-down list.
Pop-up menus are indeed very useful for their “store the first column, but display the corresponding value from the second column” behavior. But they lack the “type ahead” behaviour of the drop-down lists. I too often have to rely on a “no field entry” edit box in front of a drop-down list to get the best of both world. I wish there could be a more elegant way.
Coming from a background in Access, I am really struggling with creating value lists in Filemaker. I design databases for research studies which require that most of the data be coded. Access allows the user to create two column value lists. One column defines the code and the other column provides the code. The user selects which value to store in the database–almost invariably the code. But I can’t figure out how to do this with FM. Any help with this would be greatly appreciated.
Not sure if I understand what you’re asking… if you want to define a 2-column value list it will need to be based on two fields in a table… perhaps a table that you have defined specifically for this purpose. Since I’ve already blogged extensively about this (in parts 1 and 2 of this series), I won’t rehash that here. Can you give a specific example of where you’re getting stuck?
Hi Kevin,
Thanks so much for getting back to me! I received a reply from Goeff Coffey that explained exactly what I needed to do. You were right in saying that I needed to create a table with two fields, one for the code I wanted to enter in the database and one for the text describing that field. I had done that, but my problem was that I didn’t know where to go from there to link the lookup table to my field. Now I’ve got it. I’ve used Access for about 12 years and it’s been a real challenge for me to adjust to FM.
Thanks again!
Leslie
Thank you so much for this post. It is very helpful. I am running into an unintended consequence with this approach when exporting records. The ID (first column) is exporting when I want the member of the value list to export instead. For example 1=Oregon. On my export script, I want Oregon to display for the state instead of “1.” Is there a workaround for this as “1” is meaningless to my end users.
Hi Lauren,
In demo 2, I intentionally did not relate Employees to Values, because I wanted to draw attention to the fact that values lists can display quasi-related values without there actually having to be a relationship.
In your case, if you don’t already have it, make a relationship from Employees to Values (or whatever your parent and child tables happen to be called). In my demo you would relate Employees::id_office_size to Values::id.
Now when you perform your export from Employees, instead of id_office_size, you can choose the related field from Values… in the case of my demo, it would be Values::value.
Hope this helps,
Kevin
How do I prevent a value list that uses custom values from closing when a user is editing the value list and hits the enter key under the NumLock?
User wants both enters to go to a new line for the value list.
Assuming they’re on a Mac, if they hold down the Option key while pressing numeric Enter, they will get a new line. On Windows, Ctrl-Enter does the same.
Thank you for this very useful post. I came across it while trying to find out how I could convert the records in a database that currently works like demo 1 (where changing something in the value list does not change the records) to something that works like demo 2 or even a relational structure like in you’re reply above to Lauren. I have 1500+ records so doing it manually would be too time consuming. Would greatly appreciate your thoughts.
Thanks!
You can make the transition by following these steps… I’m going from memory here, so you may need to fiddle with these steps a little for everything to work.
0. MAKE A BACKUP BEFORE PROCEEDING
1. Show all records
2. Sort by the field containing the values in question… let’s call that field Value
3. Export the Value field to a temporary file using the “summarize by” option, so you get one unique record per value in the new table
4. Define a new table in your solution; for the sake of convenience I will refer to it as Values
5. Make sure this table has these two fields: ID (unique autoentered serial number) and Value
6. Import the records you exported in step 3 and allow “auto-enter” to happen during the import (that way value::ID will be populated for all records)
7. Make a relationship between main::value and values::value
8. In your main table, show all records, and do a replace on your value field with values::ID
9. In your main table, rename the value field to idValue or whatever you’re comfortable with
10. Redefine the relationship from step 7, so it links main::idValue to values::ID
11. Define a 2-column value list based on all records in the Values table… the first column is ID and the second is Value… show the second column only.
Finally, use step 12a if you’re planning on using a popup menu, or 12b if you’re planning on using drop-down list:
12a. Attach the value list to the idValue field and format as a popup menu
12b. Attach the value list to the idValue field and format as a drop-down list, overlay the preceding with an identically sized field formatted as an Edit box displaying values::value (and do NOT allow entry)
Demo 1: I don’t understand get it. Why do you save a foreign key in id_office_size if a relationship is not being used? And, why does it even work without a relationship?
Because in this case a relationship is not necessary. The value list behavior is quasi-relational.
I am a newbie and as john 9210 mentioned and don’t understand exactly how id_office_size receives data, as in the file attached I don’t see anything about this field that relates it to a value list or to anything. obviously I am missing something. How does Id_office_size know what is in “values” table?
I think I’d like to know how this relationship is set up. I can see it in Manage:Value List, but how exactly does Id_office_size know to use this value list. It doesn’t seem to be there when I go to Manage:database and click on the field. There is no obvious relationship there where I can see how id_office_size was set to use these values from another table.
The value list in demo #2 generates a two-column list in memory… perhaps it would be helpful to think of it simply as a list of name/value pairs… or in this case, id/value pairs.
https://filemakerhacksblog.files.wordpress.com/2017/01/2017-02-16_115443.png
(This screen shot is from the Values table, but the value list represents an equivalent structure that is independent from the table, which is why you don’t need a relationship for it to resolve.)
The value list is defined to show (only) the second column, so what you “see” is column 2, but what you “get” when you make a selection is column 1 — provided the field on the layout has been formatted as a popup menu, radio buttons, or a check box set.
https://filemakerhacksblog.files.wordpress.com/2012/09/9-9-2012-10-20-56-am2.png
Point being, the value list does enough of the work that you don’t need a relationship — if all you want to do is show the value (col 2) that corresponds to the id (col 1).