Home

Export Calendar files from FileMaker Pro – Virtual List Technique

May 22, 2014 by Douglas Alder 3 Comments


Update 2015-11-25: An even easier method of creating vCards that seem to work cross platform is demonstrated by Joe Simpson of Radical Application Development. He uses Base64Encode and then Base64Decode to generate the file in a container field. The same approach should work with .ics files. Then it is a simple matter to export it. Check it out here and see if it meets your needs. Might be even easier to implement than the Virtual List approach below.

I thought I was in the clear with my previous demo/post. A number of readers have pointed out the error of my ways.

In my last post about exporting Calendar files, I glossed over the UTF-8/16 issue that is still present in FileMaker Pro. The problem is that if a script employs “Export Field Contents”, which my last demo did, there is no option for selecting the text file formatting options and it exports, by default, UTF-16 formatted files. If instead, the script uses “Export Records”, there are proper formatting options and it is possible to generate UTF-8 files. Why FileMaker Inc.’s programmers chose UTF-16 as the default for Export Field Contents, instead of the more universal UTF-8 formatting is a mystery. I have been over this issue before in a previous blog post, so I will not belabor the point. Perhaps this functionality will make it into FileMaker Pro 14.

The UTF-8 import issue seems to have gone away with the Mac OS X Calendar app, but it is still an problem if you are using earlier versions of Mac OS X. It appears that Calendar in Mavericks does recognize UTF-16 files. The current theory is that this benefit is the result of improvements to international support in Calendar.

Fortunately, there is a solution, which is to employ the Virtual List technique suggested by FileMaker Hacks. If you are deploying in a situation where all users are using FileMaker Pro 13 and running on OS X Mavericks, iOS 7 or better, my previous posting/demo/script should work. If however, you have to support a range of hardware and operating systems, you are better off using the Virtual List approach. It only adds one more step – copying in the Virtual List data table to your solution.

Download Demo

To add this script to your solution,

1) start with a copy of FileMaker Pro 13.

2) Make sure that your events table has these Required Fields for each event:

  • Start Date – Date field
  • Start Time – Time field
  • End Time – Time field
  • Title – Text field
  • UUID field – Get(UUID) is a new FileMaker Pro 13 feature

In addition, you can include these Optional Fields:

  • Location – Text field
  • Description – Text field

3) Once you have the required fields in place, add (copy and paste or recreate) the Virtual_List_Utility data table to your solution.

4) Then copy the script called ‘Export Calendar File – Virtual List’ and paste it into your file.

5) Open the script and edit the portion of the script where the required fields are referenced to match those in your solution.

6) Add a Value List called ‘1’ with a value of 1. Assign this value list to the ‘All Day’ Field on your layout.

7) Test.

Virtual_List_Utility data table

media_1400782251714.png

Copy or create this data table in your solution. There are no relationships required as we will be linking the data to a Global Variable.

Field definitions in Virtual List Utility data table

media_1400782268927.png

Key definition

media_1400782277282.png

Using the GetValue from the Global Variable, we will end up with the correct data linked up in the fields.

Run the script

media_1400782299801.png

After running the script, the Global Variable $$virtual_list is generated. The Virtual List table is populated by referencing the line number in the Global Variable.

What is going on?

media_1400782471731.png

Each line in the Global Variable lines up with a Serial Number in the Virtual List table. The ID serial number matches the line number in the Global Variable Text field.

Set Next Serial Number Value

media_1400783187804.png

Each time the script runs, the records of the Virtual List data table are all deleted and regenerated. The number of records generated matches the number of lines in the $$virtual_list Global Variable. The crucial step is to reset the serial number value to 1 each time, so that the virtual list will link to the data table correctly.

This approach, of generating just the right number of records in the Virtual List table, may cause issues in a network environment if more than one user is running the script simultaneously. Another popular approach is to simply have a large number of blank records (50,000 has been suggested) in the Virtual List, and don’t give users permission to create more or delete any records, so that the appropriate serial numbers (starting at 1) are always there. With the large number of pre-built records approach, it is necessary to do some kind of search to capture just the subset of records that will be exported.

2014-05-23 Update: Reader Bruce Robertson suggested a simpler approach to having to reset the serial number each time the script runs. Instead of using the ID as the basis of the numbering scheme in the Virtual List, he recommended using Get(RecordNumber). That way, as long as there are enough records in the Virtual_List_Utility table to match the lines of text in the $$virtual_list Global Variable, it doesn’t matter what the serial number is. What that would look like is changing the Field definition of virtual_list to:

GetValue($$virtual_list; Get(RecordNumber))

2014-07-08 Update: I tried running the Virtual List technique on FileMaker Server and my observation was that the routine of deleting all records, resetting the serial number to 1 and recreating the correct number of records was slow. It also exposed the user to a message saying ‘Deleting records’ which can be unnerving if they don’t know what is going on. Instead, at the suggestion of a clever developer on the Facebook FileMaker Users & Developers Group, I modified the Virtual List field to reference Get(RecordNumber), so it doesn’t matter what serial number it is.

GetValue($$virtual_list; Get(RecordNumber))

Then I changed the Export script to test to see if there are enough records and add more if needed. If there are too many records, the script omits the extra records. Also fixed a few bugs and added a button to display the Virtual List layout so you can see what is going on after the export is done.

2014-12-23 Update: I had originally taken a short cut to simplify the file by making the End Date always equal the Start Date. I updated the demo file to include two new fields End Date and All Day. Also added a value list called ‘1’ with a single value 1, which is applied to the All Day field as a checkbox.

If you don’t turn on the All Day field ( set it to 1), the script makes a listing that has all the hours displayed. What you probably want is the ability to tag an event as ‘All Day’ and have just the dates show, with no hours filled in.

I have modified the REQUIRED FIELDS script to accommodate the All Day events as well. I had to remove the UTC Offset because it was starting the event on the next day.

Please test in your own environment to see if this works correctly in your time zone. I am on Pacific Standard Time and it seems to work properly.

FileMaker Pro 13
, , , , , ,

Trackbacks/Pingbacks

  1. Exporting Calendar files (.ics) from FileMaker Pro 13 | HomeBase Software - May 22, 2014

    […] Update: If you have problems with the export, please see the next blog posting on Exporting Calendar files using the Virtual List […]
  2. Export Calendar files from FileMaker Pro - May 27, 2014

    […] Export Calendar files from FileMaker Pro – Virtual List Technique | HomeBase Software. […]
  3. Export Calendar files from FileMaker Pro – Virtual List Technique | Filemaker Info - May 27, 2014

    […] See on hbase.net […]