I have a field that is a Check box attached to a value list. The value list are "Amenities" and each amenity returns a number in another field. If Amenity A is selected, then the result number associated with that Amenity should be 124.

I a very close having the numbers show up correctly however some of the amenities result in two numbers. For example: Amenity "24 Hour Front Desk Attendant" (Number "6") and Amenity "24 Hour Front Desk Attendant (On Call) (Number "179"). When I check "24 Hour Front Desk Attendant", the result number shows the correct number as "6" however, when if I check "24 Hour Front Desk Attendant (On Call), the result number shows both, numbers "6" AND "179". I have played around with this for days and I just can't seem to get it right. I have my calculation show in the screen shots attached.

The calculation is set to display certain amenity numbers in a certain order separated by a comma.

Please help anyone.

Yikes!

I believe that you'd be better off with a table of Amenities, each with a unique ID and a Description.

This massive checkbox is not the way to go (as you see, reporting is impossible). This should be a child table (join btw parent and Amenity), storing the parent ID and the Amenity ID. Then, you report off this child table.

As for Sort Order, just put a SortBy field in the child table. It can be lookup from the Amenities table.

Edited October 23, 201015 yr by Guest

Even without a join table, you should have a table of Amenities with fields for description and the associated code number. The value list should use either the code number or a serial AmenityID as the first field, and description as the second. See a very similar example here:

http://fmforums.com/forum/showtopic.php?tid/217298/post/368710/#368710