Blue Card Date
I have been using FMP as my data base for a high school Driver Education program for more than twenty years. You would think I would be good at it by now, but my skills are only slightly better than newbie. Everything has been running good for the last four or five years, but I have recently had trouble with a function in a calculation field that is not sophisticated enough and I haven't been able to improve it.
When the kids are done with the classroom phase they are issued a Blue Card which they take to the State DMV center to take the test for a Learners Permit. State law requires that they be fifteen years old to take the test and some of the kids are still fourteen so they have to wait
Among other things, the Blue Card layout has a field for printing the "Blue Card Date" which is the issue date of the card. For kids fifteen years old the "Blue Card Date" is going to be the last day of class. I have another field for "Last Day of Class". For kids that are less than fifteen years old, I want to have the "Blue Card Date" printed on their card to be the day of their fifteenth birthday. I have used the following function to calculate "Blue Card Date" with the calculation result being Text
If( Age >= 15 , Date of Last Class, Date( Month(Birthdate), Day(Birthdate), Year(Birthdate) + 15)) It has been working flawlessly for three or four years. Recently, however, on the last day of class, a fourteen year old was given a Blue Card that had an "Blue Card Date" that coincided with his fifteenth birthday. That was as it should be, only when he turned fifteen he did not go take his Learner's Permit test. Six months later he decided to take the test, but, of course, by that time he had lost the Blue Card. His mother called for a new card, but when the teacher printed it the "Blue Card Date", it came out as the "Date of the Last Class". This happened because he was now fifteen years old when the new card was printed. We can't let a card go out like that, because the "Blue Card Date" was before the kids fifteenth birthday. The State frowns on that.
The bottom line is that if the kid is fifteen on the "Date of Last Class'' I need the "Blue Card Date" to be "Date of Last Class'' and if he is fourteen on "Date of Last Class'' I need the kid's "Blue Card Date" to be his fifteenth birthday regardless of his age at the time the Blue Card is printed. I tried to modify the calculation by nesting another If function in it, but I couldn't make it work.
Here are the other fields involved:
Age is a Calculation field with result being a number. This is the formula: Year(Status(CurrentDate)) - Year(Birthdate) - If(Status(CurrentDate)< Date(Month(Birthdate), Day(Birthdate),Year(Status(CurrentDate))),1,0)
Date of Last Class is a Date field. Here is the calculation formula with the calculation result being Text: TextToDate(Case( Session= "CDH 14-15 Sep" , "9/30/2014" , Session= "CDH 14-15 Oct", "11/04/2014", Session="CDH 14-15 Dec", "12/16/2014",Session= "CDH 14-15 Jan", "01/22/2015", Session= "CDH 14-15 Mar", "03/25/2015", Session= "CDH 14-15 Apr", "04/28/2015", Session= "CDH 14-15 Jul", "07/28/2015","06/23/2015"))
Birthdate is a date field
Session is a text field
I'm using FMP 6.0v2