A scripted button bar FileMaker calendar date picker

This method for a FileMaker calendar date picker uses button bars and scripting only. No table of date records, no relationships, no portals, no custom functions and no web viewer are required. It is context agnostic, and handles leap years without additional consideration. Purely button bars, script and global variable list, and 2 global number fields. The method builds a list of day numbers via a conventional FileMaker script loop, and tests faster than using a Virtual List. It is rapid under Web Direct

This page describes the FileMaker button bar configuration and scripting required to build a single month date picker button bar matrix. A variation as a 3 month calendar date picker button bar matrix and scripting can also be built and is detailed in a separate page.

The month structure is 6 button bars, each of 7 segments, forming a conventional 6 week calendar 'month' matrix, with leading and trailing month days. Two globals are used for month number and year. On changing the global month value (+/- year), the script then builds a list of 42 date day numbers:

  • calculate the day number of the first of the month date for the specified month and year, using FileMaker GetAsNumber(date) function syntax which returns the number of days since 1/1/0001; e.g. 1/1/2018 = 736695.
  • calculate the day number of the first Monday to display, of the week that the first of month date falls in
  • build a list of 42 day numbers, starting from the first Monday day number, using a simple standard FileMaker script loop sequence, and set the list into a global variable.

A calculation in each button bar segment parses the day number value, respective to the segment sequence, from the $$_variable list of 42 day number values, and calculates the relevant day number component of the date to display. The weeks start on Monday as per British convention. Days of the preceding month and following month that may occur in the first, fifth and sixth weeks are greyed using conditional formatting defined on each of the relevant button bar segments.

The method can be readily expanded to a 3 month matrix of button bar dates, and scripting is modified to handle the 3 months spanning consecutive years.

The method is reasonably fast. Testing on LAN executes on the order of 6 msec (laptop client) to 30-40msec (iPads of c. 2013, 2012 vintage) for a single month , and 65 msec for the 3 month matrix using the FileMaker script loop to establish the day number list . Via FileMaker WebDirect the same c. 4 msec performance is obtained for laptop and iPads tested A virtual list modification to the same script by comparison takes substantially longer to execute.

filemaker month date picker

Globals for month and year

Two number fields are defined in a utility table; g_month and g_year. A dual segment button bar is added to the layout to step up or down through the months and the assigned change month script rolls the year up for December to January and down for January to December; simple and conventional.

The calendar month button bar matrix

The Calendar month matrix uses 6 horizontal button bars, each of 7 button bar segments. There are 2 components that need to be defined for each segment to display the date day number; the actual Day of the date, and the Conditional Format to grey out prior and next month days. Adding a third component; the Action to run a script or open a popover for each segment is straightforward.

Object names are defined for the six Button bars: bb_w1, bb_w2, bb_w3, bb_w4, bb_w5, bb_w6. The last steps of the script are to refresh objects using the object names. Refresh Object is quicker than a Refresh Window step, 5 vs 30msec (local or hosted to client), and provide a smoother redraw in Web Direct.

a). The calendar button bar segment - calendar day calculation

The script creates a global variable list of day numbers ($$_list). Each button bar segment from 1 to 42 parses the respective button bar sequence value (N) from the list. FileMaker Day numbers for January 2018 illustrated.

Global variable day number list scripted output

736695
736696
736697
736698
736699
736700
736701
736702
736703
736704
736705
736706
736707
736708
736709
736710
736711
736712
736713
736714
736715
736716
736717
736718
736719
736720
736721
736722
736723
736724
736725
736726
736727
736728
736729
736730
736731
736732
736733
736734
736735
736736
736737

Calendar month button bar segment date calculation example

Let([ N = 1 ;
LST = $$_list;
];
Day( GetAsDate( GetAsNumber(GetValue (LST; N) )))
)

b). The calendar button bar segment - Conditional Format

A conditional format is defined for each button bar segment in the first, fifth and sixth rows of button bars, with a paler grey text CF result. This shows days from prior and following months where applicable.

The conditional format calculation is written with the first row to include N=x, as this makes it easier to check the sequence of values for the button segments, without having to burrow down and open the actual FileMaker - Specify calculation sub-window. The calculation is the same for all segments except for the N value. The calculation could probably be rewritten to evaluate the segment object name; but less efficient than a hard coded number. Same strategy applies to the segment date calculation.

Calendar button bar segment Conditional Format calculation

Let([ N = 1 ;
LST = $$_list;
M = Cal::g_month
];
Month( GetAsDate( GetAsNumber(GetValue (LST; N) ))) ≠ M
)

filemaker month date picker conditional format

c). The calendar week starts MONDAY

The real world thinks of the working week as starting Monday. That's why the weekEND is both Saturday and Sunday. So does this calendar display. The script calculates an offset from Monday to the first of the month; this can be easily modified for the week to start on Sunday. The weekend Saturday and Sunday columns are slightly differentiated by placing a grey fill rectangle object behind the button bars. The Button Bars Segments themselves are styled to have no fill colour.

The scripting - build a list of 42 day numbers for the Calendar month

GetAsNumber(date) is used as per FileMaker convention to return the date as the number of days since 1/1/0001. Date is used consistent with the sensible order convention of DD/MM/YYYY.

The month up/down buttons are attached to the edit_month script, and this in turn calls the ss_date_list script as a subscript. For the sake of completeness, the edit_month_up_down script is described.

a). Script sequence logic

  • (dev) start timer
  • set month +/- year; up/down
  • do subscript to build list
    • calculate fdom date
    • calculate fdom daynum
    • calculate fdom dow
    • calculate daynum of first day of the first week of the matrix to display
    • build daynum list x42 via script loop
  • refresh object - for each button bar object
  • (dev) end timer
  • (dev) update stats

b). Step the calendar month up/down

The two down/up buttons have simple script parameters; month|down, month|up. There is a trap for empty month or year values added into the script.

The change month script needs to capture the global month and year values before changing them, for the subsequent evaluate year change.

A refresh object last step for each of the six button bars is mandatory for the button bars to update. While this is not strictly necessary on a local file, it is mandatory when served.

Script sequence - reset calendar month and conditionally year
Script step Defn.
Set Variable $_fn getSP_n ( 1 ) ] month
Set Variable $_q getSP_n ( 2 ) ] up
Set Variable $_month_ref Cal::g_month 1
Set Variable $_year_ref Cal::g_year 2018
// trap for empty month or year
IF
IsEmpty($_month_ref) or IsEmpty($_year_ref)
set field Cal::g_month = Month( Get ( CurrentDate ))
set field Cal::g_year = Year( Get ( CurrentDate ))
set variable $_fn = ""
set variable $_q = ""
ELSE
IF $_q = "down"
Set Field Cal::g_month Let([
M = $_month_ref
];
Case(
M ≥ 2; M-1 ;
M = 1; 12
))
// conditionally reset the year - when jan to dec
If [ $_month_ref = 1 ]
Set Field Cal::g_year 		$_year_ref -1
Else If $_q = "up"
Set Field Cal::g_month Let([
M = M = $_month_ref
];
Case(
M ≤ 11; M + 1 ;
M = 12; 1
))
 2
// conditionally reset the year - when dec to jan
If [ $_month_ref = 12 ]
Set Field Cal::g_year 		$_year_ref + 1
END IF
END IF
Perform subscript: ss_date_list
Refresh button bar objects: bb_w1, bb_w2, bb_w3, bb_w4, bb_w5, bb_w6

c). Subscript - build list of Calendar month day numbers to display

Subscript sequence - calculate first day of the 42 button bar calendar matrix
Script step example result
Set Variable [ $_month; Value:Cal::g_month ] 2
Set Variable [ $_year; Value:Cal::g_year ] 2018
Set Variable [ $_fom_date; Value:GetAsDate( "1/" & $_month & "/" & $_year) ] 1/2/2018
Set Variable [ $_fom_daynum; Value:GetAsNumber(GetAsDate( $_fom_date )) ] 736726
Set Variable [ $_fom_dow; Value:DayOfWeek( $_fom_date) ]
// requires text input; so wrapping $var in GetAsDate fails		 5
Set Variable [ $_start_daynum; Value:Let([
DOW = $_fom_dow
];
$_fom_daynum -
Case(
DOW = 1; 6;
DOW = 2; 0;
DOW = 3; 1;
DOW = 4; 2;
DOW = 5; 3;
DOW = 6; 4;
DOW = 7; 5;
)
)]
736723
Subscript sequence - script loop to build list of 42 day numbers for calendar month to display
Script loop sequence to build list example result
build day number list via loop - no date table required
Set Variable [ $_n; Value:1 ]
Set Variable [ $_exit; Value:$_day_count ]
Set Variable [ $_list; Value:$_start_daynum ]
Loop
Set Variable [ $_day_n; Value:$_start_daynum + $_n ]
Set Variable [ $_list; Value:Let([ LST = $_list ;VAL = $_day_n ];LST & ¶ & VAL )]
Set Variable [ $_n; Value:$_n + 1 ]
Exit Loop If [ $_n > $_exit ]
End Loop

Set Variable [ $$_list; Value:$_list ] 		736723
736724
736725
736726
736727
736728
736729
736730
736731
736732
736733
736734
736735
736736
736737
736738
736739
736740
736741
736742
736743
736744
736745
736746
736747
736748
736749
736750
736751
736752
736753
736754
736755
736756
736757
736758
736759
736760
736761
736762
736763
736764

Comparison executeSQL to build list

For comparison testing only, the loop sequence is disabled and an executeSQL step enabled. This virtual list approach does require a table of dates and daynumbers, and uses two custom functions. None of which are required for the normal calendar script loop method.

Comparison testing showed eSQL/VL approach to slower than the simple loop list build, and performance progressively degraded with more date records, in the requisite Days table.

Script step to build list example result
Let([
// retrieve these values
RSLT = sqlFN( Days::day_num ) ;

// from table
TBL = sqlTN( Days::id) ;

//where field:
fa = sqlFN( Days::day_num) ;

// where fm values for placeholders are:
va = $_start_daynum ;

~query = "
SELECT ~result
FROM ~table
WHERE ~fa >= ?
ORDER BY ~sort ASC
FETCH FIRST 42 ROWS ONLY
"
;
~sqlQuery = Substitute( ~query;
["~result" ; RSLT ] ;
["~table" ; TBL ] ;
["~fa" ; fa ] ;
["~sort" ; fa ]
)
];
ExecuteSQL ( ~sqlQuery ; "" ; "" ; va )
)
 736723
736724
736725
736726
736727
736728
736729
736730
736731
736732
736733
736734
736735
736736
736737
736738
736739
736740
736741
736742
736743
736744
736745
736746
736747
736748
736749
736750
736751
736752
736753
736754
736755
736756
736757
736758
736759
736760
736761
736762
736763
736764

Transportability

Once an initial calendar is built, it is readily transportable into other files:

  • create table called Cal (or temporarily rename the existing utility table)
  • create global number fields in table Cal: g_month and g_year
  • import scripts: edit_change_month and ss_date_list
  • copy and paste in calendar button bar matrix, step month button bar, and month and year global fields
  • all of which takes about 2 minutes.
  • then update the theme for the added styles attached to the objects:
    • bb_cal_month
    • bb_w1, bb_w2, bb_w3, bb_w4, bb_w5, bb_w6
    • day labels
    • fld_cal_month and fld_cal_year
    • cal_panel
    • cal_panel_weekend

Speed Testing - FileMaker button bar calendar date picker

Using a script loop approach to build the day number list is faster than using a executeSQL Virtual List approach to parse a list from a table of date records. Both methods are quick on LAN testing.

Web Direct using the loop script method to establish the day list is both quick, and offers equivalent performance across laptop and iPad devices tested.

Speed of execution of Virtual List executeSQL method is directly dependent on how many records there are in the date table, more is slower. It is universally slower by orders of magnitude than the script loop approach to build the day number list in all tests. This might be improved by running the eSQL step as a Perform Script on Server construct.

Refresh of the button bar objects, needs to be done via refresh Objects rather than a refresh window, and is mandatory when served.

a). local file on client

method device msec av msec sd n
FM script loop MBP 6
4		 0.7
1		 100
101
FM script loop iPad mini 2 21 8.4 135
FM script loop iPad 3 40 6.9 100
Virtual list eSQL MBP 18 5.8 100
Virtual list eSQL iPad mini 2 72 20.7 100
Virtual list eSQL iPad 3 144 65.5 100

b). Server hosted file - access via FMPA and FMGo

method client av msec sd msec n
FM script loop MBP 6 1.0 91
FM script loop iPad mini 2 31 5.1 100
FM script loop iPad 3 40 4.3 100
Virtual list eSQL MBP 71
85		 39.2
29.8		 100
101
Virtual list eSQL iPad mini 2 185
313		 60.1
122.5		 100
101
Virtual list eSQL iPad 3 271
1253		 56.3
232		 60
51

c). Server hosted file - access via Web Direct

method device msec av msec sd n
FM script loop MBP Safari 11.0 5
4		 2
0.7		 100
101
FM script loop iPad mini 2 Safari 11.0 4
4		 1.2
0.7		 100
101
FM script loop iPad 3 4
4		 0.7
0.8		 100
101
Virtual list eSQL MBP 13 8.5 101
Virtual list eSQL iPad mini 2 71 13.1 97
Virtual list eSQL iPad 3 1010 315 101

c).Virtual list tests

The prior tests were carried out on a file with 753 day records in the Days table. Tests were repeated over a few days. To alleviate the tedium of button pressing, a simple test script was constructed to run the month up and down script, for 25 up or 25 down iterations. The stats sequence was removed to a separate script for manual execution. Except for baseline test locally on MBP, all other tests conducted on the file hosted on server.

One interesting quirk in manual testing, is that month changes for current year 2018 run in 35 sd 6.5 msec, in 2019 in 25 msec sd 5.9, and 2017 45 msec sd 7.2. It was noticing this curious pattern during manually stepping through months, that lead to scripting the month change iterations as 25 up or down starting from the same month/year each repetition of 25 month changes. in order to further standardise testing.

i). Virtual list tests - FMPA and FMGo clients

device client day records msec av msec sd n
MBP local 1800
1857
1826		 29
29
25		 13.7
12.7
7.7		 51
101
101
MBP fmpa 761
761
761		 26
27
25		 16
20.3
18.8		 97
97
101
ipad mini 2 fmgo 761
761		 57
50		 31.1
20.6		 97
101
ipad 3 fmgo 761
761		 199
180		 112
102		 97
101
MBP fmpa 1800
1857
1826		 49
49
53		 31.5
17
31		 101
101
101
ipad mini 2 fmgo 1800
1857		 49
136		 31.5
57		 101
101
ipad 3 fmgo 1800
1857		 481
600		 257
245		 101
101
MBP fmpa 4657 95
90		 28
19.2		 51
97
ipad mini 2 fmgo 4657 327
308		 107
62		 101
97
ipad 3 fmgo 4657 1072
1021		 235
147		 101
97

ii). Virtual list tests - Web Direct

device client day records msec av msec sd n
MBP webdirect 761 12 5.9 101
ipad mini 2 webdirect 761 11 5.6 101
ipad 3 webdirect 761 11 5.6 101
MBP webdirect 1857 31 13.5 101
ipad mini 2 webdirect 1857 34 12.2 101
ipad 3 webdirect 1857 34 11.2 101
MBP webdirect 4657 84
70		 64
12.9		 51
97
ipad mini 2 webdirect 4657 74
70		 20
12.9		 51
97
ipad 3 webdirect 4657 74
70		 15
12.9		 101
97

d). testing hardware

  • MBP - Macbook Pro 2012, 2.3GHz 16GB RAM,251GB SSD, OSX.12.6, FMPA 16.0.3
  • SRV - Mac Mini late 2014 2.6GHz, 16GB RAM, 1TB HDD; OSX.13.2; FMS 16.0.2
  • Ipad mini 2- 2013 64GB, iOS 11.2.2, FM Go 16.0.3
  • Ipad 3 - 2012 1.0 GHz, 1 GB RAM, iOS 9.3.5, FM Go 15.0.4

button bar date picker