FileMaker Pro Custom Functions

/* fnCalendar™ v1.6 Copyright © 2009 Drewsol GNU Lesser General Public License: This function is free software: you can redistribute it and/or modify it under the terms of the GNU Lesser General Public License as published by the Free Software Foundation, either version 3 of the License, or (at your option) any later version. This program is distributed in the hope that it will be useful, but WITHOUT ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU Lesser General Public License for more details. You should have received a copy of the GNU Lesser General Public License along with this program. If not, see <http://www.gnu.org/licenses/>. This CF consolidates a simple calendar display and selection tool complete with limitless iterations, highlights, holidays, and tooltips. fnCalendar™ ( date_field ; parameter ; iteration ; highlight_weekends ; holiday_list ) - date_field This is your table's (global) date field that should be scripted to change upon date selection. Use this field as a key in front of Calendar-related relationships. - parameter label_monthname_year (calendar header showing month and year) label_days_of_week (calendar header showing week name abbreviations - S M T W R F S) label_days_of_week_abv (calendar header showing week name abbreviations - Sun Mon Tue Wed Thu Fri Sat) date (shows date selected/moused over) calendar (actual calendar) - default calendar_highlights tooltip (m/d/yyyy) tooltip_long (Dayname, Monthname d, yyyy) holiday_list - iteration -1 (2nd month before selected month) 0 (month before selected month) 1 (selected month based off date_field) - default 2 (month after selected month) 3 (2nd month after selected month) ... - highlight_weekends 1 (weekends have gray highlight) - default 0 (weekends have no highlight) - holiday_list (empty) - default included (national holiday list generates inside CF, but comes at moderate performance cost) This function accepts lists in the following formats: <holiday date> <holiday name> <holiday date> ChangeLog: v1.6 - Added provision for 'globally stored' calendar (no redraw upon layout access), cleaner instructions/commenting. Included lite version (same calculation minus documentation and integrated holiday list). v1.5 - Added instructions for a single-layer conditional formatting method. v1.4 - LGPL license added. v1.3 - Integrated holiday list and calendar highlights transferred from 'condition' to 'statement block' to limit unnecessary code execution. v1.2 - Optimized for performance. Support for unlimited iterations and previous months. Integrated American holiday list. v1.1 - Support for three calendar display iterations, holidays, weekends, instructions. v1.0 - Basic function, field independence, tooltips. Features: - Minimal field requirement and high degree of independence - Support for unlimited (and bidirectional) calendar iterations (ie. displays for January, February, March...) - Dates surrounding month are visible, but grayed - Highlights for current day selected, present day, and holidays - Internal list of holidays for any year - Tooltips Important Note: FileMaker fields based on unstored calculations require a screen refresh before displaying current data. Therefore, your date field may require a validation that 'triggers' a script to refresh the window (using the 'Refresh Window' script step). FM versions prior to 10 may require a plug-in. Sample validation code to trigger the refresh: Case ( CAL::Date_G <> "" and FMSAUC_FindPlugIn ( "zippScript" ) > 0; 1 & zippScript_PerformScript ( Get( FileName ) ; "Calendar" ; "Refresh Window" ); 1 ) Instructions: 1. Create a single-record table named CAL or Calendar. 2. Create a globally-stored date field that will be used for date selection. Tip: In your open script, calibrate this field by setting its value to: Get ( CurrentDate ). 3. Create an unstored or globally-stored* calculation field with 42 repetitions, number or text. This will display the CALENDAR DAYS. a. In the calculation box, enter: For unstored calculations: fnCalendar ( <date field> ; "calendar" ; 1 ; "" ; "" ) For global calculations: Let ( trigger = CAL::Date_G ; fnCalendar ( Extend ( <date field> ) ; "calendar" ; 1 ; "" ; "" ) ) * NOTE: Unstored vs. Global Setting the calendar fields as unstored calculations will require them to redraw upon each layout access. Setting the calendar fields as global calculations will require the use of the date field as a trigger (see example above), and will not require a redraw. However, the scripting is a little slower since modifying the date field triggers any globally stored calculations that reference it. b. Arrange the repetitions on your layout so you have 6 stacked rows with 7 reps each. * Because these repetitions will also serve as buttons, you will have to isolate each repetition (1x1) as its own object before grouping. c. Add transparency to this 'layer' (calendar) to reveal a bottom layer of calendar highlights (for date selected, weekends, holidays...). d. Select the entire group and add a tooltip displaying the date: fnCalendar ( <date field> ; <"tooltip" or "tooltip_long"> ; 1 ; "" ; "" ) <<METHOD 1: DUAL LAYER W/O CONDITIONAL FORMATTING>> 4. Create another unstored calculation field with 42 repetitions, text. This will display the CALENDAR HIGHLIGHTS. a. In the calculation box, enter: fnCalendar ( <date field> ; "calendar_highlights" ; 1 ; 1 ; <holiday_list> ) b. Arrange the repetitions on your layout so you have 6 stacked rows with 7 reps each, and group them. * You can be more lax here and create six 7-rep horizontal objects before grouping. c. Put this layer behind the first. * My tests have shown that this dual-layer method performs faster than the conditional formatting method below. d. Proceed to Step 5. <<METHOD 2: SINGLE LAYER WITH CONDITIONAL FORMATTING>> 4. Modify the layer created in step three so that the calendar also displays the CALENDAR HIGHLIGHTS. a. Select the entire layer to apply conditional formatting rules. b. Apply the Conditions in order of least priority (top) to highest priority (bottom) using 'Formula Is:' and designate a Fill Color for each. i. Weekends: DayOfWeek ( fnCalendar ( <date field> ; "date" ; 1 ; "" ; "" ) ) = 1 or DayOfWeek ( fnCalendar ( <date field> ; "date" ; 1 ; "" ; "" ) ) = 7 ii. Holidays: PatternCount ( fnCalendar ( <date field> ; "holiday_list" ; 1 ; "" ; "included" ) ; fnCalendar ( <date field> ; "date" ; 1 ; "" ; "" ) ) > 0 iii. Selected Date: fnCalendar ( <date field> ; "date" ; 1 ; "" ; "" ) = GetAsDate ( <date field> ) c. Proceed to Step 5. 5. Create a CALENDAR SCRIPT similar to the example below: ( CAL::Date_G is my date field. ) #CALENDAR # Set Variable [ $initial_date; Value:CAL::Date_G ] #Parameter is <iteration> & " " & <repetition> If [ Filter ( Get ( ScriptParameter ) ; "012345689" ) > 0 ] Set Field [ CAL::Date_G; Let ( [ i = LeftWords ( Get ( ScriptParameter ) ; 1 ) ; n = RightWords ( Get ( ScriptParameter ) ; 1 ) ] ; fnCalendar ( CAL::Date_G ; "date" ; i ; "" ; "" ) + ( n - 1 ) ) ] Else If [ Get ( ScriptParameter ) = "Previous Month" ] Set Field [ CAL::Date_G; Date ( Month ( $initial_date ) - 1 ; Day ( $initial_date ) ; Year ( $initial_date ) ) ] Else If [ Get ( ScriptParameter ) = "Previous Week" ] Set Field [ CAL::Date_G; Date ( Month ( $initial_date ) ; Day ( $initial_date ) ; Year ( $initial_date ) ) - 7 ] Else If [ Get ( ScriptParameter ) = "Today" ] Set Field [ CAL::Date_G; Get ( CurrentDate ) ] Else If [ Get ( ScriptParameter ) = "Next Week" ] Set Field [ CAL::Date_G; Date ( Month ( $initial_date ) ; Day ( $initial_date ) ; Year ( $initial_date ) ) + 7 ] Else If [ Get ( ScriptParameter ) = "Next Month" ] Set Field [ CAL::Date_G; Date ( Month ( $initial_date ) + 1 ; Day ( $initial_date ) ; Year ( $initial_date ) ) ] Else If [ Get ( ScriptParameter ) = "Refresh Window" ] Freeze Window End If Commit Records/Requests Refresh Window 6. Ensure that the calendar created in Step 3 is ungrouped so that each repetition is an isolated object. a. Point each invidual object to the new Calendar script, customizing the paramaters with the following convention: <iteration> & " " & <repetition> For example, if the object the 7th repetition of a 3rd iteration calendar, your parameter would be: 3 & " " & 7 */ Let ( [ // === CALENDAR HIGHLIGHT COLORS === // Off-month Days (Default: Gray) color_off_month_day = RGB ( 160 ; 160 ; 160 ) ; // Weekends (Default: Light-Gray) color_weekend = RGB ( 238 ; 238 ; 238 ) ; // Date Selected (Default: Light-Yellow) color_date_selected = RGB ( 255 ; 238 ; 192 ) ; // Holidays (Default Light-Green) color_holiday = RGB ( 224 ; 255 ; 238 ) ; highlight = TextSize ( "██" ; 40 ) ; highlight_date_selected = TextSize ( "██" ; 40 ) ; // === SET DEFAULTS FOR EMPTY PARAMETERS === date_field = Case ( date_field <> "" ; date_field ; Get ( CurrentDate ) ) ; parameter = Case ( parameter <> "" ; parameter ; "calendar" ) ; iteration = Case ( iteration <> "" ; iteration ; 1 ) ; highlight_weekends = Case ( highlight_weekends <> "" ; highlight_weekends ; 0 ) ; holiday_list = Case ( holiday_list <> "" ; holiday_list ; "" ) ; // === MAIN VARIABLES === i = iteration ; cal_index = Get ( CalculationRepetitionNumber ) ; date_selected = GetRepetition ( date_field ; 1 ) ; mx_total_days = Day ( Date ( Month ( date_selected ) + i ; 1 ; Year ( date_selected ) ) - 1 ) ; mx_date_monthbeg = Date ( Month ( date_selected ) + ( i - 1 ) ; 1 ; Year ( date_selected ) ) ; mx_date_monthend_prev_month = Date ( Month ( date_selected ) + ( i - 1 ) ; 1 ; Year ( date_selected ) ) - 1 ; mx_date_monthend_prev_month_dayofweek = DayOfWeek ( mx_date_monthend_prev_month ) ; mx_total_days_prev_month = Day ( mx_date_monthend_prev_month ) ; mx_index = Let ( [ mx_date_monthbeg_dayofweek = DayOfWeek ( mx_date_monthbeg ) ; X = mx_date_monthbeg_dayofweek - 1 ] ; Case ( X > 0 ; cal_index - X ; X = 0 ; cal_index - X - 7 ) ) ; mx_date = Date ( Month ( date_selected ) + ( i - 1 ) ; mx_index ; Year ( date_selected ) ) ; // === CALENDAR === mx_buffer = Case ( DayOfWeek ( mx_date_monthbeg ) = 1 ; 8 - 1 ; DayOfWeek ( mx_date_monthbeg ) - 1 ) ; mx_textstyle = If ( mx_date = Get ( CurrentDate ) ; Bold ; Plain ) ; mx_calendar = TextStyleAdd ( Case ( // Dates Before Month X (Grayed) mx_index ≤ 0 ; TextColor ( cal_index + ( mx_total_days_prev_month - mx_date_monthend_prev_month_dayofweek ) ; color_off_month_day ) ; // Dates Within Month X mx_index > 0 and mx_index ≤ mx_total_days ; mx_index ; // Dates After Month X (Grayed) mx_index > mx_total_days ; TextColor ( cal_index - mx_total_days - mx_buffer ; color_off_month_day ) ) ; mx_textstyle ) ] ; Case ( parameter = "label_monthname_year" ; MonthName ( mx_date ) & " " & Year ( mx_date ) ; parameter = "label_days_of_week" ; // Better localized on the label field to avoid processing declarations above. Let ( n = Get ( CalculationRepetitionNumber ) ; Case ( n = 1 ; "S" ; n = 2 ; "M" ; n = 3 ; "T" ; n = 4 ; "W" ; n = 5 ; "R" ; n = 6 ; "F" ; n = 7 ; "S" ) ) ; parameter = "label_days_of_week_abv" ; // Better localized on the label field to avoid processing declarations above. Let ( n = Get ( CalculationRepetitionNumber ) ; Case ( n = 1 ; "Sun" ; n = 2 ; "Mon" ; n = 3 ; "Tue" ; n = 4 ; "Wed" ; n = 5 ; "Thu" ; n = 6 ; "Fri" ; n = 7 ; "Sat" ) ) ; parameter = "date" ; mx_date ; parameter = "calendar" ; mx_calendar ; parameter = "index" ; cal_index ; parameter = "calendar_highlights" or PatternCount ( parameter ; "tooltip" ) > 0 or parameter = "holiday_list" ; Let ( [ Y = Year ( mx_date ) ; // === INTEGRATED HOLIDAY LIST - BEG === (This section can be commented out / removed for better performance over remote networks) holiday_list = Case ( holiday_list = "" ; "" ; holiday_list <> "" and holiday_list <> "included" ; holiday_list ; holiday_list = "included" ; Date ( 1 ; 1 ; Y ) & " " & "New Year's Day / Kwanzaa" & ¶ & Let ( [ n = 3 ; dow = 2 ; m = 1 ; hol_monthbeg_dow = DayOfWeek ( Date ( m ; 1 ; Y ) ) ; d = Case ( hol_monthbeg_dow ≤ dow ; n * 7 - ( 7 - dow ) - hol_monthbeg_dow + 1 ; n * 7 - ( 7 - dow ) - hol_monthbeg_dow + 1 + 7 ) ] ; Date ( m ; d ; Y ) & " " & "Birthday of Martin Luther King, Jr." ) & ¶ & Date ( 2 ; 2 ; Y ) & " " & "Groundhog Day" & ¶ & Date ( 2 ; 14 ; Y ) & " " & "Valentine's Day" & ¶ & Let ( [ n = 3 ; dow = 2 ; m = 2 ; hol_monthbeg_dow = DayOfWeek ( Date ( m ; 1 ; Y ) ) ; d = Case ( hol_monthbeg_dow ≤ dow ; n * 7 - ( 7 - dow ) - hol_monthbeg_dow + 1 ; n * 7 - ( 7 - dow ) - hol_monthbeg_dow + 1 + 7 ) ] ; Date ( m ; d ; Y ) & " " & "Washington's Birthday" ) & ¶ & Date ( 3 ; 17 ; Y ) & " " & "St. Patrick's Day" & ¶ & Date ( 4 ; 1 ; Y ) & " " & "April Fool's Day" & ¶ & Let ( [ d = Mod ( ( ( 255 - 11 * Mod ( Y ; 19 ) ) - 21 ) ;30 ) + 21 ; EasterDate = Date ( 3 ; 1 ; Y ) + d + ( d > 48 ) + 6 - Mod ( ( Y + Floor ( Y / 4 ) + d + ( d > 48 ) + 1 ) ; 7 ) ] ; EasterDate - 3 & " " & "Good Friday" & ¶ & EasterDate & " " & "Easter" ) & ¶ & Date ( 4 ; 22 ; Y ) & " " & "Earth Day" & ¶ & Date ( 5 ; 5 ; Y ) & " " & "Cinco de Mayo" & ¶ & Let ( [ n = 1 ; dow = 5 ; m = 5 ; hol_monthbeg_dow = DayOfWeek ( Date ( m ; 1 ; Y ) ) ; d = Case ( hol_monthbeg_dow ≤ dow ; n * 7 - ( 7 - dow ) - hol_monthbeg_dow + 1 ; n * 7 - ( 7 - dow ) - hol_monthbeg_dow + 1 + 7 ) ] ; Date ( m ; d ; Y ) & " " & "National Day of Prayer" ) & ¶ & Let ( [ n = 2 ; dow = 1 ; m = 5 ; hol_monthbeg_dow = DayOfWeek ( Date ( m ; 1 ; Y ) ) ; d = Case ( hol_monthbeg_dow ≤ dow ; n * 7 - ( 7 - dow ) - hol_monthbeg_dow + 1 ; n * 7 - ( 7 - dow ) - hol_monthbeg_dow + 1 + 7 ) ] ; Date ( m ; d ; Y ) & " " & "Mother's Day" ) & ¶ & Let ( [ n = 3 ; dow = 7 ; m = 5 ; hol_monthbeg_dow = DayOfWeek ( Date ( m ; 1 ; Y ) ) ; d = Case ( hol_monthbeg_dow ≤ dow ; n * 7 - ( 7 - dow ) - hol_monthbeg_dow + 1 ; n * 7 - ( 7 - dow ) - hol_monthbeg_dow + 1 + 7 ) ] ; Date ( m ; d ; Y ) & " " & "Armed Forces Day" ) & ¶ & Let ( [ n = 1 ; dow = 2 ; m = 6 ; hol_monthbeg_dow = DayOfWeek ( Date ( m ; 1 ; Y ) ) ; d = Case ( hol_monthbeg_dow ≤ dow ; n * 7 - ( 7 - dow ) - hol_monthbeg_dow + 1 ; n * 7 - ( 7 - dow ) - hol_monthbeg_dow + 1 + 7 ) ] ; Date ( m ; d ; Y ) - 7 & " " & "Memorial Day" ) & ¶ & Date ( 6 ; 14 ; Y ) & " " & "Flag Day" & ¶ & Let ( [ n = 3 ; dow = 1 ; m = 6 ; hol_monthbeg_dow = DayOfWeek ( Date ( m ; 1 ; Y ) ) ; d = Case ( hol_monthbeg_dow ≤ dow ; n * 7 - ( 7 - dow ) - hol_monthbeg_dow + 1 ; n * 7 - ( 7 - dow ) - hol_monthbeg_dow + 1 + 7 ) ] ; Date ( m ; d ; Y ) & " " & "Father's Day" ) & ¶ & Date ( 7 ; 4 ; Y ) & " " & "Independence Day" & ¶ & Let ( [ n = 1 ; dow = 2 ; m = 9 ; hol_monthbeg_dow = DayOfWeek ( Date ( m ; 1 ; Y ) ) ; d = Case ( hol_monthbeg_dow ≤ dow ; n * 7 - ( 7 - dow ) - hol_monthbeg_dow + 1 ; n * 7 - ( 7 - dow ) - hol_monthbeg_dow + 1 + 7 ) ] ; Date ( m ; d ; Y ) & " " & "Labor Day" ) & ¶ & Let ( [ n = 2 ; dow = 2 ; m = 10 ; hol_monthbeg_dow = DayOfWeek ( Date ( m ; 1 ; Y ) ) ; d = Case ( hol_monthbeg_dow ≤ dow ; n * 7 - ( 7 - dow ) - hol_monthbeg_dow + 1 ; n * 7 - ( 7 - dow ) - hol_monthbeg_dow + 1 + 7 ) ] ; Date ( m ; d ; Y ) & " " & "Columbus Day" ) & ¶ & Date ( 10 ; 31 ; Y ) & " " & "Halloween" & ¶ & Date ( 11 ; 11 ; Y ) & " " & "Veterans Day" & ¶ & Let ( [ n = 4 ; dow = 5 ; m = 11 ; hol_monthbeg_dow = DayOfWeek ( Date ( m ; 1 ; Y ) ) ; d = Case ( hol_monthbeg_dow ≤ dow ; n * 7 - ( 7 - dow ) - hol_monthbeg_dow + 1 ; n * 7 - ( 7 - dow ) - hol_monthbeg_dow + 1 + 7 ) ] ; Date ( m ; d ; Y ) & " " & "Thanksgiving Day" ) & ¶ & Date ( 12 ; 12 ; Y ) & " " & "Hanukkah" & ¶ & Date ( 12 ; 13 ; Y ) & " " & "Hanukkah" & ¶ & Date ( 12 ; 14 ; Y ) & " " & "Hanukkah" & ¶ & Date ( 12 ; 15 ; Y ) & " " & "Hanukkah" & ¶ & Date ( 12 ; 16 ; Y ) & " " & "Hanukkah" & ¶ & Date ( 12 ; 17 ; Y ) & " " & "Hanukkah" & ¶ & Date ( 12 ; 18 ; Y ) & " " & "Hanukkah" & ¶ & Date ( 12 ; 19 ; Y ) & " " & "Hanukkah" & ¶ & Date ( 12 ; 25 ; Y ) & " " & "Christmas Day" & ¶ & Date ( 12 ; 26 ; Y ) & " " & "Kwanzaa" & ¶ & Date ( 12 ; 27 ; Y ) & " " & "Kwanzaa" & ¶ & Date ( 12 ; 28 ; Y ) & " " & "Kwanzaa" & ¶ & Date ( 12 ; 29 ; Y ) & " " & "Kwanzaa" & ¶ & Date ( 12 ; 30 ; Y ) & " " & "Kwanzaa" & ¶ & Date ( 12 ; 31 ; Y ) & " " & "New Year's Eve / Kwanzaa" ) // INTEGRATED HOLIDAY LIST - END ] ; Case ( parameter = "calendar_highlights" ; // CALENDAR HIGHLIGHTS (current day, date selected, holidays). Case ( mx_index > 0 and mx_index ≤ mx_total_days and mx_date = date_selected ; TextColor ( highlight_date_selected ; color_date_selected ) ; PatternCount ( Substitute ( " " & holiday_list & " " ; [ "¶" ; " " ] ) ; " " & mx_date & " " ) > 0 ; TextColor ( highlight ; color_holiday ) ; highlight_weekends = 1 and ( DayOfWeek ( mx_date ) = 1 or DayOfWeek ( mx_date ) = 7 ) ; TextColor ( highlight ; color_weekend ) ) ; PatternCount ( parameter ; "tooltip" ) > 0 ; Case ( mx_date = Get ( CurrentDate ) ; "Today:" & "¶" ) & Case ( parameter = "tooltip" ; mx_date ; parameter = "tooltip_long" ; DayName ( mx_date ) & ", " & MonthName ( mx_date ) & " " & Day ( mx_date ) & ", " & Year ( mx_date ) ) & Case ( PatternCount ( Substitute ( " " & holiday_list & " " ; [ "¶" ; " " ] ) ; " " & mx_date & " " ) ; Let ( [ pos1 = Position ( holiday_list ; mx_date ; 1 ; 1 ) ; pos2 = Position ( holiday_list ; " " ; pos1 ; 1 ) + 1 ; pos3 = Position ( holiday_list & "¶" ; "¶" ; pos1 ; 1 ) ; holiday_name = Middle ( holiday_list ; pos2 ; pos3 - pos2 ) ] ; Case ( holiday_name <> "" ; "¶" & holiday_name ) ) ) ; parameter = "holiday_list" ; holiday_list ; ) /*End Case*/ ) /*End Let*/ ) /*End Case*/ ) /*End Let*/
 Then copy & paste into FileMaker Advanced's Edit Custom Function window.