Virtual Value List

by Jeremy Bante



Virtual Value List helps developers build transient value lists based on the contents of global variables. By basing value lists on global variables, developers can be spared most of the schema overhead associated with other techniques for value lists with programmatically specified values. This module extends techniques demonstrated by other developers by supporting arbitrarily many simultaneous virtual value lists and by preserve the sort order of the values as they are provided to the module.

You must use a copy of FileMaker Pro Advanced to install the custom functions for this module.


This module is based on value list demos by John Ahn and Andries Heylen, and a technique by Marcelo Piñeyro for coercing value list ordering.


Anyone may do anything with this software. There is no warranty.


Download from GitHub. If you aren’t comfortable working with GitHub, you can download the repository directly.

51 responses to “Virtual Value List”

  1. Josh Ormond says:

    Is it possible to get this set up as a module? It would be very beneficial for those using the site to get modules that have the same basic setup for modules.

  2. Jeremy Bante says:

    I presume you’re asking if it’s possible for this module to use scripts only, and not use the custom functions or the table. (Each of these objects may be part of a module, the essential nature of a module being generality and minimizing external dependencies more than medium.) It is not possible to implement the technique without the table and and fields. It is possible to implement all of the behaviors in some applications without custom functions, and it is possible to implement some behaviors in all applications without custom functions, but not all behaviors in all applications.

    The most common use I’ve found for this module so far is the use from the first demo where the values displayed in a drop-down list or pop-up menu are not defined until the user clicks on the field. For this case, it is not possible to set the global variable referenced by the value list via script. (The order of operations is: script parameter calculation evaluation -> value list display -> user selection -> OnObjectEnter trigger.) It is possible to set the global variable in the script parameter calculation without a custom function, but at the expense of encapsulation and SSOFT (single source of truth) code. Implementing the sort-preserving behavior in this scenario without a custom function can be done, but it requires a calculation sneaky enough that perhaps it shouldn’t exist outside a custom function anyway.

    The first drafts of the custom functions were scripts, and I suppose I could restore those for the benefit of folks who do not have a copy of FileMaker Pro Advanced (who I imagine may be a minority of this site’s audience). However, setting up what I anticipate being the most common use case will be much more onerous for them.

  3. Bruce Robertson says:

    I see that except for the State list, no selections can be made from the keyboard by typeahead. Though you can use cursor keys to move up/down the list.

  4. Bruce Robertson says:

    I also note that the results are incomplete; you can only ever get 100 values. Select any state, for instance, you only get the first 100 unique city names. This may be a side effect of FileMaker’s new value list performance technique, which caches value list items.

  5. Josh Ormond says:

    No, Jeremy. I think it functions fine as it. A scripted version would be cool too, but I was just thinking setting up the module with the Read Me and other items in the script list to explain how it works, and how to install it. Along with the CF’s already installed in the file.

    Primarily, a user should be able to download the file, and have everything they need to install/use the module. I always appreciate your contributions and approach to many things. It has helped make me a better developer.

  6. Bruce Robertson says:

    Oh, I see: 100 item limit is result of record count of (undisplayed) virtual list table . More records longer value list!

  7. Jeremy Bante says:

    Josh, there already is a README script in the file. Is it not in the file as you downloaded it?

  8. Josh Ormond says:

    Actually the whole file is acting weird. It’s mostly blank, and forces a conversion when I first open it.

  9. If you try to download the fmp12 file from github you end up with a text file , like Josh Ormond said.
    Download the zip file instead.

  10. Tom R. says:

    I haven’t been able to download the file from github. When I click on the “VirtualValueList.fmp12” link, it takes me to the file’s page but says, “(Sorry about that, but we can’t show files that are this big right now)”. If I click the Raw button to download, a new page opens that says, “Error: blob is too big”. Don’t know if this has anything to do with the problems Josh is having.

  11. Jeremy Bante says:

    Everyone’s trying to download the ZIP of (or clone) the whole repository, not just VirtualValueList.fmp12, right? Because that’s how it’s supposed to work. I suppose that’s one problem with posting things on GitHub — there’s some expectation of prior knowledge that is not widespread among FileMaker folks. I hope the benefits are worth that tradeoff.

    • G-Smith says:

      Jeremy – sorry to be a bother, but I’m stumped just like Tom is. I can view/download the CFs just fine, but get the same error messages when trying to access the .fmp12 file. Though that could be due to my lack of any knowledge of GitHub ( or GitaJob or GitaLife, etc. for that matter!). Is there another site where a gray-matter deficient type like me can download it from?

      Thanks much for all of your time and selfless sharing of your knowledge.

      • Todd Geist says:

        You need to download the Zip. File look in the upper left hand corner of the screen when you get to GitHub. You will see button That says “ZIP” with a little cloud and an Arrow. Click that.

        • G-Smith says:

          Thanks very much, Todd – that was the Only button I didn’t click on, for crying out loud! I guess I shouldn’t have had that second bowl of Numbskull flakes for breakfast 🙂

  12. Josh Ormond says:

    I reinstalled FM12, and it seems I can see the file fine now. Though, I am seeing some odd performance issues…I am assuming its a Windows 8 thing. I’ll test this on a Windows 7 machine over the next couple days and report my results.

  13. Gilbert Osmond says:

    I second this comment – why isn’t type-ahead working in the subsequent drop-downs? Total keyboard access is very important for my solutions.
    Bruce Robertson says:
    May 18, 2013 at 3:55 pm

    I see that except for the State list, no selections can be made from the keyboard by typeahead. Though you can use cursor keys to move up/down the list.

  14. Jeremy Bante says:

    Gilbert and Bruce, after some poking around, I’ve found that FileMaker doesn’t seem to do the type-ahead behavior for two-field value lists. All Virtual Value Lists are two-field lists — even when you only give it one column of values, it uses two fields: one for the original data you entered, and one with BOM character prefixes so that it respects the sort order you give it. This seems like a worthy feature request for FileMaker:

    • Justin says:

      Jeremy, great little module you have here.

      I wanted to ask about the type-ahead functionality though. I’m using a system more akin to the original Andries version, e.g. not using custom sort orders – no second field. I just have a list of labels that sorted alphabetically is fine.

      But I would really like the type ahead to work. So based on what you said, I think I’m only using a single field for this list. The definition of the VL in FM itself does not have ‘display value from 2nd field…’ enabled. So it seems that I don’t fall into this category of 2-field VLs not permitting type-ahead.

      Interestingly, if you have the drop-down open (so you see the values) and you start typing, it DOES start selecting an item that matches what you type. What you type does not show up in the field, though.

      Anyone have any thoughts about this limitation? I’ll go make a feature request right now. 🙂

  15. Matt Bowlby says:

    This is fantastic! I have run in to some problems however. I had it working well in a test file, however when I tried to move to my solution if no longer seems to work. Are there problems if implementing this with the data separation model? I have the VirtualValueList table and fields in my data file, but the layouts and value lists in my application file.

    Also – I would love a screencast on this technique. I have the basic implementation down, but need some more clarification on customizing – i.e. how to make a value lists (i.e. cities) dependent on a selection in another value list (i.e. states). Thanks.

  16. Jeremy Bante says:

    I’ve used Virtual Value List in a few solutions using the separation model; but I always put the VirtualValueList table in the interface file, not the data file, because it’s a means to implementing a UI feature rather than a place to store real data. The values that appear in virtual value lists are driven by the contents of global variables, and global variables are not shared between files. I’m guessing that the problem you’re seeing is that the calculations in the data file can’t see the global variables in the interface file, so the value lists have no data to render. Try putting the VirtualValueList table in your interface file, and let us know if that resolves the issue.

  17. Matt Bowlby says:

    Jeremy –

    I did as you suggested and left the Virtual Value List table in the interface file. It works fine now. Thanks!

    How about a nice screencast? 🙂 Would really help clarify some of the details in this module.

  18. debi says:

    Really interested in this, but having trouble downloading – getting messages like blob is too big and can’t show files of this size. Is it just me?

  19. Josh Ormond says:

    Is anyone else experiencing serious performance issues with this file on Windows? After selection a State, or change it’s value to something else, there is a long pause/freeze before the list of cities appears. Roughly in the2-3 sec range.

  20. Ben Graham says:

    Hi Bruce or Jeremy, I don’t see how to get more cities to show up. Say you select WA and you want to see Seattle. The Cities value list does not display beyond Creston, yet there are 737 cities in the PostCode table within WA.

    • Jeremy Bante says:

      Ben, the size of the value list is limited by the number of records in the VirtualValueList table. If everything is set-up correctly, you should be able to modify the “Get Virtual Value List Settings” script in the module to specify the size of the largest value list you expect to use, and the records will be created when the OnFirstWindowOpen script calls the “Enforce Virtual Value List Record Requirement” script. Alternately, just create as many records in the VirtualValueList table as you think you’ll need.

      That said, if your users need to select from among hundreds of options, any of the value-list-driven controls available to us are not great user interface design fits. Using a separate list view layout that can be narrowed down with finds tends to be a better solution.

  21. Skip Jensen says:

    Hi Jeremy-
    Thank you for this awesome module. I am just now trying to bend my brain cells around SQL and all of the benefits it brings. I have successfully figure out how to use Virtual Value List on a very beginner level, but I am having one issue I am sure is rookie oversight, but it has me flummoxed. I am trying to set up the “Second Field” type of Value list, and it works fine, except in your example, the same field displays both the Name, and the ID, and I have been unsuccessful in making this behavior happen in my file. I get the ID in both instances of the field. Any ideas on what I am missing ?

  22. Jeremy Bante says:

    Skip, the demo example in my file of a 2-field value list is using a pop-up menu control style for the data entry field. If you’re using a drop-down list instead of a pop-up menu, that would explain what you’re seeing in your file. Pop-up menus will show the second field in a 2-field value list after you’ve selected a value, even though the result actually stored in the field is only the first value from the value list. Drop down lists don’t do that; after the user makes a selection, a drop-down list will show what’s actually stored in the field rather than the corresponding value from the second field of the value list.

    • Matt Bowlby says:

      Jeremy – in reference to this comment I am having a problem. I have a pop-up with the id values and the display values. When I select the option, the id value remains the value showing through the pop-up rather than the display value as is the normal behavior. Any ideas why that might be happening?

  23. Eldad says:

    I cannot get it to return a list of greater than 110 values.
    I changed the limit to 250 and indeed the record count in the Virtual table is 250, but still i get only 110 values back in the drop down list… (FMA 13)…

    what am I doing wrong??

  24. Jeremy Bante says:

    Eldad, do you mind if I follow up with you via email? I don’t think the comments thread will be a great medium for sussing out the problem you’re seeing. You don’t have to post your email address in a comment; I already have it through the forum software.

  25. Eldad says:

    sure 🙂

  26. I’m trying to use this with FMPro13 but when I use Import in the function manager all the fmfn files are grayed out. Am I doing something wrong?

  27. Hi,
    I am sure I’ve got most thing right like the readme file says.
    There is this part that I am not sure how to do :

    “You will need to create new fields in the VirtualValueList table for each virtual value list you create.”

    Can you please explain how to make these new fields ?


  28. Jeremy Bante says:

    Binu, duplicate the displayListNN, displayValueNN, and idValueNN fields in the VirtualValueList table, rename them to match the numbers you need, and update the numbers in the calculations to match.

  29. Matt Bowlby says:

    I am trying to implement this on a solution but I get “” as the value list. Any ideas?

  30. Matt Bowlby says:

    I am trying to implement this on a solution but I get “Index Missing” as the value list. Any ideas?

  31. Jeff Drake says:

    Is there any way to have separators in the list? Everything I’ve tried just gives a “-”.

  32. Ole Buddrus says:

    Jeremy, this is great- an eye opener for me. Started implementing parts of it- great work. Thank you.

  33. Lorena says:

    Hi Jeremy. I have recently implemented the VVL into one of my solutions and am having an issue with all of my values displaying. I saw that there was a comment from someone on FM Modular that had the same issue where only 110 values would display, despite having more records in the VVL table. I also am only able to get 110 values to display even though my VVL table has 1000 records. Could you give me some feedback via email? I can give you access to the file if you need. Thanks!

  34. Lorena says:

    I implemented the VVL into a solution that is going to be used with WebDirect. On FileMaker, the VVL scrolls, but in WebDirect there is an arrow that you have to click to move the list page by page… Does anyone know if there is a reason that WebDirect doesn’t scroll? Thanks!

  35. There seems to be an issue with FileMaker 15 WebDirect when using this module for drop-down lists.
    Listitems get a prefix of “feff” each time increasing so the next have “fefffeff”.
    It seems to be caused by the appending of the nonbreakingspace in VirtualValueListSet custom function: $~vvl.nonBreakingSpace = Char ( 65279 ) ;
    A quick and dirty fix is to remove the nonbreakingspace like so $~vvl.nonBreakingSpace = “” ;

    Has anybody else seen this happen? “feff” is the UTF-16 hex value of the non-breakable space, however it worked on FM14 WebDirect and still works on FM15 Pro but not WebDirect.

  36. Jeremy Bante says:

    Kevin, the non-breaking space character is used to coerce the sort order of the value list. Without it, we can’t guarantee that the order of values in the displayed list will match the input. I tried every control character (Char ( 1 ) – Char ( 31 )) that might not have the same problem, but none of them have a suitable effect on sort order.

  37. Daniel Weiss says:

    Agreed – appears broken in Web Direct with FileMaker 15 🙁

    Any suggestions to solve ?

  38. Tobias Sjögren says:

    I’m having the same problem as Eldad and Lorena where only the first 110 values are displayed even though the VVL table has a lot more records in it.

    Is there a 110 values limit?

  39. Dale Long says:

    I can’t seem to get this technique to respect my assigned sort order in my own solution. I’ve used the data viewer to check my SQL – the values are sorting correctly there. Even the $$~VirtualValueList.ID_LIST and $$~VirtualValueList.DISPLAY_LIST values are showing my lists in the proper sort order. I’ve flipped through the records on the VirtualValueList table while my dropdown in question is selected and found that the displayValue01 is listing the values in the sort order I’m trying to assign. But despite everything I’ve tried so far, the value list that shows up in the dropdown sorts the display list values alphabetically, ascending, ignoring the sorting I’ve tried to apply to it.

    The only thing I can figure is that it’s somehow related to how I’m trying to sort. I’m trying to sort on a secondary field in the relevant table, sort_weight, that is not being displayed in the drop-down. To try this, I used the GROUP BY sql statement to include the sort field. This sorts the items as desired when viewed in the data viewer. However, it seems like this is what’s undoing my dropdown display. If I remove that, and include the sort_weight in the SELECT statement, I can get them to display in the correct order in the dropdown. But nothing I’ve tried seems to allow me to sort on a secondary field while displaying the primary field. Any chance anybody knows how to get this technique to work in this scenario?

    this is the SQL I tried to use at first, which shows things in the proper sort order in the web viewer, $$~Virtual vars, etc., but not in the actual dropdown:

    Let (


    ~value_list_items = GTN ( VALUELIST::a_id ) ;
    ~category = GFN ( VALUELIST::category ) ;
    ~description = GFN ( VALUELIST::description ) ;
    ~sort_weight = GFN ( VALUELIST::sort_weight ) ;

    ~current_table_context = UOM::z_key_context ;

    ~list = ExecuteSQL (

    SELECT ” & ~description & ”
    FROM ” & ~value_list_items & ”
    WHERE ” & ~category & ” = ? ” & ”
    GROUP BY ” & ~sort_weight & “,” & ~description & ”
    ORDER BY ” & ~sort_weight

    ; “” ; “” ; ~current_table_context )

    ] ;

    VirtualValueListSet ( ~list ; “” ; “” )


    This next version actually achieves the sort I want in the dropdown, but with the caveat that I’m displaying both the sort weight and the description in the dropdown:

    Let (


    ~value_list_items = GTN ( VALUELIST::a_id ) ;
    ~category = GFN ( VALUELIST::category ) ;
    ~description = GFN ( VALUELIST::description ) ;
    ~sort_weight = GFN ( VALUELIST::sort_weight ) ;

    ~current_table_context = UOM::z_key_context ;

    ~list = ExecuteSQL (

    SELECT ” & ~sort_weight & “,” & ~description & ”
    FROM ” & ~value_list_items & ”
    WHERE ” & ~category & ” = ?
    ORDER BY ” & ~sort_weight

    ; “” ; “” ; ~current_table_context )

    ] ;

    VirtualValueListSet ( ~list ; “” ; “” )


  40. Hey Jeremy,

    This module has been great! Thanks. Have a complex UI for inputting lots of values for government compliance and I have 13 lists running on some layouts. Works great. I’m also running into the 110 record limit. I have a handful of lists over 100 (130-160) and for those lists, 110 are available in the dropdown. I have 400 records in the table and I can see the correct amount in each global list variable.

    Thanks. Michael

  41. Matthew Dahlquist says:

    I use this frequently and I love it. Just ran into the type-ahead issue on 2-field lists. In my use case, I didn’t need any custom sort defined in a SQL query or anything else fancy. Alpha sort by the visible 2nd field (default FileMaker behavior) was just fine. The easiest solution for me was to copy the value list and turn off Unicode sorting in the FileMaker Value List config window.

    Thanks again for a *very* useful module.

Leave a Reply

Your email address will not be published. Required fields are marked *