2 Replies Latest reply on Jul 20, 2012 3:43 PM by MichaelMoul

    General relational database question

    MichaelMoul

      Title

      General relational database question

      Post

      Hi.  I'm the adviser for a high school newspaper, and I've spent some time this summer designing a database that will facilitate communication between students on the newspaper staff about the articles they're working on.  I'm using Filemaker 12 Pro.

      I've got three tables in the database:  One is Staff Members, which holds various information about the students, incluing full name, position, and the account name that they use to log into the database.  The second table is Articles, which holds information about each article including the section of the paper the article will run in, an abstract, possible sources, multimedia ideas, etc.  And the third table is Tasks, which joins Staff Members and Articles in a many-to-many relationship, and also records fields like duedates and completion status.  Each table automatically creates a unique record ID for every record in the table.

      I've about finished implementing the database, and nearly everything works the way I want it to.  But now I'm looking to add a few features that will make it more user-friendly, and in doing so I'm realizing that I've got some very basic questions about the way relational databases work, and I don't know where to find the answers.

      Two examples of my confusion:

      When creating a record in the Tasks table that is to be related to both a staff member and an article, it seems to me that I should be able to assign the task to a record in the Staff table by staff member name, and assign the task to a record in the Article table by article name.  And since I've read that it's always a good idea to use unique ID fields as match keys when relating tables, it seems like I'll want Filemaker to look up that staff member's unique ID number from the Staff table, and use that field to relate the task to a particular article in the Article table.  And all that should happen behind the scenes, because I'll go insane if I try to remember everyone's staff ID, and after all, isn't this exactly what relational databases were designed to do in the first place?  But I can't figure out how to make Filemaker do that, and so I must be missing something.

      Example two:  I've been trying to write a script that runs when a new record in the Articles table is created.  I want it to interrogate the current user's account name, then look up that user's position on staff to determine if that user is either the news editor or the sports editor.  If they are, I want Filemaker to automatically fill in the "Section" field in the articles table to "News" or "Sports" as appropriate.  Again, I'm completely stumped as to how to make this work.

      These seem like very basic questions, and I'd assume that the fact I can't answer them means that I really don't understand the way relational databases work on a fundamental level.  Would other people agree with that assessment?  Does anyone know of any resources online that might help me out with the basics?

      Thanks.

        • 1. Re: General relational database question
          philmodjunk

          and I don't know where to find the answers.

          Sure you do! Right here in the forum is one place. There are also books and tutorials that can teach you more about FileMaker and relational databases.

          Example 1:

          You can define a value list that lists ID numbers for your staff in column 1 and the staff member names  in column 2. If you have separate fields for first and last name, you can define a calculation field to combine them and then use that calculation for the second field specified in your value list. This allows you to select a staff member by name, but the value list will enter the ID number as you need to link the records.

          This works well for fairly short lists of people. For longer lists there are alternative approaches that can save you from scrolling through very long lists of names.

          Example 2:

          In your table of staff members add two fields if you do not already have them: AccountName and Section. Put "News", "Sports" or whatever is appropriate in the section field for each staff member. It is OK to leave the field blank if no value is appropriate for a given member of your staff.

          In File options you can specify that a script run automatically when the file is opened. Have it run a script like this:

          Freeze Window
          Go to Layout [Staff Members]
          Enter Find Mode []
          Set Field [Staff Members::AccountName ; Get ( accountName ) ]
          Perform Find []
          Set variable [$$Section ; value: Staff Members::Section ]

          This script automatically loads the global variable $$Section with the current user's section name when they first open the file.

          Go to the Section Field in Articles and specify this auto-entered calculation for the Section Field:

          $$Section

          For each user where you specify a Section in their Staff Members::Section field, any new record in articles that they create will automatically enter that text into the section field of the new articles record.

          You may find this demo file on many to many relationships helpful if you have not seen it already: https://www.dropbox.com/s/oyir7cs0yxmbn6i/ManyToManywDemoWExtras.fp7

          • 2. Re: General relational database question
            MichaelMoul

            Wow, thanks!  I've implemented your second set of suggestions already, and it works great.  I'll get to work on the other set tonight.  Thanks also for the demo file. It's darned cool.