5 Replies Latest reply on Mar 5, 2014 9:01 AM by philmodjunk

    Logic Question - Project Tracking



      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!!!


        • 1. Re: Logic Question - Project Tracking

               This looks like something that could be done with one row filtered portals if your layout is set up for list view. Your portal filters would specify the semester.

          • 2. Re: Logic Question - Project Tracking

                 You were correct, as usual.  I figured out how to get the filtered portals working.  I also wanted to be able to display more semester fields but have limited layout space, so I've created a way to scroll "left" and "right" through my semesters.  It works beautifully as far as the headings, but my portals are not updating accordingly.  They are based on the same fields as the headings.  (I created 5 new global fields called Spot1, Spot2, Spot3, Spot4, and Spot5)  These global fields are placeholders for the semesters and update as you scroll left or right.

                 Any idea on how to refresh the portal fields (which are in a related table, naturally) as these fields update?  Let me know if that doesn't make sense...it's hard to describe.  Thanks!!

            • 3. Re: Logic Question - Project Tracking

                   Is this the scenario?

                   Your portal filters access data in a global field. When you change the value of the global field, the records listed in the portal do not update to reflect the change in the value of the global field?

              • 4. Re: Logic Question - Project Tracking

                     That's exactly the case.

                • 5. Re: Logic Question - Project Tracking

                       I'll keep this theoretical and you can then apply this to the specifics of each portal.

                       Say you have this relationship for a filtered portal:

                       LayoutTable::__pkPrimaryKey = PortalTable::_fkForeignKey

                       And there is a a field named LayoutTable::GlobalField used in the portal filter expression.

                       If you modify the relationship to be:

                       LayoutTable::__pkPrimaryKey = PortalTable::_fkForeignKey AND
                       LayoutTable::GlobalField X POrtalTable::anyfield

                       It will still match to the same related records, but now your filtered portal should update when the value in GlobalField is modified and the portal filter expression thus needs to re-evaluate.