2 Replies Latest reply on Nov 24, 2014 7:55 PM by philmodjunk

Sort Multi-Value Checkbox Field in Specific Sequence

Title

Sort Multi-Value Checkbox Field in Specific Sequence

Post

I have a checkbox field (Students::DaysAttending) with four possible values: "M,"  "T,"  "Th,"  "F," these being the equivalent of Monday, Tuesday, Thursday and Friday (school days where students attend at my wife's school). Being a checkbox, the user can of course select/de-select any value in any random order, and being a checkbox field, these values are added into the return-delimited list of values in the order they are selected.  Therein lies my problem.

I take the values from this field and manipulate them in another calc field and process the values in a number of ways for a variety of uses.  One particular use involves the application of a Merge Field  (<<DaysAttending_Horiz_spelled_w_and>>) in a list view sub-summary part which yields the following example results, depending upon user selection in the DaysAttending field:

EXAMPLE RESULTS

• Monday, Tuesday and Friday
•
• Monday and Friday
•
• Tuesday and Friday
•
• Monday, Tuesday, Thursday and Friday

Of course, more day combinations are possible, but you get the point.

My above example assumes the user selected the checkboxes in the natural order in which they occur during a week and so the sequencing looks perfectly fine here, but that won't necessarily be the case in every event.

I need to sort the resulting calculation field to ensure that the days, as few or as many as they are selected by the user, always display to reflect the natural order they occur in the week.  My online search results find some examples that vaguely relate to my problem but not exactly.

Has anyone done this before?  Does a built-in function accomplish this?

Thanks, as always.

• 1. Re: Sort Multi-Value Checkbox Field in Specific Sequence

You could use the Substitute function to replace your day name abbreviations with day-of-the-week numbers, use a custom function like this to sort those, then Substitute the day names back in place of the numbers.

• 2. Re: Sort Multi-Value Checkbox Field in Specific Sequence

You may ultimately find that a set of related records is a better option than a single field with a check box set, but here's an auto-enter calculation that will automatically sort the selected values into the same order as they are listed in the value list:

FilterValues ( ValueListItems ( Get ( FileName ) ; "YourValueListNameInQuotesHere" ) ; self )

And be sure to clear the "do not replace existing values..." check box.

To see examples of a set of related records in a portal looking and functioning like a check box field (but with a scroll bar), see "Adventures in FileMaking #2 - Enhanced Value Selection"