10 Replies Latest reply on Sep 17, 2010 12:17 PM by philmodjunk

    Newbie - Relationships

    RebeccaBoyd

      Title

      Newbie - Relationships

      Post

      I am new to Filemaker and we are using Pro 11.

      I am trying to figure out relationships and where to put some of the information I have so that I can properly 'pull it out' later. I do not know how to upload my relationships table to show you what I have and ask where I need to put information, please help.

       

      Ok, now that the screen is posted. My database will ultimately need to tell me 1. How many and what kind of workshops did we have? 2. How many teacher attended the workshops or one in particluar? 3. What schools participated in the workshops as a whole or individually? 4. How many children attended the workshops as a whole or individually? The area and trustee information will be needed as well. I have the tables split up this way because I may need to get specific information from each table. I currently do not understand how to relate the schools with the workshops. Some of them are actually held at a particular school. Or Where do I put the number of children that attended? 

      I am sorry for the amount of questions, but I am new and trying to be a pro in a short amount of time... 

      Relationships.jpg

        • 1. Re: Newbie - Relationships
          philmodjunk

          Do you know how to take a "screen capture" of your screen?

          It's different Mac than Windows but it can be done on either machine. Once you have that image file, you can easily upload it to your first post above (click the edit button).

          You can also document your relationships with just text:

          Table1::FieldName = Table2::FieldName2 AND
          Table1::FieldName3 = Table2::FieldName4

          Is one way to describe a relationship between table1 and table2 based on two pairs of "match" fields.

          • 2. Re: Newbie - Relationships
            RebeccaBoyd

            I edited, please see above...

            • 3. Re: Newbie - Relationships
              philmodjunk

              1. How many and what kind of workshops did we have?

              What's the context? You can perform any number of different finds on the Workshops table. You could, for example perform a find for all Workshops held during the Month of September or all held at a particular location or....

              2. How many teacher attended the workshops or one in particluar? 

              The number of Workshop Line Item records will tell you that. Best bet is probably a summary report based on this table.

              3. What schools participated in the workshops as a whole or individually? 

              Not sure how you can tell from the given tables how to distinguish between "Whole" and "individually". You can use the existing relationships to pull up a list of all school records for a given workshop. You can perform a find on Schools and specify the related workshop id number or numbers.

              4. How many children attended the workshops as a whole or individually? 

              I don't follow what's different between "whole" and "individually"  here, but assuming each child comes with their teacher, you can record class sizes in a number field in the Workshop Line Item table. Then you can include student counts in the summary report I described earlier.

              I currently do not understand how to relate the schools with the workshops.

              Currently, your schools are related to workshops via the teacher and line item tables. If you need to, you can include a location field in your workshops table to record that a workshop was held at a specific school.

              You might consider one more table. I'm guessing you actually hold the same workshop on several different dates and locations. If this is so, you may want to add a "sessions" table for each date and location for a given workshop. Put it where you currently have your workshops table and link the workshops table to it.

              Do you know you can add fields from other related tables to a given layout to show related information? You can use portals and also just add the related fields directly to a layout or even a portal. Thus you can put a portal to workshop line items on a teacher based layout and put fields from workshops in the portal row to list the workshops that teacher attended.

              Here's a tutorial on summary reports you may find useful:  Creating Filemaker Pro summary reports--Tutorial

              • 4. Re: Newbie - Relationships
                RebeccaBoyd

                Thank you for your help - I will work on this right away...

                • 5. Re: Newbie - Relationships
                  RebeccaBoyd

                  Ok, so I added a portal to my teacher table, and figured out the related fields and input them.

                  I still need help with where to put my students. They actually do not always attend with their teacher. Some of the workshops (classroom development) are at a specific school, but may be in 3 different classrooms, and on the same day, they will go to a different school and be in 3 more classrooms - I currently have the workshops distinguished by date and time. There is a school participation line, but I have nothing in it, since I am unsure of how to put the information there - I think it is a many to many 'thing'.  Some (professional development) are held in a Conference Room and only teachers attend. So, I have the professional development ones figured out, but how do I do the classroom ones? Do I need an additional table? Or would it somehow be added to the Workshop Line item one?  

                  • 6. Re: Newbie - Relationships
                    philmodjunk

                    That's an interesting set of challenges and I'm not sure which option is best here. You could create a workships line item record for your "students with no teacher" to complete the record of who has attended a workship. You might create a record in Teachers that links to the school, but is identified as "unaccompanied students" which you could then link to this WorkShop line item record. That allows you to record how many students from each school, but it'll make counting teachers more complex as you won't want to count a record in teacher used in this way.

                    As I suggested earlier, I think you need two tables for your actual workshop records. One would record the subject and other details of a given workshop while a second "sessions" table has one record for each date and location where the workshop is held.

                    • 7. Re: Newbie - Relationships
                      RebeccaBoyd

                      If I add an additional table, will it mess up the information that I already have entered? I currently have the workshops separated by the date or time that they were performed. Example, Workshop 1 is Hoby - Puppets - 8/30/2010 - 10:30 am - Classroom Development. Workshop 2 is Hoby - Puppets - 8/30/2010 - 4:30pm - Professional Development. So, I have 61 Records for Workshops because I split them up that way, but in one table. Is there a way to do as you have suggested without messing-up the information I currently have entered? Would I be able to then add a student count in this Sessions table to record the amount of students - just putting a 0 if it is a Professional Development?

                      • 8. Re: Newbie - Relationships
                        philmodjunk

                        You can use Import Records to move data from one table to another. It may well be easier in your case to rename your workshops table as "Sessions" and then use Import Records to copy data from the existing table into the new one, or you may find it easier just to type the data into the new table as 61 sessions isn't a huge number and the list of related Workshop records will be shorter than this. Once you've got the data in Workshops, you can delete the now redundant fields in Sessions that held the same info. On your layouts, you'd first respecify the fields for this data to refer to the new Worshops fields in place of the now redundant sessions fields.

                        Things would link up like this: WorkShopLineItems>----Sessions>-----Workshops (   >--- means "many to one" )

                        Note that your existing Workshops table is already linked where I've put sessions, so renaming the table leaves it related correctly to the other tables and you just add in the new Workshops table.

                        You could certainly put number fields in the Sessions table to record the number of students and teachers attending. If, however, you need a breakdown by school or teacher and one workshop session can draw from multiple teachers or schools, you'd need to record participation numbers in the line items table. A calculation field in Sessions, could then use the Sum() function to compute the total numbers attending a given session--so it depends on what you need here.

                        • 9. Re: Newbie - Relationships
                          RebeccaBoyd

                          How do I add the Location field for my sessions table since the locations are schools? I know to use related records, but if I use the school name, it is going through my teacher table and pulling from there (not accurately). 

                          • 10. Re: Newbie - Relationships
                            philmodjunk

                            You can create a second table occurrence (that's what we call each "box" in the relationship graph) of schools and link directly to this second table occurrence.

                            For more on the subject of Table Occurrences and how to use them:  

                            Tutorial: What are Table Occurrences?