It’s Sorta a Value List Thing

Editor’s note: Today it’s my privilege to present an informative guest article written by Jonathan Fletcher on the elusive topic of sorted dynamic value lists. Jonathan is a well-regarded, thoughtful and thought-provoking fixture in the FileMaker community, known for his generous and frequent contributions to various online FileMaker forums.

Have you ever had a need for a value list that displayed a generated list of years in descending order? Have you ever had a client ask for a popup of dynamic characteristics or statuses in the order they are most often employed by the users?

1386121790383

Most FileMaker developers can remember times when they were asked to display a drop-down, pop-up or other dynamic value list in a particular sort order. And we have always had to say, “Naw, you don’t want that. Picker portals are way better!”

Inspiration!

A little over a year ago, Soliant developer Marcelo Piñeyro posted about his idea of using one of the two “Byte Order Marks” (Unicode 65279, in particular) to control sorting dynamic value lists.

1386121784870

The basic idea is to use a character that has little effect on the list other than forcing FileMaker to ignore its alphabeticity and display it in whatever order it’s provided. The Byte Order Mark fits the bill in that it is a zero-space, non-breaking character, totally invisible to the naked eye, yet still having a useful effect. [1][2].

For example, say you have a list of names of executives and you want them to display in the order that they appear on the org. chart:

1386121784755

If you fed their records to a dynamic value list in FileMaker you would get:

1386121784590

Hardly appropriate (“The first shall be last?”).

Using Mr. Piñeyro’s technique, we would add the BOMs to the beginning of each line of the original list (bullets represent the BOM here for illustration purposes):

•Tim

••Eddy

•••Craig

••••Jonathan

•••••Peter

••••••Dan

•••••••Phillip

••••••••Bruce

•••••••••Jeff

You have to remember to set the value list to sort by Unicode (waaaaay down at the bottom of the list of languages to sort by) but that’s it.

1386121784915

When you go to use this value list FileMaker sees the really high (65279!) unicode value(s) and pretty much—for all practical purposes—ignores the alpha characters.

1386121784603

So, how do we generate such a list in FileMaker? Well, I can already see the wheels turning in your minds, my fine-feathered FileMaker functionistas, but let’s look at a couple of approaches:

The way that Marcelo applied it was to use a sort order field and have the prepended characters calculated onto the visible value according to a manually entered ordinal [3]:

1386121791048

And his dialog looks like this:

1386121785491

His approach is excellent for when you have your data in a related table and want to store IDs but only show the value in the popup. You can read his article and get his demo here: <http://www.soliantconsulting.com/blog/2012/09/extending-filemaker-pro’s-value-list-sort-capabilities-using-char-function> to get the gist of that approach.

Hmmm…

A recent discussion I was involved in sparked a different idea, however: out of the cerebral fog began to emerge a custom function that would take any list and return it to a calculation with these strange and wonderful characters appropriately prepended to the beginning of each line. That way you could employ any type of list: a calculated list, a related field or combination of fields, a manually entered list in a preference field, or effectively any combination that can be calculated at all:

1386121785027

Send your list to the CF, point your value list to the result (Likely, it would be in a single-record preferences table, but it doesn’t have to be) and violá: “Hey, Martha, lookee that! A dynamic value list that isn’t alpha sorted! Have you ever seen such a thing?!”

The accompanying file [http://www.fletcherdata.com/FunWithSorts.zip] demonstrates how that might be done. The file starts off slowly, defining the problem for even the greenest viewer by setting the stage with different behaviors resulting from some everyday uses of value lists.

1386121786598

One useful application of the technique is immediately visible, if not readily apparent: the popup navigation field on the layout gathers the layout names and puts them in a list, in the order that the layouts are in the file.

1386121785375

Digging Into The File

Perusing the file, you see some applications of the idea and the results. There are three CFs that do the heavy lifting:

  • Repeat: an elegant character repeat function suggested by Nils Waldherr in the comments below Marcelo’s article.
  • CustomList: Agnes Barouh’s ever-useful method for generating a list of just about whatever you can write a calculation for.
  • SortableList: A basic CF that I wrote to coerce the other two CFs into joining it in the creation of the required assemblages. This could definitely be made a recursive function, but I got lazy and passed on the hard work to CustomList.

The guts of the CFs are not the point here, though. You can use them as is and put them to work right now without ever attempting to divine their aura or reading their entrails.

The calculations in the file apply the technique in various (slightly) different ways, but the concept is the same:

  1. Create a list
  2. Feed it to the CF(s)
  3. Point the value list definition at the result
  4. Set the value list sort to Unicode

1386121786254

The original request that started the aforementioned recent conversation was about a reverse-sorted, dynamically-generated list of years, starting with the current (or other management-defined) year. Done:

1386121786587

An issue that is addressed in the file is the fact that the BOMs remain in the data after you select a value from the list. You can choose to leave them there if the data is for display only, or you can remove them with a trimmer auto-enter calc on the field, if you need to use the data right away:

1386121785893

However, if you “clean” it, you lose the ability to see any already-selected value in the pop-up upon reenter. If that bothers you, you can remove the BOM’s later, anytime before you need to use the value.

1386121785932

[A friend pointed out that the BOMs can be added back in with a trigger upon clicking into the field, but you have to decide if you want to go to the extra trouble for that.]

Another point to remember here—just in case you like your value lists really long—is that FileMaker only indexes, and thus sorts, the first hundred characters of a field’s contents, so the maximum number of BOMs that FileMaker will pay attention to is 100. That, in a practical sense, limits your lists to 100 values with this particular technique. That’s more than enough for normal situations, but still a good thing to know, just in case.

Sorting It Out

So, to recap: sorting in a dynamic value list is solved. You now have at least two ways to do it: Mr. Piñeyro’s related-table-two-field approach and the above single-list-custom-function approach.

Now you can tell your clients that they can have their dynamic value list and sort it, too.

j.

Footnotes:

[1] Please note that the Byte Order Mark is an essential part of some UTF-16 data types and, as such, will have a different meaning in text exported as UTF-16 (Nope, not a clue. You?). Filtering it out prior to export will likely avoid any conflicts that might cause.

[2] Some Windows users have noticed that the BOM’s show up as boxes, but others who have used this technique on Windows haven’t been able to replicate that behavior. If you happen to run across the boxes while applying this technique, please be so kind as to relay the conditions under which you saw them, so our journey to the sorted side might be made complete.

[3] Since version 10 and the advent of the “Char” function, this technique is much simpler to implement, but earlier permutations of The Platform could possibly work with the BOM character copied and pasted into a CF or global field from elsewhere. The author is unaware of definitive testing being done in earlier versions, however. (That said, it hasn’t been tested in future versions of FileMaker, either, likely owing to that little detail about being in the future!)

Jonathan Fletcher lives in Louisville, KY, with his wife and daughters, and dreams of playing a FileMaker developer on TV. He coordinates his local FileMaker developers group, and hosts the FileMaker Talk: Business Edition podcast.

15 thoughts on “It’s Sorta a Value List Thing

  1. Rachel Laskowski

    First of all, thank you for providing this custom function. It’s great! I’m wondering if there is a way to apply this to a conditional value list. I’m relatively new to filemaker, so I’m not even sure if it’s even possible. I don’t want to waste to much time trying to make it work and then discover it just can’t be done. Your help would be greatly appreciated!

    Many thanks!
    -Rachel

    Reply
  2. Louise Nadon

    I have successfully sorted my value lists using the BOMs as discussed above in Filemaker 11 on a MacBook Air running Yosemite but when I opened the file under Windows XP, I saw the dreaded boxes…I checked and it was still sorted as Unicode.

    Reply
    1. Gilles Becker

      Hi

      I have tried to adapt your hack to my DB, but unfortunately, my know-how isn’t high enough to get it done.

      However with some of your parts I found a solution which works for me:
      Repeat ( Char ( 65279 ) ; 300 – ZeichenRechts ( ${mois/an} ; 2 )*14 – ZeichenLinks ( ${mois/an} ; 2 ) )&${mois/an}

      my date (${mois/an} ) is under the format of mm/yy, and i want the most recent month on top of my list. I get this done, but after 2021, this will not work because my calculation exceeds 300. If I try to use 650 instead of 300, I get as a result ?12/15, and I don’t know why. Maybe someone has a solution for this?

      Thanx in advance

      Reply
  3. Kirk

    I find that this technique works fine, but only for lists of 15 items or less. Attempted to sort a states list using this technique, and FM [14] stops displaying states at 15 and does not provide a scroll bar to move beyond those 15. (want P.R., Virgin Island, Marianna’s, etc. at 50+)

    Reply
  4. joelstonerblog

    Have been using this BOM technique with success, but just loaded FMS15, with with WebDirect, it appears that menus created this way are displaying the Byte Order Mark, but in the form of “feff” for each instance. The resulting menu looks like this:

    feff9
    fefffeff8
    fefffefffeff7
    fefffefffefffeff6
    fefffefffefffefffeff5

    Can someone else confirm they’re seeing this too? (still works fine in FMP15 client)

    Not very ‘eff-ing cool! I hope this is a WebDirect bug with a quick fix!!

    Reply
    1. Joel Stoner

      I’ve heard back from FMI that they’re able to replicate this issue, but (of course) no promise as to if/when they’ll fix it. Leaves be debating if I should un-do our sorted menus, search for yet another workaround to implement all over the solution, or cross fingers that there’ll be an early update with a fix… Thoughts anyone?

      Reply
        1. joel stoner

          WebDirect 15 only. FMPro 15 is fine. Looks like they made some other significant improvements in how menus appear in WD, and this is an unintended side affect. Just checked FMGo15 accessing a FMS15, and this trick is working fine there too.

          Reply
  5. Damon Casey

    I’ve found a problem with this technique in FileMaker 14 and 15 when a file is hosted. The first character is removed after a value is selected from a value list. If the selected value is Zebra and there is one non-breaking space in front of it, the length of the field afterwards is 5, not 6. It does work correctly when the file is opened locally.

    FileMaker 14’s WebDirect does work correctly but I can’t test in 15 because of the issue Joel Stoner has reported above.

    I’ve reported this to FileMaker on the Community site, including an example file, here:
    https://community.filemaker.com/message/589014#589014

    Reply
  6. Dina T

    This is great, and thanks for following up with the WebDirect resolution! Just implemented this for a new client including on WebDirect.

    Reply
  7. Matt Larson

    Interesting technique. Definitely hacky, and leaves me wondering what is wrong at FileMaker for making something like this necessary!

    I think an appropriate step, at least for the Manually Sorted Text example, is to apply the following auto-enter calc (do not replace existing value of field, if any):
    Substitute(Self; Char(65279); “”)

    That will effectively strip out the BOM character, which affects the character Length() count and is actually still selectable in the field. Downside is that reselecting the value list will not have this option as “pre-selected”, since the stored value is now missing the BOM.

    Reply

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s