Avoid Relationships Using Value Lists

I've been experimenting with value lists. My main goal is to create smaller table occurrence groups. I don't have any performance metrics but I'm looking at several aspects of value lists.

  1. Value lists are implicitly global. Attaching a value list to a field allows you to bypass ( or not need ) relationships.
  2. Value lists are indexed, so they should be immediately available.

Is anyone else doing this? Or using value lists in other interesting ways?

1 Like

Not all value lists are created equal. While it's true all primary fields in a value list need to be indexed (and in most cases the secondary field) - there is a fundamental difference between an "all values" list, and a "show related values from" list.

The former is fast - if you have a value list showing all values from a field in a table - then the index is what is used for display. However once you choose related values, it is a query that is user-specific and the records used in the value list have to be downloaded from server to client - it's like any other operation such as sorting records, aggregating, or listing.

Yes value lists are implicitly global in most cases - with exception of conditional value lists which are context dependent. Value lists that display related values cannot be used in find mode whereas "all values" ones can.

One use I have utilised value lists for is to obtain sub-sets of primary keys from a table. Let's say I have a table with 100,000 records, and I wish to obtain a list of primary keys where the "type" field is of value "contact".

I could create a relationship based on the type field to relate to just contact records, and build a value list off that - but because it is related values, a query is performed and the resulting records have to be downloaded from server to client - the more records the slower the value list will take to display.

An alternative approach may be to create a stored and indexed calculation field on the table. This calculation would be:

if ( type = "contact" ; primaryKey ; "" )

The primary key of any given record is only indexed if it's type is contact. With this done you can now build an "all values" value list based on this calculation. Because it is all values, only the index of this field is sent from server to client (no record data) and as a result you have a list of primary keys without any record transfer. As far as I'm aware this is the only way I have found to actually transfer legitimate field information from a record without being sent the entire record.

There are obvious caveats to this method. One being you need to store and index the primary key for each scenario. If you want 20 different lists, that's 20 calcs - it will increase the overall size of the table, so like I say it's to only be used sparingly where the query is simple (and not dependent on any related tables so can be indexed) and where the resulting number of IDs is large (where record transfer is not desirable).


Malcolm, as most of our systems run within Windows, we now avoid using value lists as much as we can.

The FileMaker/Windows combination is horrible when it comes to selecting from drop down lists/pop up menus.

In the vast majority of cases, we now use our own list layouts with header finder fields using OnObjectModify scripts (with an OnTimer delay) to allow type ahead searching presented in a card or similar window with a selection button on each record listed. This also allows us to utilise small dedicated TOGs. We've a lot of legacy lengthy TOG chains, which we will need to eventually strip out from our future versions, but we'll do as much as we can these days to avoid adding a TO.

We've just gone live with v19.5.2 on the majority of our FMP and FMS servers, having skipped v19.5.1. So far, so good but we haven't really seen any performance change so far from the earlier v19 versions, albeit we've not carried out any dedicated testing due to current workload.

Kind regards

1 Like

Minimizing the TOG is also one of my maxims. How can value lists help you to do this?

In many different ways! :smile:

The simplest and most common method is to define a value list that is based on the ID of a record and then uses a second field as the human-friendly label. Wherever the ID field is used it can be formatted as a pop-up menu. Pop-up menus display the second field, so you get a human-friendly display without needing to have a chain of relationships between the two tables.

Here's the setup

and here is what you see in browse mode

@weetbicks has outlined the boundaries of what is sensible in his post above.


Yes ! This is my preferred way for ValueLists. And this adds many goodies. For example, say one of the actual values can't be used anymore, for some reasons, in the creation of new records. Although you can't create new records, the old one one are still there and you may have to report on them. How do you this ? This way:

  • You add columns whose values are 1 or "" that can be represented by checkboxexs:
    • isCreation : 1 if this choice can be used to create new records
    • isReport : 1 if this choice is available for reports (should be all set to 1)
    • etc.
  • For each of the isXXX columns, you add a column called IDxxx, for example IDCreation, IDReport, etc., that are calculations
    • IDCreation = iif(isCreation; ID; "")
    • isReport = iif(isReport; ID; "")
  • And you cerate as many ValueLists as there are IDxxx columns.

I agree with using picker lists. One note of caution with using the on-Timer method to allow for type—-ahead functionality is webdirect. In my brief testing, FileMaker can start slowing down and behave erratically.

Absolutely, not only WebDirect but FileMaker Go as well - unless you like on screen keyboards appearing and disappearing as you type​:+1::grinning:

Picker lists are a great option. The ability to have a portal from the current table is a welcome addition that helps support this sort of UI.

In my case, I'm not using the value list to support data input. I'm using it to display data from unrelated tables.