7 Replies Latest reply on Mar 29, 2010 1:33 PM by philmodjunk

    Date calculation help.



      Date calculation help.


      I am very new to FMP and am learning as I construct this database so any help is appreciated.

      I have a course table that has Start and End date fields. I have a Calculation field in that table that totals the days trained. I have a Team table that has two calculations for Total training days that just Sums up the Daystrained, and another calculation that subtracts the total days trained from the days alloted 26. 


      Here is the thing I want to be able to do with these calculations but am not sure of how to do it. I want to have it calculate for the specific year. So each year it will start over calculating up the total days trained, and the total remaining days from 26 alloted days.


      Help please.



        • 1. Re: Date calculation help.

          These calculations use the Sum function to sum up data in a related table? (or the same table linked in a self join relationship)


          If so, you can include another field with the a calculation that returns the year to limit your calculation to all matching records with the same year.

          • 2. Re: Date calculation help.

            Yes, I had them sum the data in the table and related table.

            I had the sum be in the Team table because I figured that is what I was trying to get the amounts for.

            So in my team table it looks like this:


            Team ID (number, indexed, auto-enter serial)

            TeamName (Text, indexed)

            AllottedTrainingDays (number, auto-enter Data, Numeric Only, Allow Override) : this is the total amount per    Team allowed 26 total training days, this number stays the same each year).

            TotalDaysTrained (Calculation, unstored Sum(CourseTbl::Days;))

            TotalRemainingTrainingDays (Calculation, unstored, = AllottedTrainingDays - TotalDaysTrained)


            How do I do the other calculation based on the year. I am still learning how to use more than one relationship to do calculations and what not. I am used to using Access and doing querys so this is all new and confusing to me.


            THank you,




            • 3. Re: Date calculation help.

              I've developed in Access also and created quite a few forms and reports that relied on manipulted SQL expressions to pull up the right recordset, so feel free to use those terms to describe what you need if that make's it easier.


              What relationship have you defined between Team and CourseTbl?


              I think you have:

              Team::Team ID = CourseTbl::Team ID


              You need to add a calculation field like this to CourseTbl: cYear = Year ( StartDate )

              Add a number field in team: Year


              Now modify your relationship to be:

              Team::Team ID = CourseTbl::Team ID AND

              Team::Year = CourseTbl::cYear

              (To add more fields to an existing relationship, double click the relationship line in Manage | Database | Relationships)


              Now enter 2010 in Team::Year to see your sum function compute the total days for 2010. Change it to 2009 and you'll see the total for 2009.


              Note if a training season starts in one calendar year and ends in the next, you'll need a more sophisticated calculation to identify the training season correctly.

              • 4. Re: Date calculation help.

                I started to implement you solution. I am not that great with Access either which is why I though Filemaker would be easier but (I am still learning how the relationships work in Filemaker). Here is what I have as far as table structure.


                I have a Teamtbl (contains, TeamID, TeamName, AllottedTrainingDays, TotalDaysTrained, TotalRemainingTrainingDays, and now Year)

                Traineetbl (contains, TraineeID, FirstName, LastName, Prefix_suffix, TeamID, and a calculated field for FullName) This table is linked to the TeamTbl via TeamID

                Course_Traineetbl this is my join table (contains, Course_TraineeID my primarykey, CourseID, TraineeID) This table is joined to my Traineetbl via TraineeID and is also joined to my CourseTbl via CourseID.

                Coursetbl (contains CourseID, CourseTitle, StartDate, EndDate, Notes, CourseType, DaysofCourse, and now per your suggestion CYear)


                Will your solution work with the way that my tables are setup with having the Course_TraineeTbl in there. The only part I dont see to make it work is I do not have Team ID in the Course Tbl. Do you think I need to add it into that table. Or do you see a flaw in my design.


                Note: I tried adding a second TeamTbl in my relationship page, and added CourseID_fk in my Teamtbl and did the relationship as TeamTbl:CourseID = CourseTbl:CourseID and TeamTbl:Year = Coursetbl:cYear  but it does not seem to work for me.



                Thank you

                • 5. Re: Date calculation help.

                  That does complicate things quite a bit.


                  In Manage | Database | Relationships...


                  Make Year a global field defined in Course_Traineetbl.

                  Make a new table occurrence of Coursetbl

                  Link Course_Trainnetbl to Coursetbl 2 by both CourseID and Year.

                  Define a field in Course_Trainee that uses the sum function to compute the days trained for that trainee for the year entered into the global year field.

                  In Teamtbl, make your calculation a sum of this new field to get the total training days for the team for the given year.


                  Since Year is now a global field, you can place it on any layout to select a year and all your records will compute totals based on this value.



                  • 6. Re: Date calculation help.

                    I am not sure how to write the sum statement.

                    I put =Sum (CourseTbl:: Days) in the Course_TraineeTbl and it seems to be summing up the number of training days for that individual but I dont know how to get it to base it off of the year global field.


                    This is what it looks like in the Course_Traineetbl

                    DaysTrained....calculation....unstored, =sum (coursetbl::days)






                    • 7. Re: Date calculation help.

                      The relationship will filter the values by year as long as you refer to the correct table occurrence in your sum function.


                      Last Post I said:


                      "Link Course_Trainneetbl to Coursetbl 2 by both CourseID and Year."


                      That looks like this:

                      Course_Trainneetbl::CourseID = Coursetbl 2::CoursID AND

                      Course_Trainnetbl::Year = Coursetble 2::cYear


                      Then Sum ( Coursetbl 2::DaysofCourse )


                      Should compute the total trainee days for this trainee.