14 Replies Latest reply on Jan 3, 2010 11:06 AM by sunspotsbonaire

    Multiple fields may contain same info.  How do I collate?

    sunspotsbonaire

      Title

      Multiple fields may contain same info.  How do I collate?

      Post

      I have a database for an after-school program in which the kids can choose ten different activities from a list of about 50.  Any given activity could appear in any of the ten fields (choices 1-10).

       

      I need to be able to collate all the fields at once to determine how many kids want each activity and also which kids want which activity but I have no idea how to do this.  I am a novice with relational databases and could use some help.

       

      Thanks

       

      Alan 

        • 1. Re: Multiple fields may contain same info.  How do I collate?
          philmodjunk
            

          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.

          • 2. Re: Multiple fields may contain same info.  How do I collate?
            sunspotsbonaire
              

            Thanks for the rapid response.  I have never used portals and, frankly, found the help and user manual confusing.  Any advice is greatly appreciated.

             

            Alan 

            • 3. Re: Multiple fields may contain same info.  How do I collate?
              philmodjunk
                

              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)

              Name (text)

               

              In Activities you might have the following fields:

              StudentID (number)

              Activity (text)

               

              Now to create a portal... 

              1. In Manage | Database | Relationships, click and drag from
              2. Students::StudentID to Actvities::StudentID to relate the two table occurrences.
              3. Double click the line linking these two table occurrence boxes and select the "Allow creation of records via this relationship" option for Activities.
              4. Click OK as many times as you need to exit Manage | Database
              5. Find your Students layout, enter layout mode and use the portal tool to draw a rectangle on your layout.
              6. Select Activities as the source table for your new portal
              7. Select 10 as the number of rows you want to see in your portal
              8. Select the Activity field as the field you want to place in your portal.
              9. Click OK to exit the Portal Setup dialog box.
              10. Select your Activities field in your portal and use Field/Control | Setup... to format it to use your value list of activities.
              11. Now save changes and return to Browse Mode
              12. You should see a single blank line in your portal.
              13. 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.)

               

               

              • 4. Re: Multiple fields may contain same info.  How do I collate?
                sunspotsbonaire
                  

                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

                 

                Alan

                 

                 

                • 5. Re: Multiple fields may contain same info.  How do I collate?
                  philmodjunk
                    

                  No problem. I'm about to leave my office also.

                   

                  Happy New Year.

                  • 6. Re: Multiple fields may contain same info.  How do I collate?
                    sunspotsbonaire
                      

                    And the same to you.

                     

                    Alan

                     

                     

                    • 7. Re: Multiple fields may contain same info.  How do I collate?
                      sunspotsbonaire
                        

                      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

                       

                      Alan 

                      • 8. Re: Multiple fields may contain same info.  How do I collate?
                        philmodjunk
                          
                        1. Go to Manage | Database | Fields and add the following new field to the Activities table:
                        2. StudentCount, Summary, Total of StudentID
                        3. Click OK until you've dismissed the Manage | Database dialog
                        4. Find your Activities layout that was created automatically when that table was created.
                        5. Double click the body part label and change this part to Subsummary when sorted by Activity.
                        6. Edit this subsummary part of your layout so that you have just two fields in it:
                        7. [Activity   ][StudentCount]
                        8. Resize this part so that it is just barely taller than this pair of fields.
                        9. Enter browse mode and sort your records by Activities::Activity
                        10. If you are using Filemaker Pro 10, you're done.
                        11. If you are using an older version of filemaker, switch to preview mode to see the report.
                        • 9. Re: Multiple fields may contain same info.  How do I collate?
                          sunspotsbonaire
                             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
                          • 10. Re: Multiple fields may contain same info.  How do I collate?
                            sunspotsbonaire
                              

                            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.

                             

                            Alan 

                            • 11. Re: Multiple fields may contain same info.  How do I collate?
                              comment_1
                                

                              sunspotsbonaire wrote:
                              Should I have structured the database differently?

                              Yes.

                               

                              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:

                              http://www.fmforums.com/forum/showpost.php?post/246136/

                               

                               

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

                              • 12. Re: Multiple fields may contain same info.  How do I collate?
                                sunspotsbonaire
                                  

                                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.

                                 

                                Alan 

                                • 13. Re: Multiple fields may contain same info.  How do I collate?
                                  comment_1
                                    

                                  sunspotsbonaire wrote:

                                  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.


                                  • 14. Re: Multiple fields may contain same info.  How do I collate?
                                    sunspotsbonaire
                                      

                                    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.

                                     

                                    Alan