FileMaker Pro® Scripting

Developer Tech Help  |  DOS Tips & Tricks  |  Mac OS X  |  Windows

Singular or Plural

Use the Show Custom Dialog script step to show messages that are grammatically appropriate to the number of records found.

Title Code:
Let ( [
n = Get ( FoundCount )
] ;

"Found " & n & " record" & If ( n > 1 or n = 0 ; "s " ; " " ) & "with" & If ( n = 1 ; " a " ; " " ) & "valid email address" & If ( n > 1 or n = 0 ; "es" ; "" ) & "."
)

Message Code:
Let ([
n = Get ( FoundCount )
] ;

"Found " & n & " record" & If ( n > 1 or n = 0 ; "s " ; " " ) & "with" & If ( n = 1 ; " a " ; " " ) & "valid email address" & If ( n > 1 or n = 0 ; "es " ; " " ) & "for " & Get ( ScriptParameter ) & "."
)

------------------------------------------------------------------------------------------------------

"New Note" Script

Use the Set Field script step to create a "new note" script.
Code for the Calculation:
(you can copy and paste all of the colored text below)

/*
==================================================
Author: Tom Beek (
tombeek@gmail.com)
Date: 4/28/2008
Notes: This code for the SET function that will insert a datestamp at the top of a notes field for new entries.
The code below assumes a notes field named "Notes." Change it to suit your actual field name if different.
==================================================
*/
Let ( [

x = If ( IsEmpty ( Database::Notes ) ; 1 ; 0 ) ;
notes = Database::Notes
] ;

Get ( CurrentDate ) & " - " & If ( x ; "" ; "¶¶" &
notes)

)

To put the cursor just after the new text, use the following script step (assumes note field named "Notes"):

Set Selection[ Notes ; Start Position: -1 ; End Position: Position ( Notes ; "¶" ; 1 ; 1 ) - 1 ]

The -1 at the end is the same as pressing the left-arrow key; it de-selects the text so that typing can begin immediately without replacing selected text.

U.S. Telephone/Fax Format Calculation Field

This code relies on the magic of the filter() function. For numerical fields it can filter out everything besides digits, and is therefore the workhorse for any numbers-only fields that you would like to apply a formatting mask on, like telephone numbers.

/*
====================================================
Author: Tom Beek (tombeek@gmail.com)
Date: 4/25/2008
Useage: Telephone/Fax number format. Use this code in a calculation field that goes on top of the
plain text field telephone number field that it references. Modify its behavior to not accept the focus in either Browse or Find modes. When a user clicks on this field, the focus goes to the underlying editable text field. Also, be sure to format this calc field with an opaque background so that the underlying text field does not show through until you click on it.
The code below assumes a telephone number field named "Telephone". If yours has a different name, just change the field reference in the first line.
====================================================
*/
Let ( phone = filter ( Telephone ; "0123456789" ) ; //keeps only digits
Case (
// if all ten digits are present: (###) ###-####
Length( phone ) = 10 ; "(" & Left( phone ; 3) & ") " & Middle( phone ; 4 ; 3 ) & "-" & Middle ( phone ; 7 ; 30 ) ;

// if extra digit, usually a "1" for long distance, is present: #(###) ###-####
Length( phone ) = 11 ; Left( phone ; 1) & "(" & Middle( phone ; 2 ; 3 ) & ") " & Middle( phone ; 5 ; 3 ) & "-" & Middle( phone ; 8 ; 30 ) ;
// if seven digits: ###-####
Length( phone ) = 7 ; Left( phone ; 3) & "-" & Right ( phone ; 4 ) ;
//default value
phone
)

)

-------------------------------------------------------------------------------------------------------
Date Ranges for Reports
The Godsend for this one is the Let() function that allows you to assign unwieldy value strings to tidy little variables.

/*
===============================================================
Author: Tom Beek (tombeek@gmail.com)
Date: 4/15/2008
Useage: Menu-driven date ranges for reports
===============================================================

Calculate date ranges from menu choices using a global field "DateRangeglobal" as follows:
*/

Let (
[
$Today = Get ( CurrentDate ) ;
$DayNum = DayOfWeek ( $Today ) - 2 ;
$Choice = Main Database::DateRangeglobal ;
$ThisWeek = $Today - $DayNum & "..." & $Today ;
$ThisMonth = Month ( $Today ) & "/" & 1 & "/" & Year($Today) & "..." & $Today ;
$Yesterday = ($Today - 1) & "..." & ($Today - 1) ;
$LastWeek = ( $Today - ( $DayNum + 7 ) ) & "..." & ( $Today - ( $DayNum + 3 ) ) ;
$LMStart = Month ( $Today ) - 1 & "/1/" & Year ( $Today ) ;
$LMEndpre = Month ( $Today ) & "/1/" & Year ( $Today ) ;
$LMEnd = GetAsDate ( $LMEndpre ) - 1 ;
$LastMonth = $LMStart & "..." & $LMEnd ;
$Past1Week = ($Today - 7) & "..." & $Today ;
$Past2Weeks = ($Today - 14) & "..." & $Today ;
$Past3Weeks = ($Today - 21) & "..." & $Today ;
$Past1Month = ($Today - 31) & "..." & $Today ;
$Past2Months = ($Today - 62) & "..." & $Today ;
$Past3Months = ($Today - 93) & "..." & $Today ;
$Past6Months = ($Today - 186) & "..." & $Today ;
$PastYear = ($Today - 365) & "..." & $Today ;
$YTD = Date( 1 ; 1 ; Year($Today) ) & "..." & $Today
];

Case (
$Choice = "Today" ; $Today & "..." & $Today ;
$Choice = "Yesterday" ; $Yesterday ;
$Choice = "This week" ; $ThisWeek ;
$Choice = "Last week" ; $LastWeek ;
$Choice = "This month" ; $ThisMonth ;
$Choice = "Last month" ; $LastMonth ;
$Choice = "Past week" ; $Past1Week ;
$Choice = "Past 2 weeks" ; $Past2Weeks ;
$Choice = "Past 3 weeks"; $Past3Weeks ;
$Choice = "Past month"; $Past1Month ;
$Choice = "Past 2 months" ; $Past2Months ;
$Choice = "Past 3 months"; $Past3Months ;
$Choice = "Past 6 months "; $Past6Months ;
$Choice = "Past year" ; $PastYear ;
$Choice = "Year to date" ; $YTD ;
$Choice = "All" ; "..." ;
PatternCount ( $Choice; "/" ) > 1 ; $Choice ;
"..."
)
)

Pseudo Triggers

FileMaker Pro® does not have triggers or stored procedures like some database systems do, but you can create the equivalent for some circumstances, like updating a date or time field based on changes in another field. Here's a nifty way to update a dependent field:

Evaluate("Get(CurrentTimeStamp)", [FieldB, FieldC])

This will store a timestamp in the calculation field whenever FieldB or FieldC changes.

------------------------------------------------------------------------------------------------------- 

Validating Email Addresses

This calculation returns text.  It is convenient to place the resulting field next to an Email Address field, and to format it as transparent, so that text only shows if there is a problem with the Email field it references.

/*
Creation Date: 10.13.2008
Modified Date: 12.08.2008
Functionality: Returns text message for malformed email address
Field name used as input: Email
Note: List of TLDs from http://data.iana.org/TLD/tlds-alpha-by-domain.txt
(For more information see http://www.answers.com/topic/list-of-internet-top-level-domains)

This code does not recognize the form local@domain.tld/more.here
*/

Let ( [
  _validDomainCharacters =
  "abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789-_." ;
  _invalidLocalCharacters = "!\"#$%&'*,/:;<>?@[\]^`{}|~" ;
  _theLocalPart = Left ( Email ; Position ( Email ; "@" ; 1; 1 ) - 1 ) ;
  _theDomainPart = Right ( Email ; Length ( Email ) - Position ( Email ; "@" ; 1; 1 ) ) ;
  _theTopLevelDomain = RightWords ( Substitute ( _theDomainPart ; "." ; " " ) ; 1 ) ;
  _ValidTopLevelDomains =
"AC¶AD¶AE¶AERO¶AF¶AG¶AI¶AL¶AM¶AN¶AO¶AQ¶AR¶ARPA¶AS¶ASIA¶AT¶AU¶AW¶AX¶AZ¶
BA¶BB¶BD¶BE¶BF¶BG¶BH¶BI¶BIZ¶BJ¶BM¶BN¶BO¶BR¶BS¶BT¶BV¶BW¶BY¶BZ¶CA¶CAT¶CC¶
CD¶CF¶CG¶CH¶CI¶CK¶CL¶CM¶CN¶CO¶COM¶COOP¶CR¶CU¶CV¶CX¶CY¶CZ¶DE¶DJ¶DK¶DM¶
DO¶DZ¶EC¶EDU¶EE¶EG¶ER¶ES¶ET¶EU¶FI¶FJ¶FK¶FM¶FO¶FR¶GA¶GB¶GD¶GE¶GF¶GG¶GH¶
GI¶GL¶GM¶GN¶GOV¶GP¶GQ¶GR¶GS¶GT¶GU¶GW¶GY¶HK¶HM¶HN¶HR¶HT¶HU¶ID¶IE¶IL¶
IM¶IN¶INFO¶INT¶IO¶IQ¶IR¶IS¶IT¶JE¶JM¶JO¶JOBS¶JP¶KE¶KG¶KH¶KI¶KM¶KN¶KP¶KR¶KW¶
KY¶KZ¶LA¶LB¶LC¶LI¶LK¶LR¶LS¶LT¶LU¶LV¶LY¶MA¶MC¶MD¶ME¶MG¶MH¶MIL¶MK¶ML¶MM¶
MN¶MO¶MOBI¶MP¶MQ¶MR¶MS¶MT¶MU¶MUSEUM¶MV¶MW¶MX¶MY¶MZ¶NA¶NAME¶NC¶NE¶
NET¶NF¶NG¶NI¶NL¶NO¶NP¶NR¶NU¶NZ¶OM¶ORG¶PA¶PE¶PF¶PG¶PH¶PK¶PL¶PM¶PN¶PR¶
PRO¶PS¶PT¶PW¶PY¶QA¶RE¶RO¶RS¶RU¶RW¶SA¶SB¶SC¶SD¶SE¶SG¶SH¶SI¶SJ¶SK¶SL¶SM¶
SN¶SO¶SR¶ST¶SU¶SV¶SY¶SZ¶TC¶TD¶TEL¶TF¶TG¶TH¶TJ¶TK¶TL¶TM¶TN¶TO¶TP¶TR¶
TRAVEL¶TT¶TV¶TW¶TZ¶UA¶UG¶UK¶US¶UY¶UZ¶VA¶VC¶VE¶VG¶VI¶VN¶VU¶WF¶WS¶
XN--0ZWM56D¶XN--11B5BS3A9AJ6G¶XN--80AKHBYKNJ4F¶XN--9T4B11YI5A¶XN--DEBA0AD¶XN--G6W251D¶XN--HGBK6AJ7F53BBA¶XN--HLCJ6AYA9ESC7A¶XN--JXALPDLP¶
XN--KGBECHTV¶XN--ZCKZAH¶YE¶YT¶YU¶ZA¶ZM¶ZW¶"
] ;

Case (
    IsEmpty ( Email ) ; "no value supplied" ;
    PatternCount ( Email ; "@" ) ? 1 ; "invalid address" ;
    PatternCount ( _theDomainPart ; "." ) = 0 ; "invalid formatting" ;
    Length ( Filter ( _theLocalPart ; _invalidLocalCaracters ) ) ;
        "invalid characters in the local part" ;
    Length ( Filter ( _theDomainPart ; _validDomainCharacters ) ) ? Length ( _theDomainPart ) ;
        "invalid characters in the domain part" ;
    Left ( _theLocalPart ; 1) = "." or
      Right ( _theLocalPart ; 1 ) = "." or
      Left ( _theDomainPart ; 1 ) = "." or
      Right ( _theDomainPart ; 1 ) = "." ; "invalid formatting" ;
    IsEmpty ( FilterValues ( _theTopLevelDomain ; _ValidTopLevelDomains ) ) ;
        "invalid top level domain" ;

    1
    )
)