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....!

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....!

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?

[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?

Dear Sanjai - Thank you for your guidance - it works!

This is another classic School Database setup - Tables: [color:blue]STUDENTS, ENROLLMENTS, CLASSES. From the STUDENTS table, I've created a portal which contains student enrollment data. [color:red]Issue: I've been sucessful in setting up and displaying data from this relationship, however I also need the capability to [color:red]MODIFY records for that student contained in the portal. [color:red]For example: from the STUDENTS table the user [registrar] should be able to [color:blue]ADD a new class to the students record. Currently, I can only [color:red]view those classes (via the PORTAL) which had been created in the ENROLLMENTS table. Any modification must be made through enrollments table directly, and not on the student record being viewed. How can I add this [color:red]edit capability within the PORTAL - Please advise... :

YES! - that's the solution I needed. Thanks for your input. ???

I am attempting to build upon a classic school database which is designed to track [color:blue]STUDENTS, [color:blue]CLASSES and [color:blue]INSTRUCTORS. In this database, Classes are identified only by their unique id field and not by a discernable name convention such as "Biology 101" or "Algebra-2" for example. Thus, to enroll a student in the proper section, the user (registrar), must be able to match the following fields to each class id: [color:brown]- ClassDay - ClassTime - ClassInstructor From a UI perspective, I need to show the user a [color:blue]drop down menu lsiting ALL the possible classes, diplaying {[color:brown]ClassDay, ClassTime, ClassInstructor} for each menu item. e.g. [color:blue] -------------------------------------- Mon, 3:45 PM, Jackson Wed, 1:30 PM, Sanders Wed, 8:30 AM, Peterson Fri, 2:00 PM, Wilson --------------------------------------- On the STUDENTS Detail page, the registrar should be able to simply select one of the menu items listed to add that student to the class roster. _________________________________________ _________________________________________ [color:red]Q: What do I need to do on the students detail page to make this menu selection work dynamically with the data contained in the [color:blue]CLASSES table? - [color:red]NOTE: The Value List option I tried will only show a max of two attributes (I need to show all three). - Is there a better method to achieve this design goal? : Please HELP

Wonderful! Thanks for the heads up on this feature. I'm still finding my way around FM, and loving it when I learn a new trick or get a good tip. cheers...

Objective: I'm trying to generate a report which will automatically generate a sequential 'number' for each record in the found set. For example: I have a database of students which contains both active and inactive students. Each school quarter, I need to create a list showing just the active students enrolled. The printed list looks like a simple spreadsheet with the fist column (# field) displaying the students number in the list. e.g., the first student record shown would display "1", the second "2", and so on... ============================= # Student Name --- ------------ 1 Adams, Patick 2 Davis, Myla 3 Jackson, Kendal 4 Smith, Chad ============================= Question: How do I create these numbers dynamically, so that when I run a report, they are automatically generated for each record? Obviously the number assigned to the record would depend on the sort order. Also, It wouldn't make sense to simply hard code the numeric values, since the values would change based on the total records in the found set. Any suggestions?

Thank you all for your posts on this topic. I was just about to ask the same question myself when I came upon this wonderful thread. You all have saved me countless hours. It would seem calculating one's age would be a fairly common task to many applications. Couldn't FileMaker just include this as simple function (i.e., a drag and drop solution we could simply plug into a calculation field)? Anyway, thanks again.