Days of Week, in proper ascending order?

[kennedy]

What's the best way to define a DaysOfTheWeek value list?

Because I use simple column sort (click on the heading of the column you want to sort by), I'd like "Sort Ascending" on such fields to sort M-Tu-W-Th-F-Sa-Su. But I want the field values to look pretty "Monday", rather than "1".

I've come up with the following approach:

Define the field to be a Number field that strictly demands to be a member of the "Days" value list. Define the value list to be "Monday 1", "Tuesday 2", "Wednesday 3", and so on to "Sunday 7".

Then when you see the field, it looks good... if you size it just right, you won't even see the number. But even if you do, no big deal. Further, by making it a number field and leveraging the fact that FMP will ignore all but the digits in interpreting a Number value, it will sort ascending just the way I want... even when pulled via GetField in my nifty multi-column sort.

The one disadvantage is that if you had to type in the value, it would be annoying to have to remember the extra digit. So, a popup list or menu would be a necessity. But that means, to support power users (tabbing and typing), it will be a popup list and they will need to remember to hit <Enter> instead of <Tab> after typing the first letter of the day they want.

Other options would be two-letter codes followed by digit, making it easier to type. Another would be to just use numbers 1-7 and not worry about the ugliness. Or provide a computed field that computes the pretty name for display, but you use just the number for entry (despite the associated weirdness).

So, what do you smart guys recommend/use for Day of the Week?

What issues should I be aware of with my plan above?

Thanks.

[LiveOak]

I think you are working a little too hard. You can perform a sort on a custom order defined by a value list.

-bd

[kennedy]

I understand that... but my users won't find the FMP Sort Dialog particularly friendly... and I don't want to write a complex sort of my own. So, I offer my users an easy, but flexible sort mechanism... just click on the little button in the column header, and it sorts by that column. Click on a second column and it sorts by those two columns. Easy. Up to three. (Topic of a Left Brain thread.)

But that nice user mechanism would make doing a custom sort very difficult. Hence my solution... a numeric mapping of the value list so that it automatically sorts in the right order. (I wish FMP had more traditional enumerations, where each value in the value list was automatically mapped to the number it is in the list... then I wouldn't have to put the number in the name.)

Hope that explains why I am trying to do this.

[The Bridge]

For what it's worth, you can find the position of an item in a valuelist with this calculation:

ValueListPosition = PatternCount("

[Vaughan]

"But that nice user mechanism would make doing a custom sort very difficult. Hence my solution..."

Harder how? You just need to define a value list, create a sort script that custom-sorts ascending (and another for descending) and use these sort scripts in the interface you have already made

Your "solution" vis the mapping stuff is a right royal kludge, the stuff that developers like me get paid $$$$ to fix up later when it stops working and you've left the company. And the $$$$ are hard earned, because once a kludge like this is used, all sorts of other kludges need to be added to keep the thing working and stop it from breaking.

Just do it the right way and you'll find it easy, because it is easy. Kludges are always harder.

[kennedy]

If I was doing simple single-column sorts, then I'd agree with you. However, I am doing the nifty multi-column sort that we devised in the thread:

http://www.fmforums.com/threads/showflat.php?Cat=&Board=UBB15&Number=44072&page=0&view=collapsed&sb=5&o=&fpart=1

Thus... to me its a choice of kludges, as "the right way" involves fixing FMP... either the ability to define proper enumerated types... or an ability to construct sorts programmatically... or at the very least, have FieldType return the value list associated with a field definition (right now the design functions only tell you the value list associated with an object on a layout... but I cannot get that in the sort field calculation).

But assuming FMP is staying as it is, my choice of kludges are:

1) Define the field as Text and the value list normally and let the column sort alphabetically, resulting in Fri, Mon, Sat, Sun, Thu, Tue, Wed sorts. If the user doesn't like that, they can always use the FMP sort dialog, if they can grasp it.

2) Define the field as Text but the value list with digits at the front, "1 Monday", such that it sorts properly as Text. Its fairly readable, but users will have to use the number system for typing Days of Week.

3) Define the field as a Number and use 1 through 7 as Days of Week. You lose readability, but gain some simplicity.

4) Define the field as a Number and the value list as "Monday 1" such that you get readability and you input it by using the first letter of the day name.

After playing with this some more, I find FMP's popup lists a little clunky when choices have common starting letters. It doesn't allow you to type the first two letters and complete. Nor does it consistently cycle through the options that start with a particular letter... it will only go in one direction and then stops. So, with either options 1 or 4 (value lists starting with the day names), the power user will learn that "Thursday" is spelled "mtt[enter]"... the "m" moves it to the beginning such that "tt" always lands on Thursday. Ick.

So, now I am thinking options 2 or 3 might be better for power users... just hit the number and then either enter or tab, respectively. Option 3 has the advantage of avoiding popup lists and their disruption of tab-between-fields. But for the non-power users, option 2 has a distinct readability advantage, as the numbers are self-documenting.

So, assuming my users need simple multi-column sort far more often than they need Day of Week types, which of those 4 options would you smart-guys choose? Or am I missing a better option #5?

Thanks.

P.S. I suppose I should post a cross-reference from the multi-column sort thread to this one, so that anyone who decides to use that will be aware of this issue.

[BobWeaver]

Regarding so called 'power users' who tab and type, I just got back from trying to clean up a customer's mess where some user had bypassed the popup menu and typed in their own values. They used incorrect terminology and/or misspelled words which caused scripts and relationships to be broken because they depended on the exact values from the value list.

As an example, suppose you do summaries by day of week, and someone accidentally types in 'Teusday' instead of 'Tuesday'. Now your summary report will have 8 days on it because of user data entry error. Do not ever allow users to manually enter things that need exact spelling or wording. It will be a disaster. Make sure you have the field validation option ensure that the data is in the value list.

[kennedy]

Regarding so called 'power users' who tab and type, [...] Do not ever allow users to manually enter things that need exact spelling or wording. It will be a disaster. Make sure you have the field validation option ensure that the data is in the value list.

Agreed, I have strict validation on. The tab and type users are just typing the first letter to select from the popup list... no need for mouse to use the popup list.

After more experimentation, the "1 Monday" approach feels real good, at least for small enumerations that will never change (like days of the week). For enumerations that have possibility of change, the numerics would be problematic... you'd get a goofy ordering or you'd have to go through and re-set numbers. Ick.