FileMaker Custom Functions

Note: The entries starting with 'z_' are calculations that can be used in fields or scripts. The first line of a custom function is usually a comment to demonstrate how to use that function. I try to comment code using only double slash so when a calculation is copied from one file to another, the paste command can comment the whole calc with /* */ instead of pasting a blank entry. One exception is multi-line sample code because all the // for each line can be confusing.

Favorites: fnHelpText, fnScrapeText, fnSuperTrim, fnThousands, fnCleanText

Other Pages: Microsoft XML Documents, FileMaker Downloads

Custom Function List:

fnArabic2Roman

// fnArabic2Roman ( myNumber )

Case (
   myNumber <> Int (myNumber) ; "ERROR: invalid input, input must be a postive INTEGER less than 4000";
   myNumber <= 0 ; "ERROR: invalid input, input must be a POSITIVE integer less than 4000";
   myNumber > 3999 ; "ERROR: invalid input, input must be a positive integer LESS THAN 4000";

   Let ( [
      thousands = Mod ( Int ( myNumber / 1000 ) ; 10 ) ; 
      comment = Case ( 1 = 2; "other values skipped since 3K is max"); 
      postMs = Left ( "MMM" ; thousands )
      ] ;  
      postMs
   ) & //end Let

   Let ( [
      hundreds = Mod ( Int ( myNumber / 100 ) ; 10 ) ; 
      preCs = Case ( Mod ( hundreds; 5 ) = 4 ; "C" ) ; 
      ofMorD = Case ( hundreds = 9; "M"; hundreds >= 4 ; "D" ) ; 
      postCs = Case ( Mod ( hundreds ; 5 ) ≤ 3 ; Left ( "CCC" ; Mod ( hundreds ; 5 ) ) )
      ] ; 
      preCs & ofMorD & postCs
   ) & //end Let

   Let ( [
      tens = Mod ( Int ( myNumber / 10 ) ; 10 ) ; 
      preXs = Case ( Mod ( tens ; 5 ) = 4 ; "X" ) ; 
      ofCorL = Case ( tens = 9 ; "C" ; tens >= 4 ; "L" ) ; 
      postXs = Case ( Mod ( tens ; 5 ) ≤ 3 ; Left ( "XXX" ; Mod ( tens ; 5 ) ) )
      ] ; 
      preXs & ofCorL & postXs
   ) & //end Let

   Let ( [
      ones = Mod ( Int ( myNumber / 1 ) ; 10 ) ; 
      preIs = Case ( Mod(ones; 5) = 4 ; "I" ) ; 
      ofXorV = Case ( ones = 9 ; "X" ; ones >= 4 ; "V" ) ; 
      postIs = Case ( Mod ( ones ; 5 ) ≤ 3 ; Left ( "III" ; Mod ( ones ; 5 ) ) )
      ] ; 
      preIs & ofXorV & postIs
   ) //end Let

)  //end case


// Allows you to take any poisitive integer value and 
// return the equivelent Roman Numeral up to 3,999.
// From BrianDunning.com  -rcaldwell 2/11/2010
// https://www.briandunning.com/cf/84
// Also see fnRoman2Arabic.

fnAuditLogLoad

// fnAuditLogLoad ( field_list ; acct_name )
Let ( [ 
vFieldFullName = GetValue ( field_list ; 1 ) ; 
vFieldShortName = GetValue ( Substitute ( vFieldFullName ; "::" ; ¶ ) ; 2 ) ; 
vNewValue = GetField ( vFieldFullName ) ; 
vAcct = If ( acct_name ≠ "" ; acct_name ; "logstart" )
] ; 

If ( IsValid ( GetField ( vFieldFullName ) ) ; 
   If ( vNewValue ≠ "" ; 
	GetAsDate ( Get ( CurrentHostTimestamp ) ) & Char(9) &
	GetAsTime ( Get ( CurrentHostTimestamp ) ) & Char(9) &
	vAcct & Char(9) &
	vFieldShortName & Char(9) & 
	"[---]" & Char(9) & "-»" & Char(9) & 
	If ( vNewValue ≠ "" ; vNewValue ; "[---]" ) & ¶ 
	)
) &  //end if

If ( ValueCount ( field_list ) > 1 ; 
   fnAuditLogLoad ( RightValues ( field_list ; ValueCount ( field_list ) - 1 ) ; acct_name ) )

)  //end Let


// Run this custom function to pre-populate an audit log field with existing values.
// eg. fnAuditLogLoad ( GetFieldName ( NameFirst ) &¶& GetFieldName ( NameLast ) ; "" )
// 6/22/2019 - Changed field name to strip off table off of the field name because the final log process does not have the table.
// 9/9/2019 - Took recursion out of the IF statement to prevent the function from stopping short.

fnBigDollar

// fnBigDollar ( number , precision )
Let ( A = Round ( Abs ( number ) ; 0 ) ; 
  If ( number < 0 ; "-" ) & 
  "$" & Case ( 
    A < 1000 ; A ; 
    A < 10000 ; Left ( A ; 1 ) & "," & Right ( A ; 3 ) ; 
    A < 100000 ; Left ( A ; 2 ) & "," & Right ( A ; 3 ) ; 
    A < 1000000 ; Left ( A ; 3 ) & "," & Right ( A ; 3 ) ; 
    A < 1000000000 ; fnRound ( A / 1000000 ; precision ) & "-million" ; 
    A < 1000000000000 ; fnRound ( A / 1000000000 ; precision ) & "-billion" ; 
    A < 1000000000000000 ; fnRound ( A / 1000000000000 ; precision ) & "-trillion" ; 
    A < 1000000000000000000 ; fnRound ( A / 1000000000000000 ; precision ) & "-quadrillion" ; 
    A 
  ) //end Case
) //end Let


// Converts large dollar values from all digits to $XX.X-million or $XX.X-billion.
// Uses fnRound to prevent display errors over 1-billion (1e+09).

fnBuildRSSFeed

// fnBuildRSSFeed ( rssTitle ; rssPubDate ; IDLink ; rssLink ; rssDescription ; rssXMLurl ; rssEnclosure )

//------------ Sample data

Let ( [ 
vFirstRun = IsEmpty ( $RSSHeader ) ; 
vLastRun = IsEmpty ( rssTitle & rssPubDate & vIDLink & rssLink & rssDescription & rssXMLurl & rssEnclosure ) ; 

//------------ Confiure default values
rssAuthor = "info@2geckos.com( 2Geckos Consulting, LLC )" ; 
rssEditor = rssAuthor ; 
rssLastPubDate = rssPubDate ; 
rssCopyright = "http://www.webwizdirect.com/ww2g/copyright.html" ; 
rssWebmaster = "info@2geckos.com( 2Geckos Consulting, LLC )" ; 
rssContact = "Roger Biel" ; 
rssTimeTillLive = 1440 ; 
rssCategory = "Website Maintenance" ; 
vPodcast = 0 ; 
rssLink = If ( IsEmpty ( rssLink ) ; "http://www." & $$Domain ; rssLink ) ; 

//-------------- Format initalize, and verify data
rssEnclosureLink = If ( IsEmpty ( rssEnclosure ) ; 
	"http://www.webwizdirect.com/images/2Geckos_logo.26.png" ; fnValidURL ( "URL" ; rssEnclosure ) ) ; 
rssGenerator = "WebWizDirect( www.webwizdirect.com )" ; 

//---------------
vIDLink = If ( IsEmpty ( vIDLink ) ; "IDran" & fnJulian ( rssPubDate ) ; vIDLink ) ; 
rssPermaLink = If( IsEmpty ( fnValidURL ( "Domain" ; vIDLink ) ) ; "false" ; "true" ) ; 
rssEditor = If ( PatternCount ( rssEditor ; "@" ) < 1 ; rssWebmaster ; rssEditor ) ; 
$$RSSerrors = If ( vFirstRun ; "" ; $$RSSerrors ) ; // reset errors log
rssEditor = Substitute ( rssEditor ; [ "<" ; "( " ] ; [ ">" ; " )" ] ) ; 
rssEnclosureLength = 1000 ; 
rssEnclosureType = fnMimeType ( rssEnclosureLink ; "text" ) ; 
rssEnclosure = If ( IsEmpty ( rssEnclosureLink ) ; "" ; Substitute ( fnBuildRSSFeedLib ( "enclosure" )
	; [ "rssEnclosureLength" ; rssEnclosureLength ]
	; [ "rssEnclosureType" ; rssEnclosureType ]
	; [ "rssEnclosureLink" ; rssEnclosureLink ]
	) ) ; 
rssPodcast = If ( not vPodcast ; "" ; fnBuildRSSFeedLib ( "iTunes" ) ) ; 
rssDescription = fnXMLencode ( fnText2HTM ( rssDescription ) ) ; 
rssCategory = fnXMLencode ( rssCategory ) ; 
rssLink = fnXMLencode ( fnValidURL ( "URL" ; rssLink ) ) ; 
rssTitle = fnXMLencode ( rssTitle ) ; 
rssPubDate = fnRSS_DateCode ( rssPubDate ; rssPubDate ) ; 
rssLastPubDate = fnRSS_DateCode ( rssLastPubDate ; rssLastPubDate ) ; 
rssPodcastHeader = If ( not Podcast ; "" ; fnBuildRSSFeedLib ( "iTunesHeader" ) ) ; 

//------------- Create RSS Feed header
Header = Substitute ( fnBuildRSSFeedLib ( "Header" ) & rssPodcastHeader 
	; [ "rssAuthor" ; rssAuthor ]
	; [ "rssCopyright" ; rssCopyright ]
	; [ "rssDescription" ; rssDescription ]
	; [ "rssEditor" ; rssEditor ]
	; [ "rssGenerator" ; rssGenerator ]
	; [ "rssLastPubDate" ; rssLastPubDate ]
	; [ "rssEnclosureLink" ; rssEnclosureLink ]
	; [ "rssLink" ; rssLink ]
	; [ "rssXMLurl" ; rssXMLurl ]
	; [ "rssPubDate" ; rssPubDate ]
	; [ "rssTimeTillLive" ; rssTimeTillLive ]
	; [ "rssTitle" ; rssTitle ]
	; [ "rssWebmaster" ; rssWebmaster ]
	) ; 

//----------- Validate that RSS article is properly formatted
$$RSSerrors = Case ( vLastRun ; $$RSSerrors ; not vFirstRun ; $$RSSerrors ; 
	PatternCount ( Header ; "title>" ) < 2 ; "ERROR in Header Title of " & rssTitle & ¶ & Header ; 
	PatternCount ( Header ; "description>" ) < 2 ; "ERROR in Header Description of " & rssTitle & ¶ & Header ; 
	PatternCount ( Header ; "pubdate>" ) < 2 ; "ERROR in Header Pubdate of " & rssTitle & ¶ & Header ; 
	PatternCount ( Header ; "webMaster>" ) < 2 ; "ERROR in Header WebMaster of " & rssTitle & ¶ & Header ; 
	IsEmpty ( rssTitle ) ; "ERROR in Header Title is missing¶" & Header ; "" ) ; 

$RSSHeader = Case ( not vFirstRun ; $RSSHeader ; not IsEmpty ( $$RSSerrors ) ; "" ; Header ) ; 

//------------- Create RSS Feed article
item = Substitute ( fnBuildRSSFeedLib ( "Body" ) ; 
	; [ "IDLink" ; vIDLink ]
	; [ "rssAuthor" ; rssAuthor ]
	; [ "rssDescription" ; rssDescription ]
	; [ "rssEnclosure" ; rssEnclosure ]
	; [ "rssLink" ; rssLink ]
	; [ "rssPermaLink" ; rssPermaLink ]
	; [ "rssPodcast" ; rssPodcast ]
	; [ "rssPubDate" ; rssPubDate ]
	; [ "rssTitle" ; rssTitle ]
	) ; 

//----------- Validate that RSS article is properly formatted
ItemError = Case ( vFirstRun or vLastRun ; "" ; 
	PatternCount ( item ; "title>" ) < 2 ; "ERROR in item Title of " & rssTitle & ¶ & item ; 
	PatternCount ( item ; "description>" ) < 2 ; "ERROR in item Description of " & rssTitle & ¶ & item ; 
	PatternCount ( item ; "pubdate>" ) < 2 ; "ERROR in item PubDate of " & rssTitle & ¶ & item ; 
	PatternCount ( item ; "author>" ) < 2 ; "ERROR in item Author of " & rssTitle & ¶ & item ; 
	IsEmpty ( rssTitle ) ; "ERROR in item Title is missing¶" & item ; "" ) ; 

$RSSBody = $RSSBody & Case ( vFirstRun or vLastRun ; "" ; not IsEmpty ( ItemError ) ; "" ; item ) ; 

$$RSSerrors = $$RSSerrors & ItemError

] ; 

Case ( IsEmpty ( $RSSHeader ) ; "" ; //"ERROR in RSS Header" ; 
	vFirstRun ; $RSSHeader ; //"Header stored in $RSSHeader = " & rssTitle ; 
	not IsEmpty ( rssTitle ) ; item ; //"Body stored in $RSSBody = " & rssTitle ; 
	$RSSHeader & $RSSBody & "</channel></rss>¶"
)  //end case

)  //end Let



// Source 2012 - http://www.briandunning.com/cf/1399
// by Roger Biel, 2Geckos Consulting, www.2geckos.com

// Custom function Dependencies: fnText2HTM, fnRSS_DateCode, fnMimeType, fnJulian, fnXMLencode, fnValidURL, fnBuildRSSFeedLib, fnValidEmail (used by fnValidURL)
// Global Variables: $$Domain, $$RSSerrors
// Could not find Text2HTM function on source site.  -rcaldwell 5/2015

// DESCRIPTION
// There are many ways to publish RSS Feeds but this is the simple way.
// Enter the information and a valid RSS feed will be generated.
// The first time the function is exicuted it creates the RSS Header, 
//    then with a script loop add the information for each feed item, 
//    when complete execute the funtion with no parameters to add the footer to the RSS Feed.
// I use CNS FTPiT to upload the feed to the web.
// If the default values are referenced then fewer command line options are needed.
// I use this function in any application that needs to report results to others.

fnBuildRSSFeedLib

// fnBuildRSSFeedLib ( SubstitionCode )
Case(
SubstitionCode = "Header" ; 	"<?xml version=\"1.0\" encoding=\"UTF-8\"?>¶<rss xmlns:atom=\"http://www.w3.org/2005/Atom\" version=\"2.0\">¶<channel>¶<title>rssTitle</title>¶<link>rssLink</link>¶<description>rssDescription</description>¶<language>en-us</language>¶<docs>http://blogs.law.harvard.edu/tech/rss</docs>¶<generator>rssGenerator</generator>¶<managingEditor>rssEditor</managingEditor>¶<pubDate>rssPubDate</pubDate>¶<lastBuildDate>rssLastPubDate</lastBuildDate>¶<webMaster>rssWebmaster</webMaster>¶<category>RSS</category>¶<ttl>rssTimeTillLive</ttl>¶<copyright>rssCopyright</copyright>¶<atom:link href=\"rssXMLurl\" rel=\"self\" type=\"application/rss+xml\" />¶<image>¶<url>rssEnclosureLink</url>¶<title>rssTitle</title>¶<link>rssLink</link>¶<width>100</width>¶</image>¶" ;

SubstitionCode = "Body" ; 	"<item><title>rssTitle</title>¶	<link>rssLink</link>¶	<description>rssDescription</description>¶	<pubDate>rssPubDate</pubDate>¶	<author>rssAuthor</author>¶	<guid isPermaLink=\"rssPermaLink\">IDLink</guid>¶rssEnclosure rssPodcast</item>¶" ;

SubstitionCode = "iTunes" ; 	"<itunes:subtitle>rssCategory</itunes:subtitle>¶<itunes:summary>rssCategory</itunes:summary>¶<itunes:duration>rssEnclosureLength</itunes:duration>¶<itunes:keywords>rssCategory</itunes:keywords>¶<itunes:author>rssAuthor</itunes:author>¶<enclosure>¶	<xsl:attribute name=\"url\">rssEnclosureLink</xsl:attribute>¶	<xsl:attribute name=\"length\">rssEnclosureLength</xsl:attribute>¶	<xsl:attribute name=\"type\">rssEnclosureType</xsl:attribute>¶</enclosure>¶";

SubstitionCode = "iTunesHeader" ; "<itunes:category>rssCategory</itunes:category>¶<itunes:subtitle>rssTitle</itunes:subtitle>¶<itunes:owner>¶	<itunes:name>rssContact</itunes:name>¶	<itunes:email>rssEditor</itunes:email>¶</itunes:owner>¶<itunes:author>rssAuthor</itunes:author>¶<itunes:summary>rssCategory</itunes:summary>¶<itunes:image>¶	<xsl:attribute name=\"href\">rssEnclosureLink</xsl:attribute>¶</itunes:image>";

SubstitionCode = "enclosure" ; "	<enclosure url=\"rssEnclosureLink\" type=\"rssEnclosureType\" length=\"rssEnclosureLength\" />¶" ;

"")


// XML coding used by fnBuildRSSFeed function.
// Source 2012 - http://www.briandunning.com/cf/1400

fnBuildRSSFeed_orig

// ------ ORIGINAL CODE ------
// Pulled directly from the briandunning.com website.
// Source 2015 - http://www.briandunning.com/cf/1399
// The list of variables and function dependencies seems to be incomplete.



// BuildRSSFeed( rssTitle ; rssPubDate ; IDLink ; rssLink ; rssDescription ; rssXMLurl ; rssEnclosure )
// Custom function Dependencies: Text2HTM ; rssDateCode ; MimeType ; Julian
// Variables: rssTitle ; rssPubDate ; IDLink ; rssLink ; rssDescription ; RSSURL ; rssEnclosure ; rssCategory 
//-------------------------Sample Data---------------------------------------------------

Let( [FirstRun = IsEmpty( $RSSHeader ) ; 
LastRun = IsEmpty( rssTitle & rssPubDate & IDLink & rssLink & rssDescription & rssXMLurl & rssEnclosure ) ; 

//--------------------Confiure default values--------------------------------------------
rssAuthor = "info@2geckos.com( 2Geckos Consulting, LLC )" ; 
rssEditor = rssAuthor ; 
rssLastPubDate = rssPubDate ; 
rssCopyright = "http://www.webwizdirect.com/ww2g/copyright.html" ; 
rssWebmaster = "info@2geckos.com( 2Geckos Consulting, LLC )" ; 
rssContact = "Roger Biel" ; 
rssTimeTillLive = 1440 ; 
rssCategory = "Website Maintenance" ; 
Podcast = 0 ; 
rssLink = If( IsEmpty( rssLink ) ; "http://www." & $$Domain ; rssLink ) ; 

//--------------Format initalize, and Verify Data----------------------------------------
//---------------------------------------------------------------------------------------
rssEnclosureLink = If( IsEmpty( rssEnclosure ) ; "http://www.webwizdirect.com/images/2Geckos_logo.26.png" ; ValidURL( "URL" ; rssEnclosure ) ) ; 
rssGenerator = "WebWizDirect( www.webwizdirect.com )" ; 
//---------------------------------------------------------------------------------------
IDLink = If( IsEmpty( IDLink ) ; "IDran" & Julian( rssPubDate ) ; IDLink ) ; 
rssPermaLink = If( IsEmpty( ValidURL( "Domain" ; IDLink ) ) ; "false" ; "true" ) ; 
rssEditor = If( PatternCount( rssEditor ; "@" )<1 ; rssWebmaster ; rssEditor ) ; 
$$RSSerrors = If( FirstRun ; "" ; $$RSSerrors ) ; //reset errors log
rssEditor = Substitute( rssEditor ; ["<" ; "( "] ; [">" ; " )"] ) ; 
rssEnclosureLength = 1000 ; 
rssEnclosureType = MimeType( rssEnclosureLink ; "text" ) ; 
rssEnclosure = If( IsEmpty( rssEnclosureLink ) ; "" ; Substitute( BuildRSSFeedLib( "enclosure" ) ; 
	["rssEnclosureLength" ; rssEnclosureLength] ; ["rssEnclosureType" ; rssEnclosureType] ; ["rssEnclosureLink" ; rssEnclosureLink] ) ) ; 
rssPodcast = If( not Podcast ; "" ; BuildRSSFeedLib( "iTunes" ) ) ; 
rssDescription = XMLencode( Text2HTM( rssDescription ) ) ; 
rssCategory = XMLencode( rssCategory ) ; 
rssLink = XMLencode( ValidURL( "URL" ; rssLink ) ) ; 
rssTitle = XMLencode( rssTitle ) ; 
rssPubDate = rssDateCode( rssPubDate ; rssPubDate ) ; 
rssLastPubDate = rssDateCode( rssLastPubDate ; rssLastPubDate ) ; 
rssPodcastHeader = If( not Podcast ; "" ; BuildRSSFeedLib( "iTunesHeader" ) ) ; 

Header = Substitute( BuildRSSFeedLib( "Header" )& rssPodcastHeader ; //---------Create RSS Feed Header----------
	["rssAuthor" ; rssAuthor] ; ["rssCopyright" ; rssCopyright] ; ["rssDescription" ; rssDescription] ; ["rssEditor" ; rssEditor] ; 
	["rssGenerator" ; rssGenerator] ; ["rssLastPubDate" ; rssLastPubDate] ; ["rssEnclosureLink" ; rssEnclosureLink] ; 
	["rssLink" ; rssLink] ; ["rssXMLurl" ; rssXMLurl] ; ["rssPubDate" ; rssPubDate] ; ["rssTimeTillLive" ; rssTimeTillLive] ; ["rssTitle" ; rssTitle] ; 
	["rssWebmaster" ; rssWebmaster] ) ; 
$$RSSerrors = Case( LastRun ; $$RSSerrors ; not FirstRun ; $$RSSerrors ; 
	//-----------Validate that RSS article is properly formatted-------------------------
	PatternCount( Header ; "title>" )<2 ; "ERROR in Header Title of " & rssTitle & ¶ & Header ; 
	PatternCount( Header ; "description>" )<2 ; "ERROR in Header Description of " & rssTitle & ¶ & Header ; 
	PatternCount( Header ; "pubdate>" )<2 ; "ERROR in Header Pubdate of " & rssTitle & ¶ & Header ; 
	PatternCount( Header ; "webMaster>" )<2 ; "ERROR in Header WebMaster of " & rssTitle & ¶ & Header ; 
	IsEmpty( rssTitle ) ; "ERROR in Header Title is missing¶" & Header ; "" ) ; 

$RSSHeader = Case( not FirstRun ; $RSSHeader ; not IsEmpty( $$RSSerrors ) ; "" ; Header ) ; 

item = Substitute( BuildRSSFeedLib( "Body" ) ; //-------------Create RSS Feed Article-----------------
	["IDLink" ; IDLink] ; ["rssAuthor" ; rssAuthor] ; ["rssDescription" ; rssDescription] ; ["rssEnclosure" ; rssEnclosure] ; ["rssLink" ; rssLink] ; 
	["rssPermaLink" ; rssPermaLink] ; ["rssPodcast" ; rssPodcast] ; ["rssPubDate" ; rssPubDate] ; ["rssTitle" ; rssTitle] ) ; 
ItemError = Case( FirstRun or LastRun ; "" ; //---------Validate that RSS article is properly formatted----------------
	PatternCount( item ; "title>" )<2 ; "ERROR in item Title of " & rssTitle & ¶ & item ; 
	PatternCount( item ; "description>" )<2 ; "ERROR in item Description of " & rssTitle & ¶ & item ; 
	PatternCount( item ; "pubdate>" )<2 ; "ERROR in item PubDate of " & rssTitle & ¶ & item ; 
	PatternCount( item ; "author>" )<2 ; "ERROR in item Author of " & rssTitle & ¶ & item ; 
	IsEmpty( rssTitle ) ; "ERROR in item Title is missing¶" & item ; "" ) ; 

$RSSBody = $RSSBody & Case( FirstRun or LastRun ; "" ; not IsEmpty( ItemError ) ; "" ; item ) ; 
$$RSSerrors = $$RSSerrors & ItemError] ; 

Case( IsEmpty( $RSSHeader ) ; "" ; //"ERROR in RSS Header" ; 
	FirstRun ; $RSSHeader ; //"Header stored in $RSSHeader = " & rssTitle ; 
	not IsEmpty( rssTitle ) ; item ; //"Body stored in $RSSBody = " & rssTitle ; 
	$RSSHeader & $RSSBody & "</channel></rss>¶" )
)

fnCleanCheck_field

// fnCleanCheck_field ( fieldname )
Let ( [ 
vFieldName = fieldname ; 
vText = GetField ( fieldname )
] ; 

If ( PatternCount ( vText ; Char(0) ) ; ¶ & vFieldName & " - " & PatternCount ( vText ; Char(0) ) & " char(0) null character" ) & 
If ( PatternCount ( vText ; Char(1) ) ; ¶ & vFieldName & " - " & PatternCount ( vText ; Char(1) ) & " char(1) start of header" ) & 
If ( PatternCount ( vText ; Char(2) ) ; ¶ & vFieldName & " - " & PatternCount ( vText ; Char(2) ) & " char(2) start of text" ) & 
If ( PatternCount ( vText ; Char(3) ) ; ¶ & vFieldName & " - " & PatternCount ( vText ; Char(3) ) & " char(3) end of text" ) & 
If ( PatternCount ( vText ; Char(4) ) ; ¶ & vFieldName & " - " & PatternCount ( vText ; Char(4) ) & " char(4) end of transmission" ) & 
If ( PatternCount ( vText ; Char(5) ) ; ¶ & vFieldName & " - " & PatternCount ( vText ; Char(5) ) & " char(5) enquiry" ) & 
If ( PatternCount ( vText ; Char(6) ) ; ¶ & vFieldName & " - " & PatternCount ( vText ; Char(6) ) & " char(6) acknowledgment" ) & 
If ( PatternCount ( vText ; Char(7) ) ; ¶ & vFieldName & " - " & PatternCount ( vText ; Char(7) ) & " char(7) bell" ) & 
If ( PatternCount ( vText ; Char(8) ) ; ¶ & vFieldName & " - " & PatternCount ( vText ; Char(8) ) & " char(8) backspace" ) & 
If ( PatternCount ( vText ; Char(9) ) ; ¶ & vFieldName & " - " & PatternCount ( vText ; Char(9) ) & " char(9) horizontal tab" ) & 
If ( PatternCount ( vText ; Char(10) ) ; ¶ & vFieldName & " - " & PatternCount ( vText ; Char(10) ) & " char(10) line feed (LF)" ) & 
If ( PatternCount ( vText ; Char(11) ) ; ¶ & vFieldName & " - " & PatternCount ( vText ; Char(11) ) & " char(11) vertical tab" ) & 
// Vertical tabs (11) replace in-field returns when exporting FileMaker data to tab or comma delimited files.
If ( PatternCount ( vText ; Char(12) ) ; ¶ & vFieldName & " - " & PatternCount ( vText ; Char(12) ) & " char(12) form feed" ) & 
// Char(13) carriage returns (CR) are fine.
If ( PatternCount ( vText ; Char(14) ) ; ¶ & vFieldName & " - " & PatternCount ( vText ; Char(14) ) & " char(14) shift out" ) & 
If ( PatternCount ( vText ; Char(15) ) ; ¶ & vFieldName & " - " & PatternCount ( vText ; Char(15) ) & " char(15) shift in" ) & 
If ( PatternCount ( vText ; Char(16) ) ; ¶ & vFieldName & " - " & PatternCount ( vText ; Char(16) ) & " char(16) data link escape" ) & 
If ( PatternCount ( vText ; Char(17) ) ; ¶ & vFieldName & " - " & PatternCount ( vText ; Char(17) ) & " char(17) device control 1" ) & 
If ( PatternCount ( vText ; Char(18) ) ; ¶ & vFieldName & " - " & PatternCount ( vText ; Char(18) ) & " char(18) device control 2" ) & 
If ( PatternCount ( vText ; Char(19) ) ; ¶ & vFieldName & " - " & PatternCount ( vText ; Char(19) ) & " char(19) device control 3" ) & 
If ( PatternCount ( vText ; Char(20) ) ; ¶ & vFieldName & " - " & PatternCount ( vText ; Char(20) ) & " char(20) device control 4" ) & 
If ( PatternCount ( vText ; Char(21) ) ; ¶ & vFieldName & " - " & PatternCount ( vText ; Char(21) ) & " char(21) negative acknowledgment" ) & 
If ( PatternCount ( vText ; Char(22) ) ; ¶ & vFieldName & " - " & PatternCount ( vText ; Char(22) ) & " char(22) synchronous idle" ) & 
If ( PatternCount ( vText ; Char(23) ) ; ¶ & vFieldName & " - " & PatternCount ( vText ; Char(23) ) & " char(23) end of transmission block" ) & 
If ( PatternCount ( vText ; Char(24) ) ; ¶ & vFieldName & " - " & PatternCount ( vText ; Char(24) ) & " char(24) cancel" ) & 
If ( PatternCount ( vText ; Char(25) ) ; ¶ & vFieldName & " - " & PatternCount ( vText ; Char(25) ) & " char(25) end of medium" ) & 
If ( PatternCount ( vText ; Char(26) ) ; ¶ & vFieldName & " - " & PatternCount ( vText ; Char(26) ) & " char(26) substitute" ) & 
If ( PatternCount ( vText ; Char(27) ) ; ¶ & vFieldName & " - " & PatternCount ( vText ; Char(27) ) & " char(27) escape" ) & 
If ( PatternCount ( vText ; Char(28) ) ; ¶ & vFieldName & " - " & PatternCount ( vText ; Char(28) ) & " char(28) file separator" ) & 
// Group separator (29) separates repeating field values on export & import.
If ( PatternCount ( vText ; Char(30) ) ; ¶ & vFieldName & " - " & PatternCount ( vText ; Char(30) ) & " char(30) record separator" ) & 
If ( PatternCount ( vText ; Char(31) ) ; ¶ & vFieldName & " - " & PatternCount ( vText ; Char(31) ) & " char(31) unit separator" ) & 

If ( PatternCount ( vText ; Char(127) ) ; ¶ & vFieldName & " - " & PatternCount ( vText ; Char(127) ) & " delete" ) & 
If ( PatternCount ( vText ; Char(160) ) ; ¶ & vFieldName & " - " & PatternCount ( vText ; Char(160) ) & " no-break space" ) & 

If ( PatternCount ( vText ; Char(8206) ) ; ¶ & vFieldName & " - " & PatternCount ( vText ; Char(8206) ) & " char(8206) left-to-right mark" ) & 
If ( PatternCount ( vText ; Char(8211) ) ; ¶ & vFieldName & " - " & PatternCount ( vText ; Char(8211) ) & " char(8211) en dash" ) & 
If ( PatternCount ( vText ; Char(8212) ) ; ¶ & vFieldName & " - " & PatternCount ( vText ; Char(8212) ) & " char(8212) em dash" ) & 
If ( PatternCount ( vText ; Char(8216) ) ; ¶ & vFieldName & " - " & PatternCount ( vText ; Char(8216) ) & " char(8216) left single quotation mark" ) & 
If ( PatternCount ( vText ; Char(8217) ) ; ¶ & vFieldName & " - " & PatternCount ( vText ; Char(8217) ) & " char(8217) right single quotation mark" ) & 
If ( PatternCount ( vText ; Char(8218) ) ; ¶ & vFieldName & " - " & PatternCount ( vText ; Char(8218) ) & " char(8218) single low-9 quotation mark" ) & 
If ( PatternCount ( vText ; Char(8219) ) ; ¶ & vFieldName & " - " & PatternCount ( vText ; Char(8219) ) & " char(8219) single high-reversed-9 quotation mark" ) & 
If ( PatternCount ( vText ; Char(8220) ) ; ¶ & vFieldName & " - " & PatternCount ( vText ; Char(8220) ) & " char(8220) left double quotation mark" ) & 
If ( PatternCount ( vText ; Char(8221) ) ; ¶ & vFieldName & " - " & PatternCount ( vText ; Char(8221) ) & " char(8221) right double quotation mark" ) & 
If ( PatternCount ( vText ; Char(8232) ) ; ¶ & vFieldName & " - " & PatternCount ( vText ; Char(8232) ) & " char(8232) line separator (change to return)" ) & 
If ( PatternCount ( vText ; Char(8236) ) ; ¶ & vFieldName & " - " & PatternCount ( vText ; Char(8236) ) & " char(8236) pop directional formatting from Google Maps" ) & 
If ( PatternCount ( vText ; Char(8239) ) ; ¶ & vFieldName & " - " & PatternCount ( vText ; Char(8239) ) & " char(8239) narrow no-break space" ) & 

If ( PatternCount ( vText ; Char(65533) ) ; ¶ & vFieldName & " - " & PatternCount ( vText ; Char(65533) ) & " char(65533) replacement character (used when website does not recognize a character)" )

)  //end Let


// Counts gremlin characters in text.  -rcaldwell 3/2014

fnCleanCheck_text

// fnCleanCheck_text ( text )
Let ( [ vText = text ] ; 

If ( PatternCount ( vText ; Char(0) ) ; ¶ & PatternCount ( vText ; Char(0) ) & " char(0) null character at " & Position ( vText ; Char(0) ; 1 ; 1 ) ) & 
If ( PatternCount ( vText ; Char(1) ) ; ¶ & PatternCount ( vText ; Char(1) ) & " char(1) start of header at " & Position ( vText ; Char(1) ; 1 ; 1 ) ) & 
If ( PatternCount ( vText ; Char(2) ) ; ¶ & PatternCount ( vText ; Char(2) ) & " char(2) start of text at " & Position ( vText ; Char(2) ; 1 ; 1 ) ) & 
If ( PatternCount ( vText ; Char(3) ) ; ¶ & PatternCount ( vText ; Char(3) ) & " char(3) end of text at " & Position ( vText ; Char(3) ; 1 ; 1 ) ) & 
If ( PatternCount ( vText ; Char(4) ) ; ¶ & PatternCount ( vText ; Char(4) ) & " char(4) end of transmission at " & Position ( vText ; Char(4) ; 1 ; 1 ) ) & 
If ( PatternCount ( vText ; Char(5) ) ; ¶ & PatternCount ( vText ; Char(5) ) & " char(5) enquiry at " & Position ( vText ; Char(5) ; 1 ; 1 ) ) & 
If ( PatternCount ( vText ; Char(6) ) ; ¶ & PatternCount ( vText ; Char(6) ) & " char(6) acknowledgment at " & Position ( vText ; Char(6) ; 1 ; 1 ) ) & 
If ( PatternCount ( vText ; Char(7) ) ; ¶ & PatternCount ( vText ; Char(7) ) & " char(7) bell at " & Position ( vText ; Char(7) ; 1 ; 1 ) ) & 
If ( PatternCount ( vText ; Char(8) ) ; ¶ & PatternCount ( vText ; Char(8) ) & " char(8) backspace at " & Position ( vText ; Char(8) ; 1 ; 1 ) ) & 
If ( PatternCount ( vText ; Char(9) ) ; ¶ & PatternCount ( vText ; Char(9) ) & " char(9) horizontal tab at " & Position ( vText ; Char(9) ; 1 ; 1 ) ) & 
If ( PatternCount ( vText ; Char(10) ) ; ¶ & PatternCount ( vText ; Char(10) ) & " char(10) line feed (LF) at " & Position ( vText ; Char(10) ; 1 ; 1 ) ) & 
If ( PatternCount ( vText ; Char(11) ) ; ¶ & PatternCount ( vText ; Char(11) ) & " char(11) vertical tab at " & Position ( vText ; Char(11) ; 1 ; 1 ) ) & 
// Vertical tabs (11) replace in-field returns when exporting FileMaker data to tab or comma delimited files.
If ( PatternCount ( vText ; Char(12) ) ; ¶ & PatternCount ( vText ; Char(12) ) & " char(12) form feed at " & Position ( vText ; Char(12) ; 1 ; 1 ) ) & 
// Char(13) carriage returns (CR) are fine.
If ( PatternCount ( vText ; Char(14) ) ; ¶ & PatternCount ( vText ; Char(14) ) & " char(14) shift out at " & Position ( vText ; Char(14) ; 1 ; 1 ) ) & 
If ( PatternCount ( vText ; Char(15) ) ; ¶ & PatternCount ( vText ; Char(15) ) & " char(15) shift in at " & Position ( vText ; Char(15) ; 1 ; 1 ) ) & 
If ( PatternCount ( vText ; Char(16) ) ; ¶ & PatternCount ( vText ; Char(16) ) & " char(16) data link escape at " & Position ( vText ; Char(16) ; 1 ; 1 ) ) & 
If ( PatternCount ( vText ; Char(17) ) ; ¶ & PatternCount ( vText ; Char(17) ) & " char(17) device control 1 at " & Position ( vText ; Char(17) ; 1 ; 1 ) ) & 
If ( PatternCount ( vText ; Char(18) ) ; ¶ & PatternCount ( vText ; Char(18) ) & " char(18) device control 2 at " & Position ( vText ; Char(18) ; 1 ; 1 ) ) & 
If ( PatternCount ( vText ; Char(19) ) ; ¶ & PatternCount ( vText ; Char(19) ) & " char(19) device control 3 at " & Position ( vText ; Char(19) ; 1 ; 1 ) ) & 
If ( PatternCount ( vText ; Char(20) ) ; ¶ & PatternCount ( vText ; Char(20) ) & " char(20) device control 4 at " & Position ( vText ; Char(20) ; 1 ; 1 ) ) & 
If ( PatternCount ( vText ; Char(21) ) ; ¶ & PatternCount ( vText ; Char(21) ) & " char(21) negative acknowledgment at " & Position ( vText ; Char(21) ; 1 ; 1 ) ) & 
If ( PatternCount ( vText ; Char(22) ) ; ¶ & PatternCount ( vText ; Char(22) ) & " char(22) synchronous idle at " & Position ( vText ; Char(22) ; 1 ; 1 ) ) & 
If ( PatternCount ( vText ; Char(23) ) ; ¶ & PatternCount ( vText ; Char(23) ) & " char(23) end of transmission block at " & Position ( vText ; Char(23) ; 1 ; 1 ) ) & 
If ( PatternCount ( vText ; Char(24) ) ; ¶ & PatternCount ( vText ; Char(24) ) & " char(24) cancel at " & Position ( vText ; Char(24) ; 1 ; 1 ) ) & 
If ( PatternCount ( vText ; Char(25) ) ; ¶ & PatternCount ( vText ; Char(25) ) & " char(25) end of medium at " & Position ( vText ; Char(25) ; 1 ; 1 ) ) & 
If ( PatternCount ( vText ; Char(26) ) ; ¶ & PatternCount ( vText ; Char(26) ) & " char(26) substitute at " & Position ( vText ; Char(26) ; 1 ; 1 ) ) & 
If ( PatternCount ( vText ; Char(27) ) ; ¶ & PatternCount ( vText ; Char(27) ) & " char(27) escape at " & Position ( vText ; Char(27) ; 1 ; 1 ) ) & 
If ( PatternCount ( vText ; Char(28) ) ; ¶ & PatternCount ( vText ; Char(28) ) & " char(28) file separator at " & Position ( vText ; Char(28) ; 1 ; 1 ) ) & 
// Group separator (29) separates repeating field values on export & import.
If ( PatternCount ( vText ; Char(30) ) ; ¶ & PatternCount ( vText ; Char(30) ) & " char(30) record separator at " & Position ( vText ; Char(30) ; 1 ; 1 ) ) & 
If ( PatternCount ( vText ; Char(31) ) ; ¶ & PatternCount ( vText ; Char(31) ) & " char(31) unit separator at " & Position ( vText ; Char(31) ; 1 ; 1 ) ) & 

If ( PatternCount ( vText ; Char(127) ) ; ¶ & PatternCount ( vText ; Char(127) ) & " delete at " & Position ( vText ; Char(127) ; 1 ; 1 ) ) & 
If ( PatternCount ( vText ; Char(160) ) ; ¶ & PatternCount ( vText ; Char(160) ) & " no-break space at " & Position ( vText ; Char(160) ; 1 ; 1 ) ) & 

If ( PatternCount ( vText ; Char(8206) ) ; ¶ & PatternCount ( vText ; Char(8206) ) & " char(8206) left-to-right mark at " & Position ( vText ; Char(8206) ; 1 ; 1 ) ) & 
If ( PatternCount ( vText ; Char(8211) ) ; ¶ & PatternCount ( vText ; Char(8211) ) & " char(8211) en dash at " & Position ( vText ; Char(8211) ; 1 ; 1 ) ) & 
If ( PatternCount ( vText ; Char(8212) ) ; ¶ & PatternCount ( vText ; Char(8212) ) & " char(8212) em dash at " & Position ( vText ; Char(8212) ; 1 ; 1 ) ) & 
If ( PatternCount ( vText ; Char(8216) ) ; ¶ & PatternCount ( vText ; Char(8216) ) & " char(8216) left single quotation mark at " & Position ( vText ; Char(8216) ; 1 ; 1 ) ) & 
If ( PatternCount ( vText ; Char(8217) ) ; ¶ & PatternCount ( vText ; Char(8217) ) & " char(8217) right single quotation mark at " & Position ( vText ; Char(8217) ; 1 ; 1 ) ) & 
If ( PatternCount ( vText ; Char(8218) ) ; ¶ & PatternCount ( vText ; Char(8218) ) & " char(8218) single low-9 quotation mark at " & Position ( vText ; Char(8218) ; 1 ; 1 ) ) & 
If ( PatternCount ( vText ; Char(8219) ) ; ¶ & PatternCount ( vText ; Char(8219) ) & " char(8219) single high-reversed-9 quotation mark at " & Position ( vText ; Char(8219) ; 1 ; 1 ) ) & 
If ( PatternCount ( vText ; Char(8220) ) ; ¶ & PatternCount ( vText ; Char(8220) ) & " char(8220) left double quotation mark at " & Position ( vText ; Char(8220) ; 1 ; 1 ) ) & 
If ( PatternCount ( vText ; Char(8221) ) ; ¶ & PatternCount ( vText ; Char(8221) ) & " char(8221) right double quotation mark at " & Position ( vText ; Char(8221) ; 1 ; 1 ) ) & 
If ( PatternCount ( vText ; Char(8232) ) ; ¶ & PatternCount ( vText ; Char(8232) ) & " char(8232) line separator (change to return) at " & Position ( vText ; Char(8232) ; 1 ; 1 ) ) & 
If ( PatternCount ( vText ; Char(8236) ) ; ¶ & PatternCount ( vText ; Char(8236) ) & " char(8236) pop directional formatting from Google Maps at " & Position ( vText ; Char(8236) ; 1 ; 1 ) ) & 
If ( PatternCount ( vText ; Char(8239) ) ; ¶ & PatternCount ( vText ; Char(8239) ) & " char(8239) narrow no-break space at " & Position ( vText ; Char(8239) ; 1 ; 1 ) ) & 

If ( PatternCount ( vText ; Char(65533) ) ; ¶ & PatternCount ( vText ; Char(65533) ) & " char(65533) replacement character (used when website does not recognize a character) at " & Position ( vText ; Char(65533) ; 1 ; 1 ) )

)  //end Let


// Counts gremlin characters in text.  -rcaldwell 3/2014

fnCleanFilter

// fnCleanFilter ( text )
Filter ( 
Substitute ( TextFormatRemove ( text ) ; 
   [ Char(9) ; "    "] ; 
   [ Char(10) ; ¶ ] ; 
   [ Char(8216) ; "'"] ; 
   [ Char(8217) ; "'"] ; 
   [ Char(8220) ; "\""] ; 
   [ Char(8221) ; "\""] 
) //end substitute

; "0123456789 abcdefghijklmnopqrstuvwxyz ABCDEFGHIJKLMNOPQRSTUVWXYZ -_,:;<>.!@#$%^&*=+?¶•\|/'\"[]{}()"

)  //end filter


// Strips out all but the most basic characters from text.
// Substitute tabs, line feeds, and curly quotes first, then filter for valid characters.  -rcaldwell 1/2014
// fnFilter is stronger than fnCleanText, but it may eliminate too many characters from the text.

fnCleanText

// fnCleanText ( text )  v2
TextFormatRemove ( Trim ( Substitute ( text 
   ; [ Base64Decode("AA==") ; "" ]   // null character
   ; [ Char(1) ; "" ]   // start of header
   ; [ Char(2) ; "" ]   // start of text
   ; [ Char(3) ; "" ]   // end of text
   ; [ Char(4) ; "" ]   // end of transmission
   ; [ Char(5) ; "" ]   // enquiry
   ; [ Char(6) ; "" ]   // acknowledgment
   ; [ Char(7) ; "" ]   // bell
   ; [ Char(8) ; "" ]   // backspace
   ; [ Char(9) ; " " ]   // horizontal tab (change to space)
   ; [ Char(13) & Char(10) ; ¶ ]   // Windows CRLF carriage return & line feed (change to return)
   ; [ Char(10) ; ¶ ]   // line feed (LF) change to return
   ; [ Char(11) ; " " ]   // vertical tab change to space
   // Vertical tabs (11) replace in-field returns when exporting FileMaker data to tab or comma delimited files.
   ; [ Char(12) ; "" ]   // form feed
   // Char(13) carriage returns (CR) are fine
   ; [ Char(14) ; "" ]   // shift out
   ; [ Char(15) ; "" ]   // shift in
   ; [ Char(16) ; "" ]   // data link escape
   ; [ Char(17) ; "" ]   // device control 1
   ; [ Char(18) ; "" ]   // device control 2
   ; [ Char(19) ; "" ]   // device control 3
   ; [ Char(20) ; "" ]   // device control 4
   ; [ Char(21) ; "" ]   // negative acknowledgment
   ; [ Char(22) ; "" ]   // synchronous idle
   ; [ Char(23) ; "" ]   // end of transmission block
   ; [ Char(24) ; "" ]   // cancel
   ; [ Char(25) ; "" ]   // end of medium
   ; [ Char(26) ; "" ]   // substitute
   ; [ Char(27) ; "" ]   // escape
   ; [ Char(28) ; "" ]   // file separator
   // Char(29) group separator, splits repeating field values on FileMaker export & import
   ; [ Char(30) ; "" ]   // record separator
   ; [ Char(31) ; "" ]   // unit separator

   ; [ Char(127) ; " " ]   // delete
   ; [ Char(160) ; " " ]   // no-break space

   // General Punctuation - http://www.utf8-chartable.de/unicode-utf8-table.pl
   ; [ Char(8206) ; "" ]    // left-to-right mark
   ; [ Char(8211) ; "-" ]   // en dash
   ; [ Char(8212) ; "-" ]   // em dash
   ; [ Char(8216) ; "'" ]   // left single quotation mark
   ; [ Char(8217) ; "'" ]   // right single quotation mark
   ; [ Char(8218) ; "\"" ]  // single low-9 quotation mark
   ; [ Char(8219) ; "'" ]   // single high-reversed-9 quotation mark
   ; [ Char(8220) ; "\"" ]  // left double quotation mark
   ; [ Char(8221) ; "\"" ]  // right double quotation mark
   ; [ Char(8232) ; ¶ ]     // line separator (change to return)
   ; [ Char(8236) ; "" ]    // pop directional formatting from Google Maps 4/2/2013
   ; [ Char(8239) ; "" ]    // narrow no-break space 3/2016

   ; [ Char(65279) ; "" ]   // zero width no-break space or byte order mark (bom)
   ; [ Char(65532) ; "" ]   // object replacement character, found in HTML formatted emails (6/2010)
   ; [ Char(65533) ; "" ]   // <?> replacement character is used when website does not recognize a character

)  //end substitute
)  //end trim
)  //end format remove


// Strips out invisible and control characters so output doesn't damage next system with 'end of transmission' commands.  -rcaldwell 9/2012
// Added dash and quote changes.  -rcaldwell 7/2013
// Added all remaining control character codes (31 and below), now change CRLF to CR and remove delete (127).  -rcaldwell 5/2014
// FM14-16+ lost the ability to evaluate null Char(0), so using base64 trick instead.  -rcaldwell 11/2017
// Source - http://en.wikipedia.org/wiki/ASCII
// Source 2 - http://www.utf8-chartable.de/unicode-utf8-table.pl?unicodeinhtml=dec

fnConnectionType

// fnConnectionType ( text ) -- v2
Let ( [ 
vDigits = Filter ( text ; "1234567890" ) 
] ; 

Case ( 
   Left ( text ; 1 ) = "@" ; "Twitter" ; 
   PatternCount ( text ; "://" ) or 
      ( ( Left ( text ; 3 ) = "www" or 
      Right ( text ; 4 ) = ".com" or 
      Right ( text ; 4 ) = ".org" ) and 
      PatternCount ( text ; "@" ) = 0 ) ; "Web" ; 
   // Email comes after web so web credentials read as website.
   // eg.  http://admin:pass@example.com
   PatternCount ( text ; "@" ) ; "Email" ; 
   IsValid ( GetAsDate ( text ) ) and GetAsDate ( text ) ≠ "" ; "Date" ; 
   Length ( vDigits ) = 9 and 
      Length ( text ) < Length ( vDigits ) * 2 ; "TeamViewer" ; 
   ( Length ( vDigits ) = 16 or Length ( vDigits ) = 15 ) and 
      Length ( text ) < Length ( vDigits ) * 2 ; "CC" ; 
   Length ( vDigits ) < 15 and Length ( vDigits ) > 6 and 
      Length ( text ) < Length ( vDigits ) * 2 ; "Phone" ; 
)  //end case

)  //end Let


// Reads text to determine how to engage the connection. -rcaldwell 10/2013
// Moved Facebook and Linkedin types to the label field calc.
// Added check to make sure there are more numbers than other characters for phones, CC & TV.
// Moved Email type after Web type to allow @ in URLs like Flickr.  -rcaldwell 7/2015

fnConvertBytes

// fnConvertBytes ( number )

Let ( vSize = Get ( FileSize ) ; 

Case ( 
vSize < 1024 ; Round ( vSize ; 0 ) & " B" ; 
vSize < 1024^2 ; Round ( vSize / 1024 ; 0 ) & " KB" ; 
vSize < 1024^3 ; Round ( vSize / 1024^2 ; 0 ) & " MB" ; 
vSize < 1024^4 ; Round ( vSize / 1024^3 ; 0 ) & " GB" ; 
Round ( vSize ; 0 ) & " B" )  //end case

)  //end Let


)  //end Let


// Convert file size or container size (length) from bytes into more readable KB, MB, GB... values.
// bytes/1000 matches Mac OS KB calculation better than bytes/1024.  (2/2016)

fnConvertPathBE

// fnConvertPathBE ( path ; format )

Let ( [ 
vPlatform = Abs ( Get ( SystemPlatform ) ) ; 
vSystemDrive = Left ( Get ( DocumentsPath ) ; Position ( Get ( DocumentsPath ) ; "/" ; 1 ; 2 ) )
] ; 

If ( format = "BE" ; 

Case ( 

// Mac external drive path
vPlatform = 1 and Left ( path ; 5 ) = "file:" ; 
	Substitute ( path ; [ "file:/" ; "/Volumes/" ] ) ; 

// Mac internal drive path
vPlatform = 1 ; 
	Substitute ( path ; [ vSystemDrive ; "/" ] ) ; 

// Win external drive path
vPlatform = 2 and Left ( path ; 5 ) = "file:" ; 
	Substitute ( Right ( path ; Length ( path ) - 6 ) ; [ "/" ; "\\" ] ) ; 

// Win external drive path
vPlatform = 2 ; 
	Substitute ( Right ( path ; Length ( path ) - 1 ) ; [ "/" ; "\\" ] ) ; 

path )  //end case


; // else if not BE


Case ( 

vPlatform = 1 ; 
	Substitute ( If ( Left ( path ; 9 ) ≠ "/Volumes/" ; 
		vSystemDrive ) & path ; [ "//" ; "/" ] ) ; 

vPlatform = 2 ; 
	"/" & Substitute ( path ; [ "\\" ; "/" ] ) ; 

path )  //end case


)  //end if format

)  //end Let


// Sending 'BE' as the format converts a FileMaker path to a BaseElements path.  Otherwise converts a BaseElements path to a FileMaker path.
// Examples:
// FileMaker Pro Mac internal drive path = /Macintosh HD/Users/Rob/Desktop/
// BaseElements Mac internal drive path  = /Users/Rob/Desktop
// FileMaker Pro Win internal drive path = /C:/Users/Rob/Desktop/
// BaseElements Win internal drive path  = C:\Users\Rob\Desktop
// FileMaker Mac external drive path - file:/ExternalDrive/Projects/my_file.txt
// BaseElements Mac external drive path - /Volumes/ExternalDrive/Projects/my_file.txt

// FileMaker 16 no longer supports Get(SystemDrive) when run on server, so have to find drive name a different way.  -rcaldwell 6/2018

fnConvertValue

// fnConvertValue ( sourcelist ; search ; resultlist )

Let ( i = ValueCount ( Left ( sourcelist ; Position ( ¶ & sourcelist & ¶ ; ¶ & search & ¶ ; 1 ; 1 ) ) ) ; 

GetValue ( resultlist ; i )

)  //end Let


// Finds the first occurrence of the search value in the source list and returns the corresponding value from the result list.
// The search is not case-sensitive. Lists should be return-delimited. Useful in combination with fnGetColumn to return different values from the same row.
// Source - http://www.briandunning.com/cf/908

fnDataXML

// fnDataXML ( fieldlist )
Let ( [ 
vFieldName = GetValue ( fieldlist ; 1 ) ; 
vFieldsRemain = RightValues ( fieldlist ; ValueCount ( fieldlist ) - 1 )
] ; 

"<" & vFieldName & ">" & 

Substitute ( GetField ( vFieldName ) 
   ; [ "&" ; "&amp;" ] 
   ; [ "<" ; "&lt;" ] 
   ; [ ">" ; "&gt;" ] 
   ; [ "'" ; "&apos;" ] 
   ; [ "\"" ; "&quot;" ] ) & 

"</" & vFieldName & ">" 

& If ( GetValue ( vFieldsRemain ; 1 ) ≠ "" ; fnDataXML ( vFieldsRemain ) )

)  //end Let


// Takes all field names from 'fieldlist' param and produces XML output for use in Flash.
// Reserved characters in XML.  Source:  http://www.w3schools.com/xml/xml_syntax.asp
// Make sure the ampersand is the first to encode and last to decode.
// -rcaldwell 3/15/2011

fnDateListValid

// fnDateListValid ( text )
Let ( [ 
vStart = "Not IsValid ( GetAsDate ( \"" ; 
vEnd = "\" ) ) + " ; 
vFullString = vStart & 
   Substitute ( TrimAll ( text ; 0 ; 0 ) ; ¶ ; vEnd & vStart )
   & vEnd & 0 ; 
vFullString = Substitute ( vFullString ; "GetAsDate ( \"\" )" ; 1 ) ; 
   // second substitute fixes empty returns
vResult = Evaluate( vFullString )
] ; 

vResult
//&¶& vFullString

)  //end Let



// Verifies that every value in a list is a valid date. Counts the number of invalid values.
// Does this by changing the value list into a big substitute calculation.

fnDateSQL

// fnDateSQL ( timestamp )
Year ( timestamp ) & "-" & 
Right ( 0 & Month ( timestamp ) ; 2 ) & "-" & 
Right ( 0 & Day (timestamp) ; 2 )


// This simple function changes a FileMaker date or timestamp into the format needed for SQL queries.
// eg.  SELECT COUNT("bids") FROM auction WHERE "bid_date" = (DATE '2018-07-31')

fnDateTimeSQL

// fnDateTimeSQL ( DateOrTS )

Let ( vTS = GetAsTimestamp ( DateOrTS ) ; 

If ( vTS = "" or vTS = "?"; "NULL" ; "'" & 
   Year ( vTS ) & "-" & 
   If ( Month ( vTS ) < 10 ; 0 ) & Month ( vTS ) & "-" & 
   If ( Day ( vTS ) < 10 ; 0 ) & Day ( vTS ) & " " & 
   
   If ( Hour ( vTS ) < 10 ; 0 ) & Hour ( vTS ) & ":" & 
   If ( Minute ( vTS ) < 10 ; 0 ) & Minute ( vTS ) & ":" & 
   If ( Seconds ( vTS ) < 10 ; 0 ) & Seconds ( vTS ) & "'" 
)  //end vTS

)  //end Let

// Takes a FileMaker timestamp value and turns it into a SQL datetime value for searching, adding, or updating records in OrgsInfo.  -rcaldwell 4/2011 (updated 3/7/2012 for ECPublic)

fnDateTimeUTC

// fnDateTimeUTC ( DateOrTS )
Let ( [ 
vTS = GetAsTimestamp ( DateOrTS ) ; 
vTZ = Get ( CurrentTimestamp ) - 
   GetAsTimestamp ( Floor ( Get ( CurrentTimeUTCMilliseconds ) / 1000 ) ) ; 
vTZ_hour = GetValue ( Substitute ( vTZ ; ":" ; ¶ ) ; 1 ) ; 
vTZ_minute = GetValue ( Substitute ( vTZ ; ":" ; ¶ ) ; 2 ) ; 
vTZ_final = If ( vTZ_hour < 0 ; "-" ; "+" ) & 
   Right ( 0 & Abs ( vTZ_hour ) ; 2 ) & ":" & 
   Right ( 0 & vTZ_minute ; 2 )
] ; 

If ( vTS = "" or vTS = "?" ; "" ; 

Year ( vTS ) & "-" & 
Right ( 0 & Month ( vTS ) ; 2 ) & "-" & 
Right ( 0 & Day ( vTS ) ; 2 ) & 

If ( GetAsTime ( vTS ) ≠ "12:00 AM" ; "T" & 
   Right ( 0 & Hour ( vTS ) ; 2 ) & ":" & 
   Right ( 0 & Minute ( vTS ) ; 2 ) & ":" & 
   Right ( 0 & Seconds ( vTS ) ; 2 ) & 
   vTZ_final
)  //end if not just date

)  //end if timestamp is blank

)  //end Let


// Converts a date or timestamp into UTC or RFC 3339 format.
// If given just a date, return date with no time data.
// Example:  5/8/2017 2:27:37 PM  ->  2017-05-08T14:27:37-04:00

fnDaylightSaving

// fnDaylightSaving ( year )
Date ( 3 ; 8 ; year )  // earliest possible date
+ Choose ( DayOfWeek ( Date ( 3 ; 1 ; vYear ) ) - 1 ; 
   0 ; 6 ; 5 ; 4 ; 3 ; 2 ; 1 )
&¶& 
Date ( 11 ; 1 ; year )  // earliest possible date
+ Choose ( DayOfWeek ( Date ( 11 ; 1 ; vYear ) ) - 1 ; 
   0 ; 6 ; 5 ; 4 ; 3 ; 2 ; 1 )


// Returns the start and end dates (return separated) for Daylight Saving in the given year.
// Daylight Saving Time in 2018 begins on the second Sunday of March and ends on the first Sunday of November.
// To calculate start date, if March starts on a Sunday (1), and you subtract 1 (0) because Choose starts at zero, then Choose will result in 0 days past the earliest possible date.

fnDefinition

// fnDefinition (~number)

ExecuteSQL ( "SELECT FieldDefinition FROM Definitions WHERE FieldNumber = " & ~number ; "" ; "" )
 
// So the tool tip is, for example, just fnDefinition(3) which shows the field definition associated with the record number 3 in the definitions table.

//~number is the number of the definition you want to call in the tool tip
 
// Prerequisite: (1) Table called Definitions; Field called FieldDefinition &  field called FieldNumber
// FieldNumber has to be unique
// allows admin user to modify definitions
// by Susan Frank 2019

fnExcelHeader

// fnExcelHeader ( title_list )

"	set value of cell 1 of column " & ValueCount ( title_list ) & " to \"" & GetValue ( RightValues ( title_list ; 1 ) ; 1 ) & "\"¶" & 
If ( ValueCount ( title_list ) > 1 ; fnSetExcelHeader ( LeftValues ( title_list ; ValueCount ( title_list ) - 1 ) ) )


// Takes a return-separated list of values and formats it for use in an AppleScript 
// to replace the default column names for a FileMaker Excel export to the names in the list.  -rcaldwell 3/2017
// Exported field list must be fixed since column names is likely a fixed list. 
// FileMaker must automatically open file after export for the AppleScript to work.
// Sample FM script step for AppleScript Event:
// "tell application \"Microsoft Excel\"¶	activate¶" & fnExcelHeader ( $title_list ) & "end tell"

fnExcelText

// fnExcelText ( text ; type )

Case ( 

type = "number" ; 
   if ( GetAsNumber ( text ) ≠ "" ; 
   "<Data ss:Type=\"Number\">" & 
   If ( Length ( GetAsNumber ( text ) ) < 30 ; GetAsNumber ( text ) ; 
      Round ( GetAsNumber ( text ) ; 30 - Length ( Int ( text ) ) ) ) & 
   "</Data>" )
; 
type = "date" or type = "timestamp" ; 
   If ( GetAsTimestamp ( text ) ; 
   "<Data ss:Type=\"DateTime\">" & 
   Year ( text ) & "-" & 
   If ( Month ( text ) < 10 ; 0 ) & Month ( text ) & "-" & 
   If ( Day ( text ) < 10 ; 0 ) & Day ( text ) & 
   "T" & 
   If ( GetAsDate ( text ) = GetAsTimestamp ( text ) ; "00:00:00.000" ; 
      If ( Hour ( text ) < 10 ; 0 ) & Hour ( text ) & ":" & 
      If ( Minute ( text ) < 10 ; 0 ) & Minute ( text ) & ":" & 
      If ( Seconds ( text ) < 10 ; 0 ) & Seconds ( text ) & 
      ".000" ) & 
   "</Data>" )
; 
//type = "text" or type = "" ; 
   "<Data ss:Type=\"String\">" & 
   Substitute ( text ; 
      [ "&" ; "&amp;" ] ; 
      [ "<" ; "&lt;" ] ; 
      [ ">" ; "&gt;" ] ; 
      [ "\"" ; "&quot;" ] ; 
      [ ¶ ; "&#13;" ] 
   ) &  //end substitute
   "</Data>"

)  //end case


// Convert ampersand and other characters to coded version for Excel XML output.  -rcaldwell 1/2014
// Excel can only handle ~31 sig figs for numbers.  -rcaldwell 10/2016

fnExtractDataXML

// fnExtractDataXML ( xml ; attribute ; instance )

Let ( [ 
vLengthXML = Length ( xml ) ; 
vAttribLength = Length ( attribute ) ; 
vOccurrence = If ( IsEmpty ( instance ) ; 1 ; instance )
] ; 

If ( xml = "" or attribute = "" or PatternCount ( xml ; "<" & attribute & ">" ) = 0 ; "" ;

Middle ( xml ;  //text
   Position ( xml ; "<" & attribute & ">" ; 1 ; vOccurrence ) + vAttribLength + 2 ;  //start
   vLengthXML - ( vLengthXML - Position ( xml ; "</" & attribute & ">" ; 1 ; vOccurrence ) ) - 
   ( Position ( xml ; "<" & attribute & ">" ; 1 ; vOccurrence ) + vAttribLength + 2 ) )  //numberOfCharacters

)  //end if

)  //end Let


// Source US-Holidays.fp7 12/2012 - http://fmnexus.com/groups/ws/wiki/89ee2/Legacy_Examples.html
// FM.Nexus plugin by Beezwax transforms the WSDL file into temporary FileMaker functions for SOAP integration.

fnFMErrorCodes

// fnFMErrorCode ( ErrorNum )

// Replaced 23 instances 'mi$$ing' with 'g0ne' and 3 instances of 'nkn0wn with 'ndeterm1ned' to help with key words in DDR. Left the 'U' off the second word to preserve capitalization. This not text is strange to prevent you next replace command from changing the explanation.

"ERROR " & ErrorNum & ": "  & 
Case(
ErrorNum = -1 ; "Undetermined error"; 
// ErrorNum = 0 ; "No error";
ErrorNum = 1 ; "User canceled action";
ErrorNum = 2 ; "Memory error"; 
ErrorNum = 3 ; "Command is unavailable (for example, wrong operating system, wrong mode, etc.)"; 
ErrorNum = 4 ; "Command is undetermined"; 
ErrorNum = 5 ; "Command is invalid (for example, a Set Field script step does not have a calculation specified)"; 
ErrorNum = 6 ; "File is read-only"; 
ErrorNum = 7 ; "Running out of memory"; 
ErrorNum = 8 ; "Empty result"; 
ErrorNum = 9 ; "Insufficient privileges"; 
ErrorNum = 10 ; "Requested data is gone"; 
ErrorNum = 11 ; "Name is not valid"; 
ErrorNum = 12 ; "Name already exists"; 
ErrorNum = 13 ; "File or object is in use"; 
ErrorNum = 14 ; "Out of range"; 
ErrorNum = 15 ; "Can't divide by zero"; 
ErrorNum = 16 ; "Operation failed, request retry (for example, a user query)"; 
ErrorNum = 17 ; "Attempt to convert foreign character set to UTF-16 failed"; 
ErrorNum = 18 ; "Client must provide account information to proceed"; 
ErrorNum = 19 ; "String contains characters other than A-Z, a-z, 0-9 (ASCII)"; 
ErrorNum = 100 ; "File is gone"; 
ErrorNum = 101 ; "Record is gone"; 
ErrorNum = 102 ; "Field is gone"; 
ErrorNum = 103 ; "Relationship is gone"; 
ErrorNum = 104 ; "Script is gone"; 
ErrorNum = 105 ; "Layout is gone"; 
ErrorNum = 106 ; "Table is gone"; 
ErrorNum = 107 ; "Index is gone"; 
ErrorNum = 108 ; "Value list is gone"; 
ErrorNum = 109 ; "Privilege set is gone"; 
ErrorNum = 110 ; "Related tables are gone"; 
ErrorNum = 111 ; "Field repetition is invalid"; 
ErrorNum = 112 ; "Window is gone"; 
ErrorNum = 113 ; "Function is gone"; 
ErrorNum = 114 ; "File reference is gone"; 
ErrorNum = 115 ; "Specified menu set is not present"; 
ErrorNum = 116 ; "Specified layout object is not present"; 
ErrorNum = 117 ; "Specified data source is not present"; 
ErrorNum = 130 ; "Files are damaged or gone and must be reinstalled"; 
ErrorNum = 131 ; "Language pack files are gone (such as template files)"; 
ErrorNum = 200 ; "Record access is denied"; 
ErrorNum = 201 ; "Field cannot be modified"; 
ErrorNum = 202 ; "Field access is denied"; 
ErrorNum = 203 ; "No records in file to print, or password doesn't allow print access"; 
ErrorNum = 204 ; "No access to field(s) in sort order"; 
ErrorNum = 205 ; "User does not have access privileges to create new records; import will overwrite existing data"; 
ErrorNum = 206 ; "User does not have password change privileges, or file is not modifiable"; 
ErrorNum = 207 ; "User does not have sufficient privileges to change database schema, or file is not modifiable"; 
ErrorNum = 208 ; "Password does not contain enough characters"; 
ErrorNum = 209 ; "New password must be different from existing one"; 
ErrorNum = 210 ; "User account is inactiveUser account is inactive"; 
ErrorNum = 211 ; "Password has expired"; 
ErrorNum = 212 ; "Invalid user account and/or password. Please try again"; 
ErrorNum = 213 ; "User account and/or password does not exist"; 
ErrorNum = 214 ; "Too many login attempts"; 
ErrorNum = 215 ; "Administrator privileges cannot be duplicated"; 
ErrorNum = 216 ; "Guest account cannot be duplicated"; 
ErrorNum = 217 ; "User does not have sufficient privileges to modify administrator accountUser does not have sufficient privileges to modify administrator account"; 
ErrorNum = 300 ; "File is locked or in use"; 
ErrorNum = 301 ; "Record is in use by another user"; 
ErrorNum = 302 ; "Table is in use by another user"; 
ErrorNum = 303 ; "Database schema is in use by another user"; 
ErrorNum = 304 ; "Layout is in use by another user"; 
ErrorNum = 306 ; "Record modification ID does not match"; 
ErrorNum = 400 ; "Find criteria are empty"; 
ErrorNum = 401 ; "No records match the request"; 
ErrorNum = 402 ; "Selected field is not a match field for a lookup"; 
ErrorNum = 403 ; "Exceeding maximum record limit for trial version of FileMaker Pro"; 
ErrorNum = 404 ; "Sort order is invalid"; 
ErrorNum = 405 ; "Number of records specified exceeds number of records that can be omitted"; 
ErrorNum = 406 ; "Replace/Reserialize criteria are invalid"; 
ErrorNum = 407 ; "One or both match fields are gone (invalid relationship)"; 
ErrorNum = 408 ; "Specified field has inappropriate data type for this operation"; 
ErrorNum = 409 ; "Import order is invalid"; 
ErrorNum = 410 ; "Export order is invalid"; 
ErrorNum = 412 ; "Wrong version of FileMaker Pro used to recover file"; 
ErrorNum = 413 ; "Specified field has inappropriate field type"; 
ErrorNum = 414 ; "Layout cannot display the result"; 
ErrorNum = 415 ; "One or more required related records are not available"; 
ErrorNum = 416 ; "Primary key required from data source table"; 
ErrorNum = 417 ; "Database is not supported for ODBC operations"; 
ErrorNum = 500 ; "Date value does not meet validation entry options"; 
ErrorNum = 501 ; "Time value does not meet validation entry options"; 
ErrorNum = 502 ; "Number value does not meet validation entry options"; 
ErrorNum = 503 ; "Value in field is not within the range specified in validation entry options"; 
ErrorNum = 504 ; "Value in field is not unique as required in validation entry options"; 
ErrorNum = 505 ; "Value in field is not an existing value in the database file as required in validation entry options"; 
ErrorNum = 506 ; "Value in field is not listed on the value list specified in validation entry option"; 
ErrorNum = 507 ; "Value in field failed calculation test of validation entry option"; 
ErrorNum = 508 ; "Invalid value entered in Find mode"; 
ErrorNum = 509 ; "Field requires a valid value"; 
ErrorNum = 510 ; "Related value is empty or unavailable"; 
ErrorNum = 511 ; "Value in field exceeds maximum number of allowed characters"; 
ErrorNum = 512 ; "Record was already modified by another user"; 
ErrorNum = 513 ; "Record must have a value in some field to be created"; 
ErrorNum = 600 ; "Print error has occurred"; 
ErrorNum = 601 ; "Combined header and footer exceed one page"; 
ErrorNum = 602 ; "Body doesn't fit on a page for current column setup"; 
ErrorNum = 603 ; "Print connection lost"; 
ErrorNum = 700 ; "File is of the wrong file type for import"; 
ErrorNum = 706 ; "EPSF file has no preview image"; 
ErrorNum = 707 ; "Graphic translator cannot be found"; 
ErrorNum = 708 ; "Can't import the file or need color monitor support to import file"; 
ErrorNum = 709 ; "QuickTime movie import failed"; 
ErrorNum = 710 ; "Unable to update QuickTime reference because the database file is read-only"; 
ErrorNum = 711 ; "Import translator cannot be found"; 
ErrorNum = 714 ; "Password privileges do not allow the operation"; 
ErrorNum = 715 ; "Specified Excel worksheet or named range is gone"; 
ErrorNum = 716 ; "A SQL query using DELETE, INSERT, or UPDATE is not allowed for ODBC import"; 
ErrorNum = 717 ; "There is not enough XML/XSL information to proceed with the import or export"; 
ErrorNum = 718 ; "Error in parsing XML file (from Xerces)"; 
ErrorNum = 719 ; "Error in transforming XML using XSL (from Xalan)"; 
ErrorNum = 720 ; "Error when exporting; intended format does not support repeating fields"; 
ErrorNum = 721 ; "Undetermined error occurred in the parser or the transformer"; 
ErrorNum = 722 ; "Cannot import data into a file that has no fields"; 
ErrorNum = 723 ; "You do not have permission to add records to or modify records in the target table"; 
ErrorNum = 724 ; "You do not have permission to add records to the target table"; 
ErrorNum = 725 ; "You do not have permission to modify records in the target table"; 
ErrorNum = 726 ; "There are more records in the import file than in the target table. Not all records were imported"; 
ErrorNum = 727 ; "There are more records in the target table than in the import file. Not all records were updated"; 
ErrorNum = 729 ; "Errors occurred during import. Records could not be imported"; 
ErrorNum = 730 ; "Unsupported Excel version. (Convert file to Excel 7.0 (Excel 95), Excel 97, 2000, or XP format and try again)"; 
ErrorNum = 731 ; "The file you are importing from contains no data"; 
ErrorNum = 732 ; "This file cannot be inserted because it contains other files"; 
ErrorNum = 733 ; "A table cannot be imported into itself"; 
ErrorNum = 734 ; "This file type cannot be displayed as a picture"; 
ErrorNum = 735 ; "This file type cannot be displayed as a picture. It will be inserted and displayed as a file"; 
ErrorNum = 736 ; "Too much data to export to this format. It will be truncated"; 
ErrorNum = 800 ; "Unable to create file on disk"; 
ErrorNum = 801 ; "Unable to create temporary file on System disk"; 
ErrorNum = 802 ; "Unable to open file"; 
ErrorNum = 803 ; "File is single user or host cannot be found"; 
ErrorNum = 804 ; "File cannot be opened as read-only in its current state"; 
ErrorNum = 805 ; "File is damaged; use Recover command"; 
ErrorNum = 806 ; "File cannot be opened with this version of FileMaker Pro"; 
ErrorNum = 807 ; "File is not a FileMaker Pro file or is severely damaged"; 
ErrorNum = 808 ; "Cannot open file because access privileges are damaged"; 
ErrorNum = 809 ; "Disk/volume is full"; 
ErrorNum = 810 ; "Disk/volume is locked"; 
ErrorNum = 811 ; "Temporary file cannot be opened as FileMaker Pro file";
ErrorNum = 813 ; "Record Synchronization error on network"; 
ErrorNum = 814 ; "File(s) cannot be opened because maximum number is open"; 
ErrorNum = 815 ; "Couldn't open lookup file"; 
ErrorNum = 816 ; "Unable to convert file"; 
ErrorNum = 817 ; "Unable to open file because it does not belong to this solution"; 
ErrorNum = 819 ; "Cannot save a local copy of a remote file"; 
ErrorNum = 820 ; "File is in the process of being closed"; 
ErrorNum = 821 ; "Host forced a disconnect"; 
ErrorNum = 822 ; "FMI files not found; reinstall gone files"; 
ErrorNum = 823 ; "Cannot set file to single-user, guests are connected"; 
ErrorNum = 824 ; "File is damaged or not a FileMaker file"; 
ErrorNum = 900 ; "General spelling engine error"; 
ErrorNum = 901 ; "Main spelling dictionary not installed"; 
ErrorNum = 902 ; "Could not launch the Help system"; 
ErrorNum = 903 ; "Command cannot be used in a shared file"; 
ErrorNum = 905 ; "No active field selected; command can only be used if there is an active field"; 
ErrorNum = 906 ; "Current file must be shared in order to use this command"; 
ErrorNum = 920 ; "Can't initialize the spelling engine"; 
ErrorNum = 921 ; "User dictionary cannot be loaded for editing"; 
ErrorNum = 922 ; "User dictionary cannot be found"; 
ErrorNum = 923 ; "User dictionary is read-only"; 
ErrorNum = 951 ; "An unexpected error occurred (*)"; 
ErrorNum = 954 ; "Unsupported XML grammar (*)"; 
ErrorNum = 955 ; "No database name (*)"; 
ErrorNum = 956 ; "Maximum number of database sessions exceeded (*)"; 
ErrorNum = 957 ; "Conflicting commands (*)"; 
ErrorNum = 958 ; "Parameter gone (*)"; 
ErrorNum = 1200 ; "Generic calculation error"; 
ErrorNum = 1201 ; "Too few parameters in the function"; 
ErrorNum = 1202 ; "Too many parameters in the function"; 
ErrorNum = 1203 ; "Unexpected end of calculation"; 
ErrorNum = 1204 ; "Number, text constant, field name or ( expected"; 
ErrorNum = 1205 ; "Comment is not terminated with *\/" ; 
ErrorNum = 1206 ; "Text constant must end with a quotation mark"; 
ErrorNum = 1207 ; "Unbalanced parenthesis"; 
ErrorNum = 1208 ; "Operator gone, function not found or ( not expected"; 
ErrorNum = 1209 ; "Name (such as field name or layout name) is gone"; 
ErrorNum = 1210 ; "Plug-in function has already been registered"; 
ErrorNum = 1211 ; "List usage is not allowed in this function"; 
ErrorNum = 1212 ; "An operator (for example, +, -, *) is expected here"; 
ErrorNum = 1213 ; "This variable has already been defined in the Let function"; 
ErrorNum = 1214 ; "AVERAGE, COUNT, EXTEND, GETREPETITION, MAX, MIN, NPV, STDEV, SUM and GETSUMMARY: expression found where a field alone is needed"; 
ErrorNum = 1215 ; "This parameter is an invalid Get function parameter"; 
ErrorNum = 1216 ; "Only Summary fields allowed as first argument in GETSUMMARY"; 
ErrorNum = 1217 ; "Break field is invalid"; 
ErrorNum = 1218 ; "Cannot evaluate the number"; 
ErrorNum = 1219 ; "A field cannot be used in its own formula"; 
ErrorNum = 1220 ; "Field type must be normal or calculated"; 
ErrorNum = 1221 ; "Data type must be number, date, time, or timestamp"; 
ErrorNum = 1222 ; "Calculation cannot be stored"; 
ErrorNum = 1223 ; "The function is not implemented"; 
ErrorNum = 1224 ; "The function is not defined"; 
ErrorNum = 1225 ; "The function is not supported in this context"; 
ErrorNum = 1300 ; "The specified name can't be used"; 
ErrorNum = 1400 ; "ODBC driver initialization failed; make sure the ODBC drivers are properly installed"; 
ErrorNum = 1401 ; "Failed to allocate environment (ODBC)"; 
ErrorNum = 1402 ; "Failed to free environment (ODBC)"; 
ErrorNum = 1403 ; "Failed to disconnect (ODBC)"; 
ErrorNum = 1404 ; "Failed to allocate connection (ODBC)"; 
ErrorNum = 1405 ; "Failed to free connection (ODBC)"; 
ErrorNum = 1406 ; "Failed check for SQL API (ODBC)"; 
ErrorNum = 1407 ; "Failed to allocate statement (ODBC)"; 
ErrorNum = 1408 ; "Extended error (ODBC)"; 
ErrorNum = 1409 ; "Error (ODBC)"; 
ErrorNum = 1413 ; "Failed communication link ODBC ";

ErrorNum = 1450 ; "Action requires PHP privilege extension (*)" ;
ErrorNum = 1451 ; "Action requires that current file be remote" ;
ErrorNum = 1501 ; "SMTP authentication failed" ;
ErrorNum = 1502 ; "Connection refused by SMTP server" ;
ErrorNum = 1503 ; "Error with SSL" ;
ErrorNum = 1504 ; "SMTP server requires the connection to be encrypted" ;
ErrorNum = 1505 ; "Specified authentication is not supported by SMTP server" ;
ErrorNum = 1506 ; "Email(s) could not be sent successfully" ;
ErrorNum = 1507 ; "Unable to log in to the SMTP server" 
)

// Use this function to translate 'Evaluation' field error codes to text. This helps users troubleshoot their text field calculation rather than showing just a question mark.  -rcaldwell 5/2012

// Sample calculation:
//   Let ( [ vFormula = HoldCalc_g ; vError = EvaluationError ( Evaluate ( vFormula ) ) ] ; 
//      If ( vError ≠ 0 ; "Error " & vError & " - " & fnFMErrorCode ( vError ) ; Evaluate ( vFormula ) )
//   )  //end Let

fnFieldCheck

// fnFieldCheck ( field , numReturns )

Let ( [ 
vFieldName = Right ( GetFieldName ( field ) ; 
   Length ( GetFieldName ( field ) ) - Position ( GetFieldName ( field ) ; "::" ; 1 ; 1 ) - 1 )
] ; 

If ( Left ( field ; 1 ) = " " ; 
   vFieldName & " starts with a space. " ) & 
If ( Right ( field ; 1 ) = " " and Length ( field ) > 1 ; 
   vFieldName & " ends with a space. " ) & 
If ( PatternCount ( Trim ( GetValue ( field ; 1 ) ) ; "  " ) > 0 
   or PatternCount ( Trim ( GetValue ( field ; 2 ) ) ; "  " ) > 0 
   or PatternCount ( Trim ( GetValue ( field ; 3 ) ) ; "  " ) > 0 ; 
   vFieldName & " contains a double-space. " ) & 
If ( Left ( field ; 1 ) = ¶ ; 
   vFieldName & " starts with a return. " ) & 
If ( Right ( field ; 1 ) = ¶ and Length ( field ) > 1 ; 
   vFieldName & " ends with a return. " ) & 
If ( PatternCount ( field ; ¶ ) > numReturns ; 
   vFieldName & " has too many returns. " )

)  //end Let

// Flags common data entry errors in a given field (leading/trailing spaces, leading/trailing returns, double-spaces).
// Use numReturns 0 to remove all returns, 1-3 for number of allowable returns for that field.

fnFormatConnection

// fnFormatConnection ( text ; type )
Let ( [ 
vConnectionText = TextFormatRemove ( text ) ; 
vNum = Filter ( vConnectionText ; "+0123456789" )
] ; 

Case ( 

Left ( vConnectionText ; 4 ) = "www." or 
   PatternCount ( vConnectionText ; "@" ) = 0 and WordCount ( vConnectionText ) < 5 and 
   ( Right ( vConnectionText ; 4 ) = ".com" and PatternCount ( vConnectionText ; "://" ) = 0 or 
   Right ( vConnectionText ; 4 ) = ".org" and PatternCount ( vConnectionText ; "://" ) = 0 ) ; 
      "http://" & vConnectionText ; 

Length ( vNum ) = 10 and ( type = "Phone" or type = "" ) and 
   PatternCount ( vConnectionText ; "://" ) = 0 and PatternCount ( vConnectionText ; "@" ) = 0 ; 
   // Don't format URLs or emails with 10 digits as phone numbers
   "(" & Left ( vNum ; 3 ) & ") " & Middle ( vNum ; 4 ; 3 ) & "-" & Middle ( vNum ; 7 ; 4 ) ; 

vConnectionText )  //end case

)  //end Let


// Adds 'http://' in front of URLs that need it, 
// and formats 10 digit values to phone format with parentheses.
// Plus in vNum means international phone numbers will not get converted to American format.

fnGeoDistance

// fnGeoDistance ( lat1 ; long1 ; lat2 ; long2 )

//6367500	// radius of Earth in meters
3956.6      // radius of Earth in miles
* Acos (
	Cos ( Radians ( lat2 - lat1 ) )
	- Cos ( Radians ( lat1 ) )
	* Cos ( Radians ( lat2 ) )
	* ( 1 - Cos ( Radians ( long2 - long1 ) ) )
)

// Returns the approximate distance in meters between two points on the surface of the Earth.
// Source - http://www.briandunning.com/cf/1490

fnGetCell

//fnGetCell ( text ; row ; column ; delimiter )
Substitute ( 

Let ( [ 
vDelimiter = If ( delimiter = "" ; Char(9) ; delimiter ) ; 
vLine = GetValue ( text ; row ) ; 
vStart = Position ( vLine ; vDelimiter ; 1 ; column - 1 ) + Length ( vDelimiter ) ; 
vEnd = Position ( vLine ; vDelimiter ; 1 ; column ) 
] ; 

Case ( 
   column = 1 ; Left ( vLine ; vEnd - 1 ) ; 
   column <= PatternCount ( vLine ; vDelimiter ) ; 
      Middle ( vLine ; vStart ; vEnd - vStart ) ; 
   column - 1 = PatternCount ( vLine ; vDelimiter ) ; 
      Right ( vLine ; Length ( vLine ) - vStart + 1 ) ; 
   ""
) //end case

) //end Let

; "‡" ; ¶ ) //end substitute


// Based on fnAuditLog, but used 'row' instead of Get ( ActiveRepetitionNumber ) to create separate fields for each related value to allow better control when exporting data vs. repeating fields.   -rcaldwell 1/18/2011
// Added 'Length ( vDelimiter )' to vStart value to get the correct starting point when the delimiter is more than one character.  -rcaldwell 1/1/2017

fnGetColPosition

// fnGetColPosition ( text ; start ; end ; option )

Let ( [ 
vCell = Trim ( GetValue ( Middle ( text ; start ; end - start + 1 ) ; 1 ) ) ; 
vCountRows = ValueCount ( text )
] ; 

Case ( 
   vCell = "" ; "" ; 
   option = "startnum" ; If ( Filter ( Left ( vCell ; 1 ) ; "1234567890P" ) ≠ "" ; vCell & ¶ ) ; 
   vCell & ¶ ) & 

If ( vCountRows > 1 ; fnGetColPosition ( RightValues ( text ; vCountRows - 1 ) ; start ; end ; option ) )

)  //end Let


// Similar to fnGetColumn, this function uses character position to evaluate columns.  -7/2015
// This version does not need to preserve blank vlaues.
// Option controls what type of data is recognized as valid so unnecessary lines are skipped.
// Used by LaneGainer_Support.  -7/2015

fnGetColumn

// fnGetColumn ( text ; colnum ; delimiter )

Let ( [ 
vCell = MiddleValues ( Substitute ( GetValue ( text ; 1 ) ; delimiter ; ¶ ) ; colnum ; 1 ) ; 
vCountRows = ValueCount ( text )
] ; 

If ( vCell = "" and vCountRows >0 ; ¶ ; vCell ) & 
If ( vCountRows > 1 ; fnGetColumn ( RightValues ( text ; vCountRows - 1 ) ; colnum ; delimiter ) )

)  //end Let


// Returns one column from a block of delimited text. Use Char(9) as the delimiter for tab separated text.
// Added return if vCell is blank to preserve item position in list.  -rcaldwell 7/2015
// Added vCountRows > 0 check so blank lists would not result in a carrige return.  -rcaldwell 11/2017
// Source - http://www.briandunning.com/cf/877

fnGetEmails

// fnGetEmails ( text )
Let ( [ 
vFilteredText = Substitute ( Text 
   ; [ " " ; ":" ] 
   ; [ ":" ; " " ] 
   ; [ "[" ; " " ] 
   ; [ "]" ; " " ] 
   ; [ "<" ; " " ] 
   ; [ ">" ; " " ] 
   ; [ ";" ; " " ]  
   ; [ "," ; " " ] 
   ; [ "'" ; " " ] 
   ; [ ")" ; " " ] 
   ; [ "(" ; " " ] 
   ; [ "  " ; " " ] 
   ; [ "\"" ; " " ]
   ) ; 
vAdjustedText = Substitute ( vFilteredText ; ¶ ; " " ) ; 
vCount = PatternCount ( vAdjustedText ; "@" ) ; 
vLen = Length ( vAdjustedText ) ; 
vPos = Position ( vAdjustedText ; "@" ; 1 ; 1 ) ; 
vStartWord = Position ( vAdjustedText ; " " ; vPos ; -1 ) + 1 ; 
vEndWord = If ( Position ( vAdjustedText ; " " ; vPos ; 1 ) ≠ 0 ; 
   Position ( vAdjustedText ; " " ; vPos ; 1 ) ; vLen + 1  ) ; 
vWord = Middle ( vAdjustedText ; vStartWord ; vEndWord - vStartWord );
vNextText = Middle ( vAdjustedText ; vEndWord + 1 ; vLen )
] ; 

Case ( 
vCount = 1 ; vWord ; 
vCount ; vWord &¶& fnGetEmails ( vNextText ) ; 
"" )  //end case

)  //end Let


// Returns a list of email addresses from a string of text.
// Used by the Load Linkedin Data script.  -rcaldwell 10/2015
// Source - https://www.briandunning.com/cf/1617

fnGetLayoutID

// fnGetLayoutID ( layout_name )
Let ( [
// use "current" layout or the layoutname that is coming from the "layout"-parameter
vLayout = If ( layout_name = "" or layout_name = "current" ; Get ( LayoutName ) ; layout_name ); 

vLayoutNames = LayoutNames ( Get ( FileName ) ) ; 

// Count all ¶ from the start to where the layout name is and add 1.
vCurrentLayoutPosition =  PatternCount ( 
   Middle ( vLayoutNames ; 1 ; 
   Position ( ¶ & vLayoutNames & ¶ ; ¶ & vLayout & ¶ ; 1 ; 1 ) ) ; ¶ ) + 1 
] ; 
 
GetValue ( LayoutIDs ( Get ( FileName ) ) ; vCurrentLayoutPosition )

)  //end Let



// Layout names change and layout numbers (sort order) change, but the Layout ID is unique and permanent for a FileMaker database file.
// Based on Michael Bijnens's function from 1/1/2014.
// This version does not have dependencies on other custom functions and does not use recursion.  -rcaldwell 9/2017

// To get the layout number from the ID, use this.
// PatternCount ( Middle ( LayoutIDs ( Get ( FileName ) ) ; 1 ; Position ( ¶ & LayoutIDs ( Get ( FileName ) ) & ¶ ; ¶ & layout_id & ¶ ; 1 ; 1 ) ) ; ¶ ) + 1

fnGetLayoutName

// fnGetLayoutName ( layout_id )
Let ( [ 
vLayoutIDs = LayoutIDs ( Get ( FileName ) ) ; 

// Count all ¶ from the start to where the layout ID is and add 1
vCurrentLayoutPosition =  PatternCount ( 
   Middle ( vLayoutIDs ; 1 ; 
   Position ( ¶ & vLayoutIDs & ¶ ; ¶ & layout_id & ¶ ; 1 ; 1 ) ) ; ¶ ) + 1

] ; 
 
GetValue ( LayoutNames ( Get ( FileName ) ) ; vCurrentLayoutPosition )

)  //end Let



// This is the opposite of fnGetLayoutID.

fnGetLine

// fnGetLine ( text ; search ; occurrence )
Let ( [ 
vPosition = Position ( text ; search ; 1 ; occurrence ) ; 
vRow = PatternCount ( Left ( text ; vPosition ) ; 
   If ( PatternCount ( text ; ¶ ) = 0 ; Char(10) ; ¶ ) ) + 1 ; 
vLine = If ( PatternCount ( text ; search ) > 0 ; GetValue ( text ; vRow ) )
] ; 

vLine

)  //end Let

// Counts the number of returns before the desired value, and uses that value in a GetValue() statement.
// Works for both CR and LF lists.
// Similar to fnGetRow.
// Used in the RocketShipIt UPS integration solution.  -rcaldwell 12/2012

fnGetRow

// fnGetRow ( relatedfield ; key ; valuenum )
Let ( [ 
vText = List ( relatedfield ) ; 
vStart = Position ( vText ; key ; 1 ; 1 ) ; 
vRow = PatternCount ( Left ( vText ; vStart ) ; ¶ ) + 1 ; 
vValues = Substitute ( Trim ( GetValue ( vText ; vRow ) ) ; Char(9) ; ¶ )
] ; 

If ( key = GetValue ( vValues ; 1 ) or 
   key = GetValue ( vValues ; 2 ) or 
   key = GetValue ( vValues ; 3 ) ; 
   GetValue ( vValues ; valuenum )
)  //end if

)  //end Let



// RelatedField is a tab-delimited calculation of values.
// The Let statement looks for the key value in all related records, and returns the row where the key value exists.
// The final calc returns the requested value from the requested slot in the tab delimited related field calc.

fnGetValueNumber

// fnGetValueNumber ( listOfValues ; value )
Let ( [ 
vList = ¶& listOfValues &¶ ; 
vPosition = Position ( vList ; ¶& value &¶ ; 1 ; 1 )
] ; 

ValueCount ( Left ( vList ; vPosition ) )

)  //end Let



// Searches for a value in a list of values and returns the row number.
// Source 2017 - https://www.briandunning.com/cf/1300

fnHelpLinks

// fnHelpLinks ( text ; script )
Let ( [ 
vTextLeft = Left ( text ; Position ( text ; "[[" ; 1 ; 1 ) - 1 ) ; 
vStart = Position ( text ; "[[" ; 1 ; 1 ) + Length ( "[[" ) ; 
vBlock = If ( PatternCount ( text ; "[[" ) >= 1 ; 
   Trim ( 
   Middle ( text ; vStart ; 
      Position ( text ; "]]" ; vStart + 1 ; 1 ) - vStart
   )  //end middle
   )  //end trim
   ) ;  //end patterncount
vStart2 = Position ( vBlock ; "|" ; 1 ; 1 ) ; 
vSearch = If ( vStart2 = 0 ; vBlock ; Left ( vBlock ; vStart2 - 1 ) ) ; 
vText = If ( vStart2 = 0 ; vBlock ; Middle ( vBlock ; vStart2 + 1 ; Length ( vBlock ) ) ) ; 
vLink = "fmp://" & If ( Get ( HostIPAddress ) = "" ; "$" ; Get ( HostIPAddress ) ) & "/" & 
   Get ( FileName ) & "?script=" & GetAsURLEncoded ( script ) & "&param=" & GetAsURLEncoded ( vSearch )
] ; 


If ( PatternCount ( text ; "[[" ) = 0 ; text ; 
   vTextLeft & 
   If ( LeftWords ( vSearch ; 1 ) = "image" ; "" ; "<a href='" & vLink & "' title='" & vSearch & "'>" & vText & "</a>" ) & 
   fnHelpLinks ( Right ( text ; Length ( text ) - Position ( text ; "]]" ; 1 ; 1 ) - Length ( "]]" ) + 1 ) ; script )
)  //end if

)  //end Let



// Part of my user-driven database help system. Text is entered in a Markdown-style format and transformed into HTML for the reader.
// Recursive script converts [[internal links]] into FileMaker script calls to perform actions.   -rcaldwell 4/2014
// Script parameter is the name of the FileMaker script to be called that will handle the request.

// Content within brackets becomes the parameter for the script.
// Send a value like [[1234]] to make a hyperlink.
// <a href='fmp://192.168.202.99/MyFile?script=GoToRecord&param=1234' title='1234'>1234</a>

// Use a pipe to separate script parameter value from hyperlink text that should appear on the page.
// Send a value like [[1234|Dolly]] to make a hyperlink.
// <a href='fmp://192.168.202.99/MyFile?script=GoToRecord&param=1234' title='1234'>Dolly</a>

fnHelpLists

// fnHelpLists ( text )
Case ( 

PatternCount ( ¶& text ; "¶*" ) ; 
Let ( [ 
vPosition = Position ( text ; "¶*" ; 1 ; 1 ) ; 
vRow = If ( Left ( text ; 1 ) = "*" ; 1 ; PatternCount ( Left ( text ; vPosition ) ; ¶ ) + 1 ) ; 
vTextLeft = If ( Left ( text ; 1 ) = "*" ; "" ; Left ( text ; vPosition - 1 ) ); 
vTextMiddle = GetValue ( text ; vRow ) ; 
vTextMiddle = Trim ( Right ( vTextMiddle ; Length ( vTextMiddle ) - 1 ) ) ; 
vNextLine = GetValue ( text ; vRow + 1 ) ; 
vTextRight = RightValues ( text ; ValueCount ( text ) - vRow )
] ; 

vTextLeft & 
If ( vRow ≠ 1 ; "<ul>" ) & 
"<li>" & vTextMiddle & "</li>" & 
If ( Left ( vNextLine ; 1 ) ≠ "*" ; "</ul>" ) & 
fnHelpLists ( vTextRight )

)  //end Let
; 

PatternCount ( ¶& text ; "¶#" ) ; 
Let ( [ 
vPosition = Position ( text ; "¶#" ; 1 ; 1 ) ; 
vRow = If ( Left ( text ; 1 ) = "#" ; 1 ; PatternCount ( Left ( text ; vPosition ) ; ¶ ) + 1 ) ; 
vTextLeft = If ( Left ( text ; 1 ) = "#" ; "" ; Left ( text ; vPosition - 1 ) ) ; 
vTextMiddle = GetValue ( text ; vRow ) ; 
vTextMiddle = Trim ( Right ( vTextMiddle ; Length ( vTextMiddle ) - 1 ) ) ; 
vNextLine = GetValue ( text ; vRow + 1 ) ; 
vTextRight = RightValues ( text ; ValueCount ( text ) - vRow )
] ; 

vTextLeft & 
If ( vRow ≠ 1 ; "<ol>" ) & 
"<li>" & vTextMiddle & "</li>" & 
If ( Left ( vNextLine ; 1 ) ≠ "#" ; "</ol>" ) & 
fnHelpLists ( vTextRight )

)  //end Let
; 

text )  //end case



// Part of my user-driven database help system. Text is entered in a Markdown-style format and transformed into HTML for the reader.
// Converts internal links into FileMaker script calls to perform similar actions.   -rcaldwell 4/2014
// Script parameter is the name of the FileMaker script to be called that will handle the request.

// Send a value like [[MYID_0084]] to make a hyperlink.
// <a href='fmp://192.168.202.99/MyFile?script=GoToRecord&param=MYID_0084>MYID_0084</a>

// Send a value like [[MYID_0084|Dolly]] to make a hyperlink.
// <a href='fmp://192.168.202.99/MyFile?script=GoToRecord&param=MYID_0084>Dolly</a>

fnHelpText

//fnHelpText ( selftext ; type )
Case ( 

type = 0 ;  // text Tooltip
   If ( $$HideTooltips ; "" ; selftext )
; 
type = 1 ;  // field comment Tooltip
   If ( GetAsNumber ( Get ( ApplicationVersion ) ) < 10 or $$HideTooltips or 
      IsEmpty ( FieldComment ( Get ( FileName ) ; GetFieldName ( selftext ) ) ) ; "" ; 
      FieldComment ( Get ( FileName ) ; GetFieldName ( selftext ) )
   )  //end if
; 
type = 2 ;  // field name and comment Tooltip
   If ( GetAsNumber ( Get ( ApplicationVersion ) ) < 10 or $$HideTooltips = 1 ; "" ; 
      GetFieldName ( selftext ) & 
      If ( FieldComment ( Get ( FileName ) ; GetFieldName ( selftext ) ) ≠ "" ; 
         " - " & FieldComment ( Get ( FileName ) ; GetFieldName ( selftext ) ) 
      )  //end if
   )  //end if
; 
type = 3 ;  // field comment Dialog (ignores user pref)
   Case ( GetAsNumber ( Get ( ApplicationVersion ) ) < 10 ; 
      "This help text needs FileMaker 10 or higher." ; 
      IsEmpty ( FieldComment ( Get ( FileName ) ; GetFieldName ( selftext ) ) ) ; "" ; 
      FieldComment ( Get ( FileName ) ; GetFieldName ( selftext ) )
   )  //end case
; 
) //end case


// This FileMaker custom function turns field comments into tooltips.
// Can be applied to every field on a layout at once:  fnHelpText ( self ; 1 )
// Can be used in a dialog box:  fnHelpText ( my_table::my_field ; 3 )
// $$HideTooltips allows advanced users to turn off tips.  -rcaldwell 11/2010

fnHexDecSwitch

// fnHexDecSwitch ( charCode ; hexDec )
If ( hexDec = "dec" ; 

// Dec2Hex
Let ( [
vHexDigits = "0123456789ABCDEF" ;
vMod = Mod ( charCode ; 16 ) ;
vRemain = Int ( charCode / 16 ) 
] ; 

If ( vRemain ; fnHexDecSwitch ( vRemain ; hexDec ) ) & Middle ( vHexDigits ; vMod + 1 ; 1 )

)  //end Let

; 

// Hex2Dec
Let ( [
vHexDigits = "0123456789ABCDEF" ;  // 16 hex characters (0-15)
vHexCode = Filter ( Upper ( charCode ) ; vHexDigits ) ;  // drop non-hex characters
vLeftNum = Position ( vHexDigits ; Left ( vHexCode ; 1 ) ; 1 ; 1 ) - 1 ;
   // convert the first (left) character to a digit (0-15)
vRemain = Length ( vHexCode ) - 1   // left character position
] ; 

vLeftNum * ( 16 ^ vRemain ) + 
If ( vRemain > 0 ; fnHexDecSwitch ( Right ( vHexCode ; vRemain ) ; hexDec )
)

)  //end Let


)  //end if hexDec



// This function is used to translate data returned from 
// api.mymemory.translated.net for languages like Arabic.  -rcaldwell 8/2014

// Convert the first (left) character to a digit (0-15).
// Then multiply by 16 to the power of the character position.
// Convert each character and add up the results.

// First parameter 'code' should represent one character, 
// encoded as hexadecimal or decimal passed as the second variable.
// 0x prefix is shorthand for a hex code in reference to the abbreviated pronunciation of hexadecimal.

// Source - http://www.fmdiff.com/fm/numberconversion.html?session=a54hk0luhcs5qt3vt5k50pg853

fnHexRGB

// fnHexRGB ( hex_or_rgb )
Let ( [ 
vData = Trim ( GetValue ( hex_or_rgb ; 1 ) ) ; 
vRemain = MiddleValues ( hex_or_rgb ; 2 ; ValueCount ( hex_or_rgb ) ) ; 
vHEXData = Filter ( Upper ( vData ) ; "0123456789ABCDEF" ) ; 
vRGBData = Substitute ( vData ; [ "," ; ¶ ] ; [ ";" ; ¶ ] ) ; 
vHEXList = "0¶1¶2¶3¶4¶5¶6¶7¶8¶9¶A¶B¶C¶D¶E¶F" ; 
vRed = GetValue ( vRGBData ; 1 ) ; 
vGreen = GetValue ( vRGBData ; 2 ) ; 
vBlue = GetValue ( vRGBData ; 3 )
] ; 

Case ( 

Filter ( Upper ( vData ) ; "ABCDEF" ) ≠ "" or 
   Length ( vHEXData ) = 6 and 
   ( Length ( vData ) = 6 or Length ( vData ) = 7 ) ;  // HEX to RGB
   // HEX value could start with #

Substitute ( Middle ( vHEXData ; 1 ; 1 )    // RED
   ; [ "A" ; 10 ] ; [ "B" ; 11 ] ; [ "C" ; 12 ] 
   ; [ "D" ; 13 ] ; [ "E" ; 14 ] ; [ "F" ; 15 ] 
   ) * 16 + 
   Substitute ( Middle ( vHEXData ; 2 ; 1 ) 
   ; [ "A" ; 10 ] ; [ "B" ; 11 ] ; [ "C" ; 12 ] 
   ; [ "D" ; 13 ] ; [ "E" ; 14 ] ; [ "F" ; 15 ] 
   ) * 1  & "," & 

Substitute ( Middle ( vHEXData ; 3 ; 1 )    // GREEN
   ; [ "A" ; 10 ] ; [ "B" ; 11 ] ; [ "C" ; 12 ] 
   ; [ "D" ; 13 ] ; [ "E" ; 14 ] ; [ "F" ; 15 ] 
   ) * 16 + 
   Substitute ( Middle ( vHEXData ; 4 ; 1 ) 
   ; [ "A" ; 10 ] ; [ "B" ; 11 ] ; [ "C" ; 12 ] 
   ; [ "D" ; 13 ] ; [ "E" ; 14 ] ; [ "F" ; 15 ] 
   ) * 1  & "," & 

Substitute ( Middle ( vHEXData ; 5 ; 1 )    // BLUE
   ; [ "A" ; 10 ] ; [ "B" ; 11 ] ; [ "C" ; 12 ] 
   ; [ "D" ; 13 ] ; [ "E" ; 14 ] ; [ "F" ; 15 ] 
   ) * 16 + 
   Substitute ( Middle ( vHEXData ; 6 ; 1 ) 
   ; [ "A" ; 10 ] ; [ "B" ; 11 ] ; [ "C" ; 12 ] 
   ; [ "D" ; 13 ] ; [ "E" ; 14 ] ; [ "F" ; 15 ] 
   ) * 1 
; 

vRed and vGreen and vBlue ;    // RGB to HEX
   GetValue ( vHEXList ; Div ( vRed ; 16 ) + 1 ) &    // vDivR
   GetValue ( vHEXList ; Mod ( vRed ; 16 ) + 1 ) &    // vModR
   GetValue ( vHEXList ; Div ( vGreen ; 16 ) + 1 ) &  // vDivG
   GetValue ( vHEXList ; Mod ( vGreen ; 16 ) + 1 ) &  // vModG
   GetValue ( vHEXList ; Div ( vBlue ; 16 ) + 1 ) &   // vDivB
   GetValue ( vHEXList ; Mod ( vBlue ; 16 ) + 1 )     // vModB
; 

"error" )  //end case

& 

If ( Trim ( GetValue ( vRemain ; 1 ) ) ; ¶ & fnHexRGB ( vRemain ) )

)  //end Let



// Send a list of HEX and/or RGB values and convert them to the other format.  -rcaldwell 9/2017
// Combines two custom functions
// Source 1 - https://www.briandunning.com/cf/2034
// color_convert_decimal ( color_decimal ; output_format )
// Source 2 - https://www.briandunning.com/cf/874
// Hex2RGB ( hex ; delimiter ; alert )

fnHolidayOffice

// fnHolidayOffice ( vYear )
Let ( [
vDateNewYear1 = Date ( 1 ; 1 ; vYear ) ; 
vDateJuly4 = Date ( 7 ; 4 ; vYear ) ; 
vDateVeteran = Date ( 11 ; 11 ; vYear ) ; 
vDateTurkey = Date ( 11 ; 22 ; vYear )  // earliest possible Thanksgiving date
 + Choose ( DayOfWeek ( Date ( 11 ; 1 ; vYear ) ) - 1 ; 
   4 ; 3 ; 2 ; 1 ; 0 ; 6 ; 5 ) ; 
vDateXmas = Date ( 12 ; 25 ; vYear ) ; 
vDateNewYear2 = Date ( 1 ; 1 ; vYear + 1 )

] ; 

List (

Case (
DayOfWeek ( vDateNewYear1 ) = 1 ; "New Years Day¶" & vDateNewYear1 + 1 ;  // following Monday
DayOfWeek ( vDateNewYear1 ) = 7 ; "" ;  // previous Friday (in previous year)
"New Years Day¶" & vDateNewYear1 ) ; 

"Martin Luther King, Jr. Day¶" &  // 3rd Monday in January
Date ( 1 ; 15 ; vYear )  // earliest possible date
 + Choose ( DayOfWeek ( Date ( 1 ; 1 ; vYear ) ) - 1;
   1 ; 0 ; 6 ; 5 ; 4 ; 3 ; 2 ) ; 

"Presidents' Day¶" &  // 3rd Monday in February
Date ( 2 ; 15 ; vYear )  // earliest possible date
 + Choose ( DayOfWeek ( Date ( 2 ; 1 ; vYear ) ) - 1;
   1 ; 0 ; 6 ; 5 ; 4 ; 3 ; 2 ) ; 

"Memorial Day¶" &  // last Monday in May
Date ( 5 ; 25 ; vYear )  // earliest possible date
 + Choose ( DayOfWeek ( Date ( 5 ; 31 ; vYear ) ) - 1;
   0 ; 6 ; 5 ; 4 ; 3 ; 2 ; 1 ) ; 

"Independence Day (US)¶" &  // July 4
Case (
DayOfWeek ( vDateJuly4 ) = 1 ; vDateJuly4 + 1 ;  // following Monday
DayOfWeek ( vDateJuly4 ) = 7 ; vDateJuly4 - 1 ;  // previous Friday
vDateJuly4 ) ; 

"Labor Day¶" &  // 1st Monday in September
Date ( 9 ; 1 ; vYear )  // earliest possible date
 + Choose ( DayOfWeek ( Date ( 9 ; 1 ; vYear ) ) - 1;
   1 ; 0 ; 6 ; 5 ; 4 ; 3 ; 2 ) ; 

"Columbus Day¶" &  // 2nd Monday in October
Date ( 10 ; 8 ; vYear )  // earliest possible date
 + Choose ( DayOfWeek ( Date ( 10 ; 1 ; vYear ) ) - 1;
   1 ; 0 ; 6 ; 5 ; 4 ; 3 ; 2 ) ; 

"Veterans Day¶" &  // November 11
Case (
DayOfWeek ( vDateVeteran ) = 1 ; vDateVeteran + 1 ;  // following Monday
DayOfWeek ( vDateVeteran ) = 7 ; vDateVeteran - 1 ;  // previous Friday
vDateVeteran ) ; 

"Thanksgiving Day¶" &  // 4th Thursday in November
vDateTurkey ; 
"Thanksgiving Day After¶" & 
vDateTurkey + 1 ; 

"Christmas Eve¶" & 
Case (
DayOfWeek ( vDateXmas ) = 1 ; vDateXmas - 2 ;  // previous Friday
DayOfWeek ( vDateXmas ) = 7 ; vDateXmas - 2 ;  // previous Thursday
vDateXmas - 1 ) ; 

"Christmas Day¶" &  // December 25
Case (
DayOfWeek ( vDateXmas ) = 1 ; vDateXmas + 1 ;  // following Monday
DayOfWeek ( vDateXmas ) = 7 ; vDateXmas - 1 ;  // previous Friday
vDateXmas ) ; 

If ( DayOfWeek ( vDateNewYear2 ) = 7 ; 
"New Years Day¶" &  // (observed from next year)
vDateNewYear2 - 1 )

)  //end List

)  //end Let


// Return delimited list of OFFICE VACATION HOLIDAYS and dates for the given year.
// Dates are shifted to show vacation days observed by the US government.
// Source - http://www.briandunning.com/cf/1363

fnHolidays

// fnHolidays ( vYear )
Let ( [

// Easy Dates
vDateVeteran = Date ( 11 ; 11 ; vYear ) ; 
vDateTurkey = Date ( 11 ; 22 ; vYear )  // earliest possible Thanksgiving date
 + Choose ( DayOfWeek ( Date ( 11 ; 1 ; vYear ) ) - 1 ; 
   4 ; 3 ; 2 ; 1 ; 0 ; 6 ; 5 ) ; 

// EASTER
vEpochDiff = 693594 ; 
vPctDay = Mod ( vYear / 38 ; 1 ) ; 
vTime = GetAsTimestamp ( 86400 * vPctDay ) ; 
vMinutes = Minute ( vTime ) ; 
vEasDay01 = vMinutes / 2 + 56 + vEpochDiff ; 
vEasDate01 = Date ( 5 ; Day ( vEasDay01 ) ; vYear ) ; 
vEasDay02 = GetAsNumber ( vEasDate01 ) - vEpochDiff ; 
vFloor = Int ( vEasDay02 / 7 ) * 7 ; 
vDateEaster = GetAsDate ( vFloor + vEpochDiff - 34 ) ; 

// ROSH HASHANAH and PASSOVER
vG = Mod ( vYear ; 19 ) + 1 ;  // Golden Number
vN = Floor ( vYear / 100) - Floor ( vYear / 400 ) - 2 + 
   765433 / 492480 * Mod ( 12 * vG ; 19 ) + 
   Mod ( vYear ; 4 ) / 4 - ( 313 * vYear + 89081 ) / 98496 ; 
vFraction = vN - Int ( vN ) ; 
vMonMinFraction = 23269 / 25920 ; 
vTueMinFraction =  1367 / 2160 ; 
vRemainder = Mod ( 12 * vG ; 19 ) ; 
vRoshDate = Date ( 9 ; Int ( vN ) ; vYear ) ; 
// Postpone Rule 1
vRoshDate01 = Case ( DayOfWeek ( vRoshDate ) = 1 or  // Sunday
   DayOfWeek ( vRoshDate ) = 4 or    // Wednesday
   DayOfWeek ( vRoshDate ) = 6 ;    // Friday
   vRoshDate + 1 ; 
   vRoshDate ) ; 
// Postpone Rule 2
vRoshDate02 = Case ( DayOfWeek ( vRoshDate01 ) = 2 and  // Monday
   vFraction >= vMonMinFraction and 
   vRemainder > 11 ; 
   vRoshDate01 + 1 ; 
   vRoshDate01 ) ; 
// Postpone Rule 3
vRoshDate03 = Case ( DayOfWeek ( vRoshDate02 ) = 3 and  // Tuesday
   vFraction >= vTueMinFraction and 
   vRemainder > 6 ; 
   vRoshDate02 + 2 ; 
   vRoshDate02 ) ; 
vDateRoshThis = vRoshDate03 ; 
vM = Day ( vDateRoshThis ) + If ( Month ( vDateRoshThis ) = 10 ; 30 ; 0 ) ; 
vPassover = Date ( 3 ; 21 ; vYear ) + vM ; 

// Now calculate Rosh Hashanah for next year.

vYear02 = vYear + 1 ; 
vG = Mod ( vYear02 ; 19 ) + 1 ;  // Golden Number
vN = Floor ( vYear02 / 100) - Floor ( vYear02 / 400 ) - 2 + 
   765433 / 492480 * Mod ( 12 * vG ; 19 ) + 
   Mod ( vYear02 ; 4 ) / 4 - ( 313 * vYear02 + 89081 ) / 98496 ; 
vFraction = vN - Int ( vN ) ; 
vRemainder = Mod ( 12 * vG ; 19 ) ; 
vRoshDate = Date ( 9 ; Int ( vN ) ; vYear02 ) ; 
// Postpone Rule 1
vRoshDate01 = Case ( DayOfWeek ( vRoshDate ) = 1 or  // Sunday
   DayOfWeek ( vRoshDate ) = 4 or    // Wednesday
   DayOfWeek ( vRoshDate ) = 6 ;    // Friday
   vRoshDate + 1 ; 
   vRoshDate ) ; 
// Postpone Rule 2
vRoshDate02 = Case ( DayOfWeek ( vRoshDate01 ) = 2 and  // Monday
   vFraction >= vMonMinFraction and 
   vRemainder > 11 ; 
   vRoshDate01 + 1 ; 
   vRoshDate01 ) ; 
// Postpone Rule 3
vRoshDate03 = Case ( DayOfWeek ( vRoshDate02 ) = 3 and  // Tuesday
   vFraction >= vTueMinFraction and 
   vRemainder > 6 ; 
   vRoshDate02 + 2 ; 
   vRoshDate02 ) ; 
vDateRoshNext = vRoshDate03 ; 
vCheshvan = If ( vDateRoshNext - vDateRoshThis = 355 or vDateRoshNext - vDateRoshThis = 385 ; 84 ; 83 ) ; 
vDateHanukkah = vDateRoshThis + vCheshvan 

] ; 

List (

"New Years Day" & Char(9) &   // January 1
Date ( 1 ; 1 ; vYear ) ; 

"Martin Luther King, Jr. Day" & Char(9) &  // 3rd Monday in January
Date ( 1 ; 15 ; vYear )  // earliest possible date
 + Choose ( DayOfWeek ( Date ( 1 ; 1 ; vYear ) ) - 1 ; 
   1 ; 0 ; 6 ; 5 ; 4 ; 3 ; 2 ) ; 

"Valentine's Day" & Char(9) &   // February 14
Date ( 2 ; 14 ; vYear ) ; 

"Presidents' Day" & Char(9) &  // 3rd Monday in February
Date ( 2 ; 15 ; vYear )  // earliest possible date
 + Choose ( DayOfWeek ( Date ( 2 ; 1 ; vYear ) ) - 1 ; 
   1 ; 0 ; 6 ; 5 ; 4 ; 3 ; 2 ) ; 

"Daylight Saving Time begins" & Char(9) &  // 2nd Sunday in March
Date ( 3 ; 8 ; vYear )  // earliest possible date
 + Choose ( DayOfWeek ( Date ( 3 ; 1 ; vYear ) ) - 1 ; 
   0 ; 6 ; 5 ; 4 ; 3 ; 2 ; 1 ) ; 

"St. Patrick's Day" & Char(9) &  // March 17
Date ( 3 ; 17 ; vYear ) ; 

if ( vYear > 1900 and vYear < 2098 ; 
   "Passover begins at sundown" & Char(9) & vPassover - 1 ) ; 

// Easter holiday is the first Sunday after the first ecclesiastical full moon that occurs on or after March 21.
"Easter" & Char(9) & vDateEaster ; 

"Mother's Day" & Char(9) &  // 2nd Sunday in May
Date ( 5 ; 8 ; vYear )  // earliest possible date
 + Choose ( DayOfWeek ( Date ( 5 ; 1 ; vYear ) ) - 1 ; 
   0 ; 6 ; 5 ; 4 ; 3 ; 2 ; 1 ) ; 

"Memorial Day" & Char(9) &  // Last Monday in May
Date ( 5 ; 25 ; vYear )  // earliest possible date
 + Choose ( DayOfWeek ( Date ( 5 ; 31 ; vYear ) ) - 1 ; 
   0 ; 6 ; 5 ; 4 ; 3 ; 2 ; 1 ) ; 

"Father's Day" & Char(9) &  // 3rd Sunday in June
Date ( 6 ; 15 ; vYear )  // earliest possible date
 + Choose ( DayOfWeek ( Date ( 6 ; 1 ; vYear ) ) - 1 ; 
   0 ; 6 ; 5 ; 4 ; 3 ; 2 ; 1 ) ; 

"Juneteenth" & Char(9) &  // June 19
Date ( 6 ; 19 ; vYear ) ; 

"Independence Day (US)" & Char(9) &  // July 4
Date ( 7 ; 4 ; vYear ) ; 

"Labor Day" & Char(9) &  // 1st Monday in September
Date ( 9 ; 1 ; vYear )  // earliest possible date
 + Choose ( DayOfWeek ( Date ( 9 ; 1 ; vYear ) ) - 1 ; 
   1 ; 0 ; 6 ; 5 ; 4 ; 3 ; 2 ) ; 

if ( vYear > 1900 and vYear < 2098 ; 
   "Rosh Hashanah begins at sundown" & Char(9) & vDateRoshThis - 1 & 
   "¶Yom Kippur begins at sundown" & Char(9) & vDateRoshThis + 9 - 1 ) ; 

"Columbus Day" & Char(9) &  // 2nd Monday in October
Date ( 10 ; 8 ; vYear )  // earliest possible date
 + Choose ( DayOfWeek ( Date ( 10 ; 1 ; vYear ) ) - 1 ; 
   1 ; 0 ; 6 ; 5 ; 4 ; 3 ; 2 ) ; 

"Halloween" & Char(9) &   // October 31
Date ( 10 ; 31 ; vYear ) ; 

"Daylight Saving Time ends" & Char(9) &  // 1st Sunday in November
Date ( 11 ; 1 ; vYear )  // earliest possible date
 + Choose ( DayOfWeek ( Date ( 11 ; 1 ; vYear ) ) - 1 ; 
   0 ; 6 ; 5 ; 4 ; 3 ; 2 ; 1 ) ; 

"Election Day" & Char(9) &  // 1st Tuesday in November
Date ( 11 ; 1 ; vYear )  // earliest possible date
 + Choose ( DayOfWeek ( Date ( 11 ; 1 ; vYear ) ) - 1 ; 
   2 ; 1 ; 0 ; 6 ; 5 ; 4 ; 3 ) ; 

"Veterans Day" & Char(9) &  // November 11 or closest weekday
Case (
DayOfWeek ( vDateVeteran ) = 1 ; vDateVeteran + 1 ;  // following Monday
DayOfWeek ( vDateVeteran ) = 7 ; vDateVeteran - 1 ;  // previous Friday
vDateVeteran ) ; 

"Thanksgiving Day" & Char(9) &  // 4th Thursday in November
vDateTurkey ; 

If ( vYear > 1900 and vYear < 2098 ; 
   "Hanukkah begins at sundown" & Char(9) & vDateHanukkah - 1 ) ; 

"Christmas Day" & Char(9) &   // December 25
Date ( 12 ; 25 ; vYear ) ; 

"New Years Eve" & Char(9) &   // December 31
Date ( 12 ; 31 ; vYear )

)  //end List

)  //end Let


// Return delimited list of holidays and dates for the given year.
// Unlike the fnHolidayOffice function, these dates have not been shifted to show vacation days observed by the US government.
// Source - http://www.briandunning.com/cf/1363

// Split Holiday function into fnHolidayOffice and fnHolidays to distinguish the regular holiday calendar from the calendar with dates shifted to show vacation days observed by the US government.
// Dates added for St. Patrick's Day, Easter, Rosh Hashanah and Passover.  -rcaldwell 9/2019
// Added more non-work-vacation holidays including Valentine's Day, Junteenth, Halloween. Removed day after Thanksgiving and Christmas Eve.  -rcaldwell 1/2024


// DayOfWeek starts on Sunday(1).
// Choose selects a value based on position, zero being the first value.
// So when building a calculation for the Nth weekday in a month, 
// make sure the 0 value is on that day of the week. 
// So to select a Tuesday, make sure the third value in the 
// choose list is 0.
//    Choose ( DayOfWeek ( Date ( 11 ; 1 ; vYear ) ) - 1 ; 
//    2 ; 1 ; 0 ; 6 ; 5 ; 4 ; 3 )


// PASSOVER
// 
// Date of Rosh Hashanah on the Gregorian calendar for 1900-2099.
// Source 2017 - http://quasar.as.utexas.edu/BillInfo/ReligiousCalendars.html
// Note: I could not tell if the postpone rules were cumulative or not. If not, this calc may need to be adjusted.
// 
// Alternate method for calculating N
// vy2 = vYear - 1900 ; 
// vN2 = 6.057778996 + 1.554241797 * Mod ( 12 * vG ; 19 ) + 
//    .25 * Mod ( vy2 ; 4 ) - .003177794 * vy2 ; 


// EASTER
// 
// Easter holiday is the first Sunday after the first ecclesiastical full moon that occurs on or after March 21.
// Excel formula for calculating Easter day using A2 as the year.
// =FLOOR("5/"&DAY(MINUTE(A2/38)/2+56)&"/"&A2,7)-34
// 
// Excel reads MINUTE number as % of day passed.
// FileMaker reads MINUTE number as seconds from midnight.
// 86,400 = Number of seconds in a day.
// 
// Excel reads DAY number as number of days from 12/31/1899.
// FileMaker reads DAY number as number of days from 12/31/0000.
// 693,595 = Difference in days between Excel epoch and FileMaker epoch.
// Subtract 1 day from epoch difference because Excel thinks 1900 was a leap year, but it was not.
// 
// Excel FLOOR function rounds the first number down to the nearest multiple of the second number (7).
// To replicate this, divide the days by 7, take the integer value (left of decimal), and multiply 7 back in.
// 
// Notes found in comments:  The formula gives 4/16/2079 for Easter 2079. This should be 4/23/2079. Check 2011, 2038, 2052, and 2079, all 7 days early. 
// 
// Source 2019 - https://www.extendoffice.com/documents/excel/3654-excel-calculate-easter-date.html
// Source 1997 - https://www.filemakermagazine.com/articles/functions-part-3-date-time

fnIsPhoto

// fnIsPhoto ( file_name )
Let( [ 
vFileExt = Case ( 
   Left ( Right ( file_name ; 4 ) ; 1 ) = "." ; Right ( file_name ; 4 ) ; 
   Left ( Right ( file_name ; 5 ) ; 1 ) = "." ; Right ( file_name ; 5 ) ; 
   "" )  //end case

] ; 

If ( 
vFileExt = ".bmp" or 
vFileExt = ".gif" or 
vFileExt = ".jpg" or vFileExt = ".jpeg" or 
vFileExt = ".pct" or 
vFileExt = ".png" or 
vFileExt = ".psd" or 
vFileExt = ".tif" or vFileExt = ".tiff"
; 1 )  //end if

)  //end Let



// GetContainerAttribute does not work when the file is hosted on a FileMaker 12 Server.

// Best way to convert an image loaded as a file into a proper image is to use base64 encode/decoding.  Decoding uses the original file name to determine how the container data should be saved.
// eg. Base64Decode ( Base64Encode ( Test::File ) ; Test::File )

// BE_ConvertContainer - Converts an image loaded as a file (no image preview in container) into an image so preview and file attributes are returned correctly.
// If the width and height are not supplied, FM appears to display the image correctly but GetWidth and GetHeight return 0.
// However, GetContainerAttribute ( Test::Data ; "width" ) and GetContainerAttribute ( Test::Data ; "height” ) both return the correct value.
// So run the following calculation twice will set things correctly.
// BE_ConvertContainer ( Test::File ; "JPEG" ; GetContainerAttribute ( Test::Data ; "width" ) ; GetContainerAttribute ( Test::Data ; "height" ) )
// Source 2016 - https://baseelementsplugin.zendesk.com/hc/en-us/articles/206609078-BE-ConvertContainer

fnJulian

// fnJulian ( date )
Let( [ 
zDate = GetAsDate( vDate )
] ; 

Year( zDate ) & Right ( "000" & zDate+1 - GetAsDate ( "1/1/" & Year ( zDate ) ) ; 3 )

)  //end Let


// Used by fnBuildRSSFeed function.
// Source 2012 - http://www.briandunning.com/cf/1455

fnListDupes

// fnListDupes ( ValueList )
Let ( [ 
$i = $i + 1 ;
vNext = GetValue ( ValueList ; $i ) ;
$dupes = If ( ValueCount ( FilterValues ( ValueList ; vNext ) ) > 1 
   // if there is more than one copy of the value in the list, 
   // and it's not already in the dupe list
   and not ValueCount ( FilterValues ( $dupes ; vNext ) ) 
   ; List ( vNext ; $dupes ) ; $dupes )
] ; 

If ( $i ≤ ValueCount ( ValueList ) ; 
   fnListDupes ( ValueList ) ; 
   $dupes & Let ( [ $i = "" ; $dupes = "" ] ; "" )
)  //end if

)  //end Let


// Returns duplicate values from a list.
// Source 2015 - https://www.briandunning.com/cf/1571

fnLoadAuditLog

//fnLoadAuditLog ( field_list )
Let ( [ 
vFieldName = GetValue ( field_list ; 1 ) ; 
vNewValue = GetField ( vFieldName )
] ; 

if ( isvalid ( getfield ( vFieldName ) ) ; 
	GetAsDate ( Get ( CurrentHostTimeStamp ) ) & Char(9) &
	GetAsTime ( Get ( CurrentHostTimeStamp ) ) & Char(9) &
	Get ( AccountName ) & Char(9) &
	vFieldName & Char(9) & 
	"[---]" & Char(9) & "-»" & Char(9) & 
	vNewValue & ¶ & 
	if ( ValueCount ( field_list ) > 1 ; 
	   fnLoadAuditLog ( RightValues ( field_list ; ValueCount ( field_list ) - 1 ) ) )
)  //end if

)  //end Let


// Run this custom function to pre-populate an audit log field with existing values.

fnLuhn

// fnLuhn(number)
Let( [ 
vJustNumbers = Filter( number ; "0123456789" ) ;  // remove any non-numeric characters
vOddOrEvenLength = Mod ( Length(vJustNumbers) ; 2 ) ;  // odd length numbers return 1, even length numbers return 0
vMakeEven = Case ( vOddOrEvenLength = 1 ; "0" ; "" ) & vJustNumbers ;  // adds 0 to the front of any odd length number to make it even
vDoubleEven = fnLuhnDoubleEven ( vMakeEven ) ;  // doubles every 2nd number
vProduct = fnLuhnProduct ( vDoubleEven ) ;  // Adds all the numbers together
vValid = Case( Mod ( vProduct ; 10 ) = 0 ; 1 ; 0 )  // The number is valid if it is exactly divisible by 10
] ; 

vValid

) //  end Let

// The Luhn Mod-10 algorithm is used to validate ( check for input errors ) of many credit cards and Canadian Social Security numbers. This is a custom function implementation of the method used by Brian dunning in his Card Validator example. The functions fnLuhnDoubleEven and fnLuhnProduct are required for it to operate and must be defined prior to defining this function.
// Source - http://www.briandunning.com/cf/87

fnLuhnDoubleEven

// fnLuhnDoubleEven ( makeEven )
If ( Length ( makeEven ) > 1 ; 
   2 * Middle ( makeEven ; 1 ; 1 ) & Middle ( makeEven ; 2 ; 1 ) & 
   fnLuhnDoubleEven ( Right ( makeEven ; Length ( makeEven ) - 2 ) ) ; "" )

// A component of the Luhn function. Take the number string supplied by Luhn and double every even position character of the original number. Adding 0 to the front of odd length number strings adjusted the original number for this to work.
// Source - http://www.briandunning.com/cf/88

fnLuhnProduct

// fnLuhnProduct ( doubleEven )
If ( Length ( doubleEven )  > 0 ; 
   Middle ( doubleEven ; 1 ; 1 ) + Middle ( doubleEven ; 2 ; 1 ) + 
   fnLuhnProduct ( Right ( doubleEven ; Length ( doubleEven )  - 2 )  ) ; "" )

// A component of the Luhn function. Add all the numbers together.
// Source - http://www.briandunning.com/cf/90

fnMimeType

// fnMimeType( medialocation ; default )

Let( [ 

medialocation = Trimall ( Lower ( GetAsText ( medialocation ) ) ; 0 ; 0 ) ; 

medialocation = Filter ( medialocation ; "abcdefghijklomnopqrstuvwxyz1234567890:./-?|!#@$%^&*()_+={}[]" ) ; 

default = If ( IsEmpty ( default ) ; "unknown" ; default )

] ; 

Case( IsEmpty ( medialocation ) ; "no media" ;
      Left( medialocation ; 1 ) = ">" ; "no media" ;

medialocation = "?" ; "Image file" ; // assume no extension as a WMF file

Right( medialocation ; 4 ) = ".php" ; "application/php" ;
Right( medialocation ; 4 ) = ".asp" ; "application/asp" ;
Right( medialocation ; 4 ) = ".cgi" ; "application/cgi" ;

//  173 Mime types compatable with podcasting  
//  http://en.wikipedia.org/wiki/Mime_type
Right( medialocation ; 3 ) = ".ai" ; "application/postscript" ;
Right( medialocation ; 4 ) = ".aif" ; "audio/x-aiff" ;
Right( medialocation ; 5 ) = ".aifc" ; "audio/x-aiff" ;
Right( medialocation ; 5 ) = ".aiff" ; "audio/x-aiff" ;
Right( medialocation ; 4 ) = ".asc" ; "text/plain" ;
Right( medialocation ; 5 ) = ".atom" ; "application/atom+xml" ;
Right( medialocation ; 3 ) = ".au" ; "audio/basic" ;
Right( medialocation ; 4 ) = ".avi" ; "video/x-msvideo" ;
Right( medialocation ; 6 ) = ".bcpio" ; "application/x-bcpio" ;
Right( medialocation ; 4 ) = ".bin" ; "application/octet-stream" ;
Right( medialocation ; 4 ) = ".bmp" ; "image/bmp" ;
Right( medialocation ; 4 ) = ".cdf" ; "application/x-netcdf" ;
Right( medialocation ; 4 ) = ".cgm" ; "image/cgm" ;
Right( medialocation ; 6 ) = ".class" ; "application/octet-stream" ;
Right( medialocation ; 5 ) = ".cpio" ; "application/x-cpio" ;
Right( medialocation ; 4 ) = ".cpt" ; "application/mac-compactpro" ;
Right( medialocation ; 4 ) = ".csh" ; "application/x-csh" ;
Right( medialocation ; 4 ) = ".css" ; "text/css" ;
Right( medialocation ; 4 ) = ".dcr" ; "application/x-director" ;
Right( medialocation ; 4 ) = ".dif" ; "video/x-dv" ;
Right( medialocation ; 4 ) = ".dir" ; "application/x-director" ;
Right( medialocation ; 4 ) = ".djv" ; "image/vnd.djvu" ;
Right( medialocation ; 5 ) = ".djvu" ; "image/vnd.djvu" ;
Right( medialocation ; 4 ) = ".dll" ; "application/octet-stream" ;
Right( medialocation ; 4 ) = ".dmg" ; "application/octet-stream" ;
Right( medialocation ; 4 ) = ".dms" ; "application/octet-stream" ;
Right( medialocation ; 4 ) = ".doc" ; "application/msword" ;
Right( medialocation ; 4 ) = ".dtd" ; "application/xml-dtd" ;
Right( medialocation ; 3 ) = ".dv" ; "video/x-dv" ;
Right( medialocation ; 4 ) = ".dvi" ; "application/x-dvi" ;
Right( medialocation ; 4 ) = ".dxr" ; "application/x-director" ;
Right( medialocation ; 4 ) = ".eps" ; "application/postscript" ;
Right( medialocation ; 4 ) = ".etx" ; "text/x-setext" ;
Right( medialocation ; 4 ) = ".exe" ; "application/octet-stream" ;
Right( medialocation ; 3 ) = ".ez" ; "application/andrew-inset" ;
Right( medialocation ; 4 ) = ".flv" ; "video/x-flv" ;
Right( medialocation ; 4 ) = ".gif" ; "image/gif" ;
Right( medialocation ; 5 ) = ".gram" ; "application/srgs" ;
Right( medialocation ; 6 ) = ".grxml" ; "application/srgs+xml" ;
Right( medialocation ; 5 ) = ".gtar" ; "application/x-gtar" ;
Right( medialocation ; 4 ) = ".hdf" ; "application/x-hdf" ;
Right( medialocation ; 4 ) = ".hqx" ; "application/mac-binhex40" ;
Right( medialocation ; 4 ) = ".htm" ; "text/html" ;
Right( medialocation ; 5 ) = ".html" ; "text/html" ;
Right( medialocation ; 4 ) = ".ice" ; "x-conference/x-cooltalk" ;
Right( medialocation ; 4 ) = ".ico" ; "image/x-icon" ;
Right( medialocation ; 4 ) = ".ics" ; "text/calendar" ;
Right( medialocation ; 4 ) = ".ief" ; "image/ief" ;
Right( medialocation ; 4 ) = ".ifb" ; "text/calendar" ;
Right( medialocation ; 4 ) = ".iges" ; "model/iges" ;
Right( medialocation ; 4 ) = ".igs" ; "model/iges" ;
Right( medialocation ; 5 ) = ".jnlp" ; "application/x-java-jnlp-file" ;
Right( medialocation ; 4 ) = ".jp2" ; "image/jp2" ;
Right( medialocation ; 4 ) = ".jpe" ; "image/jpeg" ;
Right( medialocation ; 4 ) = ".jpeg" ; "image/jpeg" ;
Right( medialocation ; 4 ) = ".jpg" ; "image/jpeg" ;
Right( medialocation ; 3 ) = ".js" ; "application/x-javascript" ;
Right( medialocation ; 4 ) = ".kar" ; "audio/midi" ;
Right( medialocation ; 6 ) = ".latex" ; "application/x-latex" ;
Right( medialocation ; 4 ) = ".lha" ; "application/octet-stream" ;
Right( medialocation ; 4 ) = ".lzh" ; "application/octet-stream" ;
Right( medialocation ; 4 ) = ".m3u" ; "audio/x-mpegurl" ;
Right( medialocation ; 4 ) = ".m4a" ; "audio/mp4a-latm" ;
Right( medialocation ; 4 ) = ".m4b" ; "audio/mp4a-latm" ;
Right( medialocation ; 4 ) = ".m4p" ; "audio/mp4a-latm" ;
Right( medialocation ; 4 ) = ".m4u" ; "video/vnd.mpegurl" ;
Right( medialocation ; 4 ) = ".m4v" ; "video/x-m4v" ;
Right( medialocation ; 4 ) = ".mac" ; "image/x-macpaint" ;
Right( medialocation ; 4 ) = ".man" ; "application/x-troff-man" ;
Right( medialocation ; 7 ) = ".mathml" ; "application/mathml+xml" ;
Right( medialocation ; 4 ) = ".me" ; "application/x-troff-me" ;
Right( medialocation ; 4 ) = ".mesh" ; "model/mesh" ;
Right( medialocation ; 4 ) = ".mid" ; "audio/midi" ;
Right( medialocation ; 4 ) = ".midi" ; "audio/midi" ;
Right( medialocation ; 4 ) = ".mif" ; "application/vnd.mif" ;
Right( medialocation ; 4 ) = ".mov" ; "video/quicktime" ;
Right( medialocation ; 4 ) = ".movie" ; "video/x-sgi-movie" ;
Right( medialocation ; 4 ) = ".mp2" ; "audio/mpeg" ;
Right( medialocation ; 4 ) = ".mp3" ; "audio/mpeg" ;
Right( medialocation ; 4 ) = ".mp4" ; "video/mp4" ;
Right( medialocation ; 4 ) = ".mpe" ; "video/mpeg" ;
Right( medialocation ; 4 ) = ".mpeg" ; "video/mpeg" ;
Right( medialocation ; 4 ) = ".mpg" ; "video/mpeg" ;
Right( medialocation ; 4 ) = ".mpga" ; "audio/mpeg" ;
Right( medialocation ; 4 ) = ".ms" ; "application/x-troff-ms" ;
Right( medialocation ; 4 ) = ".msh" ; "model/mesh" ;
Right( medialocation ; 4 ) = ".mxu" ; "video/vnd.mpegurl" ;
Right( medialocation ; 4 ) = ".nc" ; "application/x-netcdf" ;
Right( medialocation ; 4 ) = ".oda" ; "application/oda" ;
Right( medialocation ; 4 ) = ".ogg" ; "application/ogg" ;
Right( medialocation ; 4 ) = ".pbm" ; "image/x-portable-bitmap" ;
Right( medialocation ; 4 ) = ".pct" ; "image/pict" ;
Right( medialocation ; 4 ) = ".pdb" ; "chemical/x-pdb" ;
Right( medialocation ; 4 ) = ".pdf" ; "application/pdf" ;
Right( medialocation ; 4 ) = ".pgm" ; "image/x-portable-graymap" ;
Right( medialocation ; 4 ) = ".pgn" ; "application/x-chess-pgn" ;
Right( medialocation ; 4 ) = ".pic" ; "image/pict" ;
Right( medialocation ; 4 ) = ".pict" ; "image/pict" ;
Right( medialocation ; 4 ) = ".png" ; "image/png" ;
Right( medialocation ; 4 ) = ".pnm" ; "image/x-portable-anymap" ;
Right( medialocation ; 4 ) = ".pnt" ; "image/x-macpaint" ;
Right( medialocation ; 4 ) = ".pntg" ; "image/x-macpaint" ;
Right( medialocation ; 4 ) = ".ppm" ; "image/x-portable-pixmap" ;
Right( medialocation ; 4 ) = ".ppt" ; "application/vnd.ms-powerpoint" ;
Right( medialocation ; 4 ) = ".ps" ; "application/postscript" ;
Right( medialocation ; 4 ) = ".qt" ; "video/quicktime" ;
Right( medialocation ; 4 ) = ".qti" ; "image/x-quicktime" ;
Right( medialocation ; 4 ) = ".qtif" ; "image/x-quicktime" ;
Right( medialocation ; 4 ) = ".ra" ; "audio/x-pn-realaudio" ;
Right( medialocation ; 4 ) = ".ram" ; "audio/x-pn-realaudio" ;
Right( medialocation ; 4 ) = ".ras" ; "image/x-cmu-raster" ;
Right( medialocation ; 4 ) = ".rdf" ; "application/rdf+xml" ;
Right( medialocation ; 4 ) = ".rgb" ; "image/x-rgb" ;
Right( medialocation ; 4 ) = ".rm" ; "application/vnd.rn-realmedia" ;
Right( medialocation ; 4 ) = ".roff" ; "application/x-troff" ;
Right( medialocation ; 4 ) = ".rss" ; "application/rss+xml " ;
Right( medialocation ; 4 ) = ".rtf" ; "text/rtf" ;
Right( medialocation ; 4 ) = ".rtx" ; "text/richtext" ;
Right( medialocation ; 4 ) = ".sgm" ; "text/sgml" ;
Right( medialocation ; 4 ) = ".sgml" ; "text/sgml" ;
Right( medialocation ; 4 ) = ".sh" ; "application/x-sh" ;
Right( medialocation ; 4 ) = ".shar" ; "application/x-shar" ;
Right( medialocation ; 4 ) = ".silo" ; "model/mesh" ;
Right( medialocation ; 4 ) = ".sit" ; "application/x-stuffit" ;
Right( medialocation ; 4 ) = ".skd" ; "application/x-koan" ;
Right( medialocation ; 4 ) = ".skm" ; "application/x-koan" ;
Right( medialocation ; 4 ) = ".skp" ; "application/x-koan" ;
Right( medialocation ; 4 ) = ".skt" ; "application/x-koan" ;
Right( medialocation ; 4 ) = ".smi" ; "application/smil" ;
Right( medialocation ; 4 ) = ".smil" ; "application/smil" ;
Right( medialocation ; 4 ) = ".snd" ; "audio/basic" ;
Right( medialocation ; 4 ) = ".so" ; "application/octet-stream" ;
Right( medialocation ; 4 ) = ".spl" ; "application/x-futuresplash" ;
Right( medialocation ; 4 ) = ".src" ; "application/x-wais-source" ;
Right( medialocation ; 4 ) = ".sv4cpio" ; "application/x-sv4cpio" ;
Right( medialocation ; 4 ) = ".sv4crc" ; "application/x-sv4crc" ;
Right( medialocation ; 4 ) = ".svg" ; "image/svg+xml" ;
Right( medialocation ; 4 ) = ".swf" ; "application/x-shockwave-flash" ;
Right( medialocation ; 4 ) = ".t" ; "application/x-troff" ;
Right( medialocation ; 4 ) = ".tar" ; "application/x-tar" ;
Right( medialocation ; 4 ) = ".tcl" ; "application/x-tcl" ;
Right( medialocation ; 4 ) = ".tex" ; "application/x-tex" ;
Right( medialocation ; 4 ) = ".texi" ; "application/x-texinfo" ;
Right( medialocation ; 4 ) = ".texinfo" ; "application/x-texinfo" ;
Right( medialocation ; 4 ) = ".tif" ; "image/tiff" ;
Right( medialocation ; 4 ) = ".tiff" ; "image/tiff" ;
Right( medialocation ; 4 ) = ".tr" ; "application/x-troff" ;
Right( medialocation ; 4 ) = ".tsv" ; "text/tab-separated-values" ;
Right( medialocation ; 4 ) = ".txt" ; "text/plain" ;
Right( medialocation ; 4 ) = ".ustar" ; "application/x-ustar" ;
Right( medialocation ; 4 ) = ".vcd" ; "application/x-cdlink" ;
Right( medialocation ; 4 ) = ".vrml" ; "model/vrml" ;
Right( medialocation ; 4 ) = ".vxml" ; "application/voicexml+xml" ;
Right( medialocation ; 4 ) = ".wav" ; "audio/x-wav" ;
Right( medialocation ; 4 ) = ".wbmp" ; "image/vnd.wap.wbmp" ;
Right( medialocation ; 4 ) = ".wbmxl" ; "application/vnd.wap.wbxml" ;
Right( medialocation ; 4 ) = ".wmv" ; "video/x-ms-wmv" ;
Right( medialocation ; 4 ) = ".wml" ; "text/vnd.wap.wml" ;
Right( medialocation ; 4 ) = ".wmlc" ; "application/vnd.wap.wmlc" ;
Right( medialocation ; 4 ) = ".wmls" ; "text/vnd.wap.wmlscript" ;
Right( medialocation ; 4 ) = ".wmlsc" ; "application/vnd.wap.wmlscriptc" ;
Right( medialocation ; 4 ) = ".wrl" ; "model/vrml" ;
Right( medialocation ; 4 ) = ".xbm" ; "image/x-xbitmap" ;
Right( medialocation ; 4 ) = ".xht" ; "application/xhtml+xml" ;
Right( medialocation ; 4 ) = ".xhtml" ; "application/xhtml+xml" ;
Right( medialocation ; 4 ) = ".xls" ; "application/vnd.ms-excel" ;
Right( medialocation ; 4 ) = ".xml" ; "application/xml" ;
Right( medialocation ; 4 ) = ".xpm" ; "image/x-xpixmap" ;
Right( medialocation ; 4 ) = ".xsl" ; "application/xml" ;
Right( medialocation ; 4 ) = ".xslt" ; "application/xslt+xml" ;
Right( medialocation ; 4 ) = ".xul" ; "application/vnd.mozilla.xul+xml" ;
Right( medialocation ; 4 ) = ".xwd" ; "image/x-xwindowdump" ;
Right( medialocation ; 4 ) = ".xyz" ; "chemical/x-xyz" ;
Right( medialocation ; 4 ) = ".zip" ; "application/zip" ;

default )

)  //end Let


// Used by fnBuildRSSFeed function.
// Source - http://www.briandunning.com/cf/865

fnMonthToNum

// fnMonthToNum ( text )

Case(
   text = "January" ; 1 ; 
   text = "February" ; 2 ; 
   text = "March" ; 3 ; 
   text = "April" ; 4 ; 
   text = "May" ; 5 ; 
   text = "June" ; 6 ; 
   text = "July" ; 7 ; 
   text = "August" ; 8 ; 
   text = "September" ; 9 ; 
   text = "October" ; 10 ; 
   text = "November" ; 11 ; 
   text = "December" ; 12 ; 
   ""
) //end case

fnParseJSON

// fnParseJSON ( json_name ; json_data )
Let ( [ 
vStart = Position ( json_data ; Quote ( json_name ) ; 1 ; 1 ) + Length ( json_name ) + 5 
] ; 

Middle ( json_data ; vStart ; Position ( json_data ; "\"" ; vStart ; 1 ) - vStart )

)  //end Let


// Returns the value for the requested data from a block of JSON text.
// Source - Excelisys demo file for Ziptastic API.
// http://zip.elevenbasetwo.com/v2/US/20005

fnPhoneFormat

// fnPhoneFormat ( phone ; country )

Let ( [ 
vPhone11 = Trim ( Substitute ( phone ; [ ¶ ; " " ] ; [ Char(9) ; " " ] ; [ Char(160) ; " " ] ; [ "  " ; " " ] ) ) ; 
vPhone12 = Filter ( vPhone11 ; "0123456789" ) ; 
vAlpha11 = Filter ( vPhone11 ; "abcdefghjiklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ" ) ; 
vPhone21 = LeftWords ( vPhone11 ; 1 ) ; 
vPhone22 = Filter ( vPhone21 ; "0123456789" ) ; 
vAlpha21 = Filter ( vPhone21 ; "abcdefghjiklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ" ) 
] ; 

Case ( 
// If phone is 10 digits with no alpha, turn it into a phone number.
vAlpha11 = "" and Length ( vPhone12 ) = 10 and ( country = "" or country = "United States" ) ; 
   "(" & Left ( vPhone12 ; 3 ) & ") " & Middle ( vPhone12 ; 4 ; 3 ) & "-" & Middle ( vPhone12 ; 7 ; 4 ) ; 


// If first word of phone in 10 digits with no alpha, turn it into a phone number and add the other words after.
vAlpha21 = "" and Length ( vPhone22 ) = 10 and ( country = "" or country = "United States" ) ; 
   "(" & Left ( vPhone22 ; 3 ) & ") " & Middle ( vPhone22 ; 4 ; 3 ) & "-" & Middle ( vPhone22 ; 7 ; 4 ) & 
   " " & RightWords ( vPhone11 ; WordCount ( vPhone11 ) - 1 ) ; 

// Otherwise just use the cleaned up text value.
vPhone11 )  //end case

)  //end Let


// This formats 10-digit the phone numbers.  -rcaldwell 10/13/2011
// Will not change international phone numbers.

fnPieChart360

// fnPieChart360 ( RawValues ; ColorList ; ColorBkg ; ChartWidth ; ChartHeight ; ChartTitle )

Let ( [ 
vNum01 = LeftWords ( GetValue ( RawValues ; 1 ) ; 1 ) ; 
vLabel01 = RightWords ( GetValue ( RawValues ; 1 ) ; 
   WordCount ( GetValue ( RawValues ; 1 ) ) - 1 ) ; 
vNum02 = LeftWords ( GetValue ( RawValues ; 2 ) ; 1 ) ; 
vLabel02 = RightWords ( GetValue ( RawValues ; 2 ) ; 
   WordCount ( GetValue ( RawValues ; 2 ) ) - 1 ) ; 
vNum03 = LeftWords ( GetValue ( RawValues ; 3 ) ; 1 ) ; 
vLabel03 = RightWords ( GetValue ( RawValues ; 3 ) ; 
   WordCount ( GetValue ( RawValues ; 3 ) ) - 1 ) ; 
vNum04 = LeftWords ( GetValue ( RawValues ; 4 ) ; 1 ) ; 
vLabel04 = RightWords ( GetValue ( RawValues ; 4 ) ; 
   WordCount ( GetValue ( RawValues ; 4 ) ) - 1 ) ; 
vNum05 = LeftWords ( GetValue ( RawValues ; 5 ) ; 1 ) ; 
vLabel05 = RightWords ( GetValue ( RawValues ; 5 ) ; 
   WordCount ( GetValue ( RawValues ; 5 ) ) - 1 ) ; 
vNum06 = LeftWords ( GetValue ( RawValues ; 6 ) ; 1 ) ; 
vLabel06 = RightWords ( GetValue ( RawValues ; 6 ) ; 
   WordCount ( GetValue ( RawValues ; 6 ) ) - 1 ) ; 
vNum07 = LeftWords ( GetValue ( RawValues ; 7 ) ; 1 ) ; 
vLabel07 = RightWords ( GetValue ( RawValues ; 7 ) ; 
   WordCount ( GetValue ( RawValues ; 7 ) ) - 1 ) ; 
vNum08 = LeftWords ( GetValue ( RawValues ; 8 ) ; 1 ) ; 
vLabel08 = RightWords ( GetValue ( RawValues ; 8 ) ; 
   WordCount ( GetValue ( RawValues ; 8 ) ) - 1 ) ; 
vNum09 = LeftWords ( GetValue ( RawValues ; 9 ) ; 1 ) ; 
vLabel09 = RightWords ( GetValue ( RawValues ; 9 ) ; 
   WordCount ( GetValue ( RawValues ; 9 ) ) - 1 ) ; 
vNum10 = LeftWords ( GetValue ( RawValues ; 10 ) ; 1 ) ; 
vLabel10 = RightWords ( GetValue ( RawValues ; 10 ) ; 
   WordCount ( GetValue ( RawValues ; 10 ) ) - 1 ) ; 

vColorList = fnHexRGB ( 
   MiddleValues ( ColorList ; 2 ; 
   ValueCount ( ColorList ) ) ) ; 
vColor01 = Substitute ( GetValue ( vColorList ; 1 ) ; ";" ; ¶ ) ; 
vColor01R = GetValue ( vColor01 ; 1 ) ; 
vColor01G = GetValue ( vColor01 ; 2 ) ; 
vColor01B = GetValue ( vColor01 ; 3 ) ; 
vColor02 = Substitute ( GetValue ( vColorList ; 2 ) ; ";" ; ¶ ) ; 
vColor02R = GetValue ( vColor02 ; 1 ) ; 
vColor02G = GetValue ( vColor02 ; 2 ) ; 
vColor02B = GetValue ( vColor02 ; 3 ) ; 
vColor03 = Substitute ( GetValue ( vColorList ; 3 ) ; ";" ; ¶ ) ; 
vColor03R = GetValue ( vColor03 ; 1 ) ; 
vColor03G = GetValue ( vColor03 ; 2 ) ; 
vColor03B = GetValue ( vColor03 ; 3 ) ; 
vColor04 = Substitute ( GetValue ( vColorList ; 4 ) ; ";" ; ¶ ) ; 
vColor04R = GetValue ( vColor04 ; 1 ) ; 
vColor04G = GetValue ( vColor04 ; 2 ) ; 
vColor04B = GetValue ( vColor04 ; 3 ) ; 
vColor05 = Substitute ( GetValue ( vColorList ; 5 ) ; ";" ; ¶ ) ; 
vColor05R = GetValue ( vColor05 ; 1 ) ; 
vColor05G = GetValue ( vColor05 ; 2 ) ; 
vColor05B = GetValue ( vColor05 ; 3 ) ; 
vColor06 = Substitute ( GetValue ( vColorList ; 6 ) ; ";" ; ¶ ) ; 
vColor06R = GetValue ( vColor06 ; 1 ) ; 
vColor06G = GetValue ( vColor06 ; 2 ) ; 
vColor06B = GetValue ( vColor06 ; 3 ) ; 
vColor07 = Substitute ( GetValue ( vColorList ; 7 ) ; ";" ; ¶ ) ; 
vColor07R = GetValue ( vColor07 ; 1 ) ; 
vColor07G = GetValue ( vColor07 ; 2 ) ; 
vColor07B = GetValue ( vColor07 ; 3 ) ; 
vColor08 = Substitute ( GetValue ( vColorList ; 8 ) ; ";" ; ¶ ) ; 
vColor08R = GetValue ( vColor08 ; 1 ) ; 
vColor08G = GetValue ( vColor08 ; 2 ) ; 
vColor08B = GetValue ( vColor08 ; 3 ) ; 
vColor09 = Substitute ( GetValue ( vColorList ; 9 ) ; ";" ; ¶ ) ; 
vColor09R = GetValue ( vColor09 ; 1 ) ; 
vColor09G = GetValue ( vColor09 ; 2 ) ; 
vColor09B = GetValue ( vColor09 ; 3 ) ; 
vColor10 = Substitute ( GetValue ( vColorList ; 10 ) ; ";" ; ¶ ) ; 
vColor10R = GetValue ( vColor10 ; 1 ) ; 
vColor10G = GetValue ( vColor10 ; 2 ) ; 
vColor10B = GetValue ( vColor10 ; 3 ) ; 

vResult = pieChartCreate ( ChartTitle ; "3D" ) and 
   If ( vNum01 ; pieChartSetDataPoint ( vLabel01 ; vNum01 ) and 
      pieChartSetSeriesColor ( vLabel01 ; vColor01R ; vColor01G ; vColor01B ; 255 ) ) & 
   If ( vNum02 ; pieChartSetDataPoint ( vLabel02 ; vNum02 ) and 
      pieChartSetSeriesColor ( vLabel02 ; vColor02R ; vColor02G ; vColor02B ; 255 ) ) and 
   If ( vNum03 ; pieChartSetDataPoint ( vLabel03 ; vNum03 ) and 
      pieChartSetSeriesColor ( vLabel03 ; vColor03R ; vColor03G ; vColor03B ; 255 ) ) and 
   If ( vNum04 ; pieChartSetDataPoint ( vLabel04 ; vNum04 ) and 
      pieChartSetSeriesColor ( vLabel04 ; vColor04R ; vColor04G ; vColor04B ; 255 ) ) and 
   If ( vNum05 ; pieChartSetDataPoint ( vLabel05 ; vNum05 ) and 
      pieChartSetSeriesColor ( vLabel05 ; vColor05R ; vColor05G ; vColor05B ; 255 ) ) and 
   If ( vNum06 ; pieChartSetDataPoint ( vLabel06 ; vNum06 ) and 
      pieChartSetSeriesColor ( vLabel06 ; vColor06R ; vColor06G ; vColor06B ; 255 ) ) and 
   If ( vNum07 ; pieChartSetDataPoint ( vLabel07 ; vNum07 ) and 
      pieChartSetSeriesColor ( vLabel07 ; vColor07R ; vColor07G ; vColor07B ; 255 ) ) and 
   If ( vNum08 ; pieChartSetDataPoint ( vLabel08 ; vNum08 ) and 
      pieChartSetSeriesColor ( vLabel08 ; vColor08R ; vColor08G ; vColor08B ; 255 ) ) and 
   If ( vNum09 ; pieChartSetDataPoint ( vLabel09 ; vNum09 ) and 
      pieChartSetSeriesColor ( vLabel09 ; vColor09R ; vColor09G ; vColor09B ; 255 ) ) and 
   If ( vNum10 ; pieChartSetDataPoint ( vLabel10 ; vNum10 ) and 
      pieChartSetSeriesColor ( vLabel10 ; vColor10R ; vColor10G ; vColor10B ; 255 ) ) ; 

vBkgColor = Case ( 
   ColorBkg = "black" ; "0,0,0" ; 
   ColorBkg = "white" ; "255,255,255" ; 
   fnHexRGB ( ColorBkg )
)

] ; 

pieChartShowGraphic ( ChartWidth ; ChartHeight ; 
   //"explodeSeries=" & vLabel01 ; // set the first slice apart
   "backgroundColor=" & vBkgColor ; // background of header and legend
   "plotBackgroundColor=" & vBkgColor ; // background behind chart
   "outline=false" ; // don't outline the pie graphic
   "legend=false" ; // hide the legend
   "label={1} {0}%" ; // {0} is item name, {1} is amount, and {2} is percent
   //"hideLabels=true" // remove category on pie graphic
)  //end pieChartShowGraphic

)  //end Let


// Takes data in the same format as fnPieChartSVG, but turns it into a 360Works pie chart instead of an SVG.
// FontSize and ShowZeros are not available options in 360Works Chart plugin.
// Set a container field with the output of this formula for a pie chart.
/* 
Sample RawValues:
4 Error
1 Manual
9 To Review
10 To Archive
356 To Email
461 To Print
*/

fnPieChartSVG

// fnPieChartSVG ( RawValues ; ColorList ; ColorText ; ColorBkg ; ChartSize ; FontSize ; ShowZeros ; ChartTitle )

Let ( [ 

vLegendIcon = "█" ; 

vKey01 = GetValue ( RawValues ; 1 ) ; 
vKey02 = GetValue ( RawValues ; 2 ) ; 
vKey03 = GetValue ( RawValues ; 3 ) ; 
vKey04 = GetValue ( RawValues ; 4 ) ; 
vKey05 = GetValue ( RawValues ; 5 ) ; 
vKey06 = GetValue ( RawValues ; 6 ) ; 
vKey07 = GetValue ( RawValues ; 7 ) ; 
vKey08 = GetValue ( RawValues ; 8 ) ; 
vKey09 = GetValue ( RawValues ; 9 ) ; 
vKey10 = GetValue ( RawValues ; 10 ) ; 
vKey11 = GetValue ( RawValues ; 11 ) ; 
vKey12 = GetValue ( RawValues ; 12 ) ; 
vKey13 = GetValue ( RawValues ; 13 ) ; 
vKey14 = GetValue ( RawValues ; 14 ) ; 
vKey15 = GetValue ( RawValues ; 15 ) ; 
vKey16 = GetValue ( RawValues ; 16 ) ; 
vKey17 = GetValue ( RawValues ; 17 ) ; 
vKey18 = GetValue ( RawValues ; 18 ) ; 
vKey19 = GetValue ( RawValues ; 19 ) ; 
vKey20 = GetValue ( RawValues ; 20 ) ; 

vNum01 = LeftWords ( vKey01 ; 1 ) ; 
vNum02 = LeftWords ( vKey02 ; 1 ) ; 
vNum03 = LeftWords ( vKey03 ; 1 ) ; 
vNum04 = LeftWords ( vKey04 ; 1 ) ; 
vNum05 = LeftWords ( vKey05 ; 1 ) ; 
vNum06 = LeftWords ( vKey06 ; 1 ) ; 
vNum07 = LeftWords ( vKey07 ; 1 ) ; 
vNum08 = LeftWords ( vKey08 ; 1 ) ; 
vNum09 = LeftWords ( vKey09 ; 1 ) ; 
vNum10 = LeftWords ( vKey10 ; 1 ) ; 
vNum11 = LeftWords ( vKey11 ; 1 ) ; 
vNum12 = LeftWords ( vKey12 ; 1 ) ; 
vNum13 = LeftWords ( vKey13 ; 1 ) ; 
vNum14 = LeftWords ( vKey14 ; 1 ) ; 
vNum15 = LeftWords ( vKey15 ; 1 ) ; 
vNum16 = LeftWords ( vKey16 ; 1 ) ; 
vNum17 = LeftWords ( vKey17 ; 1 ) ; 
vNum18 = LeftWords ( vKey18 ; 1 ) ; 
vNum19 = LeftWords ( vKey19 ; 1 ) ; 
vNum20 = LeftWords ( vKey20 ; 1 ) ; 
vTotal = 
   vNum01 + vNum02 + vNum03 + vNum04 + vNum05 + 
   vNum06 + vNum07 + vNum08 + vNum09 + vNum10 + 
   vNum11 + vNum12 + vNum13 + vNum14 + vNum15 + 
   vNum16 + vNum17 + vNum18 + vNum19 + vNum20 ; 

vPct01 = vNum01 / vTotal * 100 ; 
vPct02 = vNum02 / vTotal * 100 ; 
vPct03 = vNum03 / vTotal * 100 ; 
vPct04 = vNum04 / vTotal * 100 ; 
vPct05 = vNum05 / vTotal * 100 ; 
vPct06 = vNum06 / vTotal * 100 ; 
vPct07 = vNum07 / vTotal * 100 ; 
vPct08 = vNum08 / vTotal * 100 ; 
vPct09 = vNum09 / vTotal * 100 ; 
vPct10 = vNum10 / vTotal * 100 ; 
vPct11 = vNum11 / vTotal * 100 ; 
vPct12 = vNum12 / vTotal * 100 ; 
vPct13 = vNum13 / vTotal * 100 ; 
vPct14 = vNum14 / vTotal * 100 ; 
vPct15 = vNum15 / vTotal * 100 ; 
vPct16 = vNum16 / vTotal * 100 ; 
vPct17 = vNum17 / vTotal * 100 ; 
vPct18 = vNum18 / vTotal * 100 ; 
vPct19 = vNum19 / vTotal * 100 ; 
vPct20 = vNum20 / vTotal * 100 ; 

// vPctSum used when building pie slices
vPctSum10 = 
   vPct01 + vPct02 + vPct03 + vPct04 + vPct05 + 
   vPct06 + vPct07 + vPct08 + vPct09 + vPct10 ; 
vPctSum20 = 
   vPct11 + vPct12 + vPct13 + vPct14 + vPct15 + 
   vPct16 + vPct17 + vPct18 + vPct19 + vPct20 ; 

vPieColorSlice01 = GetValue ( ColorList ; 2 ) ; 
vPieColorSlice02 = GetValue ( ColorList ; 3 ) ; 
vPieColorSlice03 = GetValue ( ColorList ; 4 ) ; 
vPieColorSlice04 = GetValue ( ColorList ; 5 ) ; 
vPieColorSlice05 = GetValue ( ColorList ; 6 ) ; 
vPieColorSlice06 = GetValue ( ColorList ; 7 ) ; 
vPieColorSlice07 = GetValue ( ColorList ; 8 ) ; 
vPieColorSlice08 = GetValue ( ColorList ; 9 ) ; 
vPieColorSlice09 = GetValue ( ColorList ; 10 ) ; 
vPieColorSlice10 = GetValue ( ColorList ; 11 ) ; 
vPieColorSlice11 = GetValue ( ColorList ; 12 ) ; 
vPieColorSlice12 = GetValue ( ColorList ; 13 ) ; 
vPieColorSlice13 = GetValue ( ColorList ; 14 ) ; 
vPieColorSlice14 = GetValue ( ColorList ; 15 ) ; 
vPieColorSlice15 = GetValue ( ColorList ; 16 ) ; 
vPieColorSlice16 = GetValue ( ColorList ; 17 ) ; 
vPieColorSlice17 = GetValue ( ColorList ; 18 ) ; 
vPieColorSlice18 = GetValue ( ColorList ; 19 ) ; 
vPieColorSlice19 = GetValue ( ColorList ; 20 ) ; 
vPieColorSlice20 = GetValue ( ColorList ; 21 ) 

] ; 

Case ( 
RawValues = "" ; "" ; 
ValueCount ( RawValues ) < 2 ;   // single line of data may show an error or warning
   "data:text/html,<html><body style='color: " & 
   ColorText & "; background-color: " & 
   ColorBkg & "'>" & RawValues & "</body></html>" ; 

"data:text/html,<html>¶<style>¶body { " & 
   "font: " & FontSize & "px/100% Arial, Helvetica, sans-serif; color: " & ColorText & 
   "; background-color: " & ColorBkg & 
   "; margin: 0; padding: 0; border: 0; overflow: hidden; }¶" & 
   "svg { width: " & ChartSize & "px; " & 
   "height: " & ChartSize & "px; transform: rotate(-90deg); " & 
   "background: " & ColorBkg & 
   "; border-radius: 50%; float: left; }¶</style>¶<body>¶<svg viewBox='0 0 32 32'>¶" & 

// BUILD THE PIE

If ( ShowZeros ; 

If ( vNum20 ≠ "" ; "<circle r='16' cx='16' cy='16' fill='none' stroke='" & 
   vPieColorSlice20 & "' stroke-width='32' stroke-dasharray='" & 
   vPctSum10 + vPctSum20 & " 100' />¶" ) & 
If ( vNum19 ≠ "" ; "<circle r='16' cx='16' cy='16' fill='none' stroke='" & 
   vPieColorSlice19 & "' stroke-width='32' stroke-dasharray='" & 
   vPctSum10 + vPct11 + vPct12 + vPct13 + vPct14 + vPct15 + 
   vPct16 + vPct17 + vPct18 + vPct19 & " 100' />¶" ) & 
If ( vNum18 ≠ "" ; "<circle r='16' cx='16' cy='16' fill='none' stroke='" & 
   vPieColorSlice18 & "' stroke-width='32' stroke-dasharray='" & 
   vPctSum10 + vPct11 + vPct12 + vPct13 + vPct14 + vPct15 + 
   vPct16 + vPct17 + vPct18 & " 100' />¶" ) & 
If ( vNum17 ≠ "" ; "<circle r='16' cx='16' cy='16' fill='none' stroke='" & 
   vPieColorSlice17 & "' stroke-width='32' stroke-dasharray='" & 
   vPctSum10 + vPct11 + vPct12 + vPct13 + vPct14 + vPct15 + vPct16 + vPct17 & " 100' />¶" ) & 
If ( vNum16 ≠ "" ; "<circle r='16' cx='16' cy='16' fill='none' stroke='" & 
   vPieColorSlice16 & "' stroke-width='32' stroke-dasharray='" & 
   vPctSum10 + vPct11 + vPct12 + vPct13 + vPct14 + vPct15 + vPct16 & " 100' />¶" ) & 
If ( vNum15 ≠ "" ; "<circle r='16' cx='16' cy='16' fill='none' stroke='" & 
   vPieColorSlice15 & "' stroke-width='32' stroke-dasharray='" & 
   vPctSum10 + vPct11 + vPct12 + vPct13 + vPct14 + vPct15 & " 100' />¶" ) & 
If ( vNum14 ≠ "" ; "<circle r='16' cx='16' cy='16' fill='none' stroke='" & 
   vPieColorSlice14 & "' stroke-width='32' stroke-dasharray='" & 
   vPctSum10 + vPct11 + vPct12 + vPct13 + vPct14 & " 100' />¶" ) & 
If ( vNum13 ≠ "" ; "<circle r='16' cx='16' cy='16' fill='none' stroke='" & 
   vPieColorSlice13 & "' stroke-width='32' stroke-dasharray='" & 
   vPctSum10 + vPct11 + vPct12 + vPct13 & " 100' />¶" ) & 
If ( vNum12 ≠ "" ; "<circle r='16' cx='16' cy='16' fill='none' stroke='" & 
   vPieColorSlice12 & "' stroke-width='32' stroke-dasharray='" & 
   vPctSum10 + vPct11 + vPct12 & " 100' />¶" ) & 
If ( vNum11 ≠ "" ; "<circle r='16' cx='16' cy='16' fill='none' stroke='" & 
   vPieColorSlice11 & "' stroke-width='32' stroke-dasharray='" & 
   vPctSum10 + vPct11 & " 100' />¶" ) & 

If ( vNum10 ≠ "" ; "<circle r='16' cx='16' cy='16' fill='none' stroke='" & 
   vPieColorSlice10 & "' stroke-width='32' stroke-dasharray='" & 
   vPctSum10 & " 100' />¶" ) & 
If ( vNum09 ≠ "" ; "<circle r='16' cx='16' cy='16' fill='none' stroke='" & 
   vPieColorSlice09 & "' stroke-width='32' stroke-dasharray='" & 
   vPct01 + vPct02 + vPct03 + vPct04 + vPct05 + 
   vPct06 + vPct07 + vPct08 + vPct09 & " 100' />¶" ) & 
If ( vNum08 ≠ "" ; "<circle r='16' cx='16' cy='16' fill='none' stroke='" & 
   vPieColorSlice08 & "' stroke-width='32' stroke-dasharray='" & 
   vPct01 + vPct02 + vPct03 + vPct04 + vPct05 + 
   vPct06 + vPct07 + vPct08 & " 100' />¶" ) & 
If ( vNum07 ≠ "" ; "<circle r='16' cx='16' cy='16' fill='none' stroke='" & 
   vPieColorSlice07 & "' stroke-width='32' stroke-dasharray='" & 
   vPct01 + vPct02 + vPct03 + vPct04 + vPct05 + vPct06 + vPct07 & " 100' />¶" ) & 
If ( vNum06 ≠ "" ; "<circle r='16' cx='16' cy='16' fill='none' stroke='" & 
   vPieColorSlice06 & "' stroke-width='32' stroke-dasharray='" & 
   vPct01 + vPct02 + vPct03 + vPct04 + vPct05 + vPct06 & " 100' />¶" ) & 
If ( vNum05 ≠ "" ; "<circle r='16' cx='16' cy='16' fill='none' stroke='" & 
   vPieColorSlice05 & "' stroke-width='32' stroke-dasharray='" & 
   vPct01 + vPct02 + vPct03 + vPct04 + vPct05 & " 100' />¶" ) & 
If ( vNum04 ≠ "" ; "<circle r='16' cx='16' cy='16' fill='none' stroke='" & 
   vPieColorSlice04 & "' stroke-width='32' stroke-dasharray='" & 
   vPct01 + vPct02 + vPct03 + vPct04 & " 100' />¶" ) & 
If ( vNum03 ≠ "" ; "<circle r='16' cx='16' cy='16' fill='none' stroke='" & 
   vPieColorSlice03 & "' stroke-width='32' stroke-dasharray='" & 
   vPct01 + vPct02 + vPct03 & " 100' />¶" ) & 
If ( vNum02 ≠ "" ; "<circle r='16' cx='16' cy='16' fill='none' stroke='" & 
   vPieColorSlice02 & "' stroke-width='32' stroke-dasharray='" & 
   vPct01 + vPct02 & " 100' />¶" ) & 
If ( vNum01 ≠ "" ; "<circle r='16' cx='16' cy='16' fill='none' stroke='" & 
   vPieColorSlice01 & "' stroke-width='32' stroke-dasharray='" & 
   vPct01 & " 100' />¶" )

; 

// Don't Show Zeros

If ( vNum20 ; "<circle r='16' cx='16' cy='16' fill='none' stroke='" & 
   vPieColorSlice20 & "' stroke-width='32' stroke-dasharray='" & 
   vPctSum10 + vPctSum20 & " 100' />¶" ) & 
If ( vNum19 ; "<circle r='16' cx='16' cy='16' fill='none' stroke='" & 
   vPieColorSlice19 & "' stroke-width='32' stroke-dasharray='" & 
   vPctSum10 + vPct11 + vPct12 + vPct13 + vPct14 + vPct15 + 
   vPct16 + vPct17 + vPct18 + vPct19 & " 100' />¶" ) & 
If ( vNum18 ; "<circle r='16' cx='16' cy='16' fill='none' stroke='" & 
   vPieColorSlice18 & "' stroke-width='32' stroke-dasharray='" & 
   vPctSum10 + vPct11 + vPct12 + vPct13 + vPct14 + vPct15 + 
   vPct16 + vPct17 + vPct18 & " 100' />¶" ) & 
If ( vNum17 ; "<circle r='16' cx='16' cy='16' fill='none' stroke='" & 
   vPieColorSlice17 & "' stroke-width='32' stroke-dasharray='" & 
   vPctSum10 + vPct11 + vPct12 + vPct13 + vPct14 + vPct15 + vPct16 + vPct17 & " 100' />¶" ) & 
If ( vNum16 ; "<circle r='16' cx='16' cy='16' fill='none' stroke='" & 
   vPieColorSlice16 & "' stroke-width='32' stroke-dasharray='" & 
   vPctSum10 + vPct11 + vPct12 + vPct13 + vPct14 + vPct15 + vPct16 & " 100' />¶" ) & 
If ( vNum15 ; "<circle r='16' cx='16' cy='16' fill='none' stroke='" & 
   vPieColorSlice15 & "' stroke-width='32' stroke-dasharray='" & 
   vPctSum10 + vPct11 + vPct12 + vPct13 + vPct14 + vPct15 & " 100' />¶" ) & 
If ( vNum14 ; "<circle r='16' cx='16' cy='16' fill='none' stroke='" & 
   vPieColorSlice14 & "' stroke-width='32' stroke-dasharray='" & 
   vPctSum10 + vPct11 + vPct12 + vPct13 + vPct14 & " 100' />¶" ) & 
If ( vNum13 ; "<circle r='16' cx='16' cy='16' fill='none' stroke='" & 
   vPieColorSlice13 & "' stroke-width='32' stroke-dasharray='" & 
   vPctSum10 + vPct11 + vPct12 + vPct13 & " 100' />¶" ) & 
If ( vNum12 ; "<circle r='16' cx='16' cy='16' fill='none' stroke='" & 
   vPieColorSlice12 & "' stroke-width='32' stroke-dasharray='" & 
   vPctSum10 + vPct11 + vPct12 & " 100' />¶" ) & 
If ( vNum11 ; "<circle r='16' cx='16' cy='16' fill='none' stroke='" & 
   vPieColorSlice11 & "' stroke-width='32' stroke-dasharray='" & 
   vPctSum10 + vPct11 & " 100' />¶" ) & 

If ( vNum10 ; "<circle r='16' cx='16' cy='16' fill='none' stroke='" & 
   vPieColorSlice10 & "' stroke-width='32' stroke-dasharray='" & 
   vPctSum10 & " 100' />¶" ) & 
If ( vNum09 ; "<circle r='16' cx='16' cy='16' fill='none' stroke='" & 
   vPieColorSlice09 & "' stroke-width='32' stroke-dasharray='" & 
   vPct01 + vPct02 + vPct03 + vPct04 + vPct05 + 
   vPct06 + vPct07 + vPct08 + vPct09 & " 100' />¶" ) & 
If ( vNum08 ; "<circle r='16' cx='16' cy='16' fill='none' stroke='" & 
   vPieColorSlice08 & "' stroke-width='32' stroke-dasharray='" & 
   vPct01 + vPct02 + vPct03 + vPct04 + vPct05 + vPct06 + vPct07 + vPct08 & " 100' />¶" ) & 
If ( vNum07 ; "<circle r='16' cx='16' cy='16' fill='none' stroke='" & 
   vPieColorSlice07 & "' stroke-width='32' stroke-dasharray='" & 
   vPct01 + vPct02 + vPct03 + vPct04 + vPct05 + vPct06 + vPct07 & " 100' />¶" ) & 
If ( vNum06 ; "<circle r='16' cx='16' cy='16' fill='none' stroke='" & 
   vPieColorSlice06 & "' stroke-width='32' stroke-dasharray='" & 
   vPct01 + vPct02 + vPct03 + vPct04 + vPct05 + vPct06 & " 100' />¶" ) & 
If ( vNum05 ; "<circle r='16' cx='16' cy='16' fill='none' stroke='" & 
   vPieColorSlice05 & "' stroke-width='32' stroke-dasharray='" & 
   vPct01 + vPct02 + vPct03 + vPct04 + vPct05 & " 100' />¶" ) & 
If ( vNum04 ; "<circle r='16' cx='16' cy='16' fill='none' stroke='" & 
   vPieColorSlice04 & "' stroke-width='32' stroke-dasharray='" & 
   vPct01 + vPct02 + vPct03 + vPct04 & " 100' />¶" ) & 
If ( vNum03 ; "<circle r='16' cx='16' cy='16' fill='none' stroke='" & 
   vPieColorSlice03 & "' stroke-width='32' stroke-dasharray='" & 
   vPct01 + vPct02 + vPct03 & " 100' />¶" ) & 
If ( vNum02 ; "<circle r='16' cx='16' cy='16' fill='none' stroke='" & 
   vPieColorSlice02 & "' stroke-width='32' stroke-dasharray='" & 
   vPct01 + vPct02 & " 100' />¶" ) & 
If ( vNum01 ; "<circle r='16' cx='16' cy='16' fill='none' stroke='" & 
   vPieColorSlice01 & "' stroke-width='32' stroke-dasharray='" & 
   vPct01 & " 100' />¶" )

) &  //end show zeros


"¶</svg>¶<div style='padding-left:" & ChartSize * 1.3 & "px; line-height:1.4em;'>¶" & 
if ( ChartTitle  ≠ "" ; ChartTitle & "<br />¶" ) & 

// BUILD THE LEGEND

//If ( vTotal ; vTotal & "<br />¶" ) & 

If ( ShowZeros ; 

If ( vNum01 ≠ "" ; "<span style='color: " & vPieColorSlice01 & ";'>" & 
   vLegendIcon & "</span>&nbsp; " & vKey01 & "<br />¶" ) & 
If ( vNum02 ≠ "" ; "<span style='color: " & vPieColorSlice02 & ";'>" & 
   vLegendIcon & "</span>&nbsp; " & vKey02 & "<br />¶" ) & 
If ( vNum03 ≠ "" ; "<span style='color: " & vPieColorSlice03 & ";'>" & 
   vLegendIcon & "</span>&nbsp; " & vKey03 & "<br />¶" ) & 
If ( vNum04 ≠ "" ; "<span style='color: " & vPieColorSlice04 & ";'>" & 
   vLegendIcon & "</span>&nbsp; " & vKey04 & "<br />¶" ) & 
If ( vNum05 ≠ "" ; "<span style='color: " & vPieColorSlice05 & ";'>" & 
   vLegendIcon & "</span>&nbsp; " & vKey05 & "<br />¶" ) & 
If ( vNum06 ≠ "" ; "<span style='color: " & vPieColorSlice06 & ";'>" & 
   vLegendIcon & "</span>&nbsp; " & vKey06 & "<br />¶" ) & 
If ( vNum07 ≠ "" ; "<span style='color: " & vPieColorSlice07 & ";'>" & 
   vLegendIcon & "</span>&nbsp; " & vKey07 & "<br />¶" ) & 
If ( vNum08 ≠ "" ; "<span style='color: " & vPieColorSlice08 & ";'>" & 
   vLegendIcon & "</span>&nbsp; " & vKey08 & "<br />¶" ) & 
If ( vNum09 ≠ "" ; "<span style='color: " & vPieColorSlice09 & ";'>" & 
   vLegendIcon & "</span>&nbsp; " & vKey09 & "<br />¶" ) & 
If ( vNum10 ≠ "" ; "<span style='color: " & vPieColorSlice10 & ";'>" & 
   vLegendIcon & "</span>&nbsp; " & vKey10 & "<br />¶" ) & 
If ( vNum11 ≠ "" ; "<span style='color: " & vPieColorSlice11 & ";'>" & 
   vLegendIcon & "</span>&nbsp; " & vKey11 & "<br />¶" ) & 
If ( vNum12 ≠ "" ; "<span style='color: " & vPieColorSlice12 & ";'>" & 
   vLegendIcon & "</span>&nbsp; " & vKey12 & "<br />¶" ) & 
If ( vNum13 ≠ "" ; "<span style='color: " & vPieColorSlice13 & ";'>" & 
   vLegendIcon & "</span>&nbsp; " & vKey13 & "<br />¶" ) & 
If ( vNum14 ≠ "" ; "<span style='color: " & vPieColorSlice14 & ";'>" & 
   vLegendIcon & "</span>&nbsp; " & vKey14 & "<br />¶" ) & 
If ( vNum15 ≠ "" ; "<span style='color: " & vPieColorSlice15 & ";'>" & 
   vLegendIcon & "</span>&nbsp; " & vKey15 & "<br />¶" ) & 
If ( vNum16 ≠ "" ; "<span style='color: " & vPieColorSlice16 & ";'>" & 
   vLegendIcon & "</span>&nbsp; " & vKey16 & "<br />¶" ) & 
If ( vNum17 ≠ "" ; "<span style='color: " & vPieColorSlice17 & ";'>" & 
   vLegendIcon & "</span>&nbsp; " & vKey17 & "<br />¶" ) & 
If ( vNum18 ≠ "" ; "<span style='color: " & vPieColorSlice18 & ";'>" & 
   vLegendIcon & "</span>&nbsp; " & vKey18 & "<br />¶" ) & 
If ( vNum19 ≠ "" ; "<span style='color: " & vPieColorSlice19 & ";'>" & 
   vLegendIcon & "</span>&nbsp; " & vKey19 & "<br />¶" ) & 
If ( vNum20 ≠ "" ; "<span style='color: " & vPieColorSlice20 & ";'>" & 
   vLegendIcon & "</span>&nbsp; " & vKey20 & "<br />¶" )

; 

// Don't Show Zeros

If ( vNum01 ; "<span style='color: " & vPieColorSlice01 & ";'>" & 
   vLegendIcon & "</span>&nbsp; " & vKey01 & "<br />¶" ) & 
If ( vNum02 ; "<span style='color: " & vPieColorSlice02 & ";'>" & 
   vLegendIcon & "</span>&nbsp; " & vKey02 & "<br />¶" ) & 
If ( vNum03 ; "<span style='color: " & vPieColorSlice03 & ";'>" & 
   vLegendIcon & "</span>&nbsp; " & vKey03 & "<br />¶" ) & 
If ( vNum04 ; "<span style='color: " & vPieColorSlice04 & ";'>" & 
   vLegendIcon & "</span>&nbsp; " & vKey04 & "<br />¶" ) & 
If ( vNum05 ; "<span style='color: " & vPieColorSlice05 & ";'>" & 
   vLegendIcon & "</span>&nbsp; " & vKey05 & "<br />¶" ) & 
If ( vNum06 ; "<span style='color: " & vPieColorSlice06 & ";'>" & 
   vLegendIcon & "</span>&nbsp; " & vKey06 & "<br />¶" ) & 
If ( vNum07 ; "<span style='color: " & vPieColorSlice07 & ";'>" & 
   vLegendIcon & "</span>&nbsp; " & vKey07 & "<br />¶" ) & 
If ( vNum08 ; "<span style='color: " & vPieColorSlice08 & ";'>" & 
   vLegendIcon & "</span>&nbsp; " & vKey08 & "<br />¶" ) & 
If ( vNum09 ; "<span style='color: " & vPieColorSlice09 & ";'>" & 
   vLegendIcon & "</span>&nbsp; " & vKey09 & "<br />¶" ) & 
If ( vNum10 ; "<span style='color: " & vPieColorSlice10 & ";'>" & 
   vLegendIcon & "</span>&nbsp; " & vKey10 & "<br />¶" ) & 
If ( vNum11 ; "<span style='color: " & vPieColorSlice11 & ";'>" & 
   vLegendIcon & "</span>&nbsp; " & vKey11 & "<br />¶" ) & 
If ( vNum12 ; "<span style='color: " & vPieColorSlice12 & ";'>" & 
   vLegendIcon & "</span>&nbsp; " & vKey12 & "<br />¶" ) & 
If ( vNum13 ; "<span style='color: " & vPieColorSlice13 & ";'>" & 
   vLegendIcon & "</span>&nbsp; " & vKey13 & "<br />¶" ) & 
If ( vNum14 ; "<span style='color: " & vPieColorSlice14 & ";'>" & 
   vLegendIcon & "</span>&nbsp; " & vKey14 & "<br />¶" ) & 
If ( vNum15 ; "<span style='color: " & vPieColorSlice15 & ";'>" & 
   vLegendIcon & "</span>&nbsp; " & vKey15 & "<br />¶" ) & 
If ( vNum16 ; "<span style='color: " & vPieColorSlice16 & ";'>" & 
   vLegendIcon & "</span>&nbsp; " & vKey16 & "<br />¶" ) & 
If ( vNum17 ; "<span style='color: " & vPieColorSlice17 & ";'>" & 
   vLegendIcon & "</span>&nbsp; " & vKey17 & "<br />¶" ) & 
If ( vNum18 ; "<span style='color: " & vPieColorSlice18 & ";'>" & 
   vLegendIcon & "</span>&nbsp; " & vKey18 & "<br />¶" ) & 
If ( vNum19 ; "<span style='color: " & vPieColorSlice19 & ";'>" & 
   vLegendIcon & "</span>&nbsp; " & vKey19 & "<br />¶" ) & 
If ( vNum20 ; "<span style='color: " & vPieColorSlice20 & ";'>" & 
   vLegendIcon & "</span>&nbsp; " & vKey20 & "<br />¶" )

) &  //end if show zeros


"</div>¶</body>¶</html>"

)  //end case raw value

)  //end Let




// Quick and easy pie chart using a web viewer.
// Example Chart:  fnPieChartSVG ( "10 Orange¶10 Pink¶10 Light Green" ; "Lea Verou Colors¶#fb3¶deeppink¶yellowgreen" ; "#222222" ; "#FFF3D9" ; 60 ; 12 ; False ; "My Pie Chart" )

// Parameters:
// 1. RawValues, number (space) description, return separated
// 2. ColorList, first item in the list is ignored as the name of the color list
//      eg. "Rainbow¶#ae141e¶#e12229¶#e3902a¶#e1cb3a¶#8ac43a¶#1f9735¶#008ed6¶#0058b1¶#6b249e¶#9a1c97"
// 3. ColorText, text could be a dark gray, almost black, like:  #222222
// 4. ColorBkg, background color could be parchment, like:  #FFF3D9
// 5. ChartSize, in pixels
// 6. FontSize, in points
// 7. ShowZeros, 'False' will hide any raw value lines where the number is zero
// 8. ChartTitle, the label to go above the list of raw values

// Based on Lea Verou 2015 - https://www.smashingmagazine.com/2015/07/designing-simple-pie-charts-with-css/

// TIPS:
// Make RawValues a global variable and assign it as the tooltip for the web viewer so if the data list is longer than the web viewer, users can still see all the data in the tooltip.
// The numbers in raw values do not need to add up to 1 or 100, the percent is calculated for you.
// Pre-sort the raw values in the order you wish them to appear on the chart.
// Color for the first value will appear starting at the noon position.  The rest will follow clockwise.
// Be sure there are at least as many colors as there are values to chart.  You don't want to run out of colors.
// This function is currently limited to charting a maximum of 20 raw values.
// This was built for the National Geographic Books database.  I love this pie chart tool, but boy did it take a long time to build.  SVG is not the easiest language to learn.

// 2/2017 - Dev Note: SVG charting does not work with FileMaker 13 on Windows because IE does not support SVG natively.  It does work with FileMaker 16 and Windows 7.
// 1/2018 - ChartTitle parameter added.  Fixed vPct variables by changing vKey numerator to vNum.
// 1/2019 - Cleaned up notes and setup a better sample chart.

fnPieChart_sample_2018 html

fnPieChart_sample_2018 png

fnPositionValue

// fnPositionValue ( text ; search ; start ; occurrence )
Let ( [ 
vPosition = Position (  ¶& text &¶ ; ¶& search &¶ ; start ; occurrence ) 
] ;

If ( vPosition = 0 ; 0 ;
   ValueCount ( Left ( text ; ( vPosition - 1 ) + Length ( search ) ) )
)

)  //end Let


// Same as the Position function but for return separated values.
// Searches for the 'search' value in a list of text returning the value position.
// Source - http://www.briandunning.com/cf/62

fnProper

// fnProper ( text ; empty_quotes )
Let ( [ 

vPrefixList = "Mc" ; 
vAlwaysLower = "a¶an¶and¶aka¶at¶by¶for¶ie¶in¶is¶it¶nor¶of¶on¶or¶the¶to¶vs¶vs.¶w/¶with"; 
vUpStates = "AL¶AK¶AZ¶AR¶CA¶CO¶CT¶DC¶FL¶GA¶HI¶ID¶IL¶IA¶KS¶KY¶LA¶MD¶MA¶MI¶MN¶MS¶MO¶MT¶NE¶NV¶NH¶NJ¶NM¶NY¶NC¶ND¶OH¶OK¶OR¶PA¶RI¶SC¶SD¶TN¶TX¶UT¶VT¶VA¶WA¶DC¶DC¶DC¶WV¶WI¶WY"; 
vUpOther = "ABC¶AC¶BC¶CD¶DJ¶EMI¶EP¶H2O¶INXS¶MC¶MTV¶NRG¶NS¶NYC¶PR¶QB¶RCA¶REM¶TV¶UB40¶UK¶USA¶USSR¶"; 
vAlwaysUpper = list ( vUpStates ; vUpOther ) ; 
// Indiana (IN) competes with the all-lower 'in'.
// Delaware (DE) competes with Portia de Rossi.
// Maine (ME) competes with 'me'.

vPrevText = empty_quotes; 
vNextText = case(isempty(vPrevText) and Exact( text, Upper(text) ), lower(text), text); 

vPrevChar = Right( vPrevText; 1); 
vPrevChar_preSpace = Right( Trim(vPrevText); 1); 
vPrevChar_isUpper = Exact( vPrevChar_preSpace; Upper(vPrevChar_preSpace)); 
vPrevChar_isLower = Exact( vPrevChar_preSpace; Lower(vPrevChar_preSpace));
 
vPrevCaseType = Case( 
   vPrevChar = " "; Case(vPrevChar_isUpper and vPrevChar_isLower; "symbol"; "space"); 
   PatternCount("0123456789"; vPrevChar); "number"; 
   vPrevChar = "'"; "apos"; 
   vPrevChar_isUpper and vPrevChar_isLower; "symbol"; 
   vPrevChar_isLower; "lower"; 
   vPrevChar_isUpper; "upper"); 
   
vCurrWord = RightWords(vPrevText; 1) & Left(vNextText; Position(vNextText & " "; " "; 1; 1)-1); 
vCurrChar = Left(vNextText; 1); 

vCurrCase = Case ( 
      // First character of the title is always uppercase
   IsEmpty ( vPrevText ); "upper"; 
      // Always capitalize the first letter after any symbol 
      //   except an apostrophe (which is handled separately), even if that symbol 
      //   was followed by spaces (avoids an vAlwaysLower word from being lower if 
      //   it is really the beginning of a sentence)
   vPrevCaseType = "symbol"; "upper"; 
      // Make it lowercase if it is in your vAlwaysLower value list
   WordCount( vNextText ) > 1 and vPrevCaseType <> "symbol" 
        and not IsEmpty( FilterValues( LeftWords ( vNextText; 1); vAlwaysLower)); "lower"; 
      // First letter after a space is always capitalized (unless it 
      //   was one of the previous arguments, like vAlwaysLower)
   vPrevCaseType = "space"; "upper"; 
      // Make it uppercase if it is the first character after an item in your vPrefixList
   not IsEmpty ( FilterValues( RightWords(vPrevText; 1); vPrefixList ) ); "upper"; 
      // The letter after an apostrophe is uppercase if there are more than a couple characters
   vPrevCaseType = "apos" and Length ( LeftWords(vNextText; 1) ) > 2; "upper"; 
      // Make it uppercase if it is in your vAlwaysUpper value list
   not IsEmpty( FilterValues( vCurrWord; vAlwaysUpper)); "upper"; 
      // The character after a number is always lowercase (to handle something like "19th")
   vPrevCaseType = "number"; "lower"; 
      // You may or may not want this, as it helps but is imperfect.  
      //   It makes a "word" without vowels into all caps.
      //   This will result in converting 'lcd' to 'LCD', which is probably desirable.
      //   But it won't convert 'led' to 'LED' because 'Led' could also be a word.
      //   It also tests for a period so that 'mrs' will convert to 'Mrs.'
   IsEmpty( Filter( vCurrWord; "aeiouyAEIOUY")) and not PatternCount(vCurrWord; ".") 
       and vPrevCaseType <> "lower"; "upper"; 
      // You may or may not want this, as it may or may not help your situation. 
      //   It makes it possible to enter 'LaFond' and keep it that way (as opposed
      //   to 'Lafond').  The down side of this is that you may prefer to just 
      //   overwrite your users' potential inconsistencies.  
      //   Either way, it is imperfect for all situations.
   vPrevChar_isLower and Exact ( vCurrChar; Upper ( vCurrChar ) ); "upper"; 
   "lower" ); 

// Tweaking not recommended below this line.

vCurrChar = Case(
   vCurrCase = "upper"; Upper(vCurrChar); 
   vCurrCase = "lower"; Lower(vCurrChar); 
   "lower") ; 

vNextText = Right ( vNextText; Length ( vNextText ) - 1 ) 

] ; 


Case ( 
   IsEmpty ( Trim ( vNextText ) ) ; vPrevText & vCurrChar;
   fnProper ( vNextText ; vPrevText & vCurrChar )
)  // end case

)  // end Let


// -rcaldwell 12/2023
// This is similar to FileMaker's built-in Proper() function, 
// but expanded for always upper, always lower, and camel-case.
// This function is based on Brian Dunning's Title function.
// https://www.briandunning.com/cf/982

fnQuotedPrintableDecode

//fnQuotedPrintableDecode ( text )
Substitute ( text 
   ; [ "=F0=9F=87=BA=F0=9F=87=B8" ; HexDecode ( "F09F87BAF09F87B8" ) ]  // American flag
   ; [ "=F0=9F=8E=89" ; HexDecode ( "F09F8E89" ) ]  // party
   ; [ "=F0=9F=8E=86" ; HexDecode ( "F09F8E86" ) ]
   ; [ "=E2=80=99" ; "'" ]  // right single quote
   ; [ "=C3=A9" ; char("C3A9") ]  // e with accent
   ; [ "=C2=A0" ; " " ]
   ; [ "=09" ; Char(9) ]  // tab
   ; [ "=20" ; " " ]  // space
   ; [ "=3D" ; "=" ]
   ; [ "=¶" ; "" ]  // soft wrap
   ; [ "=2C" ; "," ]
   ; [ "=0D" ; Char(13) ]
   ; [ "=0A" ; Char(10) ]
   ; [ "=B2" ; "\"" ]  // double quote
   ; [ "=B3" ; "\"" ]  // double quote
   ; [ "=B9" ; "'" ]  // right single quote
)  //end substitute


// For computers, one bit is a 0 or 1.
// A byte is 1 character (A-Z, 0-9, etc.). Modern computers use 8 bits in a byte.
// If 8-bit data passes through a 7-bit computer, it would get messed up.
// To protect email, servers may change the text to quoted-printable encoding.
// High ASCII characters are changed to an equal sign with hex digits representing the ASCII character value.
// If the email is written in English, the encoded text may be legible, unlike base64 encoding.
// Good source for ASCII and Unicode characters.  http://ascii-table.com/ascii.php
// https://en.wikipedia.org/wiki/Quoted-printable
// - rcaldwell 10/2015

fnRSS_DateCode

// fnRSS_DateCode( rssDate ; rssTime )

Let( [ 
rssDate = If( IsEmpty ( rssDate ) ; Get( CurrentTimeStamp ) ; rssDate ) ; 
rssTime = If( IsEmpty(  rssTime ) ;Get( CurrentTimeStamp ) ; rssTime )
] ; 

Left ( DayName ( rssDate ) ; 3 ) & ", " & 
If ( Day ( rssDate  )  < 10 ; "0" ; "" ) & Day ( rssDate  )  
& " " & Left ( MonthName ( rssDate )  ; 3 ) & " " & Year ( rssDate ) & " " & 
If ( Hour ( rssTime ) < 10 ; 0 & Hour ( rssTime  ) ; Hour ( rssTime )) & ":" &
If ( Minute ( rssTime  ) < 10 ; 0 & Minute ( rssTime  ) ; Minute ( rssTime ) ) & ":00 GMT"

)  //end Let


// Used by fnBuildRSSFeed function.
// Source - http://www.briandunning.com/cf/864

fnRandomString

// fnRandomString ( charCount ; string )
Let ( [
CharSet = "123456789QWERTZUIPASDFGHJKLYXCVBNM123456789" ;
CharLen= Length ( CharSet ) 
] ; 

String & 
If ( CharCount > 0 ; 
   Middle ( CharSet ; Int ( Random * CharLen ) + 1 ; 1 ) & 
   fnRandomString ( CharCount - 1 ; String ) 
)

)  //end Let

// This creates random file names for PDFs uploaded to the web on our FTP server.
// Based on a custom function from BrianDunning.com  -rcaldwell 8/2011
// PasswordCreator ( HowManyChars ; Parameter ; String )
// Recursive Custom Function programmed by Martin D. Brunner - mdb@gmx.net 8/182008

fnReplaceValues

// fnReplaceValues ( text ; value_list )
Let ( [ 
vStart = "¶; [ \"" ; 
vEnd = "\" ; \"\" ] " ; 
vFullString = "Substitute ( ¶" & "\"" & text & "\"" & 
                vStart & Substitute ( value_list ; ¶ ;  
                vEnd & vStart )
                & vEnd & "¶)"
] ; 

TrimAll ( Evaluate( vFullString ) ; 0 ; 0 )

)  //end Let


// Strips a list of values out of a text string.
// Does this by changing the value list to a big substitute calculation.

fnReportValue

// fnReportValue ( value, format )
Case ( 
   format = "fnThousands" ; fnThousandsFormat ( value ) ; 
   format = "fnThousands$" ; "$" & fnThousandsFormat ( value ) ; 
   format = "fnBigDollar0" ; fnBigDollar ( value ; 0 ) ; 
   format = "fnBigDollar1" ; fnBigDollar ( value ; 1 ) ; 
   format = "fnRound0" ; Round ( value ; 0 ) ; 
   format = "fnRound0%" ; Round ( value ; 0 ) & "%" ; 
   format = "fnRound1" ; fnRound ( value ; 1 ) ; 
   format = "fnRound1%" ; fnRound ( value ; 1 ) & "%" ; 
   format = "fnRound2" ; fnRound ( value ; 2 ) ; 
   format = "fnRound2%" ; fnRound ( value ; 2 ) & "%" ; 
   value 
) //end Case

// Allows user to set number format in a field, then a script or calculation can apply 
// that format to a column of data for output to a file or HTML table.   -rcaldwell 2010

fnRoman2Arabic

// fnRoman2Arabic ( roman )

Let(

// normalize romanNumber to lowercase
roman = Lower( romanNumber);

// tally values for letters
(PatternCount( roman, "i")  * 1) +
(PatternCount( roman, "v")  * 5) +
(PatternCount( roman, "x")  * 10) +
(PatternCount( roman, "l")  * 50) +
(PatternCount( roman, "c")  * 100) +
(PatternCount( roman, "d")  * 500) +
(PatternCount( roman, "m")  * 1000) -


// subtract for complex values
(PatternCount( roman, "iv")  * 2) -  //(i+v)-iv=(1+5)-4=2
(PatternCount( roman, "ix")  * 2) -  //(i+x)-ix=(1+10)-9=2
(PatternCount( roman, "xl")  * 20) -  //(x+l)-xl=(10+50)-40=20
(PatternCount( roman, "xc")  * 20) -  //(x+c)-xc=(10+100)-90=20
(PatternCount( roman, "cd")  * 200) -  //(c+d)-cd=(100+500)-(400)=200
(PatternCount( roman, "cm")  * 200)  //(c+m)-cm=(100+1000)-900=200

)  //end Let


// Returns the arabic number equivalent to romanNumber [text]. 
// It will return correct values for both correctly and incorrectly 
// formed roman numerals, and ignore all characters other than 
// i, v, x, l, c, d, and m.
// https://www.briandunning.com/cf/85
// Also see fnArabic2Roman

fnScrapeRows

// fnScrapeRows ( source ; matchtext )
Let ( [ 
vRowNum = ValueCount ( Left ( source ; Position ( source ; matchtext ; 1 ; 1 ) ) ) ; 
vTextRemain = RightValues ( source ; ValueCount ( source ) - vRowNum )
] ; 

If ( vRowNum > 0 ; GetValue ( source ; vRowNum ) ) & 
If ( PatternCount ( vTextRemain ; matchtext ) > 0 ; 
   ¶& fnScrapeRows ( vTextRemain ; matchtext ) )

)  //end if


// Similar to 'grep', it takes a return delimited list of values 
// and returns all the rows that contain the matching text.
// This is an adaptation of the fnScrapeText function.
// 9/20/2020 - Added notes and removed PDF specific search.

fnScrapeText

// fnScrapeText ( source ; startText ; endText ; rep ; inEx ; loop )
Let ( [ 
//Drop any text where the start and end are next to each other, meaning there is no data to extract in between.
vSource = substitute ( source ; startText & endText ; "" ) ; 
vStart = Position ( vSource ; startText ; 1 ; rep ) + If ( inEx = "ex" ; Length ( startText ) ) ; 
// If there is no vEnd value, add one to Length of the Source to keep the last character in the text.
vEnd = If ( endText = "" or PatternCount ( vSource ; endText ) = 0 ; Length ( vSource ) + 1 ; 
   Position ( vSource ; endText ; vStart + 1 ; 1 ) + If ( inEx ≠ "ex" ; Length ( endText ) ) ) ; 
vRemain = Middle ( vSource ; vEnd ; Length ( vSource ) )
] ; 

If ( PatternCount ( vSource ; startText ) >= rep ; 
   Trim ( Middle ( vSource ; vStart ; vEnd - vStart )  //end middle
   )  //end trim
)  //end patterncount
& 
If ( loop and PatternCount ( vRemain ; startText ) ; 
   ¶ & fnScrapeText ( vRemain ; startText ; endText ; rep ; inEx ; loop ) 
)  //end if

)  //end Let


// Pulls valuable data out of HTML code.   -rcaldwell 3/2012
// inEx - 'in'clusive keeps the start and end text in the result, 'ex'clusive leaves only the middle text.
// Added loop parameter to allow recursive scrape.   -rcaldwell 2015
// Added if statement to use remaining text if endText is blank.  -rcaldwell 7/2015
// Added check for endText so if not there, text defaults to everything after the start.  -rcaldwell 6/2020
// Added +1 of no endText was declared or found, this keeps the last character on the output.  -rcaldwell 7/2020

fnSortArray

// USE THE SortValues() FUNCTION INSTEAD, introduced in FileMaker 16.


// fnSortArray ( valueList ; sortDirection ; dataType )
Let ( vListSize = ValueCount ( valueList ) ; 

If ( vListSize ≤ 1 ; valueList ; 

Let ( [ 
vSplitPoint = Div ( vListSize ; 2 ) ; 
vLeftList = LeftValues ( valueList ; vSplitPoint ) ; 
vRightList = RightValues ( valueList ; vListSize - vSplitPoint )
] ; 
fnSortArray_Merge ( 
	fnSortArray ( LeftList ; sortDirection ; dataType ) ; 
	fnSortArray ( RightList ; sortDirection ; dataType ) ; 
	sortDirection ; 
	dataType )

)  //end Let

)  //end if

)  //end Let



// Sort an array of text, number, date or time values. Sorting is done by "merge sort" (rf. Wikipedia).
// valueList = array to be sorted
// sortDirection = ASC for ascending or DES for descending sort
// dataType = text, number, date, or time; text is default

// NOTE 1: Requires SortArray_Merge ( leftList ; rightList ; sortDirection ; dataType )
// NOTE 2: Don't try to define any variable in last instruction (SortArray_Merge (Sort...) as local or global (using "$..." or "$$..."), because the custom function then will fail (probably a bug in FileMaker 8.5).

// Source - http://www.briandunning.com/cf/593




-----



// fnSortArray_Merge ( leftList ; rightList ; sortDirection ; dataType )
Case (
ValueCount ( leftList ) = 0 ; rightList ; 
ValueCount ( rightList ) = 0 ; leftList ; 

Let ( [ 
$LeftValue = LeftValues ( leftList ; 1 ) ;
$RightValue = LeftValues ( rightList ; 1 ) ;
$LeftCompare = Case ( 
   dataType = "Number" ; Let ( $RightCompare = GetAsNumber ( $RightValue ) ; GetAsNumber ( $LeftValue ) ) ; 
   dataType = "Time" ; Let ( $RightCompare = GetAsTime ( $RightValue ) ; GetAsTime ( $LeftValue ) ) ; 
   dataType = "Date" ; Let ( $RightCompare = GetAsDate ( $RightValue ) ; GetAsDate ( $LeftValue ) ) ; 
   // Text (default)
   Let ( $RightCompare = $RightValue ; $LeftValue )
   )  //end case
] ; 

If ( 
   If ( sortDirection = "Asc" ; $LeftCompare ≤ $RightCompare ; $LeftCompare > $RightCompare ) ; 
   $LeftValue & fnSortArray_Merge ( RightValues ( leftList ; ValueCount ( leftList ) - 1 ) ; rightList ; sortDirection ; dataType )
   ; 
   $RightValue & fnSortArray_Merge ( leftList ; RightValues ( rightList ; ValueCount ( rightList ) - 1 ) ; sortDirection ; dataType )
)  //end if

)  //end Let

)  //end case


// Subfunction to fnSortArray. Merges two sorted lists of text, number, date or time values. Version: 1.01
// leftList = left array
// rightList = right array
// sortDirection = ASC for ascending or DES for descending sort
// dataType = text, number, date, time; text is default

// Source - http://www.briandunning.com/cf/594

fnStripChars

// fnStripChars ( text )

Trim ( Substitute ( text ; [ "é" ; "e" ] ; [ "è" ; "e" ] ; [ "ê" ; "e" ] ; [ "ë" ; "e" ] ; [ "à" ; "a" ] ; [ "á" ; "a" ] ; [ "â" ; "a" ] ; [ "ã" ; "a" ] ; [ "ä" ; "a" ] ; [ "å" ; "a" ] ; [ "ù" ; "u" ] ; [ "ú" ; "u" ] ; [ "û" ; "u" ] ; [ "ü" ; "u" ] ; [ "˛" ; "y" ] ; [ "ÿ" ; "y" ] ; [ "ñ" ; "n" ] ; [ "ò" ; "o" ] ; [ "ó" ; "o" ] ; [ "ô" ; "o" ] ; [ "õ" ; "o" ] ; [ "ö" ; "o" ] ; [ "ì" ; "i" ] ; [ "í" ; "i" ] ; [ "î" ; "i" ] ; [ "ï" ; "i" ] ; [ "∂" ; "o" ] ; [ "æ" ; "ae" ] ; [ "ç" ; "c" ] ; [ "ˇ" ; "p" ] ; [ "œ" ; "oe" ] ; [ "¿" ; "?" ] ; [ "–" ; "--" ] ; [ "—" ; "---" ] ; [ "˜" ; "-" ] ; [ "¡" ; "!" ] ; [ "†" ; "*" ] ; [ "‡" ; "**" ] ; [ "À" ; "A" ] ; [ "Á" ; "A" ] ; [ "Â" ; "A" ] ; [ "Ã" ; "A" ] ; [ "Ä" ; "A" ] ; [ "Å" ; "A" ] ; [ "È" ; "E" ] ; [ "É" ; "E" ] ; [ "Ê" ; "E" ] ; [ "Ë" ; "E" ] ; [ "Ì" ; "I" ] ; [ "Í" ; "I" ] ; [ "Î" ; "I" ] ; [ "Ï" ; "I" ] ; [ "Ò" ; "O" ] ; [ "Ó" ; "O" ] ; [ "Ô" ; "O" ] ; [ "Õ" ; "O" ] ; [ "Ö" ; "O" ] ; [ "˙" ; "x" ] ; [ "Ù" ; "U" ] ; [ "Ú" ; "U" ] ; [ "Û" ; "U" ] ; [ "Ü" ; "U" ] ; [ "˚" ; "Y" ] ; [ "¥" ; "Y" ] ; [ "Ÿ" ; "Y" ] ; [ "Ñ" ; "N" ] ; [ "˝" ; "P" ] ; [ "ß" ; "B" ] ; [ "˘" ; "D" ] ; [ "≤" ; "S" ] ; [ "Œ" ; "OE" ] ; [ "∫" ; "S" ] ; [ "Æ" ; "AE" ] ; [ "Ç" ; "C" ] ; [ "¶" ; "" ] ; [ "   " ; " " ] ; [ "  " ; " " ] ; [ " ," ; "," ] ; [ " ." ; "." ] ; [ ".." ; "." ] ) )

// Removes unusual characters to make text more web-compatible. Based on calculation by Gwen Zanin.  -rcaldwell 2002

fnStripDelimited

// fnStripDelimited ( text ; open ; close )
Let ( [ 
vPositionOpen = Position ( text ; open ; 1 ; 1 ) ; 
vLeftText = Left ( text ; vPositionOpen - 1 ) ; 
vPositionClose = Position ( text ; close ; vPositionOpen + 1 ; 1 ) ; 
vRightText = Middle ( text ; vPositionClose + Length ( close ) ; Length ( text )  )
] ; 

If ( PatternCount ( text ; open ) = 0 or PatternCount ( text ; close ) = 0 ; text ; 
   vLeftText & fnStripDelimited ( vRightText ; open ; close )
)  //end if

)  //end Let


// Just like the fnStripHTML function, but looks for given delimiters instead of fixed ones.
// Used for file template HTML code.  -rcaldwell 4/2017
// Added vPositionOpen to set the start point for vRightText Middle calculation.  -rcaldwell 11/2019
// If statement now checks for closing delimiter to prevent infinite loops.  -rcaldwell 1/2020
// Fixed error if open and close characters are the same (like quotes).  -rcaldwell 2/2020

fnStripHTML

// fnStripHTML ( text )

Left ( text ; Position ( text ; "<" ; 1 ; 1 ) - 1 ) & 
If ( PatternCount ( text ; "<" ) = 0 ; 
   Right ( text ; Length ( text ) - Position ( text ; ">" ; 1 ; 1 ) ) ; 
   fnStripHTML ( Right ( text ; Length ( text ) - Position ( text ; ">" ; 1 ; 1 ) ) )
)  //end if

// fnCarrotStrip was built for Profiles export to Nstein.  -Sara 12/2008
// Later modified to become fnStripHTML.

fnSuperTrim

// fnSuperTrim ( text )
Let ( [ 
vVisibleText = Substitute ( text 
   ; [ Char(9) ; "" ]  // tab
   ; [ Char(10) ; "" ]  // LF
   ; [ Char(13) ; "" ]  // CR
   ; [ Char(32) ; "" ]  // space
   ; [ Char(160) ; "" ]  // nbsp
   ) ; 
vFirstChar = Position ( text ; Left ( vVisibleText ; 1 ) ; 0 ; 1 ) ; 
vLastChar = Position ( text ; Right ( vVisibleText ; 1 ) ; Length ( text ) ; -1 )
] ; 

Case ( vFirstChar ; Middle ( text ; vFirstChar ; vLastChar - vFirstChar + 1 ) )

)  //end Let


// Remove leading and trailing white space from a text string including spaces, tabs and returns.
// Does not touch any spacing WITHIN the string, and uses no recursion.
// Source - http://www.briandunning.com/cf/904

fnText2HTM

// fnText2HTM ( text )
Substitute ( text
   ; [ ¶ ; "<br />" ]
)  //end substitute


// Used by fnBuildRSSFeed function.
// Could not find this function on briandunning.com site, so I'm attempting to rebuild it through trial and error.

fnTextStylePreview

// fnTextStylePreview ( text )
Evaluate ( "\"" & 
Substitute ( text ; 
   [ "<b>" ; "\" & TextStyleAdd ( \"" ] ; 
   [ "</b>" ; "\" ; bold ) & \"" ] ; 
   [ "<B>" ; "\" & TextStyleAdd ( \"" ] ; 
   [ "</B>" ; "\" ; bold ) & \"" ] ; 
   [ "<i>" ; "\" & TextStyleAdd ( \"" ] ; 
   [ "</i>" ; "\" ; italic ) & \"" ] ; 
   [ "<I>" ; "\" & TextStyleAdd ( \"" ] ; 
   [ "</I>" ; "\" ; italic ) & \"" ] ; 
   [ "[" ; "\" & TextColor ( \"" ] ; 
   [ "]" ; "\" ; RGB ( 0 ; 0 ; 255 ) ) & \"" ] 
)  //end substitute
& "\"" 
)  //end Evaluate

// This is for preview purposes only!
// Used to convert HTML tags to FileMaker styled text.  Also used to change inline editor comments between [CO: square brackets ] 
// BLUE showing what will be removed before final output.  Substitute is case sensitive.  -rcaldwell 2011

fnThousands

//fnThousands ( number ; precision ; notationText ; nullText )

// STILL TESTING THIS VERSION

Let ( [ 
vNumber = GetAsNumber ( number ) ; 
vNumber = If ( precision < 0 ; vNumber ; Round ( vNumber ; precision ) ) ; 
vInt = Int ( Abs ( Round ( vNumber ; precision ) ) ) ; 
vDecimal = Abs ( Round ( vNumber ; precision ) - Abs ( Int ( Round ( vNumber ; precision ) ) ) ) ; 
precision = If ( vDecimal > 0 ; precision + 1 ; precision )
] ; 

If ( vNumber = "" or vNumber = "?" ; nullText ;   // Convert blanks and text to null value.

If ( vNumber < 0 ; "-" ) & 
If ( notationText ≠ "%" ; notationText ) & 

If ( precision = "" ; NumToJText ( Abs ( vNumber ) ; 1 ; 0 ) ; 
   NumToJText ( vInt ; 1 ; 0 ) & 
   Case ( 
   precision > 0 ; Left ( If ( vDecimal = 0 ; "." ) & 
      vDecimal & "0000000000" ; precision + 1 ) ; 
   vDecimal > 0 ; vDecimal ; 
   )  //end case
) &  //end if precision is blank

If ( notationText = "%" ; "%" )

)  //end if number is blank

)  //end Let


// THIS FUNCTION KEEPS ZERO PADDING AFTER THE DECIMAL
// Sample:  fnThousands ( 4321.0994 ; 2 ; "$" ; "n/a" ) = $4,321.10 (or n/a if blank)
// Format a number with commas and round to the requested decimal.
// vDecimal takes the fractional portion of the number off to pad with zeros later.
// Change blanks and text values to proper null text.
// Notation ($) is added between neg sign and first digit unless % (end).  
// Precision works up to 10 decimal places at the moment (number of padded zeros).
// No more recursion.  -11/14/2014
// Now blank precision will NOT round to a whole number.  -8/7/2016



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


//fnThousands ( number ; precision ; notationText ; nullText )

// ----- CAUTION, THIS VERSION IS STABLE, BUT LEAVING THE PRECISION VALUE BLANK WILL ROUND THE VALUE TO A WHOLE NUMBER -----

Let ( [ 
vInt = Int ( Abs ( Round ( number ; precision ) ) ) ; 
vLittlePart = Abs ( Round ( number ; precision ) - Int ( Round ( number ; precision ) ) )
] ; 

If ( GetAsNumber ( number ) = "" ; nullText ;   // Convert blanks and text to null value.

If ( GetAsNumber ( number ) < 0 ; "-" ) & 
If ( notationText ≠ "%" ; notationText ) & 
NumToJText ( vInt ; 1 ; 0 ) & 
If ( precision > 0 ; Left ( If ( vLittlePart = 0 ; "." ) & vLittlePart & 
   "0000000000" ; precision + 1 ) ) & 
If ( notationText = "%" ; "%" )

)  //end if

)  //end Let

// Sample:  fnThousands ( 4321.1234 ; 2 ; "$" ; "n/a" ) = $4,321.12 (or n/a if blank).
// Formats number with commas and round to the requested decimal.
// Change blanks and text values to proper null text.
// Notation is added between neg sign and first digit unless % (end).  
// Precision works up to 10 decimal places at the moment (number of padded zeros).
// No more recursion.  -11/14/2014

fnTrimAllReturns_v1

// fnTrimAllReturns ( text )
Case (
PatternCount ( text ; "¶¶" ) ; fnTrimAllReturns ( Substitute ( text ; [ "¶¶" ; ¶ ] ) ) ;
Left ( text ; 1 ) = ¶ ; fnTrimAllReturns ( Right ( text ; Length ( text ) - 1 ) ) ;
Right ( text ; 1 ) = ¶ ; fnTrimAllReturns ( Left ( text ; Length ( text ) - 1 ) ) ;
text )


// TrimAll_CR function takes any text string with extra carriage returns and removes all leading, trailing and mid-list returns.
// It may use recursion, but in 2018, this is still my preferred method to remove extra returns.
// Source 7/31/2012 - http://www.briandunning.com/cf/1529

fnTrimAllReturns_v2

// fnTrimAllReturns ( text )
Let ( [ 
vText = Substitute ( text ; [ "¶¶" ; "¶#" ] ; ["#¶" ; "" ] ; [ "#" ; "" ] ) ; 
vText = If ( Left ( vText ; 1 ) = ¶ ; Right ( vText ; Length ( vText ) - 1 ) ; vText ) ; 
vText = If ( Right ( vText ; 1 ) = ¶ ; Left ( vText ; Length ( vText ) - 1 ) ; vText )
] ; 

vText

)  //end Let


// Here, the # character is used for evaluating the result. You can take any character in place of #.
// eg.  text = "3¶¶¶¶¶¶8"
// First substitution =  "3¶#¶#¶#8"
// Second substitution = "3¶#8"
// Third substitution =  "3¶8"  (the result)

// This version of fnTrimAllReturns does not use recursion.
// Based on RemoveCarriages function.
// Source 2014 - https://www.briandunning.com/cf/1700

fnTrimAllReturns_v3

// fnTrimAllReturns ( text )

Let ( vText = "\"" & Substitute ( text ; ¶ ; "\";\"" ) & "\"" ; 

Evaluate ( "List ( " & vText & " )" )

)  //end Let


// Strips out blank lines without using recursion.
// May be sensitive to double quotes within the original text.
// Based on Todd Geist's HyperList module.
// Source 2013 - http://www.modularfilemaker.org/module/hyperlist/

fnTrimEmailList

// fnTrimEmailList ( text )
Let ( [ 
vValue = TextFormatRemove ( text ) ; 
vValue = Substitute ( vValue ; [ ¶ ; " " ] ; [ "," ; " " ] ; [ ";" ; " " ] ) ; 
vValue = TrimAll ( vValue ; 0 ; 0 ) ; 
vValue = Substitute ( vValue ; [ " " ; ", " ] )
] ; 

vValue

)  //end Let



// Prepares a list of email addresses to be used with the FM14 Send Mail script step.
// 1. Remove text formatting (not necessary, but looks better).
// 2. Change returns, commas and semi-colons into spaces.
// 3. Remove leading & trailing spaces, and make all spacing single-space (TrimAll).
// 4. Go back and turn spaces into comma-space (now without leading or trailing commas).
// I like keeping a space after the comma for more readability.  -rcaldwell 5/2016

fnURLDecode

// fnURLDecode ( text )  --  Also called percent encoding, this is v2 expanded version of original.

Substitute ( text
   ; [ "%0D" ; "¶" ]
   ; [ "%20" ; " " ]
   ; [ "%21" ; "!" ]
   ; [ "%22" ; "\"" ]
   ; [ "%23" ; "#" ]
   ; [ "%24" ; "$" ]
   ; [ "%26" ; "&" ]
   ; [ "%27" ; "'" ]
   ; [ "%28" ; "(" ]
   ; [ "%29" ; ")" ]
   ; [ "%2A" ; "*" ]
   ; [ "%2B" ; "+" ]
   ; [ "%2C" ; "," ]
   ; [ "%2D" ; "-" ]
   ; [ "%2E" ; "." ]
   ; [ "%2F" ; "/" ]
   ; [ "%3A" ; ":" ]
   ; [ "%3B" ; ";" ]
   ; [ "%3C" ; "<" ]
   ; [ "%3D" ; "=" ]
   ; [ "%3E" ; ">" ]
   ; [ "%3F" ; "?" ]
   ; [ "%40" ; "@" ]
   ; [ "%5B" ; "[" ]
   ; [ "%5C" ; "\\" ]
   ; [ "%5D" ; "]" ]
   ; [ "%5E" ; "^" ]
   ; [ "%5F" ; "_" ]
   ; [ "%60" ; "`" ]
   ; [ "%7B" ; "{" ]
   ; [ "%7C" ; "|" ]
   ; [ "%7D" ; "}" ]
   ; [ "%7E" ; "~" ]
   ; [ "%A0" ; " " ]
   ; [ "%A1" ; "¡" ]
   ; [ "%A2" ; "¢" ]
   ; [ "%A3" ; "£" ]
   ; [ "%A4" ; "¤" ]
   ; [ "%A5" ; "¥" ]
   ; [ "%A6" ; "¦" ]
   ; [ "%A7" ; "§" ]
   ; [ "%A8" ; "¨" ]
   ; [ "%A9" ; "©" ]
   //; [ "©" ; "©" ]
   //; [ "™" ; "™" ]

   ; [ "%C0" ; "À" ]
   ; [ "%C1" ; "Á" ]
   ; [ "%C2" ; "Â" ]
   ; [ "%C3" ; "Ã" ]
   ; [ "%C4" ; "Ä" ]
   ; [ "%C5" ; "Å" ]
   ; [ "%C6" ; "Æ" ]
   ; [ "%C7" ; "Ç" ]
   ; [ "%C8" ; "È" ]
   ; [ "%C9" ; "É" ]
   ; [ "%CA" ; ";" ]
   ; [ "%CB" ; "Ë" ]
   ; [ "%CC" ; "Ì" ]
   ; [ "%CD" ; "Í" ]
   ; [ "%CE" ; "Î" ]
   ; [ "%CF" ; "Ï" ]
   ; [ "%D0" ; "Ð" ]
   ; [ "%D1" ; "Ñ" ]
   ; [ "%D2" ; "Ò" ]
   ; [ "%D3" ; "Ó" ]
   ; [ "%D4" ; "Ô" ]
   ; [ "%D5" ; "Õ" ]
   ; [ "%D6" ; "Ö" ]
   ; [ "%D7" ; "×" ]
   ; [ "%D8" ; "Ø" ]
   ; [ "%D9" ; "Ù" ]
   ; [ "%DA" ; "Ú" ]
   ; [ "%DB" ; "Û" ]
   ; [ "%DC" ; "Ü" ]
   ; [ "%DD" ; "Ý" ]
   ; [ "%DE" ; "Þ" ]
   ; [ "%DF" ; "ß" ]
   ; [ "%E0" ; "à" ]
   ; [ "%E1" ; "á" ]
   ; [ "%E2" ; "â" ]
   ; [ "%E3" ; "ã" ]
   ; [ "%E4" ; "ä" ]
   ; [ "%E5" ; "å" ]
   ; [ "%E6" ; "æ" ]
   ; [ "%E7" ; "ç" ]
   ; [ "%E8" ; "è" ]
   ; [ "%E9" ; "é" ]
   ; [ "%EA" ; "ê" ]
   ; [ "%EB" ; "ë" ]
   ; [ "%EC" ; "ì" ]
   ; [ "%ED" ; "í" ]
   ; [ "%EE" ; "î" ]
   ; [ "%EF" ; "ï" ]
   ; [ "%F0" ; "ð" ]

   ; [ "%30" ; "0" ]
   ; [ "%31" ; "1" ]
   ; [ "%32" ; "2" ]
   ; [ "%33" ; "3" ]
   ; [ "%34" ; "4" ]
   ; [ "%35" ; "5" ]
   ; [ "%36" ; "6" ]
   ; [ "%37" ; "7" ]
   ; [ "%38" ; "8" ]
   ; [ "%39" ; "9" ]

   ; [ "%41" ; "A" ]
   ; [ "%42" ; "B" ]
   ; [ "%43" ; "C" ]
   ; [ "%44" ; "D" ]
   ; [ "%45" ; "E" ]
   ; [ "%46" ; "F" ]
   ; [ "%47" ; "G" ]
   ; [ "%48" ; "H" ]
   ; [ "%49" ; "I" ]
   ; [ "%4A" ; "J" ]
   ; [ "%4B" ; "K" ]
   ; [ "%4C" ; "L" ]
   ; [ "%4D" ; "M" ]
   ; [ "%4E" ; "N" ]
   ; [ "%4F" ; "O" ]
   ; [ "%50" ; "P" ]
   ; [ "%51" ; "Q" ]
   ; [ "%52" ; "R" ]
   ; [ "%53" ; "S" ]
   ; [ "%54" ; "T" ]
   ; [ "%55" ; "U" ]
   ; [ "%56" ; "V" ]
   ; [ "%57" ; "W" ]
   ; [ "%58" ; "X" ]
   ; [ "%59" ; "Y" ]
   ; [ "%5A" ; "Z" ]

   ; [ "%61" ; "a" ]
   ; [ "%62" ; "b" ]
   ; [ "%63" ; "c" ]
   ; [ "%64" ; "d" ]
   ; [ "%65" ; "e" ]
   ; [ "%66" ; "f" ]
   ; [ "%67" ; "g" ]
   ; [ "%68" ; "h" ]
   ; [ "%69" ; "i" ]
   ; [ "%6A" ; "j" ]
   ; [ "%6B" ; "k" ]
   ; [ "%6C" ; "l" ]
   ; [ "%6D" ; "m" ]
   ; [ "%6E" ; "n" ]
   ; [ "%6F" ; "o" ]
   ; [ "%70" ; "p" ]
   ; [ "%71" ; "q" ]
   ; [ "%72" ; "r" ]
   ; [ "%73" ; "s" ]
   ; [ "%74" ; "t" ]
   ; [ "%75" ; "u" ]
   ; [ "%76" ; "v" ]
   ; [ "%77" ; "w" ]
   ; [ "%78" ; "x" ]
   ; [ "%79" ; "y" ]
   ; [ "%7A" ; "z" ]
   ; [ "%25" ; "%" ]  // % must come last

)  //end substitute


// Converts url encoded string back to unencoded text, so opposite of GetAsURLEncoded(). -rcaldwell 2014
// Source (2012) - http://www.briandunning.com/cf/528

fnURLDecode2

// fnURLDecode ( text )
Substitute ( text

   ; [ "%3F" ; "?" ]
   ; [ "%3D" ; "=" ]
   ; [ "%3A" ; ":" ]
   ; [ "%2F" ; "/" ]
   ; [ "%2E" ; "." ]
   ; [ "%2D" ; "-" ]
   ; [ "%26" ; "&" ]

   ; [ "%7D" ; "}" ]
   ; [ "%7C" ; "|" ]
   ; [ "%7B" ; "{" ]
   ; [ "%60" ; "`" ]
   ; [ "%5E" ; "^" ]
   ; [ "%5D" ; "]" ]
   ; [ "%5C" ; "\\" ]
   ; [ "%5B" ; "[" ]
   ; [ "%40" ; "@" ]
   ; [ "%3E" ; ">" ]
   ; [ "%3C" ; "<" ]
   ; [ "%3B" ; ";" ]
   ; [ "%2C" ; "," ]
   ; [ "%2B" ; "+" ]
   ; [ "%2A" ; "*" ]
   ; [ "%29" ; ")" ]
   ; [ "%28" ; "(" ]
   ; [ "%27" ; "'" ]
   ; [ "%27" ; "'" ]
   ; [ "%24" ; "$" ]
   ; [ "%23" ; "#" ]
   ; [ "%22" ; "\"" ]
   ; [ "%21" ; "!" ]
   ; [ "%20" ; " " ]
   ; [ "%0D" ; "¶" ]
   ; [ "%25" ; "%" ]  // % must come last

)  //end substitute



// Reverses the fnURLEncode2 function.
// fnURLEncode Source 2011 - https://www.briandunning.com/cf/165

fnURLEncode2

// fnURLEncode ( text )
Substitute ( text

   ; [ "%" ; "%25" ]  // % must come first
   ; [ "¶" ; "%0D" ]
   ; [ " " ; "%20" ]
   ; [ "!" ; "%21" ]
   ; [ "\"" ; "%22" ]
   ; [ "#" ; "%23" ]
   ; [ "$" ; "%24" ]
   ; [ "'" ; "%27" ]
   ; [ "(" ; "%28" ]
   ; [ ")" ; "%29" ]
   ; [ "*" ; "%2A" ]
   ; [ "+" ; "%2B" ]
   ; [ "," ; "%2C" ]
   ; [ ";" ; "%3B" ]
   ; [ "<" ; "%3C" ]
   ; [ ">" ; "%3E" ]
   ; [ "@" ; "%40" ]
   ; [ "[" ; "%5B" ]
   ; [ "\\" ; "%5C" ]
   ; [ "]" ; "%5D" ]
   ; [ "^" ; "%5E" ]
   ; [ "`" ; "%60" ]
   ; [ "{" ; "%7B" ]
   ; [ "|" ; "%7C" ]
   ; [ "}" ; "%7D" ]

   ; [ "&" ; "%26" ]
   ; [ "-" ; "%2D" ]
   ; [ "." ; "%2E" ]
   ; [ "/" ; "%2F" ]
   ; [ ":" ; "%3A" ]
   ; [ "=" ; "%3D" ]
   ; [ "?" ; "%3F" ]

)  //end substitute



// Source 2011 - https://www.briandunning.com/cf/165
// by Jeremiah Small, Soliant Consulting

// Converts illegal and reserved characters in input string into URL-legal hex code. Complete table of hexadecimal codes can be found here.
// http://www.december.com/html/spec/ascii.html

// Be aware that curly quotes (single and double) are high-ascii, and will not be converted with this function. If you need quotes, make sure your input is with straight quotes.

fnUniqueValues

// fnUniqueValues ( valuelist )
Let ( [ 
vFirstValue = GetValue ( valuelist ; 1 ) ; 
vNextList = RightValues ( valuelist ; ValueCount ( valuelist ) - 1 )
] ; 

Case (
valuelist = "" ; "" ; 
vFirstValue = "" ; fnUniqueValues ( vNextList ) ;   // Eliminate null values here
ValueCount ( FilterValues ( valuelist ; vFirstValue ) ) < 2 ; vFirstValue &¶& fnUniqueValues ( vNextList ) ; 
fnUniqueValues ( vNextList ) 
)  //end case

)  //end Let


// Source 2016 - https://www.briandunning.com/cf/596

fnValidEmail

// fnValidEmail ( theEmail ) 
// List of TLDs from http://data.iana.org/TLD/tlds-alpha-by-domain.txt

Let ( [  

//  remove invalid charcters 
adjustedText = Substitute ( Lower( theEmail  ) ;["¶";" "];["<";" "];[">";" "];[":";" "];["\"";" "];["!";" "];["(";" "];["[";" "];["]";" "];[")";" "];["'";" "];["=";" "];["#";" "];["$";" "];["%";" "];["+";" "];["?";" "];["|";" "];["^";" "];["{";" "];["}";" "];["~";" "];
[ "postmaster@" ; " " ]  ;  [ "daemon@" ; " " ] ) ;

count = PatternCount ( adjustedText ; "@" );
len = Length ( adjustedText );
pos = Position ( adjustedText ; "@" ; 1 ; 1 );
startEmail = Position ( adjustedText ; " " ; pos ; -1 ) + 1;
endEmail  = If ( Position ( adjustedText ; " " ; pos ; 1 )  ≠  0 ; Position ( adjustedText ; " " ; pos ; 1 ) ; len + 1  );

EmailAddr = Middle ( adjustedText ; startEmail  ; endEmail  - startEmail  );
EmailAddr = If(  PatternCount ( EmailAddr ; "@" ) > 0 ; EmailAddr ; "" ) ;
EmailAddr = If(  Length( EmailAddr ) < 60 ; EmailAddr ; "" ) ;
nextText = Middle (adjustedText ; EmailAddr + 1 ; len );
nextText =  Substitute ( nextText ; EmailAddr ; " " );

    _validDomainCharacters = "abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789-." ;
    _invalidLocalCharacters = "!\"#$%&'*,/:;<>?@[\]^`{}|~" ; // from http://www.remote.org/jochen/mail/info/chars.html
    _theLocalPart = Left ( EmailAddr ; Position ( EmailAddr ; "@" ; 1; 1 ) - 1 ) ;
    _theDomainPart =  Right ( EmailAddr ; Length ( EmailAddr ) - Position ( EmailAddr ; "@" ; 1; 1 )  ) ;
    _theTopLevelDomain = RightWords ( Substitute ( _theDomainPart ; "." ; " " ) ; 1 ) ;
    _ValidTopLevelDomains = "AC¶AD¶AE¶AERO¶AF¶AG¶AI¶AL¶AM¶AN¶AO¶AQ¶AR¶ARPA¶AS¶AT¶AU¶AW¶AZ¶BA¶BB¶BD¶BE¶BF¶BG¶BH¶BI¶BIZ¶BJ¶BM¶BN¶BO¶BR¶BS¶BT¶BV¶BW¶BY¶BZ¶CA¶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¶KR¶KW¶KY¶KZ¶LA¶LB¶LC¶LI¶LK¶LR¶LS¶LT¶LU¶LV¶LY¶MA¶MC¶MD¶MG¶MH¶MIL¶MK¶ML¶MM¶MN¶MO¶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¶RU¶RW¶SA¶SB¶SC¶SD¶SE¶SG¶SH¶SI¶SJ¶SK¶SL¶SM¶SN¶SO¶SR¶ST¶SU¶SV¶SY¶SZ¶TC¶TD¶TF¶TG¶TH¶TJ¶TK¶TL¶TM¶TN¶TO¶TP¶TR¶TRAVEL¶TT¶TV¶TW¶TZ¶UA¶UG¶UK¶UM¶US¶UY¶UZ¶VA¶VC¶VE¶VG¶VI¶VN¶VU¶WF¶WS¶YE¶YT¶YU¶ZA¶ZM¶ZW" ;

validemail =    Case (    IsEmpty ( theEmail ) ; ""  ; 
        IsEmpty ( EmailAddr ) ; "no e-mail address"  ;    
        PatternCount ( EmailAddr ; "@" ) <> 1 ; "invalid address" ; 
        PatternCount ( _theDomainPart ; "." ) = 0 ; "invalid formatting" ;
        Length ( Filter ( _theLocalPart ; _invalidLocalCharacters ) ) ;  "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  ) 
    ] ;

Case(  Left( theEmail ; 1 ) = ">" ;  theEmail ;
           IsEmpty( EmailAddr ) ; "" ;
           IsEmpty( theEmail ) ; "" ;
           validemail = 1 ; TextColor ( StripReserved ( EmailAddr )  ; RGB(0;0;255) ) ;


  ""  //  do NOT display is error

//  Display message on error
// TextStyleRemove(  TextColor ( StripReserved (   "> " & validemail  & ": " & EmailAddr ) ;  RGB(255;10 ;10 ) )  ; Bold )

)
)


// Used by fnValidURL which is used by fnBuildRSSFeed function.
// Source 2012 - http://www.briandunning.com/cf/1433

fnValidURL

// fnValidURL ( style ; URL )

Let ( [ 

IsEmail = If( IsEmpty ( fnValidEmail ( URL ) ) ; 0 ; 1 ) ; 

IsCall = If ( Left ( URL ; 7 ) = "callto:" ; 1 ; 0 ) ; 

weblink = Substitute ( URL ; [ "\\" ; "/" ] ) ; 

result = Filter ( GetAsText ( TrimAll ( weblink ; 0 ; 0 ) ) ; 
   "ABCDEFGHIJKLMNOPQRSTUVWXYZ" & "abcdefghijklomnopqrstuvwxyz" & 
   "1234567890:./-?|!#~@$%^ &*()_+ = {}[]" ) ; 

result = TextStyleRemove ( TextSizeRemove ( TextFontRemove ( TextColorRemove ( result ) ) ) ; AllStyles ) ; 

result1 = Case ( 
   not IsEmpty ( Filter ( result ; "?|!#@$%^ &*()+ = {}[]" ) ) ; result ; // do not format if special characters
   MimeType ( result ; "" ) ≠ "unknown" ; result ; 
   Right ( result ; 1 ) = "/" ; result ; 
   result & "/" ) ; 

result1 = Case ( 
   Left ( result1 ; 1 ) = "/" ; Right ( result1 ; Length ( result1 ) - 1 ) ; 
   Left ( result1 ; 4 ) ≠ "http" and not IsEmpty ( result1 ) ; "http://" & result1 ; 
   result1 ) ; 

result2 = If ( PatternCount ( result1 ; "www." ) > 0 ; 
   result1 ; Substitute ( result1 ; "://" ; "://www." ) ) ; 

// Strip additional url from path to determin root domain
Domain = Substitute ( result1 
   ; [ "https:" ; "" ]
   ; [ "http:" ; "" ]
   ; [ "www." ; "" ]
   ; [ "//" ; "" ]
   ) ; 
Domain = Case ( 
   PatternCount ( Domain ; "m?" ) > 0 ; 
      Left ( Domain ; Length ( Domain ) - Position ( Domain ; "?" ; 1 ; 1 ) ) ; 
   PatternCount ( Domain ; "/" ) > 0 ; 
      Left ( Domain ; Position ( Domain ; "/" ; 1 ; 1 ) ) ; 
   PatternCount ( Domain ; "@" ) > 0 ; 
      Right ( Domain ; Length ( Domain ) - Position ( Domain ; "@" ; 1 ; 1 ) ) ; "" ) ; 

sufix = Case ( 
   PatternCount ( Domain ; ".co.uk" ) > 0 ; ".co.uk" ; 
   PatternCount ( Domain ; ".me.uk" ) > 0 ; ".me.uk" ; 
   PatternCount ( Domain ; ".org.uk" ) > 0 ; ".org.uk" ; 
   PatternCount ( Domain ; ".com" ) > 0 ; ".com" ; 
   PatternCount ( Domain ; ".org" ) > 0 ; ".org" ; 
   PatternCount ( Domain ; ".net" ) > 0 ; ".net" ; 
   PatternCount ( Domain ; ".biz" ) > 0 ; ".biz" ; 
   PatternCount ( Domain ; ".info" ) > 0 ;".info" ; 
   PatternCount ( Domain ; ".us"  ) > 0 ; ".us" ; 
   PatternCount ( Domain ; ".edu" ) > 0 ; ".edu" ; 
   PatternCount ( Domain ; ".gov" ) > 0 ; ".gov" ; 
   PatternCount ( Domain ; ".bz" ) > 0 ; ".bz" ; 
   PatternCount ( Domain ; ".cc" ) > 0 ; ".cc" ; 
   "" ) ; 

Domain = Left ( Domain ; Position ( Domain ; sufix ; 1 ; 1 ) - 1 ) & sufix ;

email = Case ( 
   not IsEmail ; "" ;
   Left ( URL ; 7 ) = "mailto:" ; URL ;
   "mailto:" & fnValidEmail ( URL )
   ) ; 

call = If ( not IsCall ; "" ; URL )

] ; 

Case ( 

Left ( URL ; 1 ) = ">" ; URL ; 
Left ( URL ; 1 ) = "#" ; URL ; 
IsEmpty ( Domain ) ; "" ;
style = 0 ; Domain ; 
style = "Domain" ; Domain ; 
IsEmail ; email ; 
IsCall ; URL ;
style = 2 or style = "Link" ; "<a href = \"" & result1 & "\">" & result & "</a>& nbsp;" ; 
style = "URL" ; result1 ; 
style = "www" ; result2 ; 
style = 1 ; result1 ; 
style = 3 ; result1 ; 
TextColor ( result ; RGB ( 0 ; 0 ; 255 ) )

)  //end case

)  //end Let


// Used by fnBuildRSSFeed function.
// Custom function Dependencies: fnValidEmail

// fnValidURL ( style ; URL ) sample results
// style = "Domain" ; 2geckos.com
// style = "URL" ; "http://2geckos.com/" ; 
// style = "www" ; "http://www.2geckos.com/" ; 
// style = "link" ; "<a href = \"http://www.2geckos.com/"> ; 

// Source 2012 - http://www.briandunning.com/cf/1396

fnWebChars

// fnWebChars ( text )

Trim ( Substitute ( text ; 
[ Char(9) ; " " ] ; 	// tab change to space
[ Char(10) ; ¶ ] ; 	// line feed change to return (pill crow or <br>)
[ Char(11) ; " " ] ; 	// vertical tab change to space
[ Char(14) ; "" ] ; 	// shift out
[ Char(15) ; "" ] ; 	// shift in
[ Char(16) ; "" ] ; 	// data link escape
[ Char(22) ; "" ] ; 	// synchronous idle
[ Char(23) ; "" ] ; 	// end of transmission
[ Char(24) ; "" ] ; 	// cancel
[ Char(27) ; "" ] ; 	// escape
[ "\"" ; "&#34;" ] ; 	// double quote
[ "&" ; "&#38;" ] ; 	// ampersand
[ "'" ; "&#39;" ] ; 	// apostrophe
[ Char(160) ; "&#160;" ] ; 	// non-breaking space
[ "¡" ; "&#161;" ] ; 	// inverted exclamation mark
[ "¥" ; "&#165;" ] ; 	// yen
[ "©" ; "&#169;" ] ; 	// copyright
[ "­" ; "&#173;" ] ; 		// soft hyphen
[ "®" ; "&#174;" ] ; 	// registered trademark
[ "°" ; "&#176;" ] ; 		// degree
[ "¿" ; "&#191;" ] ; 	// inverted question mark
[ "À" ; "&#192;" ] ; 
[ "Á" ; "&#193;" ] ; 
[ "Â" ; "&#194;" ] ; 
[ "Ã" ; "&#195;" ] ; 
[ "Ä" ; "&#196;" ] ; 
[ "Å" ; "&#197;" ] ; 
[ "Æ" ; "&#198;" ] ; 
[ "Ç" ; "&#199;" ] ; 
[ "È" ; "&#200;" ] ; 
[ "É" ; "&#201;" ] ; 
[ "Ê" ; "&#202;" ] ; 
[ "Ë" ; "&#203;" ] ; 
[ "Ì" ; "&#204;" ] ; 
[ "Í" ; "&#205;" ] ; 
[ "Î" ; "&#206;" ] ; 
[ "Ï" ; "&#207;" ] ; 
[ "Ñ" ; "&#209;" ] ; 
[ "Ò" ; "&#210;" ] ; 
[ "Ó" ; "&#211;" ] ; 
[ "Ô" ; "&#212;" ] ; 
[ "Õ" ; "&#213;" ] ; 
[ "Ö" ; "&#214;" ] ; 
[ "Ù" ; "&#217;" ] ; 
[ "Ú" ; "&#218;" ] ; 
[ "Û" ; "&#219;" ] ; 
[ "Ü" ; "&#220;" ] ; 
[ "ß" ; "&#223;" ] ; 
[ "à" ; "&#224;" ] ; 
[ "á" ; "&#225;" ] ; 
[ "â" ; "&#226;" ] ; 
[ "ã" ; "&#227;" ] ; 
[ "ä" ; "&#228;" ] ; 
[ "å" ; "&#229;" ] ; 
[ "æ" ; "&#230;" ] ; 
[ "ç" ; "&#231;" ] ; 
[ "é" ; "&#233;" ] ; 
[ "è" ; "&#232;" ] ; 
[ "ê" ; "&#234;" ] ; 
[ "ë" ; "&#235;" ] ; 
[ "ì" ; "&#236;" ] ; 
[ "í" ; "&#237;" ] ; 
[ "î" ; "&#238;" ] ; 
[ "ï" ; "&#239;" ] ; 
[ "ñ" ; "&#241;" ] ; 
[ "ò" ; "&#242;" ] ; 
[ "ó" ; "&#243;" ] ; 
[ "ô" ; "&#244;" ] ; 
[ "õ" ; "&#245;" ] ; 
[ "ö" ; "&#246;" ] ; 
[ "ù" ; "&#249;" ] ; 
[ "ú" ; "&#250;" ] ; 
[ "û" ; "&#251;" ] ; 
[ "ü" ; "&#252;" ] ; 
[ "ÿ" ; "&#255;" ] ; 
[ "Œ" ; "&#338;" ] ; 
[ "œ" ; "&#339;" ] ; 
[ "Ÿ" ; "&#376;" ] ; 	// capital Y with diaeres
[ "ˇ" ; "&#711;" ] ; 	// caron
[ "˘" ; "&#728;" ] ; 
[ "˙" ; "&#729;" ] ; 	// dot above
[ "˚" ; "&#730;" ] ; 
[ "˛" ; "&#731;" ] ; 	// ogonek
[ "˜" ; "&#732;" ] ; 	// small tilde
[ "˝" ; "&#733;" ] ; 
[ "–" ; "&#8211;" ] ; 	// en dash
[ "—" ; "&#8212;" ] ; 	// em dash
[ "‘" ; "&#8216;" ] ; 	// left single quotation mark
[ "’" ; "&#8217;" ] ; 	// right single quotation mark
[ "‚" ; "&#8218;" ] ; 	// single low-9 quotation mark
[ "\“" ; "&#8220;" ] ; 	// left double quotation mark
[ "\”" ; "&#8221;" ] ; 	// right double quotation mark
[ "\„" ; "&#8222;" ] ; 	// double low-9 quotation mark
[ "†" ; "&#8224;" ] ; 	// dagger
[ "‡" ; "&#8225;" ] ; 	// double dagger
[ "•" ; "&#8226;" ] ; 	// bullet
[ "€" ; "&#8364;" ] ; 	// euro
[ "™" ; "&#8482;" ] ; 	// trademark
[ "∂" ; "&#8706;" ] ; 	// part
[ "∫" ; "&#8747;" ] ; 	// integral
[ "≤" ; "&#8804;" ] ; 	// less or equal
[ "≥" ; "&#8805;" ]	// greater or equal

) //end substitute
) //end trim

// Replaces unusual characters with their web equivalent.
// http://www.w3schools.com/tags/ref_entities.asp
// Updated to sort by entity number.  - 4/27/2011
// Updated to not change returns and cleaned up some note text.  - 3/16/2012

fnXMLencode

// fnXMLencode ( text )
Substitute ( text
 ;  [ "|" ; " " ]
 ;  [ " " ; " " ]
 ;  [ "&" ; "&" ]
 ;  [ "<" ; "&lt;" ]
 ;  [ ">" ; "&gt;" ]
 ;  [ "\"" ; "&quot;" ]
 ;  [ "'" ; "&apos;" ]
)  //end substitute


// Used by fnBuildRSSFeed function.
// Source 2011 - http://www.briandunning.com/cf/496

fnXOR

// fnXOR ( numberOne ; numberTwo )
Let ( [
// convert parameters to non-empty positive integers
vN1 = 0 + Abs ( Int ( numberOne ) ) ;
vN2 = 0 + Abs ( Int ( numberTwo ) )
] ;

Case (

vN1 = 0 ; vN2 ;
vN2 = 0 ; vN1 ;
vN1 = vN2 ; 0 ;

Let ( [

   // isolate local variables
   x = $x ; i = $i ; fx = $fx ;

   // function to convert a decimal number ($x) to a list of binary digits (low bit first)
   $fx = "
      let([¶
         b = if( $x > 0; floor(lg( $x )) );¶
         $i = 1¶
      ];¶
         evaluate(¶
            \"mod($x;2)\"¶
            & if( b ;¶
               substitute( 10^b-1; 9; \"&\\\¶&let($i=$i+1;div(mod($x;2^$i);2^($i-1)))\" )¶
            )¶
         )¶
      )
   " ;

   // create two binary lists
   $x = vN1 ;
   vBL1 = Evaluate ( $fx ) ;
   $x = vN2 ;
   vBL2 = Evaluate ( $fx ) ;

   // apply boolean xor to the binary lists and summarize the results
   $i = -1 ;
   $x = If ( vN1 < vN2 ; vBL1 ; vBL2 ) ;
   LST = If ( vN1 > vN2 ; vBL1 ; vBL2 ) & ¶ ;
   vEXPRS = "let([x="
      & Substitute ( LST ; ¶ ;
         ";$i=$i+1;y=getvalue($x;$i+1)];(x xor y)*2^$i)¶+let([x="
      ) &"0];x)" ;
   vRSLT = Evaluate ( vEXPRS ) ;
   $x = vRSLT ;
   vBL3 = Evaluate ( $fx ) ;
   vCount = PatternCount ( vBL3 ; 1 ) ;

   // restore isolated variables
   $x = x ; $i = i ; $fx = fx

] ; vCount )  // end let

)  // end case
)  // end let


// Non-recursive binary XOR function.
// Returns the Hamming Distance, or the number of binary digets that do not match.
// For image similarity comparison.
// https://www.briandunning.com/cf/2373

fnZapValues

// fnZapValues ( keeplist ; droplist )

Let ( [ 
vKeepList = substitute ( keeplist ; [ char(13)&char(10) ; char(13) ] ; [ char(10) ; char(13) ] ) ; 
vDropList = substitute ( droplist ; [ char(13)&char(10) ; char(13) ] ; [ char(10) ; char(13) ] ) ; 
vTopValue = GetValue ( fnSuperTrim ( vKeepList ) ; 1 ) ; 
vKeep = IsEmpty ( FilterValues ( vTopValue ; ¶& vDropList ) ) and vTopValue ≠ "" ; 
vNextKeep = fnSuperTrim ( RightValues ( vKeepList ; ValueCount ( vKeepList ) - 1 ) ) ; 
vNextDrop = fnSuperTrim ( vDropList ) ; 
vReturn = If ( vKeep and ValueCount ( vNextKeep ) ≠ ValueCount ( FilterValues ( vNextKeep ; vNextDrop ) ) ; ¶ ) 
] ; 

If ( vKeepList = "" ; "" ; 
GetValue ( vKeepList ; vKeep ) & vReturn & 
fnZapValues ( vNextKeep ; vNextDrop )
)  //end if

)  //end Let



// Removes the items in ListB from ListA.  -rcaldwell 6/2013
// Custom function Dependencies: fnSuperTrim
// If the next keeplist value is in the droplist, drop it.
// Then run fnZapValues again with a shorter keep list.
// Source 1 - http://www.briandunning.com/cf/193
// Source 2 - http://www.briandunning.com/cf/596

fnzCreateData

// fnzCreateData
"CreationTimestamp¶" & 
Get ( CurrentTimestamp ) & 
"¶CreatorAccountName¶" & 
Get ( AccountName ) & 
"¶CreatorUserName¶" & 
Get ( UserName ) & 
"¶SystemPlatform¶" & 
Get ( SystemPlatform ) & 
"¶SystemVersion¶" & 
Get ( SystemVersion ) & 
"¶ApplicationVersion¶" & 
Get ( ApplicationVersion ) & 
"¶ScreenHeight¶" & 
Get ( ScreenHeight ) & 
"¶ScreenWidth¶" & 
Get ( ScreenWidth ) & 
"¶PersistentID¶" & 
Get ( PersistentID ) & 
"¶AccountPrivilegeSetName¶" & 
Get ( AccountPrivilegeSetName ) & 
"¶UTC Time¶" & 
Get ( CurrentTimeUTCMilliseconds ) & 
"¶HostName¶" & 
Get ( HostName ) & 
"¶CurrentHostTimestamp¶" & 
Get ( CurrentHostTimestamp ) & 
"¶HostApplicationVersion¶" & 
Get ( HostApplicationVersion ) & 
"¶DocumentsPath¶" & 
Get ( DocumentsPath ) & 
"¶LayoutName¶" & 
Get ( LayoutName ) & 
"¶ScriptName¶" & 
Get ( ScriptName ) & 
"¶HostIPAddress¶" & 
Get ( HostIPAddress ) & 
"¶SystemIPAddress¶" & 
Get ( SystemIPAddress )



// zz_CreateData should be a text field with auto-enter calculated value containing this formula.
// CHECK 'Do not replace existing value of field' box.
// CHECK 'Prohibit modification of value during data entry' box.
// Field order should match zz_ModData.
// This could be a text field, or custom function depending on your installation.

// Warning: You may still want separate creation account name and creation timestamp fields in case this field does not update when using the Duplicate Record command.

// Instead of making lots of maintenance fields, this one field will capture as much data as possible when a record is created, but may slow the process.  Importing 4,000 records over WAN took 10 sec with zz_ fields, 8 sec without fields (2/2015).
// To retrieve specific data for searching or sorting, make a calc field to isolate the data from this field, for example to get the creation date, use this formula:
//    GetAsDate ( GetValue ( zz_CreateData ; 2 ) )
// SystemIPAddress is last because it may contain multiple values making the GetValue statement more difficult.  -rcaldwell 11/8/2010

// PersistentID added, a new feature of FileMaker 12.  -rcaldwell 10/17/2012
// Screen height & width added.  -rcaldwell 4/28/2014
// UTC Time added, a new feature of FileMaker 13.  -rcaldwell 2015
// PrivilegeSetName and CurrentHostTimestamp added.  -rcaldwell 7/2016
// DocumentsPath added.  -rcaldwell 2/2018
// Changed zz_CreateData to fnzCreateData because I use this more as a custom function now.  -rcaldwell 6/2019
// Host Name added.  -rcaldwell 8/2019
// Layout Name and Script Name added for record diagnostics.  -rcaldwell 11/2019

fnzModData

// fnzModData ( zz_ModTS )
"ModifiedTimeStamp¶" & 
zz_ModTS & 
"¶ModifierAccountName¶" & 
Get ( AccountName ) & 
"¶ModifierUserName¶" & 
Get ( UserName ) & 
"¶SystemPlatform¶" & 
Get ( SystemPlatform ) & 
"¶SystemVersion¶" & 
Get ( SystemVersion ) & 
"¶ApplicationVersion¶" & 
Get ( ApplicationVersion ) & 
"¶ScreenHeight¶" & 
Get ( ScreenHeight ) & 
"¶ScreenWidth¶" & 
Get ( ScreenWidth ) & 
"¶PersistentID¶" & 
Get ( PersistentID ) & 
"¶AccountPrivilegeSetName¶" & 
Get ( AccountPrivilegeSetName ) & 
"¶UTC Time¶" & 
Get ( CurrentTimeUTCMilliseconds ) & 
"¶HostName¶" & 
Get ( HostName ) & 
"¶CurrentHostTimestamp¶" & 
Get ( CurrentHostTimestamp ) & 
"¶HostApplicationVersion¶" & 
Get ( HostApplicationVersion ) & 
"¶DocumentsPath¶" & 
Get ( DocumentsPath ) & 
"¶LayoutName¶" & 
Get ( LayoutName ) & 
"¶ScriptName¶" & 
Get ( ScriptName ) & 
"¶HostIPAddress¶" & 
Get ( HostIPAddress ) & 
"¶SystemIPAddress¶" & 
Get ( SystemIPAddress )



// Requires zz_ModTS field, which is simply a modification timestamp field that triggers this field to update.
// zz_ModData should be a text field with auto-enter calculated value containing this formula.
// UNCHECK 'Do not replace existing value of field' box.
// CHECK 'Prohibit modification of value during data entry' box.
// Field order should match zz_CreateData.
// This could be a text field, or custom function depending on your installation.

// Instead of making lots of maintenance fields, this one field will capture as much data as possible when a record is modified, but may slow the process.  Importing 4,000 records over WAN took 10 sec with zz_ fields, 8 sec without fields (2/2015).
// To retrieve specific data for searching or sorting, make a calc field to isolate the data from this field, for example to get the modification date, use this formula:
//    GetAsDate ( GetValue ( zz_ModData ; 2 ) )
// SystemIPAddress is last because it may contain multiple values making the GetValue statement more difficult.  -rcaldwell 11/8/2010

// PersistentID added, a new feature of FileMaker 12.  -rcaldwell 10/17/2012
// Screen height & width added.  -rcaldwell 4/28/2014
// UTC Time added, a new feature of FileMaker 13.  -rcaldwell 2015
// PrivilegeSetName and CurrentHostTimestamp added.  -rcaldwell 7/2016
// DocumentsPath added.  -rcaldwell 2/2018
// Changed zz_ModData to fnzModData because I use this more as a custom function now.  -rcaldwell 6/2019
// Host Name added.  -rcaldwell 8/2019
// Layout Name and Script Name added for record diagnostics.  -rcaldwell 11/2019

z_AuditLog

// AuditLog - Auto enter calc that replaces existing value. Prohibit modification.
// NOT be a custom function, it should be a FIELD so the result is indexed for fast find, and so non-FMAdv clients could add fields to the log list.
//v5 updated 7/2014

Let ( [ 
// List fields to log (alpha order)
vTrigger = field1 & field2
; 
vRepNum = If ( Get ( ActiveRepetitionNumber ) > 1 ; "[" & Get ( ActiveRepetitionNumber ) & "]") ; 
vFieldName = Get ( ActiveFieldName ) & vRepNum ; 
vOldRef = Position ( Self ; Char(9) & vFieldName & Char(9) ; 1 ; 1 ) ; 
vOldStart = Position ( Self ; Char(9) & "-»" & Char(9) ; vOldRef ; 1) + 4 ; 
vOldEnd = Position ( Self & ¶ ; ¶ ; vOldRef ; 1 ) ; 
vOldValue = If ( vOldRef; Middle ( Self ; vOldStart ; vOldEnd - vOldStart ) ; "[---]" ) ; 
vNewValue = Substitute ( GetField ( vFieldName ) ; ¶ ; "‡" ) ; 
vNewValue = If ( Length ( vNewValue ) ; vNewValue ; "[null]" ) 
] ; 

If ( Length ( vFieldName ) and Length ( Get ( ScriptName ) ) = 0 ;
   GetAsDate ( Get ( CurrentHostTimestamp ) ) & Char(9) &
   GetAsTime ( Get ( CurrentHostTimestamp ) ) & Char(9) &
   Get ( AccountName ) & Char(9) &
   vFieldName & Char(9) & 
   vOldValue & Char(9) & "-»" & Char(9) & 
   vNewValue & ¶ & 
   Self ; 
   Self
) //end if

) //end Let


// Fields listed in this calculation are tracked here each time data is modified by a user. This does not track changes to related tables (add another AuditLog field in that table), does not track changes when a script is running (causes problems) and does not track changes when users drag and drop text into a field or when the Set Field script step is used.  Add all the fields from the current table you want to track in the audit log as part of the vTrigger variable, then when data changes, it pulls the original value from the previous entry in this field, and compares it to the new value picked up by the field modification.
// Adapted from NightWing Enterprises (NZ) sample file.  -rcaldwell
// Use fnAuditLogLoad to fill the AuditLog field with initial values.

// Recommended audit field layout indents: first line -432pt, left 432pt.  (2017)
// Recommended audit field layout tab positions: 78pt, 172pt, 270pt, 402pt, 558pt, 588pt. (2017)

// Recommended audit field layout indents: first line -350pt, left 353pt, right 3pt.  (2014)
// Recommended audit field layout tab positions: 72pt, 160pt, 270pt, 380pt, 510pt, 540pt.  (2014)

z_Birthday

Let ( [ 
vBirthday = GetAsDate ( "1/12/1980" ) ; 
vToday = Get ( CurrentDate )
] ; 

Year ( vToday ) - Year ( vBirthday ) - If ( DayOfYear ( vToday ) <= DayOfYear ( vBirthday ) ; 1 )

)  //end Let


// Calculate age based on birthday and today's date.  -rcaldwell 2015

z_DisplayStatus

// StatusDisplay_cu

//Base ID criteria
"AccountName¶" & 
Get ( AccountName ) & 
"¶SystemPlatform¶" & 
Get ( SystemPlatform ) & 
"¶ScreenHeight¶" & 
Get ( ScreenHeight ) & 
"¶ScreenWidth¶" & 
Get ( ScreenWidth ) & 

//Other factors to consider
"ApplicationVersion¶" & 
Get ( ApplicationVersion ) & 
"¶SystemVersion¶" & 
Get ( SystemVersion ) & 
"¶CountWindows¶" & 
ValueCount ( WindowNames ) & 
"¶ScreenDepth¶" & 
Get ( ScreenDepth ) & 
"¶StatusAreaState¶" & 
Get ( StatusAreaState ) & 

//Window functions
"¶WindowName¶" & 
Get ( WindowName ) & 
"¶WindowMode¶" & 
Get ( WindowMode ) & 
"¶WindowZoomLevel¶" & 
Get ( WindowZoomLevel ) & 

"¶WindowContentHeight¶" & 
Get ( WindowContentHeight ) & 
"¶WindowContentWidth¶" & 
Get ( WindowContentWidth ) & 

"¶WindowDesktopHeight¶" & 
Get ( WindowDesktopHeight ) & 
"¶WindowDesktopWidth¶" & 
Get ( WindowDesktopWidth ) & 

"¶WindowHeight¶" & 
Get ( WindowHeight ) & 
"¶WindowWidth¶" & 
Get ( WindowWidth ) & 

"¶WindowTop¶" & 
Get ( WindowTop ) & 
"¶WindowLeft¶" & 
Get ( WindowLeft ) & 
¶& 

//Multi-value functions
"¶WindowNames¶" & 
WindowNames


// Get info as each window closes?
// Save settings per monitor using screen size?
// Application version and system specs help determine how to interpret the rest of the results.
// How will this work with older FM versions?
// Multi-value functions are listed last because their position is unpredictable when using GetValue.
// -rcaldwell 2011

z_ExecuteSQL_sample

// ExecuteSQL sample
Let ( [ 
vSelect01 = GetFieldName ( Orders::PayDue_cu ) ; 
vTable01 = GetValue ( Substitute ( vSelect01 ; "::" ; ¶ ) ; 1 ) ; 
vField01 = GetValue ( Substitute ( vSelect01 ; "::" ; ¶ ) ; 2 ) ; 
vSelect02 = GetFieldName ( Orders::StatusOrder_c ) ; 
vTable02 = GetValue ( Substitute ( vSelect02 ; "::" ; ¶ ) ; 1 ) ; 
vField02 = GetValue ( Substitute ( vSelect02 ; "::" ; ¶ ) ; 2 ) ; 
vCode01 = "SELECT SUM (\"" & vField01 & "\") FROM \"" & vTable01
   & "\" WHERE \"" & vField02 & "\" = 'Unpaid'" ; 
vTotalUnpaid = ExecuteSQL ( vCode01 ; "" ; "" ) ; 
vCode02 = "SELECT COUNT (\"" & vField02 & "\") FROM \"" & vTable02 & "\" WHERE \"" & vField02 & "\" = 'Unpaid'" ; 
vCountUnpaid = ExecuteSQL ( vCode02 ; "" ; "" )
] ; 

vCountUnpaid & " Unpaid records" & " totaling $" & vTotalUnpaid
//&¶& vCode01

)  //end Let


// This example allows field names to change without breaking the calculation.
// Shows both Sum and Count queries.

z_ExplodeAddress

// Explode Mailing Address into Fields - v3  8/2015

Let ( [ 
$field = Get ( ActiveFieldTableName ) & "::" & Get ( ActiveFieldName ) ; 

$value = GetField ( $field ) ; 

// Recommend cleaning the text block of gremlins before processing.
//vAddress = Substitute ( fnCleanText ( $value )
vAddress = Substitute ( $value
   ; [ "•" ; ¶ ]  // Split web footer addresses into lines
   ; [ "|" ; ¶ ]
   ; [ "(" ; ¶ ]  // Put phone numbers on a separate line
   ; [ "¶¶" ; ¶ ]
   ) ; 

//vNameCo will become $name1 after $name2 is determined
vNameCo = If ( GetAsNumber ( GetValue ( vAddress ; 1 ) ) = 0 ; Trim ( GetValue ( vAddress ; 1 ) ) ) ; 

vLine1 = If ( vNameCo = "" ; Trim ( GetValue ( vAddress ; 1 ) ) ) ; 
vLine2 = Trim ( GetValue ( vAddress ; 2 ) ) ; 
vLine3 = Trim ( GetValue ( vAddress ; 3 ) ) ; 
vLine4 = Trim ( GetValue ( vAddress ; 4 ) ) ; 
vLine5 = Trim ( GetValue ( vAddress ; 5 ) ) ; 
vLine6 = Trim ( GetValue ( vAddress ; 6 ) ) ; 

$attn = Case ( 
   PatternCount ( vLine1 ; "attn" ) ; vLine1 ; 
   PatternCount ( vLine2 ; "attn" ) ; vLine2 ; 
   PatternCount ( vLine3 ; "attn" ) ; vLine3 ; 
   PatternCount ( vLine4 ; "attn" ) ; vLine4 ; 
   ) ; 

// CSZ line should not start with a digit, 
// otherwise this would be a zipcode line: 1215 Park Road NW #3
vCSZ = Case ( 
   ( Filter ( Left ( vLine6 ; 1 ) ; "1234567890" ) = "" and ( 
      Length ( Filter ( vLine6 ; "1234567890" ) ) = 5 or 
      Length ( Filter ( vLine6 ; "1234567890" ) ) = 9 ) ) ; vLine6 ; 
   ( Filter ( Left ( vLine5 ; 1 ) ; "1234567890" ) = "" and ( 
      Length ( Filter ( vLine5 ; "1234567890" ) ) = 5 or 
      Length ( Filter ( vLine5 ; "1234567890" ) ) = 9 ) ) ; vLine5 ; 
   ( Filter ( Left ( vLine4 ; 1 ) ; "1234567890" ) = "" and ( 
      Length ( Filter ( vLine4 ; "1234567890" ) ) = 5 or 
      Length ( Filter ( vLine4 ; "1234567890" ) ) = 9 ) ) ; vLine4 ; 
   ( Filter ( Left ( vLine3 ; 1 ) ; "1234567890" ) = "" and ( 
      Length ( Filter ( vLine3 ; "1234567890" ) ) = 5 or 
      Length ( Filter ( vLine3 ; "1234567890" ) ) = 9 ) ) ; vLine3 ; 
   ( Filter ( Left ( vLine2 ; 1 ) ; "1234567890" ) = "" and ( 
      Length ( Filter ( vLine2 ; "1234567890" ) ) = 5 or 
      Length ( Filter ( vLine2 ; "1234567890" ) ) = 9 ) ) ; vLine2 ; 
   ( Filter ( Left ( vLine1 ; 1 ) ; "1234567890" ) = "" and ( 
      Length ( Filter ( vLine1 ; "1234567890" ) ) = 5 or 
      Length ( Filter ( vLine1 ; "1234567890" ) ) = 9 ) ) ; vLine1 ; 
   ) ; 

// Phone line check of just 10 digits,
// otherwise this would be a phone: 13190 SW 68th Parkway, Suite 200
vPhoneLine = Case ( 
   WordCount ( vLine6 ) < 5 and ( 
      Length ( Filter ( vLine6 ; "1234567890" ) ) = 7 or 
      Length ( Filter ( vLine6 ; "1234567890" ) ) = 10 ) ; vLine6 ; 
   WordCount ( vLine5 ) < 5 and ( 
      Length ( Filter ( vLine5 ; "1234567890" ) ) = 7 or 
      Length ( Filter ( vLine5 ; "1234567890" ) ) = 10 ) ; vLine5 ; 
   WordCount ( vLine4 ) < 5 and ( 
      Length ( Filter ( vLine4 ; "1234567890" ) ) = 7 or 
      Length ( Filter ( vLine4 ; "1234567890" ) ) = 10 ) ; vLine4 ; 
   WordCount ( vLine3 ) < 5 and ( 
      Length ( Filter ( vLine3 ; "1234567890" ) ) = 7 or 
      Length ( Filter ( vLine3 ; "1234567890" ) ) = 10 ) ; vLine3 ; 
   WordCount ( vLine2 ) < 5 and ( 
      Length ( Filter ( vLine2 ; "1234567890" ) ) = 7 or 
      Length ( Filter ( vLine2 ; "1234567890" ) ) = 10 ) ; vLine2 ; 
   WordCount ( vLine1 ) < 5 and ( 
      Length ( Filter ( vLine1 ; "1234567890" ) ) = 7 or 
      Length ( Filter ( vLine1 ; "1234567890" ) ) = 10 ) ; vLine1 ; 
   ) ; 
vPhone = Filter ( vPhoneLine ; "1234567890" ) ; 
$phone = Case ( 
   Length ( vPhone ) = 7 ; 
      Left ( vPhone ; 3 ) & "-" & Middle ( vPhone ; 4 ; 4 ) ; 
   Length ( vPhone ) = 10 ; "(" & Left ( vPhone ; 3 ) & ") " & 
      Middle ( vPhone ; 4 ; 3 ) & "-" & Middle ( vPhone ; 7 ; 4 ) 
   ) ; 

// Email
vEmailValue = Substitute ( $value ; ¶ ; " " ) ; 
vEmailSpace = PatternCount ( Left ( vEmailValue ; Position ( vEmailValue ; "@" ; 1 ; 1 ) ) ; " " ) ; 
vEmailStart = Position ( vEmailValue ; " " ; 1 ; vEmailSpace ) ; 
vEmailEnd = Position ( vEmailValue ; " " ; 1 ; vEmailSpace + 1 ) ; 
$email = If ( PatternCount ( vEmailValue ; "@" ) ; 
   Trim ( Middle ( vEmailValue ; vEmailStart ; vEmailEnd - vEmailStart ) ) ) ; 

$street_lines = fnSuperTrim ( Substitute ( vAddress ; [ $attn ; "" ] ; [ vNameCo ; "" ] ; [ vCSZ ; "" ] ; [ $email ; "" ] ; [ vPhoneLine ; "" ] ) ) ; 

vNamePerson = If ( ValueCount ( $street_lines ) > 2 and 
   GetAsNumber ( GetValue ( $street_lines ; 1 ) ) = 0 ; 
   GetValue ( $street_lines ; 1 ) ) ; 

// Use vNameCo if it is 2 words, unless it has a comma like MFAC, LLC
$name2 = If ( vNamePerson = "" and WordCount ( vNameCo ) = 2 and PatternCount ( vNameCo ; "," ) = 0 ; vNameCo ; vNamePerson ) ; 

$name1 = If ( vNameCo ≠ $name2 ; vNameCo ) ; 

$street_lines = fnSuperTrim ( Substitute ( $street_lines ; [ vNamePerson ; "" ] ; [ "United States" ; "" ] ) ) ; 

$city = If ( PatternCount ( vCSZ ; "," ) ; 
   Left ( vCSZ ; Position ( vCSZ ; "," ; 1 ; 1 ) - 1 ) ;  // Everything up to the first comma
   LeftWords ( vCSZ ; WordCount ( vCSZ ) - 2 ) ) ;    // If no comma, take all but the last 2 words

$state = If ( PatternCount ( vCSZ ; "," ) ; 
   LeftWords ( Substitute ( vCSZ ; $city & "," ; "" ) ; 1 ) ;  // Word after the first comma
   MiddleWords ( vCSZ ; WordCount ( vCSZ ) - 1 ; 1 ) ) ;    // If no comma, take secont-to-the last word

$zip = If ( PatternCount ( vCSZ ; "," ) ; 
   Trim ( Substitute ( vCSZ ; [ $city ; "" ] ; [ "," ; "" ] ; [ $state ; "" ] ) ) ;  // Remainder after stripping the city, state, and comma.
   RightWords ( vCSZ ; 1 ) )    // If no comma, take the last word

] ; 

""

)  //end Let


// Produces variables to load into fields - $field, $value, $name1, $name2, $attn, $street_lines, $city, $state, $zip, $phone, $email
// Dependencies - fnCleanText() and fnSuperTrim() are recommended to help clean up the final text.
// Unlike previous address explosions, these results cannot be re-assembled and compared to the original text easily.
// Updated calc to use vNameCo as vNamePerson if it is 2 words long.  - 7/9/2015

z_FM2Word

// fm2Word ( input )

// Header
"<?xml version='1.0' encoding='UTF-8' standalone='yes'?>¶" &
"<?mso-application progid='Word.Document'?>¶" &
"<w:wordDocument xmlns:w='http://schemas.microsoft.com/office/word/2003/wordml' ¶" &
"xmlns:wx='http://schemas.microsoft.com/office/word/2003/auxHint' ¶" &
"xmlns:v='urn:schemas-microsoft-com:vml' ¶" & 
"xmlns:o='urn:schemas-microsoft-com:office:office'>¶" & 

// Styles
"<w:styles>¶" &
"<w:style w:type='paragraph' w:styleId='FileMakersHomebrew'>¶" &
"<w:name w:val='FileMakersHomebrew'/>¶" &
"<w:basedOn w:val='Standard'/>¶" &
"<w:rsid w:val='00AF7A99'/>¶" &
"<w:pPr><w:spacing w:line='360' w:line-rule='auto'/></w:pPr><w:rPr>¶" &
"<w:rFonts w:ascii='Garamond' w:h-ansi='Garamond'/>¶" &
"<wx:font wx:val='Garamond'/>¶" &
"<w:color w:val='7030A0'/>¶" &
"<w:sz w:val='24'/>¶" &
"<w:lang w:val='EN-US'/>¶" &
"</w:rPr>¶" &
"</w:style>¶" &
"</w:styles>¶" &


"<w:body>¶" & 
"<w:p><w:r><w:t>" &

Substitute ( input 
//; [ ¶ ; "" ] 
; [ ¶ ; "</w:t></w:r></w:p>¶<w:p><w:r><w:t>" ] 
; [ "'" ; "\"" ] 
//; [ "<p>" ; "<w:p><w:r><w:t>" ] 
//; [ "</p>" ; "</w:t></w:r></w:p>" ] 
//; [ "</p><p>" ; "</w:t></w:r></w:p><w:p><w:r><w:t>" ] 
//; [ "<p-Style-FileMaker-Homebrew>"; "<w:p><w:pPr><w:pStyle w:val='FileMakersHomebrew'/></w:pPr><w:r><w:t>" ] 
; [ "<table>" ; "<w:tbl>" ] 
; [ "</table>" ; "</w:tbl>" ] 
; [ "<tr>" ; "<w:tr>" ] 
; [ "</tr>" ; "</w:tr>" ] 
; [ "<td>" ; "<w:tc>" ] 
; [ "</td>" ; "</w:tc>" ] 
; [ "<section>" ; "<w:p><w:pPr><w:sectPr><w:type w:val='continuous'/></w:sectPr></w:pPr></w:p>" ] 
; [ "<section-2-col>" ; "<w:p><w:pPr><w:sectPr><w:type w:val='continuous'/><w:cols w:num='2' w:space='708'/></w:sectPr></w:pPr></w:p>" ] 
; [ "<section-3-col>" ; "<w:p><w:pPr><w:sectPr><w:type w:val='continuous'/><w:cols w:num='3' w:space='708'/></w:sectPr></w:pPr></w:p>" ] 
; [ "<b>" ; "</w:t></w:r><w:r><w:rPr><w:b/></w:rPr><w:t>" ] 
; [ "</b>" ; "</w:t></w:r><w:r><w:t>" ] 
; [ "<i>" ; "</w:t></w:r><w:r><w:rPr><w:i/></w:rPr><w:t>" ] 
; [ "</i>" ; "</w:t></w:r><w:r><w:t>" ] 
; [ "<pict>" ; "<w:pict><v:shape><v:imagedata src='" ] 
; [ "</pict>" ; "'/></v:shape></w:pict>"]
) &

// Footer
"</w:t></w:r></w:p>¶" & 
"</w:body></w:wordDocument>"



// Source 2015 - https://www.briandunning.com/cf/1765
// Generates Microsoft Word 2003 XML Documents from FileMaker.
// Export result into a textfile with extension '.xml' and it can be opened in MS Word, OpenOffice, Libre Office, ...
//
//  The expected input is a text with tags:
//
//  ¶ - end + start paragraph
//
//  <table> - tables
//  <tr><td></td></tr>
//  </table>
//
//  <section> - section break, continuous no new page
//  <section-2-col> - section break starting two column page layout
//  <section-3-col> - section break starting three column page layout
//
//  <b></b> - bold
//  
//  <i></i> - italics
//  
//
//  <pict></pict>
//
// Author: Jens Teich, http://jensteich.de

z_List_of_File_MIME_Types

List of File MIME Types

- Use the MIME Type to construct a download link for files.
<a href="data:application/octet-stream;charset=utf-16le;base64,//5mAG8AbwAgAGIAYQByAAoA">text file</a>

- The octet-stream is to force a download prompt. Otherwise, it will probably open in the browser. For CSV, you can use:
<a href="data:application/octet-stream,field1%2Cfield2%0Afoo%2Cbar%0Agoo%2Cgai%0A" download='myfile.doc'>CSV Octet</a>

- Using 'octet-stream' can cause problems in Safari.


Suffixes applicable | Media type and subtype(s)
.3dm	x-world/x-3dmf
.3dmf	x-world/x-3dmf
.a	application/octet-stream
.aab	application/x-authorware-bin
.aam	application/x-authorware-map
.aas	application/x-authorware-seg
.abc	text/vnd.abc
.acgi	text/html
.afl	video/animaflex
.ai	application/postscript
.aif	audio/aiff
.aif	audio/x-aiff
.aifc	audio/aiff
.aifc	audio/x-aiff
.aiff	audio/aiff
.aiff	audio/x-aiff
.aim	application/x-aim
.aip	text/x-audiosoft-intra
.ani	application/x-navi-animation
.aos	application/x-nokia-9000-communicator-add-on-software
.aps	application/mime
.arc	application/octet-stream
.arj	application/arj
.arj	application/octet-stream
.art	image/x-jg
.asf	video/x-ms-asf
.asm	text/x-asm
.asp	text/asp
.asx	application/x-mplayer2
.asx	video/x-ms-asf
.asx	video/x-ms-asf-plugin
.au	audio/basic
.au	audio/x-au
.avi	application/x-troff-msvideo
.avi	video/avi
.avi	video/msvideo
.avi	video/x-msvideo
.avs	video/avs-video
.bcpio	application/x-bcpio
.bin	application/mac-binary
.bin	application/macbinary
.bin	application/octet-stream
.bin	application/x-binary
.bin	application/x-macbinary
.bm	image/bmp
.bmp	image/bmp
.bmp	image/x-windows-bmp
.boo	application/book
.book	application/book
.boz	application/x-bzip2
.bsh	application/x-bsh
.bz	application/x-bzip
.bz2	application/x-bzip2
.c	text/plain
.c	text/x-c
.c++	text/plain
.cat	application/vnd.ms-pki.seccat
.cc	text/plain
.cc	text/x-c
.ccad	application/clariscad
.cco	application/x-cocoa
.cdf	application/cdf
.cdf	application/x-cdf
.cdf	application/x-netcdf
.cer	application/pkix-cert
.cer	application/x-x509-ca-cert
.cha	application/x-chat
.chat	application/x-chat
.class	application/java
.class	application/java-byte-code
.class	application/x-java-class
.com	application/octet-stream
.com	text/plain
.conf	text/plain
.cpio	application/x-cpio
.cpp	text/x-c
.cpt	application/mac-compactpro
.cpt	application/x-compactpro
.cpt	application/x-cpt
.crl	application/pkcs-crl
.crl	application/pkix-crl
.crt	application/pkix-cert
.crt	application/x-x509-ca-cert
.crt	application/x-x509-user-cert
.csh	application/x-csh
.csh	text/x-script.csh
.css	application/x-pointplus
.css	text/css
.cxx	text/plain
.dcr	application/x-director
.deepv	application/x-deepv
.def	text/plain
.der	application/x-x509-ca-cert
.dif	video/x-dv
.dir	application/x-director
.dl	video/dl
.dl	video/x-dl
.doc	application/msword
.dot	application/msword
.dp	application/commonground
.drw	application/drafting
.dump	application/octet-stream
.dv	video/x-dv
.dvi	application/x-dvi
.dwf	drawing/x-dwf (old)
.dwf	model/vnd.dwf
.dwg	application/acad
.dwg	image/vnd.dwg
.dwg	image/x-dwg
.dxf	application/dxf
.dxf	image/vnd.dwg
.dxf	image/x-dwg
.dxr	application/x-director
.el	text/x-script.elisp
.elc	application/x-bytecode.elisp (compiled elisp)
.elc	application/x-elc
.env	application/x-envoy
.eps	application/postscript
.es	application/x-esrehber
.etx	text/x-setext
.evy	application/envoy
.evy	application/x-envoy
.exe	application/octet-stream
.f	text/plain
.f	text/x-fortran
.f77	text/x-fortran
.f90	text/plain
.f90	text/x-fortran
.fdf	application/vnd.fdf
.fif	application/fractals
.fif	image/fif
.fli	video/fli
.fli	video/x-fli
.flo	image/florian
.flx	text/vnd.fmi.flexstor
.fmf	video/x-atomic3d-feature
.for	text/plain
.for	text/x-fortran
.fpx	image/vnd.fpx
.fpx	image/vnd.net-fpx
.frl	application/freeloader
.funk	audio/make
.g	text/plain
.g3	image/g3fax
.gif	image/gif
.gl	video/gl
.gl	video/x-gl
.gsd	audio/x-gsm
.gsm	audio/x-gsm
.gsp	application/x-gsp
.gss	application/x-gss
.gtar	application/x-gtar
.gz	application/x-compressed
.gz	application/x-gzip
.gzip	application/x-gzip
.gzip	multipart/x-gzip
.h	text/plain
.h	text/x-h
.hdf	application/x-hdf
.help	application/x-helpfile
.hgl	application/vnd.hp-hpgl
.hh	text/plain
.hh	text/x-h
.hlb	text/x-script
.hlp	application/hlp
.hlp	application/x-helpfile
.hlp	application/x-winhelp
.hpg	application/vnd.hp-hpgl
.hpgl	application/vnd.hp-hpgl
.hqx	application/binhex
.hqx	application/binhex4
.hqx	application/mac-binhex
.hqx	application/mac-binhex40
.hqx	application/x-binhex40
.hqx	application/x-mac-binhex40
.hta	application/hta
.htc	text/x-component
.htm	text/html
.html	text/html
.htmls	text/html
.htt	text/webviewhtml
.htx	text/html
.ice	x-conference/x-cooltalk
.ico	image/x-icon
.idc	text/plain
.ief	image/ief
.iefs	image/ief
.iges	application/iges
.iges	model/iges
.igs	application/iges
.igs	model/iges
.ima	application/x-ima
.imap	application/x-httpd-imap
.inf	application/inf
.ins	application/x-internett-signup
.ip	application/x-ip2
.isu	video/x-isvideo
.it	audio/it
.iv	application/x-inventor
.ivr	i-world/i-vrml
.ivy	application/x-livescreen
.jam	audio/x-jam
.jav	text/plain
.jav	text/x-java-source
.java	text/plain
.java	text/x-java-source
.jcm	application/x-java-commerce
.jfif	image/jpeg
.jfif	image/pjpeg
.jfif-tbnl	image/jpeg
.jpe	image/jpeg
.jpe	image/pjpeg
.jpeg	image/jpeg
.jpeg	image/pjpeg
.jpg	image/jpeg
.jpg	image/pjpeg
.jps	image/x-jps
.js	application/x-javascript
.js	application/javascript
.js	application/ecmascript
.js	text/javascript
.js	text/ecmascript
.jut	image/jutvision
.kar	audio/midi
.kar	music/x-karaoke
.ksh	application/x-ksh
.ksh	text/x-script.ksh
.la	audio/nspaudio
.la	audio/x-nspaudio
.lam	audio/x-liveaudio
.latex	application/x-latex
.lha	application/lha
.lha	application/octet-stream
.lha	application/x-lha
.lhx	application/octet-stream
.list	text/plain
.lma	audio/nspaudio
.lma	audio/x-nspaudio
.log	text/plain
.lsp	application/x-lisp
.lsp	text/x-script.lisp
.lst	text/plain
.lsx	text/x-la-asf
.ltx	application/x-latex
.lzh	application/octet-stream
.lzh	application/x-lzh
.lzx	application/lzx
.lzx	application/octet-stream
.lzx	application/x-lzx
.m	text/plain
.m	text/x-m
.m1v	video/mpeg
.m2a	audio/mpeg
.m2v	video/mpeg
.m3u	audio/x-mpequrl
.man	application/x-troff-man
.map	application/x-navimap
.mar	text/plain
.mbd	application/mbedlet
.mc$	application/x-magic-cap-package-1.0
.mcd	application/mcad
.mcd	application/x-mathcad
.mcf	image/vasa
.mcf	text/mcf
.mcp	application/netmc
.me	application/x-troff-me
.mht	message/rfc822
.mhtml	message/rfc822
.mid	application/x-midi
.mid	audio/midi
.mid	audio/x-mid
.mid	audio/x-midi
.mid	music/crescendo
.mid	x-music/x-midi
.midi	application/x-midi
.midi	audio/midi
.midi	audio/x-mid
.midi	audio/x-midi
.midi	music/crescendo
.midi	x-music/x-midi
.mif	application/x-frame
.mif	application/x-mif
.mime	message/rfc822
.mime	www/mime
.mjf	audio/x-vnd.audioexplosion.mjuicemediafile
.mjpg	video/x-motion-jpeg
.mm	application/base64
.mm	application/x-meme
.mme	application/base64
.mod	audio/mod
.mod	audio/x-mod
.moov	video/quicktime
.mov	video/quicktime
.movie	video/x-sgi-movie
.mp2	audio/mpeg
.mp2	audio/x-mpeg
.mp2	video/mpeg
.mp2	video/x-mpeg
.mp2	video/x-mpeq2a
.mp3	audio/mpeg3
.mp3	audio/x-mpeg-3
.mp3	video/mpeg
.mp3	video/x-mpeg
.mpa	audio/mpeg
.mpa	video/mpeg
.mpc	application/x-project
.mpe	video/mpeg
.mpeg	video/mpeg
.mpg	audio/mpeg
.mpg	video/mpeg
.mpga	audio/mpeg
.mpp	application/vnd.ms-project
.mpt	application/x-project
.mpv	application/x-project
.mpx	application/x-project
.mrc	application/marc
.ms	application/x-troff-ms
.mv	video/x-sgi-movie
.my	audio/make
.mzz	application/x-vnd.audioexplosion.mzz
.nap	image/naplps
.naplps	image/naplps
.nc	application/x-netcdf
.ncm	application/vnd.nokia.configuration-message
.nif	image/x-niff
.niff	image/x-niff
.nix	application/x-mix-transfer
.nsc	application/x-conference
.nvd	application/x-navidoc
.o	application/octet-stream
.oda	application/oda
.omc	application/x-omc
.omcd	application/x-omcdatamaker
.omcr	application/x-omcregerator
.p	text/x-pascal
.p10	application/pkcs10
.p10	application/x-pkcs10
.p12	application/pkcs-12
.p12	application/x-pkcs12
.p7a	application/x-pkcs7-signature
.p7c	application/pkcs7-mime
.p7c	application/x-pkcs7-mime
.p7m	application/pkcs7-mime
.p7m	application/x-pkcs7-mime
.p7r	application/x-pkcs7-certreqresp
.p7s	application/pkcs7-signature
.part	application/pro_eng
.pas	text/pascal
.pbm	image/x-portable-bitmap
.pcl	application/vnd.hp-pcl
.pcl	application/x-pcl
.pct	image/x-pict
.pcx	image/x-pcx
.pdb	chemical/x-pdb
.pdf	application/pdf
.pfunk	audio/make
.pfunk	audio/make.my.funk
.pgm	image/x-portable-graymap
.pgm	image/x-portable-greymap
.pic	image/pict
.pict	image/pict
.pkg	application/x-newton-compatible-pkg
.pko	application/vnd.ms-pki.pko
.pl	text/plain
.pl	text/x-script.perl
.plx	application/x-pixclscript
.pm	image/x-xpixmap
.pm	text/x-script.perl-module
.pm4	application/x-pagemaker
.pm5	application/x-pagemaker
.png	image/png
.pnm	application/x-portable-anymap
.pnm	image/x-portable-anymap
.pot	application/mspowerpoint
.pot	application/vnd.ms-powerpoint
.pov	model/x-pov
.ppa	application/vnd.ms-powerpoint
.ppm	image/x-portable-pixmap (used by GitHub)
.pps	application/mspowerpoint
.pps	application/vnd.ms-powerpoint
.ppt	application/mspowerpoint
.ppt	application/powerpoint
.ppt	application/vnd.ms-powerpoint
.ppt	application/x-mspowerpoint
.ppz	application/mspowerpoint
.pre	application/x-freelance
.prt	application/pro_eng
.ps	application/postscript
.psd	application/octet-stream
.pvu	paleovu/x-pv
.pwz	application/vnd.ms-powerpoint
.py	text/x-script.phyton
.pyc	application/x-bytecode.python
.qcp	audio/vnd.qcelp
.qd3	x-world/x-3dmf
.qd3d	x-world/x-3dmf
.qif	image/x-quicktime
.qt	video/quicktime
.qtc	video/x-qtc
.qti	image/x-quicktime
.qtif	image/x-quicktime
.ra	audio/x-pn-realaudio
.ra	audio/x-pn-realaudio-plugin
.ra	audio/x-realaudio
.ram	audio/x-pn-realaudio
.ras	application/x-cmu-raster
.ras	image/cmu-raster
.ras	image/x-cmu-raster
.rast	image/cmu-raster
.rexx	text/x-script.rexx
.rf	image/vnd.rn-realflash
.rgb	image/x-rgb
.rm	application/vnd.rn-realmedia
.rm	audio/x-pn-realaudio
.rmi	audio/mid
.rmm	audio/x-pn-realaudio
.rmp	audio/x-pn-realaudio
.rmp	audio/x-pn-realaudio-plugin
.rng	application/ringing-tones
.rng	application/vnd.nokia.ringing-tone
.rnx	application/vnd.rn-realplayer
.roff	application/x-troff
.rp	image/vnd.rn-realpix
.rpm	audio/x-pn-realaudio-plugin
.rt	text/richtext
.rt	text/vnd.rn-realtext
.rtf	application/rtf
.rtf	application/x-rtf
.rtf	text/richtext
.rtx	application/rtf
.rtx	text/richtext
.rv	video/vnd.rn-realvideo
.s	text/x-asm
.s3m	audio/s3m
.saveme	application/octet-stream
.sbk	application/x-tbook
.scm	application/x-lotusscreencam
.scm	text/x-script.guile
.scm	text/x-script.scheme
.scm	video/x-scm
.sdml	text/plain
.sdp	application/sdp
.sdp	application/x-sdp
.sdr	application/sounder
.sea	application/sea
.sea	application/x-sea
.set	application/set
.sgm	text/sgml
.sgm	text/x-sgml
.sgml	text/sgml
.sgml	text/x-sgml
.sh	application/x-bsh
.sh	application/x-sh
.sh	application/x-shar
.sh	text/x-script.sh
.shar	application/x-bsh
.shar	application/x-shar
.shtml	text/html
.shtml	text/x-server-parsed-html
.sid	audio/x-psid
.sit	application/x-sit
.sit	application/x-stuffit
.skd	application/x-koan
.skm	application/x-koan
.skp	application/x-koan
.skt	application/x-koan
.sl	application/x-seelogo
.smi	application/smil
.smil	application/smil
.snd	audio/basic
.snd	audio/x-adpcm
.sol	application/solids
.spc	application/x-pkcs7-certificates
.spc	text/x-speech
.spl	application/futuresplash
.spr	application/x-sprite
.sprite	application/x-sprite
.src	application/x-wais-source
.ssi	text/x-server-parsed-html
.ssm	application/streamingmedia
.sst	application/vnd.ms-pki.certstore
.step	application/step
.stl	application/sla
.stl	application/vnd.ms-pki.stl
.stl	application/x-navistyle
.stp	application/step
.sv4cpio	application/x-sv4cpio
.sv4crc	application/x-sv4crc
.svf	image/vnd.dwg
.svf	image/x-dwg
.svr	application/x-world
.svr	x-world/x-svr
.swf	application/x-shockwave-flash
.t	application/x-troff
.talk	text/x-speech
.tar	application/x-tar
.tbk	application/toolbook
.tbk	application/x-tbook
.tcl	application/x-tcl
.tcl	text/x-script.tcl
.tcsh	text/x-script.tcsh
.tex	application/x-tex
.texi	application/x-texinfo
.texinfo	application/x-texinfo
.text	application/plain
.text	text/plain
.tgz	application/gnutar
.tgz	application/x-compressed
.tif	image/tiff
.tif	image/x-tiff
.tiff	image/tiff
.tiff	image/x-tiff
.tr	application/x-troff
.tsi	audio/tsp-audio
.tsp	application/dsptype
.tsp	audio/tsplayer
.tsv	text/tab-separated-values
.turbot	image/florian
.txt	text/plain
.uil	text/x-uil
.uni	text/uri-list
.unis	text/uri-list
.unv	application/i-deas
.uri	text/uri-list
.uris	text/uri-list
.ustar	application/x-ustar
.ustar	multipart/x-ustar
.uu	application/octet-stream
.uu	text/x-uuencode
.uue	text/x-uuencode
.vcd	application/x-cdlink
.vcs	text/x-vcalendar
.vda	application/vda
.vdo	video/vdo
.vew	application/groupwise
.viv	video/vivo
.viv	video/vnd.vivo
.vivo	video/vivo
.vivo	video/vnd.vivo
.vmd	application/vocaltec-media-desc
.vmf	application/vocaltec-media-file
.voc	audio/voc
.voc	audio/x-voc
.vos	video/vosaic
.vox	audio/voxware
.vqe	audio/x-twinvq-plugin
.vqf	audio/x-twinvq
.vql	audio/x-twinvq-plugin
.vrml	application/x-vrml
.vrml	model/vrml
.vrml	x-world/x-vrml
.vrt	x-world/x-vrt
.vsd	application/x-visio
.vst	application/x-visio
.vsw	application/x-visio
.w60	application/wordperfect6.0
.w61	application/wordperfect6.1
.w6w	application/msword
.wav	audio/wav
.wav	audio/x-wav
.wb1	application/x-qpro
.wbmp	image/vnd.wap.wbmp
.web	application/vnd.xara
.wiz	application/msword
.wk1	application/x-123
.wmf	windows/metafile
.wml	text/vnd.wap.wml
.wmlc	application/vnd.wap.wmlc
.wmls	text/vnd.wap.wmlscript
.wmlsc	application/vnd.wap.wmlscriptc
.word	application/msword
.wp	application/wordperfect
.wp5	application/wordperfect
.wp5	application/wordperfect6.0
.wp6	application/wordperfect
.wpd	application/wordperfect
.wpd	application/x-wpwin
.wq1	application/x-lotus
.wri	application/mswrite
.wri	application/x-wri
.wrl	application/x-world
.wrl	model/vrml
.wrl	x-world/x-vrml
.wrz	model/vrml
.wrz	x-world/x-vrml
.wsc	text/scriplet
.wsrc	application/x-wais-source
.wtk	application/x-wintalk
.xbm	image/x-xbitmap
.xbm	image/x-xbm
.xbm	image/xbm
.xdr	video/x-amt-demorun
.xgz	xgl/drawing
.xif	image/vnd.xiff
.xl	application/excel
.xla	application/excel
.xla	application/x-excel
.xla	application/x-msexcel
.xlb	application/excel
.xlb	application/vnd.ms-excel
.xlb	application/x-excel
.xlc	application/excel
.xlc	application/vnd.ms-excel
.xlc	application/x-excel
.xld	application/excel
.xld	application/x-excel
.xlk	application/excel
.xlk	application/x-excel
.xll	application/excel
.xll	application/vnd.ms-excel
.xll	application/x-excel
.xlm	application/excel
.xlm	application/vnd.ms-excel
.xlm	application/x-excel
.xls	application/excel
.xls	application/vnd.ms-excel
.xls	application/x-excel
.xls	application/x-msexcel
.xlt	application/excel
.xlt	application/x-excel
.xlv	application/excel
.xlv	application/x-excel
.xlw	application/excel
.xlw	application/vnd.ms-excel
.xlw	application/x-excel
.xlw	application/x-msexcel
.xm	audio/xm
.xml	application/xml
.xml	text/xml
.xmz	xgl/movie
.xpix	application/x-vnd.ls-xpix
.xpm	image/x-xpixmap
.xpm	image/xpm
.x-png	image/png
.xsr	video/x-amt-showrun
.xwd	image/x-xwd
.xwd	image/x-xwindowdump
.xyz	chemical/x-pdb
.z	application/x-compress
.z	application/x-compressed
.zip	application/x-compressed
.zip	application/x-zip-compressed
.zip	application/zip
.zip	multipart/x-zip
.zoo	application/octet-stream
.zsh	text/x-script.zsh

Source 2015 - http://www.sitepoint.com/web-foundations/mime-types-complete-list/

z_MacOS

BE_ExecuteSystemCommand ( "sw_vers -ProductVersion" )


// Get(SystemVersion) does not show the correct operating system 
// version when FileMaker 18 queries Mac OS 12+, so use the 
// BaseElements plugin to get the correct OS version from the shell.
// Sample result:  13.5.2

z_Map

// fnGetMap ( webViewerName ; Zoom ; Street1 ; Street2 ; City ; State ; ZIP ; Country )
Let  ( [
vAddress = Substitute ( Street1 & " " & Street2 & " " & 
	City & " " & State & " " & ZIP & " " & Country ; " " ; "+" ) ; 
vSize = GetLayoutObjectAttribute ( webViewerName ; "width" ) & "x" & 
	GetLayoutObjectAttribute ( webViewerName ; "height" ) ; 
vZoom = Case (
   not IsEmpty ( Street1 & Street2 ) ; 14 ; 
   not IsEmpty ( City ) ; 7 ; 
   not IsEmpty ( State ) ; 4 ; 
   not IsEmpty ( Country ) ; 3 )
] ; 

"http://maps.google.com/maps/api/staticmap?" &
	"center=" & vAddress & 
	"&zoom=" & vZoom & 
	"&markers=" & vAddress & 
	"&size=" & vSize &   // max size 640x640
	"&maptype=roadmap" & 
	"&sensor=false"

)  //end Let


// Based on FileMaker 14 demo files.
// UNSTORED calculation allows GetLayoutObjectAttribute to work on any layout.
// Good default zoom is 14 for street level (7/2012).
// Center and Marker parameters can be lat & long.
// eg. ?center=42.472,-83.825&zoom=14&markers=42.472,-83.825

// Map  --  v4  New Google map version pulled from FM 12 Contacts starter solution demo file.

z_NumberAsWords

// NumberAsWords   v1 added 2/2014

Let ( vNumber = Abs ( Number ) ; 

Choose ( Int ( Mod ( vNumber ; 10^12 ) / 10^11 ) ; "" ; 
   "One Hundred " ; "Two Hundred " ; "Three Hundred " ; "Four Hundred " ; "Five Hundred " ; "Six Hundred " ; "Seven Hundred " ; "Eight Hundred " ; "Nine Hundred " ) & 
If ( Int ( Mod ( vNumber ; 10^11 ) / 10^10 ) = 1 ; 
Choose ( Int ( Mod ( vNumber ; 10^10 ) / 10^9 ) ; 
   "Ten " ; "Eleven " ; "Twelve " ; "Thirteen " ; "Fourteen " ; "Fifteen " ; "Sixteen " ; "Seventeen " ; "Eighteen " ; "Nineteen " ) ; 
Choose ( Int ( Mod ( vNumber ; 10^11 ) / 10^10 ) ; ""; ""; 
   "Twenty " ; "Thirty " ; "Forty " ; "Fifty " ; "Sixty " ; "Seventy " ; "Eighty " ; "Ninety " ) &
Choose ( Int ( Mod ( vNumber ; 10^10 ) / 10^9 ) ; ""; 
   "One " ; "Two " ; "Three " ; "Four " ; "Five " ; "Six " ; "Seven " ; "Eight " ; "Nine " ) ) &
Case ( Int ( Mod ( vNumber ; 10^12) / 10^9 ) and Int ( Mod ( vNumber ; 10^9 ) / 10^3 ) ; 
   "Billion, " ; Int ( Mod ( vNumber ; 10^12 ) / 10^9 ) ; "Billion " ; "" ) 
& 
Choose ( Int ( Mod ( vNumber ; 10^9) / 10^8 ) ; "" ; 
   "One Hundred "; "Two Hundred "; "Three Hundred " ; "Four Hundred " ; "Five Hundred " ; "Six Hundred " ; "Seven Hundred " ; "Eight Hundred " ; "Nine Hundred " ) &
If ( Int ( Mod ( vNumber ; 10^8) / 10^7 ) = 1 ; 
Choose ( Int ( Mod ( vNumber ; 10^7) / 10^6) ; 
   "Ten " ; "Eleven " ; "Twelve " ; "Thirteen " ; "Fourteen " ; "Fifteen " ; "Sixteen " ; "Seventeen " ; "Eighteen " ; "Nineteen " ) ; 
Choose ( Int ( Mod ( vNumber ; 10^8) / 10^7) ; "" ; ""; "Twenty " ; "Thirty " ; "Forty " ; "Fifty " ; "Sixty " ; "Seventy " ; "Eighty " ; "Ninety " ) &
Choose ( Int ( Mod ( vNumber ; 10^7) / 10^6) ; "" ; "One "; "Two " ; "Three " ; "Four " ; "Five " ; "Six " ; "Seven " ; "Eight " ; "Nine " ) ) &
Case ( Int ( Mod ( vNumber ; 10^9 ) / 10^6) and Int ( Mod ( vNumber ; 10^6 ) / 10^3 ) ; 
   "Million, " ; Int ( Mod ( vNumber ; 10^9 ) / 10^6 ) ; "Million " ; "" ) 
& 
Choose ( Int ( Mod ( vNumber ; 10^6 ) / 10^5 ) ; "" ; 
   "One Hundred " ; "Two Hundred " ; "Three Hundred " ; "Four Hundred " ; "Five Hundred " ; "Six Hundred " ; "Seven Hundred " ; "Eight Hundred " ; "Nine Hundred " ) &
If ( Int ( Mod ( vNumber ; 10^5) / 10^4 ) = 1 ;
Choose ( Int ( Mod ( vNumber ; 10^4 ) / 10^3 ) ; 
   "Ten "; "Eleven "; "Twelve "; "Thirteen "; "Fourteen " ; "Fifteen " ; "Sixteen " ; "Seventeen " ; "Eighteen " ; "Nineteen " ) ; 
Choose ( Int ( Mod ( vNumber ; 10^5 ) / 10^4 ) ; "" ; "" ; 
   "Twenty "; "Thirty "; "Forty "; "Fifty "; "Sixty "; "Seventy "; "Eighty "; "Ninety ") &
Choose ( Int ( Mod ( vNumber ; 10^4 ) / 10^3 ) ; ""; 
   "One " ; "Two " ; "Three " ; "Four " ; "Five " ; "Six " ; "Seven " ; "Eight " ; "Nine " ) ) &
Case ( Int ( Mod ( vNumber ; 10^6 ) / 10^3 ) and Int ( Mod ( vNumber ; 10^3 ) / 10^0 ) ; 
   "Thousand, " ; Int ( Mod ( vNumber ; 10^6 ) / 10^3 ) ; "Thousand " ; "" ) 
& 
Choose ( Int ( Mod ( vNumber ; 10^3) / 100 ) ; "" ; 
   "One Hundred " ; "Two Hundred "; "Three Hundred "; "Four Hundred "; "Five Hundred "; "Six Hundred "; "Seven Hundred "; "Eight Hundred "; "Nine Hundred " ) &
If ( Int ( Mod ( vNumber ; 100 ) / 10 ) = 1 ; 
Choose ( Int ( Mod ( vNumber ; 10 ) ) ; 
   "Ten "; "Eleven "; "Twelve "; "Thirteen "; "Fourteen "; "Fifteen "; "Sixteen "; "Seventeen "; "Eighteen "; "Nineteen " ) ; 
Choose ( Int ( Mod ( vNumber ; 100 ) / 10 ) ; "" ; "" ; 
   "Twenty "; "Thirty "; "Forty "; "Fifty "; "Sixty "; "Seventy "; "Eighty "; "Ninety ") &
Choose ( Int ( Mod ( vNumber ; 10 ) ) ; "" ; 
   "One "; "Two "; "Three "; "Four "; "Five "; "Six "; "Seven "; "Eight "; "Nine " ) ) & 
Case ( Int ( vNumber ) > 1 ; "Dollars" ; Int ( vNumber ) = 1 ; "Dollar" ; "" ) 
& 
If ( Mod ( vNumber ; 1 ) and Int ( vNumber ) ; " and " ; "" ) & 
If ( Mod ( Int ( vNumber *10 ) ; 10 ) = 1 ; 
Choose ( Mod ( Int ( vNumber * 100 ) ; 10 ) ; 
   "Ten " ; "Eleven " ; "Twelve " ; "Thirteen " ; "Fourteen " ; "Fifteen " ; "Sixteen " ; "Seventeen " ; "Eighteen " ; "Nineteen " ) ; 
Choose ( Mod ( Int ( vNumber * 10 ) ; 10 ) ; ""; ""; 
   "Twenty "; "Thirty "; "Forty "; "Fifty "; "Sixty "; "Seventy "; "Eighty "; "Ninety ") &
Choose ( Mod ( Int ( vNumber * 100 ) ; 10 ) ; "" ; 
   "One "; "Two "; "Three "; "Four "; "Five "; "Six "; "Seven "; "Eight "; "Nine " ) ) & 
Case ( Mod ( vNumber ; 1 ) > .01 ; "cents" ; Mod ( vNumber ; 1 ) = .01 ; "cent" ; "" )

)  //end Let


// Spell out a number in words, as you would for writing a check.
// Source - http://help.filemaker.com/app/answers/detail/a_id/2860/~/converting-numbers-to-words-or-numbers-to-text

z_ProgressBar

Let ( [ 
vPct = If ( StatusProgressBar_g > 1 ; Round ( StatusProgressBar_g ; 0 ) ; 
   Round ( StatusProgressBar_g * 100 ; 0 ) ) ; 
vMeter = "||||||||||||||||||||" ;                               //Currently have 20 pipes.
vChars = Round ( vPct / ( 100 / Length ( vMeter ) ) ; 0 ) ; 
vSpaces = "                                                  " ;    //Currently have 50 spaces.
vBlanks = Length ( vMeter ) - vChars
] ; 

if ( vPct < 100 ; " " ) & 
if ( vPct < 10 ; " " ) & 
vPct & "% {" & 
If ( Length ( vMeter ) > Length ( vSpaces ) ; 
   "Error, need more spaces in calculation." ; 
   Left ( vMeter ; vChars ) 
) & 
Left ( vSpaces ; vBlanks ) & "}"

)  //end Let


// Shows progress as a number %, then uses pipe characters to draw distance.
// Use a monospace font on the layout like Courier or Monaco.
// You can change the number of pipes in vMeter up to the number of spaces used to separate the curly brackets.
// Spaces in front of the % number value prevent the bars from shifting as the number increases.
// Made this a global calculation to work with unrelated tables.
// Example:   25% {|||||               }

z_Run_PowerShell_scripts

// Run PowerShell scripts using the BaseElements plugin.

Let ( [ 
vBase_path = "C:\Users\Admin\Desktop\" ; 
vFile_path = vBase_path & "MyFile.doc" ; 
vResult_path = vBase_path & "MyFile.pdf" ; 
vScript_path = vBase_path & "ConvertFormat.ps1" ; 

// PowerShell script converts .doc or .docx to .pdf using MS Word.
// Source 2013 - https://stackoverflow.com/questions/16534292/basic-powershell-batch-convert-word-docx-to-pdf
vCode = "$File='{{file_path}}'¶" & 
	"$New='{{result_path}}'¶" & 
	"if (! (test-path $File)){ throw \"$File is not a valid path.\" }¶" & 
	"$Word=New-Object -ComObject Word.Application¶" & 
	"$Doc=$Word.Documents.Open($File)¶" & 
	"$Doc.SaveAs([ref]$New, [ref] 17)¶" & 
	"$Doc.Close()¶" & 
	"$Word.Application.Quit()¶" ; 
	
// Pause and Timeout are in milliseconds, so 2000 = 2 sec
vPause2Sec = BE_Pause ( 2000 ) ; 

// Make a UTF-8 file with CRLF line encoding
vResult = BE_WriteTextToFile ( vScript_path ; 
   Substitute ( vCode 
   ; [ "{{file_path}}" ; vFile_path ] 
   ; [ "{{result_path}}" ; vResult_path ] 
   )  //end substitute
   ) ; 

vCommand = "cmd.exe /c powershell.exe -File " & vScript_path

] ; 

If ( vResult ; BE_ExecuteSystemCommand ( vCommand ; 5000 ) ; 
   "Error, script file was not written.¶Powershell code¶" & vCode )

)  //end Let

z_SnapshotLink

"<?xml version=\"1.0\" encoding=\"UTF-8\"?>¶" & 
"<FPSL><UIState><UniversalPathList>fmnet:/" & 
Get ( HostName ) & "/" & 
Get ( FileName ) & 
".fmp12</UniversalPathList><Layout id=\"" & 
Get ( Layout_ID ) & 
"\"></Layout><View type=\"" & 
if ( get ( LayoutViewState ) = 0 ; "form" ; "list" ) & 
"\"></View><SelectedRow type=\"nativeID\" id=\"" & 
GetValue ( LayoutIDs ( Get ( FileName ) ) ; Get ( LayoutNumber ) ) & 
"\"></SelectedRow><StatusToolbar visible=\"" & 
"True" &  //if ( Get ( StatusAreaState ) = 1 or Get ( StatusAreaState ) = 2 ; "True" ; "False" ) & 
"\"></StatusToolbar><Mode value=\"browseMode\"></Mode>" & 
"<SortList Maintain=\"False\" value=\"False\"></SortList>" & 
"</UIState></FPSL>"



/* Build a Snapshot Link manually. Final result should look like this:

<?xml version="1.0" encoding="UTF-8"?>
<FPSL><UIState><UniversalPathList>fmnet:/my_fm_server.com/my_file.fmp12</UniversalPathList><Layout id="233"></Layout><View type="form"></View><SelectedRow type="nativeID" id="428"></SelectedRow><StatusToolbar visible="True"></StatusToolbar><Mode value="browseMode"></Mode><SortList Maintain="False" value="False"></SortList></UIState></FPSL>

File should have .fmpsl extension and UTF-8 encoding with line feed (LF) breaks. */

z_SystemStats

// SystemStats
// SystemStats function has been replaced by zz_CreateData and/or zz_ModData

"LastLogin¶" & 
Get ( CurrentTimeStamp ) & 
"¶AccountName¶" & 
Get ( AccountName ) & 
"¶UserName¶" & 
Get ( UserName ) & 
"¶ScreenWidth¶" & 
Get ( ScreenWidth ) & 
"¶ScreenHeight¶" & 
Get ( ScreenHeight ) & 
"¶SystemPlatform¶" & 
Get ( SystemPlatform ) & 
"¶SystemVersion¶" & 
Get ( SystemVersion ) & 
"¶ApplicationVersion¶" & 
Get ( ApplicationVersion ) & 
"¶HostApplicationVersion¶" & 
Get ( HostApplicationVersion ) & 
"¶HostIPAddress¶" & 
Get ( HostIPAddress ) & 
"¶SystemIPAddress¶" & 
Get ( SystemIPAddress )

z_TriangularNum

// Triangular Number - v1 2015

Let ( [ 
vNumRows =  Count ( Steps::RowNum ) ; 
vSumNums = Sum ( Steps::RowNum ) ; 
vTriangle = vNumRows * ( vNumRows + 1 ) / 2   //This provides the Triangular Number
] ; 

if ( vSumNums = vTriangle ; true ; false )

)  //end Let


// To see if a set of related records has every row number correctly, find the triangular number.
// The triangular number allows you to quickly see if a set of row numbers contains all values from 1 to X.
// The row numbers may not be in the right sequence, but that's a different test.

z_vCard

// vCard  --  v2  Better, but not perfect.

"BEGIN:VCARD¶" & 
"VERSION:3.0¶" & 
If ( NameLast ≠ "" or NameFirst ≠ "" ; "N:" & Trim ( NameLast ) & ";" & Trim ( NameFirst ) & ";" & NameMiddle & ";" & NamePrefix & ";" & NameSuffix & ";¶" ) & 
If ( NameFull_c ≠ "" ; "FN:" & NameFull_c & ¶ ) & 
If ( NameCommon_c ≠ "" ; "NICKNAME:" & NameCommon_c & ¶ ) & 
If ( NameCompany ; "ORG:" & Substitute ( Trim ( NameCompany ) ; [ ¶ ; "\n" ] ) & ";¶" ) & 
If ( aTitle ≠ "" ; "TITLE:" & Substitute ( Trim ( aTitle ) ; [ ¶ ; "\n" ] ) & ¶ ) & 

If ( Email ≠ "" ; "EMAIL;type=" & Upper ( Trim ( "main" ) ) & ":" & Trim ( Email ) & ¶ ) & 
//If ( Email2 ≠ "" ; "EMAIL;type=" & Upper ( Trim ( "work" ) ) & ":" & Trim ( Email2 ) & ¶ ) & 

If ( Phone1 ≠ "" ; "TEL;type=" & Upper ( Trim ( "main" ) ) & ":" & 
   Substitute ( Trim ( Phone1 ) ; [ ¶ ; "\n" ] ; [ ":" ; "\:" ] ; [ "," ; "\," ] ; [ ";" ; "\;" ] ) & ¶ ) & 
If ( Phone2Fax ≠ "" ; "TEL;type=" & Upper ( Trim ( "fax" ) ) & ":" & Trim ( Phone2Fax ) & ¶ ) & 
If ( Phone3Mobile ≠ "" ; "TEL;type=" & Upper ( Trim ( "mobile" ) ) & ":" & Substitute ( Trim ( Phone3Mobile ) ; [ ¶ ; "\n" ] ) & ¶ ) & 
//If ( Phone4 = "" ; "TEL;type=" & Upper ( Trim ( Phone4Type ) ) & ":" & Trim ( Phone4 ) & ¶ ) & 

If ( C1StatePostal ≠ "" ; "ADR;type=" & Upper ( "main" ) & ":;;" & 
   Substitute ( Trim ( C1Street ) ; [ ¶ ; "\n" ] ; [ ":" ; "\:" ] ; [ "," ; "\," ] ; [ ";" ; "\;" ] ) & ";" & 
   Trim ( C1City ) & ";" & C1StateFull_c & ";" & Trim ( C1ZIP ) & ";" & 
   Trim ( C1Country ) & ¶ ) & 

//If ( C2StateFull_c ≠ "" ; "ADR;type=" & Upper ( "work" ) & ":;;" & 
//   Trim ( Substitute ( C2Street_c ; ¶ ; " " ) ) & ";" & 
//   Trim ( C2City_c ) & ";" & C2StateFull_c & ";" & Trim ( C2Zip_c ) & ";" & 
//   Trim ( C2Country_c ) & ¶ ) & 

If ( aNotes ≠ "" ; "NOTE:" & Trim ( Substitute ( aNotes ; [ ¶ ; "\n" ] ; [ ":" ; "\:" ] ; [ "," ; "\," ] ; [ ";" ; "\;" ] ) ) & ¶ ) & 
If ( Company::CoWebsite ≠ "" ; "URL:" & Substitute ( Trim ( Company::CoWebsite ) ; [ ":" ; "\:" ] ) & ¶ ) & 
//If ( Category ≠ "" ; "CATEGORIES:" & Category & ¶ ) & 

"END:VCARD"


// vCard Notes:
// Escape returns with \n and commas, colons, & semicolons with slash in front.
// Windows (Outlook) cannot import multi-card files, so export records as individual files.
// vCard export file needs returns coded as CRLF (carriage-return line-feed) for Outlook 2007/2010 to recognize it on Windows, so build card in a field, export field contents, import into dummy table, then export as XML with the XSLT - http://localhost/services/export_crlf.xsl

zzzA_Note_About_Zero

11/2017 - This is an unfinished article.  I am still doing research, but don't want to lose my place in this.

Evaluating statements seems to have illogical results at times.

IF and CASE statements calculate as TRUE if the result is evaluated as not zero and not blank.
If ( 1 ; True ; False ) = True
If ( -1 ; True ; False ) = True
if ( "?" ; True ; False ) = True
If ( 0 ; True ; False ) = False
If ( "" ; True ; False ) = False
If ( "a" ; True ; False ) = False

When an error occurs, a numeric code is often returned that represents what type of error occurred. When an unknown error occurs, the result may be a (?) question mark.  It is good for the (?) question mark to evaluate as non-zero so an error check will be reported properly, like this:
If ( Get ( LastError ) ; "Error" ; "OK" )

Strange things happen when you start to compare values to each other.

if ( "?" > 0 ; True ; False ) = False
if ( "?" < 0 ; True ; False ) = True
if ( "?" > -0.00001 ; True ; False ) = True
if ( "?" > "" ; True ; False ) = True
if ( "?" < "" ; True ; False ) = False
// The question mark seems to evaluate to a number just below zero.

if ( "" > 0 ; True ; False ) = False
if ( "" < 0 ; True ; False ) = True
// Blank is below zero as well.

if ( "a" > 0 ; True ; False ) = True
if ( "a" ; True ; False ) = False
// Letters seem to have value when compared to zero, but not when evaluated alone.