Filemaker | create vCard .vcf and carriage returns in text export

AIM:

To find a solution to create a vCard file from a Filemaker contact database – ideally natively with no plug-in. (vCard [.vcf] is a file format standard for electronic business cards. vCards are often attached to e-mail messages. They can contain name and address information, phone numbers, URLs, logos, photographs, and even audio clips.)

SOLUTION:

The first step was to map out the structure of a vCard to be able to replicate it into a filemaker field.

The following is an example of a vCard file containing information for one person:

BEGIN:VCARD
VERSION:2.1
N:Gump;Forrest
FN:Forrest Gump
ORG:Gump Shrimp Co.
TITLE:Shrimp Man
TEL;WORK;VOICE:(111) 555-1212
TEL;HOME;VOICE:(404) 555-1212
ADR;WORK:;;100 Waters Edge;Baytown;LA;30314;United States of America
LABEL;WORK;ENCODING=QUOTED-PRINTABLE:100 Waters Edge=0D=0ABaytown, LA 30314=0D=0AUnited States of America
ADR;HOME:;;42 Plantation St.;Baytown;LA;30314;United States of America
LABEL;HOME;ENCODING=QUOTED-PRINTABLE:42 Plantation St.=0D=0ABaytown, LA 30314=0D=0AUnited States of America
EMAIL;PREF;INTERNET:forrestgump@walladalla.com
REV:20080424T195243Z
END:VCARD

vCard defines the following property types: FN, N, NICKNAME, PHOTO, BDAY, ADR, LABEL, TEL, EMAIL, MAILER, TZ, GEO, TITLE, ROLE, LOGO, AGENT, ORG, CATEGORIES, NOTE, PRODID, REV, SORT-STRING, SOUND, URL, UID, VERSION, CLASS, KEY [1]. In addition, because vCard augments RFC-2425, a standard for directory information, the following property types are also supported: SOURCE, NAME, PROFILE, BEGIN, END.

This is fairly straight forward to map from a contact database in Filemaker via a calculation field but here is a custom function that wraps it all up nicely:

/* Returns text formatted as a vCard

This function takes contact info and formats it into the vCard format as text.  The result field contents can be exported and opened as a .vcf file which can be opened in the Apple Address Book and other software that supports the vCard format.

Written by Chris Pye – info@mactec.com.au

vcard ( FirstName; LastName; Org; Title; Phone; Fax; Mobile; Email; Street; Suburb; State; Zip; Country )

*/

// DECLARE VARIABLES

Let ( [

// All relevant fields are mapped here

_LastName = LastName;
_FirstName = FirstName;
_MiddleName =””;
_Title = Title;
_Suffix = “”;

_Org = Org;
_Dept = “”;
_JobTitle = “”;

_Phone1Value = Phone;
_Phone1Label = “work”;

_Phone2Value = Fax;
_Phone2Label = “fax”;

_Phone3Value = Mobile;
_Phone3Label = “cell”;
_Email1Value = Email;
_Email1Label = “work”;
_Address1Street = Street;
_Address1City = Suburb;
_Address1State = State;
_Address1Code = Zip;
_Address1Country = Country;
_Address1Label =”work”;

_FullName = FirstName & ” ” & LastName;

// I have had problems with bad characters in the street line, so added this
_FilterText = “abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ1234567890.-()/¶\’ []?,!#” & “‘”;

//
// NOW START FORMATTING THEM FOR THE VCARD FORMAT
//

vname = _LastName & “;” & _FirstName & “;” & _MiddleName & “;” & _Title & “;” & _Suffix;

vfname = _FullName;

vtel1 = If( not IsEmpty ( _Phone1Value );”TEL;” & If(PatternCount (_Phone1Label ;” ” )=1;Substitute ( _Phone1Label ; ” ” ; “;” );_Phone1Label) & “:” & _Phone1Value & “¶”;”” );

vtel2 = If( not IsEmpty ( _Phone2Value );”TEL;” & If(PatternCount (_Phone2Label ;” ” )=1;Substitute ( _Phone2Label ; ” ” ; “;” );_Phone2Label) & “:” & _Phone2Value & “¶”;”” );

vtel3 = If( not IsEmpty ( _Phone3Value );”TEL;” & If(PatternCount (_Phone3Label ;” ” )=1;Substitute ( _Phone3Label ; ” ” ; “;” );_Phone3Label) & “:” & _Phone3Value & “¶”;”” );
vmail1 = If( not IsEmpty ( _Email1Value);”EMAIL;INTERNET;” & LeftWords ( _Email1Label;1 ) & “:” & _Email1Value & “¶”;”” );
vad1 = If( not IsEmpty (_Address1Street); “ADR;” & _Address1Label & “:;;” & Filter(Substitute ( _Address1Street;”¶”;”\n”) ; _FilterText) & “;” & _Address1City & “;” & _Address1State & “;” & _Address1Code & “;” & _Address1Country & “¶”;””);
vorg = If( not IsEmpty (_Org);”ORG:” & _Org & “;” & _Dept & “¶”;””);

vtitle = If( not IsEmpty ( _JobTitle ); “TITLE:” & _JobTitle & “¶”;””)
// AND THE FINAL FORMATTING

];
“BEGIN:VCARD¶VERSION:2.1¶” &
“N:” & vname & “¶” &
“FN:” & vfname & “¶” &
vorg &
vtel1 & vtel2 & vtel3 &
vmail1 &
vad1 &
vtitle &
“END:VCARD”

)

However even if you use export field contents of a field using this custom function (or any for that matter) you get the paragraph returns wiped out (Windows and Filemaker 9.0v3 – I think on a Mac this is probably not the case – Windows needs CRLF or Carriage Return followed by a Line Feed and Mac only need the CR).

I first had a search through some forums for a solution as obviously without this I could not export the field to a vcf file format as the paragraph returns are very important in the make up of this file.

To export the file to another format I personally have used the Troi file plugin and using the CreateFile and  SetContents functions which basically creates a file (.vcf in this case) and then you set the contents of that new file to the field which is displaying the output of the custom function (of the contact you are on in the database). There is also an option to convert CR to CRLF on output for Windows users.

However the aim of this was to do it without a plugin.

Therefore to tackle this I have used and Export Records native function using FMPXMRESULTS on a XSL style sheet file – the example is below and works a treat:

filemaker_export_contents_vcf_text

This solution can be used to create more than just vcf format files and hope this comes in handy for anyone else looking at this and finding problems on Windows.