1 2 Previous Next 18 Replies Latest reply on Sep 29, 2011 9:39 AM by philmodjunk

# Need to figure out how to do a calculation regarding dates

### Title

Need to figure out how to do a calculation regarding dates

### Post

Ok. I have a table for courses and a table for teams. I have the team table joined to the course table via a join table. I have a field for course start date and course end date (they are both date fields). I figured out how to get the number of days between the two and just add 1 so that I can get the total for days trained but my goal in the end is to be able to have a report where it shows all the training for each team and shows the number of days trained and the number of days remaining for the year (out of 24 days total for each team), I have to pull this report for each month so it would have to take into account the month I pulling the report for. I have no idea how to go about doing this. Any help would be greatly appreciated.

• ###### 1. Re: Need to figure out how to do a calculation regarding dates

In an unstored calculation field,

Min ( End Date - StartDate ; End Date - Get ( CurrentDate ) ) + 1

will calculate the current number of days trained for participants in that training record.

Does that produce the results you want or did you need something more?

• ###### 2. Re: Need to figure out how to do a calculation regarding dates

not exactly. Here is my DB structure:

I have the following tables:

Course

Course_Trainee

Trainee

Team

Team_Course

I did a second iteration of Course table and joined that to the Team_Course table. I did a calculation in the course table for Number of course days, it looks like this EndDate -  StartDate + 1

I also did a calulation for Remaining training days for All teams, it looks like this 96-( calNumberCourseDays )

Then I did a calculation for Training Month which looks like this MonthName (StartDate)

The thing is I dont know how to put it all together to make it to where I open up a report it will calculate the remaining days (24 training days between September 20th 2011 and September 19th 2012 for each team). I am trying to get a report that shows all the training information seperated by each team with a section showing total days used and total days remaining of the 24 for a specific month. So if I am pulling the report for August, I want to see all the training information for the month of August with the total remaining days and the total days used.

Thank you again.

• ###### 3. Re: Need to figure out how to do a calculation regarding dates

To isolate records by month--either by sorting or via a relationship, you may want to use this calculation:

cMonth : DateField - Day ( DateField ) + 1

Set to return a Date, it computes the date of the first day of the month for the month given in the date. Since this includes the year, it makes for consistent sorting by month when you sort by this field. (and Kudos to Comment for sharing this trick many months ago here in this forum...)

I don't see how "Remaining Training Days for all teams" produces a correct value as this value uses the start and end dates and does not change as time progresses--during which more training days are completed.

How are your relationships structured here?

Course---<Course_Trainee>----Trainee----<Team_Course>------Team

Is that it?

If so, I can see basing a layout on Team_Course to get one row for each pairing of team with a course and then other fields can show additional data about the team and course, but I don't see any table where you can break this down for different months in the same report, but perhaps I am misunderstanding your description of the report you want. Can you post an example of what you want to see in this report?

And how do you get "24 training days" from the start (9/20/11), end dates (9/19/12) in your example?

Neither Date2 - Date1 + 1, [365] nor 96 - (Date2 - Date1 + 1 ) [-269] will produce that count of days.

• ###### 4. Re: Need to figure out how to do a calculation regarding dates

I am sorry if I seem ignorant about how to do this stuff. It seems so much more complicated to me than just doing a query in Access. Please bare with me, I ams till reading my Filemaker Pro 11 Missing Manual book and am only on Chapter three. I have also taken intermediate filemaker training and advanced and done well in them but for some reason when it comes to the actual design part I have a lot of trouble.

My relationships are like this:

Instructor----<Instructor_Course>----Course-----<Course_Trainee>----Trainee>---Team---<Team_Course>----Course2(this is a reiteration of the course table).

I have attached a example of how I want the report (a little fancier looking but that information). I pulled this report from Access from the Database I had been using to track training.

• ###### 5. Re: Need to figure out how to do a calculation regarding dates

I can't see any such attachment. You'd need to upload this file to a file sharing site and post the link in order for me to see it. You could also just outline the report in text. It's the month by month break down of training day totals that I don't see a table for in your system. You appear to have a way to link instructors, trainees and teems to course records, but no where do I see a table for tracking how many days of training were completed each month.

You may not need such a table as I am still unclear as to exactly what do you want to produce in your report.

Here are two examples that come to my mind:

January

Team A
Course 1 20 days, 35 remaining
Course 2 30 days, 15 remaining

Team B
Course 1 15 days, 45 remaining
Course 2 40 days,   5 remaining

February

// and you see totals for February here

OR....

You see just the training day totals, for each team, broken down by course as shown above, but only as "Year to date" totals

The first report option requires an additional table. The second, since it's calculations are based only on the date fields in the course table and the current date, may not need such a table.

• ###### 6. Re: Need to figure out how to do a calculation regarding dates

PhilModJunk,

Thank you for having patience with me. I uploaded a image that shows what I had been pulling in Access. I can see it, I wonder why it isnt working. hmm... I really need to learn more about computers. I thought I was at least a little savy with them. I have the Team table setup the way it is because a trainee can switch teams in the middle of the year.

"And how do you get "24 training days" from the start (9/20/11), end dates (9/19/12) in your example?" We provide 24 training days per team through the date range. a total of 96 for the year.

This is what I am looking for: I want to be able to go in at the end of the month and pull a report hat looks like this:

August

Team              Days used         Days Remaining in year

Alpha                20                            4

Bravo                24                            0

Charlie               15                           9

Delta                 19                           5

Team             Course                   Date                    Trainee                   Trainee Hours

Alpha

team Training         8/1/2011

John Doe                     8

Jane Doe                       8

Total Hours             16

Bravo

Individual                8/15/2011

Jimmy Johnson           10

Jak beanstalk              10

TOtal Hrs          20

• ###### 7. Re: Need to figure out how to do a calculation regarding dates

Is it possible for members of the same team to have different total hours for the same course? Your examples show the same totals for each and I don't see any table in your design what would track participation such that you'd get different totals for any given member of the same team.

Where are your start and end dates recorded? (I've been assuming that they were logged in Courses or possibly course_Team, but now realize that may not be the case here....

All of your example numbers for teams imply 24 days of training. How does that 24 day figure relate to the start and end dates? (Does training take place only on specific dates/days of the week or ?)

• ###### 8. Re: Need to figure out how to do a calculation regarding dates

Yes it is possible for them to have different training times. Most of the time they are the same but every once in a while someone goes home early or comes in late and we want to be able to capture that.

My start and end date are in the Course table. Is this something I should change?

Most courses are just one day and can be any day of the week. I have the start and End date in there just in case we ever have a training that takes more than one day, although I guess I could just capture if the course ran longer than one day in the trainee hours or just enter it in the system each day that it goes on.

• ###### 9. Re: Need to figure out how to do a calculation regarding dates

...and we want to be able to capture that.

And where do you capture that? In what field and table? Possibly a field in Course_Trainee?

Since your courses are one day courses, that might be all you need here--a number field to record "days completed" for each trainee for each course. I was originally thinking in terms of multi-day courses where you'd be better off using an additional attendance table where you can record attendance for each trainee for each course in individual records for each course day.

It would seem you need to total up a trainee's total participation and subtract that from the course duration to get days remaining. Once you have individual totals, team totals are simply the sum of these individual values for a given team.

But I keep coming back to that 24 day figure you've used more than once. If each course is usually a 1 day course, where does that 24 figure come in? Are all trainees required to complete 24 days of training?

Oh yes and Muy Bad on the report example. It does appear in your first post. Somehow, I missed it earlier.

• ###### 10. Re: Need to figure out how to do a calculation regarding dates

I have the hours trained being caputered in the Course_Trainee table.

The 24 is each Team has 24 training days (really the trainee information does not come into play at all in this calculation, we just track their hours for the client to be able to see what their trainees are doing.

So for the year (or date range given, unfortunately we do not run on calendar year we run on contract year) each team is allotted 24 training days and each month they want a report showing how many have been used to date and how many remain for the rest of the year.

• ###### 11. Re: Need to figure out how to do a calculation regarding dates

Follow me on this outline and see if it works for you:

1) You need the total training days for a team this will be the sum of either: a) the duration of the course if it has been completed b) The portion of the course currently complete if the course is not yet complete for each training course that the team has been registered.

2) You need to then subtract this total from 24 to show the days remaining for each team.

3) Your example also shows a break down by each individual member. So for each individual member, you report the total hours as a total taken from the Course_Trainee table.

If that is what you want then I know how to set this up and my original post was pretty close to the mark.

How tightly wedded to the sample report format are you? If you re-arrange the report elements so that you have team totals reported as a sub head or sub total for each list of team members instead of a "recap" type list at the top, it's a bit simpler to set up  here.

And what version of FileMaker are you using here?

• ###### 12. Re: Need to figure out how to do a calculation regarding dates

PhilModJunk,

Again I just have to thank you for taking the time to look at this and help me. I wish there was someone close by that could mentor me and teach me. I love learning anything regarding computers but have a hard time learning it from just reading examples or books.

I think we are on the right track with your outline. The only thing that I dont think we need to worry about is the course has only been partially completed. In our case no matter if the team only stays half a day it still counts as one full days worth of training. So if training is on 8/9/2011 then that counts as one day used of the 24 regardless of if they only do a half day. I guess what we should do is just total up courses for each team and not base it off of the days. to throw another wrench in the plan, I need to be able to exclude any course that has Basic in the title or facility use because these do not count against the 24 days of training.

2)"You need to then subtract this total from 24 to show the days remaining for each team."  is 100 percent correct

3) "Your example also shows a break down by each individual member. So for each individual member, you report the total hours as a total taken from the Course_Trainee table." so do I need to create another calculation field to do this or can I do this in the report section.

"How tightly wedded to the sample report format are you? If you re-arrange the report elements so that you have team totals reported as a sub head or sub total for each list of team members instead of a "recap" type list at the top, it's a bit simpler to set up  here."    I am not extremly wedded to this format. I just need something that each team can quickly look at and see how many days they have trained and how many remaining and what courses they have done.

I am using Filemaker 11 Pro Advanced.

Again thank you so much!!!! and if you recommend any training that I can take that will better help me to learn this I would appreciate any recommendations. As I said I am still reading through Filemaker Pro 11 the missing manual and I also have a book on my list to read "database design for mere mortals" any other recommended books?

• ###### 13. Re: Need to figure out how to do a calculation regarding dates

The only thing that I dont think we need to worry about is the course has only been partially completed. ... I guess what we should do is just total up courses for each team and not base it off of the days

That's a key design decision for you to make. The "partially completed" that I had in mind would be those courses that are more than one day in duration and the current date is day 1 of a two day course or such. Since you don't have any courses that are more than one day in duration, you do not need this at this time and can just compare dates to total up all training days. (and there are ways to filter out specific courses from this total.) But if you want to include the needed flexibility for multi-day courses in case you should need them in the future, you are better off to build that capability in now.

I need to be able to exclude any course that has Basic in the title or facility use because these do not count against the 24 days of training.

Exclude only from this calculation or completely from the report? (the second option is simplest to implement)

Leaving that little detail aside, remember this suggested calc? Min ( End Date - StartDate ; Get ( CurrentDate ) - Start Date ) + 1

This would compute the number of days elapsed for a given course that may be more than one day in length. Total that up for a given team and you have your team total days that you can subtract from 24. (and yes, I fixed an error in the original version of this calc.)

This calculation field would be defined in Course and a summary field defined in Course can compute the total of this field.

The same type of summary field in Course_Trainee can total up the training hours.

Now you can set up a report based on Trainee (needed so you can set up the break down of hours by Trainee.)

You can add these summary fields to this layout, placing the summary field from course in a sub summary part "when sorted by Team". to show the team total hours.

Sort these records by team to group them correctly and make this sub summary part visible so you can set it up as a sub header.

This isn't done but see if that makes sense and how much of it you can set up here.

• ###### 14. Re: Need to figure out how to do a calculation regarding dates

I am sort of on the right track but not quite there.

I have The following fields setup in my Course table:

calNumberCourseDays    calculation   from Course, =Min (EndDate-StartDate; Get (CurrentDate)-StartDate)+1

TotalTeamTrainingDays Summary    =Total of CalNumberCourseDays (running with restart), when sorted by Team::TeamName)

for the course_Trainee table I have the following calulation field:

HoursTrained number

TotalHours   summary   =Total of HoursTrained (running with restart), when sorted by Team::TeamName

So far when looking at just the course layout that filemaker made the calculation for the number of course days is working correctly and only calculates the days that have occured. I believe there is something wrong with my TotalTeamTrainingDays summary.

my totalhours field on layout is totalling up all the training no matter what team they are in.

I tried doing a report and I could not get it to work correctly. It was showing blank information where the summary should be.

1 2 Previous Next