Do you know how to set up and use a "portal"? If not, this is a very useful topic you can look up in Filemaker's help system.
If you take your "10 different fields" and replace them with a portal of 10 rows (each row is a record in a related table). It'll be much, much easier to do what you want to do here. A simple summary report based on this portal's table of records could then list out how many kids have selected each activity.
Thanks for the rapid response. I have never used portals and, frankly, found the help and user manual confusing. Any advice is greatly appreciated.
A portal allows you to see and edit multiple related records in a scrolling list.
I'm guessing here that you have a table where one record records the activity selections for one student. Let's call this table Students. We can create a second table in the database called Activities to record the activity choices.
In Students, you might define the following fields:
StudentID (auto-entered serial number)
In Activities you might have the following fields:
Now to create a portal...
- In Manage | Database | Relationships, click and drag from
- Students::StudentID to Actvities::StudentID to relate the two table occurrences.
- Double click the line linking these two table occurrence boxes and select the "Allow creation of records via this relationship" option for Activities.
- Click OK as many times as you need to exit Manage | Database
- Find your Students layout, enter layout mode and use the portal tool to draw a rectangle on your layout.
- Select Activities as the source table for your new portal
- Select 10 as the number of rows you want to see in your portal
- Select the Activity field as the field you want to place in your portal.
- Click OK to exit the Portal Setup dialog box.
- Select your Activities field in your portal and use Field/Control | Setup... to format it to use your value list of activities.
- Now save changes and return to Browse Mode
- You should see a single blank line in your portal.
- Select an activity in the activity field and you should see a new blank row automatically appear for the next such activity.
See if you can get this far and then we'll tackle how to set up a summary report showing all the student activity selections.
(Note: I've simplified this a lot from what your final version may look like. For one thing, you'll probably want additional tables to handle different student's activity choices for different days that they attend the program--but we'll save that complication for later.)
Thanks again for taking the time to go through this with me. I will have to give it a try but seeing the lateness of the time it may be next year
No problem. I'm about to leave my office also.
Happy New Year.
And the same to you.
I have followed your instructions and now have the 10-line portal with the drop down menus and it skips to the next line (menu) when I enter an activity on each line. It was much simpler and quick than I thought. Guess I may be ready to move on. TTYS
- Go to Manage | Database | Fields and add the following new field to the Activities table:
- StudentCount, Summary, Total of StudentID
- Click OK until you've dismissed the Manage | Database dialog
- Find your Activities layout that was created automatically when that table was created.
- Double click the body part label and change this part to Subsummary when sorted by Activity.
- Edit this subsummary part of your layout so that you have just two fields in it:
- [Activity ][StudentCount]
- Resize this part so that it is just barely taller than this pair of fields.
- Enter browse mode and sort your records by Activities::Activity
- If you are using Filemaker Pro 10, you're done.
- If you are using an older version of filemaker, switch to preview mode to see the report.
Hi; Dd it all but got nothing when I preview. I'll give this some though over the holiday and try to see where I've gone wrong. Then I'll get back to you. Thanks again. Alan
Hi. Hope you had a nice New Year's Eve and a healthy, happy 2010. I have been breaking my head over this for hours now and i wonder if i don't need to go back to square one. Here's the situation: I have 200 students who each have selected ten activities from a list of about 50 choices. I need the following information: Number of students for each of the activities and the ability to rank the activities from most chosen to east chosen, names of the students for each activity so that they can be assigned a roster.
I set up my original database with the following fields: Student #, First name, last name, choice #1, choice #2 etc through choice #10
The problem as I se it is that any activity may appear in any of the 10 'choice' fields and I need to capture them whatever field they appear in.
Will my original setup allow me to provide the info I need? Should I have structured the database differently?
Sorry to be such a clod about this but up ntil now I have only been making very simple, flat databases and this is my first venture into three dimensions.
Should I have structured the database differently?
Ideally, you should have three tables: Students, Activitites and a join table of choices (one record for each instance of a student choosing an activity). See a basic demo here for an example of how it might work:
It is also possible to do with two tables only, by combining your 10 choice fields into a single one (which could be formatted as checkboxes).
The finding/reporting abilities of this model are rather limited, but the things you have mentioned (the count of students in each activity, the ability to rank the activities by this count, and listing the names of students for each activity) would be possible. However, I am not sure what you mean by "assign a roster".
Thanks for the suggestions. I will look at the link and also try some of the ideas you suggested. The 'assign a roster' will be done on the office computer which already has rostering capabilities. I am trying to work up something off-site to speed up the registration process.
The 'assign a roster' will be done on the office computer which already has rostering capabilities.
That doesn't anything about the data that needs to be provided for the rostering - and this could well be an issue that cannot be solved without a join table.
OK. It's clear that I need to go back to the books and study up on relational databases before I try again. Thanks very much for all your time and advice. I hope that, should there be a next time, I will be better prepared.