Logic Question - Project Tracking
I've been racking my brain on how to set up my database for a project tracking file. I used the template in FileMaker Pro 12 under "Business - Projects" called "Projects" as my starting point. This template is almost perfect for my needs, but there is one criteria I have that it does not currently meet.
The template uses tasks with assignable dates. These tasks are related to a specific project such that one project can have multiple tasks assigned to it. For my use-case I need the "tasks" to be school semesters with preset dates, and then I've added a field called "Phase" that describes what phase the project is in. For instance, in task "Spring 13" the phase would be "Design". In "Summer 13" it would be "Production". In "Fall 13" it would be "Field Test". In "Spring 14" it would be "Revisions". Each semester is a different task, all related to one project. This part seems pretty simple. Where I'm stuck is trying to get multiple "tasks" (or semesters) and their assigned "phase" to display on a single project line in the project list. It seems like this would need to be a 1 to many relationship, but I can't figure it out.
In this template, if you click "Project List" you will see a list of all projects you've entered. I want to have columns on this layout that represent each semester and what phase the project is in. I've attached a screenshot of what I'm going for. At the moment, no matter which way I try to set this up, I'm only getting the info from the first "task" in my project across all my columns.
I want the project record to essentially scan all of the entered tasks and look to see what semester they are. Then I want the phase of each task to be put into it's corresponding semester column. Using the example I typed above, for my screenshot to be accurate I would like "Fall 12" to be blank (because there is no task called Fall 12), "Spring 13" to say "Design", "Summer 13" to say "Production", "Fall 13" to say "Field Test", and "Spring 14" to say "Revisions".
Can anyone help me with the logic of how to set up these tables/fields/relationships so that this works? I keep getting lost because there are multiple related records and I don't know how to get a single record in the project table to pull data from multiple records in the task table at once. The fields on this "Project List" display could be in the Projects table or in the Tasks table, it doesn't matter to me...whatever is best/most stable.
Thanks for any help!!!