SORTING data in ValueLists / lookup fields - [ Specifically - sorting records by

[Ollie Dog!]

I am building a classic school database application for our Horseback riding school. My wife needs me to sort the "[color:blue]CLASS records" by days of week in a predefined (numerical) order - with each class displaying a list of students who ride on a particular day:

=====================================

Monday Class-1 Enrollments:

- Allen

- Betty

- Jan

- etc.....

Tueday Class-1 Enrollments:

- Tim

- Willie

- Sally

- etc...

Tueday Class-2 Enrollments:

- Stacey

- Sharon

- Betty

- etc...

=======================================

To accomplish this, I created a separate TABLE called "[color:blue]DAYSOFWEEK" with the following fields:

[color:green]

• DayNumber
  
• Day
  

(Obviously there are 7 total records in this table:

-------------------------------------------------

[color:green]Record #1: DayNum=1 Day=Monday

Record #2: DayNum=2 Day=Tuesday

Record #3: DayNum=3 Day=Wednesday

Record #4: DayNum=4 Day=Thursday

Record #5: DayNum=5 Day=Friday

Record #6: DayNum=6 Day=Saturday

Record #7: DayNum=7 Day=Sunday

My next goal is to allow the user to assign this value pair to a [color:blue]CLASS Record using a VALUELIST lookup. Thus, I created a new value list called "C[color:blue]LASSDAY".

-------------------------------------------------

VALUE LIST: "CLASSDAY"

"DAYSOFWEEK::DAY"

and

"DAYSOFWEEK::DAYNUM"

-------------------------------------------------

CLASSES RECORD:

ClassName: (field-1)

ClassDay: (field-2: Pop-Up Menu: ValueList: CLASSDAY)

-------------------------------------------------

[color:red]ISSUE: The Value list look up feature works correctly, however, I am not able to sort all the CLASS records by their repective ClassDay values.

e.g., (In this predefined numeric order):

1 Monday, 2 Tuesday, 3 Wednesday, 4 Thursday, 5 Friday, 6 Saturday, 7 Sunday

-------------------------------------------------

I tried to make this a straigtforward as possible. However, is this example too comlex to undestand without acutually seeing the database file?

Your suggestions would be most appreciated....!

[Ender]

It's not clear where you're getting stuck, but you should be able to use a columnar list with a sub-summary part by DayNum and then sort by the related DayNum and ClassName.

This is a bit confusing, however:

My wife needs me to sort the "CLASS records" by days of week - with each class displaying a list of students.

A Class table should be just the information about the Class, when and where it meets, who the teacher is, what the subject is, etc. Student data should be in a separate Student table (or Enrollment table) that's related to Class. With this, the class list report would be run out of the Student (or Enrollment) table, showing sub-summary parts for both DaysOfWeek::DayNum and Class::ClassName, with the Student Name in the Body part.

[comment]

I am not too sure what your question is, either.

If you define a value list of custom values:

Monday

Tuesday

Wednesday

Thursday

Friday

Saturday

Sunday

and your records have a WeekDay field (text) containing one of these values, you can sort records by custom order based on the value list. That is one of the options in the Sort dialog. No table or conversion to numbers is required.

This is suitable for a FIXED weekly schedule. If a schedule is prepared separately for each week, then you'd simply use the actual dates.

[Ollie Dog!]

;) Okay that may be trick. I was not aware of the custom sort feature, but that makes a lot of sense now. So it really does not have to be as complex as I was making it.

thanks, I'll give that a whirl....!