[color:purple]Maybe there is a better way to do this, but I haven't figured it out or exactly how to do it correctly.

Database example: Classic School Database. TABLES: [color:blue][students], [Classes], [Enrollments]

What I am attempting to do: Using a classic School Database application, I need to create a custom sort order for the "[color:purple]ClassDay" field contained in the [color:blue][Classes] table. Instead of analphabetical sort, I need to sort data in this field in the following custom order:

VALUE LIST field data:

_____________________________________

[color:red]- (1) Mon.

- (2) Tues.

- (3) Wed.

- (4) Thur.

- (5) Fri.

- (6) Sat.

- (7) Sun.

______________________________________

In my roundabout way of achieving this, I created a separate field called [color:purple]ClassDayValue and manually assigned each day of the week in the [color:purple]ClassDay field a value between 1 and 7 (depending on the actual day of week). I'm not sure this is the best database design, but I needed a quick and easy way to to sort this table by [color:purple]ClassDay starting with Monday as the first Day of the week and ending with Sunday.

[color:brown]

Q: Is there a better design to this database? Perhaps setting up a calculation field which would automatically assign these values? Or some other method?

Edited September 29, 200520 yr by Guest

One method would be to create another table for "Days", which would contain seven records. You'd have 2 fields in the table; one days, 1 for the numeric value of the day. Then create a value list based on the field with the numeric values of the day and also show the day itself as values from a second field. Now create a relationship between your original table and the "Days" table. The key to the relationship will be the day number. Then make your day field in your original table a lookup or auto-enter calc that derives its values from the day field in the "Days" table.

To tie it all together now you'll need to format your day-number field in your original table as a pop-up list or pop-up menu. Then when you select the day number from the list, the day of the week will appear in the day field.

Finally, do your sorting by the day number, which now will correspond to the day of the week and you'll end up with exactly what you wanted.

Hope this helps. ;)

I made the DAY field in the original table (CLASSES) an Auto-enter Calculation that derives its value from the day field in the "DAYS" table.

In Browse mode, I can now select the new POP-UP menu which display both the DAY number and Day as:

[1 Monday]

[2 Tuesday]

...etc

However, after selecting a menu option and moving my mouse off the menu, the record will only display the numeric value in the field.

How, can I display the actual DAY of week in the record in Browse mode?

The numerical value of the day is what you select from your list (sounds like that's what you're doing) in the NumericValueField. Make sure you're not trying to enter the numeric value in the text field. The text field will return the proper day when a valid entry is made into the numeric field. Also, make sure "Do not replace existing value..." is NOT checked in your Define Database - Field dialog. And make sure you have both fields on your data entry layout to view your results in FM7 (if you have FM8 you don't need both fields). If you then go to a list view in your CLASSES layout, you don't need the numeric value of the day showing to sort by the numeric value (but if you'll enter DAY data in this view, you'll need the field).

Or.....

Try the attached file. It takes a different approach.

Hope any of this helps!

DaySort.zip