I've searched around because I can't be the first person with this
problem, but I haven't really found anything. Obviously I'm using the
wrong search terms.

But I'm creating a database where a user enters data into check
boxes.

Then, this data is need to be displayed in numerical order (not entry
order) in a report separated by commas. I've got the separated by
comma's part solved. However, since the data seems to find itself in
a repeating field, I'm at a loss as to the easiest way to display it
in numerical order.

For example:

User enters in

111
143
220

Then gets new information and enters
152

I need it to diplay as

111, 143, 152, 220

Anybody have any ideas as to how I can solve this dilemma?

Thanks in advance

Kelly B.

In article <1172071321.578116.8490 (AT) t69g2000cwt (DOT) googlegroups.com>,
"Kelly B." <kellyfaboo (AT) gmail (DOT) com> wrote:

I don't quite see how "the data seems to find itself in a repeating
field" comes into it. I'm only guessing, but what I think you really
mean is that the checkbox values that are checked are stored in the
field separated by cariage return characters (as can be seen when
formatting the field as a "normal" field). This is not a repeating
field, it's just the way checkbox formatted fields work. In a real
repeating field each value is stored separately, and you could have a
set of checkboxes stored as a repeating field.


Back to the problem - you can't sort data within one field (at least
within standard FileMaker, there may well be a plug-in that does
precisely this), except by using a script. This would be rather
annoying and slow since the database would have to sort the field for
every Found record every time you want to print the report.

There is a better way, but it will mean re-entering any existing data.
(There are a couple of ways around this, but unless you've got lots of
existing data it's not really worth wasting time on.)

Instead of one Value List containing all the possible values, create a
set of Value Lists each containing just ONE value.
eg.
vl_111 = "111"

vl_143 = "143"

vl_152 = "152"

vl_220 = "220"


Next create a matching set of fields, one for each possible value. Put
these on the Layout instead of the existing single field, and format
each one as a checkbox using the appropriate Value List
eg.
Field_111 using vl_111

Field_143 using vl_143

Field_152 using vl_152

Field_220 using vl_220


Now you can create a Calculation Field that combines the checked values
in the numerical order you want, ignoring ones that are not checked /
empty.
eg.
ChosenValues {Calculation, Text Result, Unstored}
= If (IsEmpty(Field_111), "", Field_111 & ", ")
& If (IsEmpty(Field_143), "", Field_143 & ", ")
& If (IsEmpty(Field_152), "", Field_152 & ", ")
& If (IsEmpty(Field_220), "", Field_220 & ", ")

Since you'll never know which value will be last checked one, it's
easiest to add a comma (and space) to the end of every value and then
use a second Calculation field to remove the extra left-over
comma-space from the end.
eg.
ChosenValue_Display {Calculation, Text Result, Unstored}
= Left(ChosenValues, Length(ChosenValues) - 2)

All done!
Use the ChosenValues_Display field wherever you want the list of
comma-separated numbers. You can put this field temporarily on the same
layout as the checkbox fields and try checking / unchecking different
values to see it working.


There are other Calculations you could use, but this is probably the
easiest to understand. For example, you could change ChosenValues to
ALWAYS add a "#" character to the end and then use the Substitute
function to remove the text ", #".


Another way would be to keep the existing single field and write your
own scripts to add and remove values from the existing single field
using 'invisible' buttons over each checkbox to run them, but this
would be more complicated than the above separate fields approach.


Helpful Harry
Hopefully helping harassed humans happily handle handiwork hardships ;o)