FileMaker data to text file using Base64

At the August meeting of COFMUG (Central Ohio FileMaker User Group) we took a look at the Base64 functions in FileMaker 14. We were all familiar with the functions, but I had recently stumbled on the idea of using them to create simple text-based files.

Get the file:

The demo file contains a simple contact table along with a handful of fields that demonstrate the technique. The idea is to leverage the Base64Encode and Base64Decode functions in FileMaker 14 to create a file in a container field.

Example 1. Text File

For the sake of simplicity we created a calculation field to concatenate several fields and labels together.

Base64Text::Text Result

"Name: " & Base64Text::First Name & " " & Base64Text::Last Name & ¶ & 

"Company: " & Base64Text::Company & ¶ &"Phone: " & Base64Text::Phone & ¶ & 

"Email: " & Base64Text::Email & ¶ & "Web: " & Base64Text::Web

Next, we created a calculation field and set the calculation result to Container. The formula to create a text file using the field referenced above is simple:

Base64Text::Text Result File

Base64Decode ( Base64Encode ( Base64Text::Text Result ) ; "result.txt" )

So what’s going on here? First we called Base64Encode with the field Base64Text::Text Result our parameter. This returns a long string of characters in Base64 format. Next, we wrap the first function in a call to Base64Decode. This uses the Base64 text and an optional filename as the parameters. Because we evaluated this in a Container field, we are left with an actual text file in the field.

Example 2. XML File

Ok, so the ability to export text from a field is cool, but FileMaker already gives us that feature (right-click in any field and select Export Field Contents). So what’s the point? Sometimes we need to create a file in a specified format, or provide data to another application. In this example we adapted the same technique from Example 1. to create an XML document.

Base64Text::XML Result

"<?xml version=\"1.0\" encoding=\"UTF-8\"?>" & ¶ & "
<sample>" & ¶ & "
 <nameFirst>" & Base64Text::First Name & "<nameFirst/>" & ¶ & "
    <nameLast>" & Base64Text::Last Name & "<nameLast/>" & ¶ & "
   <company>" & Base64Text::Company & "<company/>" & ¶ & "    <phone>" & Base64Text::Phone & "<phone/>" & ¶ & "  <email>" & Base64Text::Email & "<email/>" & ¶ & "  <web>" & Base64Text::Web & "<web/>" & ¶ & "</sample>"

Base64Text::XML Result File

Base64Decode ( Base64Encode ( Base64Text::XML Result ) ; "result.xml" )

Example 3. VCard

The last example is something that I use from time to time in my own database. I keep all my contacts in a FileMaker database, and sometimes I want to add a contact to my exchange account.

Again, we use the same technique. A simple change to the file type and a new field to concatenate the data in VCard format is all we need.

Caveat: This format works for my version of Outlook. Your mail client may use a different format.

Base64Text::VCF Result

"begin: vCard" & ¶ & 
"version:3.0" & ¶ & 
"prodid:Microsoft-MacOutlook/0.0.0.150807" & ¶ & 
"UID:" & Get ( UUID ) & ¶ & 
"fn;charset=utf-8:" & Base64Text::First Name & " " & Base64Text::Last Name & ¶ & 
"n;charset=utf-8:" & Base64Text::Last Name & ";" & Base64Text::First Name & ";;;" & ¶ & 
"title;charset=utf-8:Founder" & ¶ & 
"org;charset=utf-8:" & Base64Text::Company & ";" & ¶ & 
"url;charset=utf-8;type=work:" & Base64Text::Web & ¶ & "tel;charset=utf-8;type=work:" & Base64Text::Phone & ¶ & "email;charset=utf-8;type=internet;type=pref;type=work:" & Base64Text::Email & ¶ & "end:vcard"

Base64Text::VCF Result File

Base64Decode ( Base64Encode ( Base64Text::VCF Result ) ; "result.vcf" )