4 Replies Latest reply on Dec 29, 2013 7:01 PM by JennySemmler

    Beginner question about tables, fields, and relationships



      Beginner question about tables, fields, and relationships


           I'm beginning to learn to use FMPro to keep track of records for a voluntary association of foreign language teachers.  Teachers' names and other personal information will be in a Teachers table.  I need to keep track of what languages teachers teach.  Most teachers teach only one language, but some teach two.  How best should I handle this.  Language1 and Language2 fields in the Teacher table?  A separate Languages table and a join table between the Languages and Teachers tables?  Something else?  And how to view the languages on the Teacher info. screen and how to sort Teachers by language...

           Thanks for any help and advice.

           Richard Detwiler


        • 1. Re: Beginner question about tables, fields, and relationships

               Welcome also.  A lot of brand new people to FileMaker this week (Santa?).

               As always it depends.   Is this the only thing you plan to do with the database?  Or do you see it growing and growing?  Best to start with the end in mind to save from re doing much of your database.

               Is it always only 2 languages max per teacher?

               If you plan to do a lot of reporting, then maybe a separate table for languages.  Put a portal on your Teachers table to show languages.  This way, you can easily set up a report by languages, i.e "Who are all the teachers who teach French?", etc.

               Or, maybe languages needs to be the main table with a portal of teachers below.

               Do you need 3 tables:  Teachers, Languages, Classes (Assignments).

               Is there ever a situation where 2 teachers teach one class together?  Or one starts for a few weeks, and another finishes?

               Do you need to report the number of classes a teacher taught, sorted by language?

               You could also use one table, and list all the languages with checkboxes, selecting each per teacher.  But, are all the languages equal--meaning are there teachers who teach basic French, and some who teach advanced French, etc.  This would be easiest to set up, but may leave you having to rebuild if you grow/require more functionality and reporting.

          • 2. Re: Beginner question about tables, fields, and relationships

                 No, not Santa, just break from school.  I bought FileMaker over the summer and started, but then got caught up with daily school life in Septemer.  Many thanks for your help.  I'd thought of the check box list, but decided I'd rather take up less space on the screen with the language information.  Yes, someone could possibly teach more than 2 languages.  I would like to be able to generate reports (mailing lists, mailing labels, etc.) by language.  As a county-wide association we keep track of names, addresses, schools, school addresses, school district, annual dues records (date and amount paid for which school year), e-mail addresses, and languages taught.  I'll likely have questions about other things, but decided to start out with looking for help with the Language piece.  We didn't include levels of language taught in the rudimentary MS Access database that I made and used for the past several years (prior to graduating to a MacBook, for which I bought FileMaker), but it would be better if we did include the ability to record levels (Elementary School, Middle School, Levels I, II, III, IV, V, AP) for each language.  I had separate Language1, Language2, and Language3 fields on the old database, but something didn't feel highly efficient about recreating the new database that way.

                 Re: schools-  Most teachers teach at one school, but occasionally someone teaches at more than one, so I have to account for that possibility.  Schools have addresses. Schools are public or private, and public schools belong to school districts.  I need to be able to ask the database things like: who are all the language teachers in a given district, or who are all the French teachers in a particular school, or what are all the school addresses of Spanish teachers in the county, which teachers teach AP (Advance Placement) courses for any language, etc.

            • 3. Re: Beginner question about tables, fields, and relationships

                   Sounds like you need that join table between a table of teachers and a table of languages.

                   Many of your reports and such can be done from the context of the join table.

              • 4. Re: Beginner question about tables, fields, and relationships

                     Welcome Richard. For what it's worth I'd set up something like this:

                     A table of teachers, autonomy ID - you may wish to include contact details, qualifications etc here

                     A table of languages, autonumID - you may wish to include levels here, e.g. with a field with a drop down list for level, or you may wish to have "level" as yet another table related to the language table.

                     A join table as Phil has suggested autonumID, with a teacher ID field and a language ID field. Relate the teacher ID to the teacher table and the language ID to the language table. Make sure you can create records through the relationships.

                     Set up a teacher layout. Put a portal on the teacher form showing records from the language table, as a drop-down or pop up value list based on the language table. If the level table is separate, you can also put a field from the level table as a value list drop-down/popup. 

                     Set up 2 reports: one based on teacher, showing languages taught. The other showing language with a list of teachers.

                     Hope this is useful. Cheers J