Creating Tcal: Part Four, Filemaker and Microsoft Access
How to implement communication between the calendar and Filemaker Pro on the Mac or Microsoft Access on Windows.

by Roberto Trevisan

This is the last in a series looking at the creation of a full featured Calendar application in Revolution. You can read Part One here, Part Two here and Part Three here.

In this last article I will talk about about how I implemented the communication between my Tcal Revolution standalone calendar and databases such as FileMaker Pro and Microsoft Access.

Tcal is a shared cross platform client/server calendar, with a fully featured graphic interface. Many small companies use a normal database, bought in a store, as a job management tool, preferring it to expensive and less flexible “custom” solutions. The main characteristic of Tcal calendar is that it uses a commercial database, through its server TcalServer, in order to record timed Events, allocate resources and jobs to Events and record each hourly resource job load (time tracking). The system is composed of Tcal (the client), TcalServer (the server) and a database (supplied by the user). Each computer where you install Tcal talks to the computer where you installed Tcalserver using TCP/IP, in a Client-Server system. TcalServer then talk to the databases in order to:

• create records
• find records
• delete records
• modify records

Since TcalServer had to be used on Macs and Windows, my choice was to use "Applescript" for Mac OS X, with FileMaker, and VBScript for Win XP or Vista, with Access. My knowledge of the two languages is not very deep. I had to piece together lots of information gleaned from the web in order to come up with functional scripts. So you will excuse me if my solutions are redundant or not very well optimized.

About Applescript
Applescript does not make it easy for the user. It seems so in the beginning. But when you try to do something a little more complex, you easily get lost. It has very bad error message handling, for example you often get the same error number for completely different reasons, and the formatting of a returned data list is something like this: {"data1","data2","data3"}. You have to do a lot of data parsing which is best done using Revolution...much easier with the "replace" command. On top of that, I have a feeling that FileMaker has been neglecting Applescript, over time. The "Apple Events Reference" database document has not been updated since FM v. 6 (!). And on the last version (FileMaker Pro 10) it is not included... I guess it is because of XML, but I am not sure I could get the same results with this.

About VBScript (Visual Basic)
In order to handle Microsoft Access data, I used Visual Basic, a VB object called ADO (ActiveX Data Object) and a thing called "Microsoft.Jet.OLEDB.4.0". These last two are installed with the operating system for XP and Vista and allow you to query a database document even if you do not own Access using yet another (!) language, called SQL. From Revolution you launch a Vbscript, that handles a SQL script, that queries the database (Pfuiii...). This solution is more complex than using the database functions of Revolution, but the idea was to give the user an application that could set-up the ODBC connection, driver, etc without having the user messing about with the control panel ODBC setup of Window XP.

• A layout (for FM) or table (for Access) named "List" on which to put all and only the required fields
  
• The fields must be named with specific names and positioned on the layout in a specific order
  
• The first field on the "list" layout is a database generated unique number: the ID of the record (both databases allow an easy creation of this calculated field)

All the Applescript or VbScript is written in the custom properties of TcalServer. Once loaded into a variable, a "replace" command in Revolution puts the necessary data in the script. You then call the script like this:

do TheScript as TheLanguage -- where the language is \
       "AppleScript" or "VbScript"

Revolution and FileMaker Pro.
To create a new record I use this Applescript:

tell application "TheAppName" --could be FileMaker Pro or \
       FileMaker Pro Advanced
with transaction --this is needed to handle traffic query in a \
       shared database (it locks the database)
try
   go to database "TheDbName" --I called it "Events.fp7"
   go to layout "list"
   set ThisID to ID of (create record of layout "List" of \
          document "TheDbName" with data TheData)
   --ThisID at this point is something like "3,7586E+4". Not
   -- very useful...
   on error number errNum
      return "_error|" & errNum --if there is an error this get \
             returned
   end try
   set RecID to cell "ID" of record ID ThisID of layout "List" of \
          document "TheDbName" as integer --better get the ID number from
   the calculated field
   return RecID as string --this will be the new ID of the created \
          record (The ID is generated by FileMaker)
end transaction
end tell 

where TheData is a string formatted as follows:

{"","field1","field2","fieldn"} 

To delete the above record (putting the above ID into TheID):

tell application "TheAppName"
with transaction
go to database "TheDbName"
go to layout "list"
try
   show every record of database "TheDbName" --see below
   delete (every record of database "TheDbName" whose cell "ID" \
          = TheID)
   on error number errNum
      return "_error|" & errNum
   end try
   return "done"
end transaction
end tell 

For some reason, if the layout of FileMaker is not showing any record at the time you run the script, Applescript is not able to find or edit anything and you get an error. This is the reason for the show every record of database "TheDbName" line.

To edit a record:

tell application "TheAppName"
with transaction
try
   go to database "TheDbName"
   go to layout "list"
   show record 1 of database "TheDbName" --same as showing all \
          the records
   show (every record of database "TheDbName" whose cell "ID" \
          contains "TheID")
   set record 1 of layout "List" of document "TheDbName" to \
          TheData --we suppose
   -- that there is only one record with that ID
   on error number errNum
      return "_error|" & errNum
   end try
   return "Done"
end transaction
end tell 

As you can see, the delete and edit scripts do also a find record...

A few more things:
To check if the application exists and the version number:

tell application "TheAppName"
try
   version
   on error number errNum
      return errNum
   end try
end tell 

to open the database, check password and user name, check if the "list" layout exists:

tell application "TheAppName"
activate
open file "PathToDb" with passwords "" for Accounts "Admin"
if exists layout "List" of database "TheDbName" then --Events.fp7
   return "opened"
else
   return "NoList Events.fp7"
end if
end tell 

where PathToDb, is an Applescript formatted path, in my Mac as

"Users:Trevix:Desktop:Database demo FM 8:Events.fp7"

Revolution and Microsoft Access
With Access things get much more complicated.
First of all, it is difficult to trap errors since with Vb script you can not do a "on error go to xxx". The only thing you can do is a:

on error resume next
   .....your script here
   if err <> 0 then
      result = "_Error|" & err & "|" & err.description
   else
      ...everything ok. Keep going
   end if 

Let's see a script to create a new record:

set MyDB = CreateObject("ADODB.Connection")
on error resume next
   --this connet to the database and check User name ("Admin"), 
   --Password and general database password.
   MyDB.Open "Provider=Microsoft.Jet.OLEDB.4.0;
   Data Source=C:/Documents and Settings/3vix/Desktop/
   Database Demo Access//Events.mdb;User ID=Admin;Password=;Jet \
          OLEDB:
   System database = ;Jet OLEDB:Database Password="
   --this is the string for the creation of the record
   SQLString = "INSERT INTO List 
   (TheUpdate, creator, Startdate, EndDate, StartTime, EndTime, 
   Team, TeamStatus, JobNum) VALUES \
          ('0','Trevisan','10/6/09','10/6/09','09:00','13:00','Trevisan','OK',Null)"
   --this is the actual creation of the record
   set rs = MyDB.Execute(SQLString)
   if err <> 0 then
      result = "_Error|" & err & "|" & err.description --err and \
             err.description are data about the error
   else
      SQLString2 = " SELECT @@IDENTITY"--all oK. These lines get the \
             ID of the new record
      set rs = MyDB.Execute(SQLString2)
      result = rs(0)--retun the ID
   end if
   --you need now to clean up things
   set SQLString = nothing
   set rs = nothing
   MyDB.Close
   set MyDB = nothing 

Note the "null" in the values list. If you want to leave the record field empty you must write "null" or you get an error.

About the variables:

Data Source=C:/Documents and Settings/3vix/Desktop/Database Demo

Access//Events.mdb

this is the complete path to the database document

Jet OLEDB:System database =

this is about the optional Workgroup information file. This is a Microsoft Access file that contains information about account names, password, group membership and preferences. The file "System.mdw" is initially created by the setup program when Microsoft Access is installed. So if you have user passwords you must write the path to the Workgroup information file after the "=".

This is the VBscript to delete the record (variables to be replaced in RunRev are red):

Set MyDB = CreateObject("ADODB.Connection")
on error resume next
   MyDB.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=PathToDb;\
   User ID=DBUSER;Password=DBPASS;Jet OLEDB:System database \
          =DBVBgroupPath;Jet OLEDB:Database Password=DBaccessPASS"
   SQLString1 = "SELECT COUNT(*) from List where ID=LaID" 
   if err <> 0 then
      result = "_error" & err & "|" & err.description --return \
           --error
   else
      set rs = MyDB.Execute(SQLString1) --do the opening of the \
            --database connection
      Var1 =rs(0)-- this now holds the number of records whose \
              --ID =TheID. Now we do the delete
      SQLString2 = "DELETE from List where ID = TheID" 
      set rs = MyDB.Execute(SQLString2) 
      --we count the records again
      SQLString3 = "SELECT COUNT(*) from List where ID= TheID" 
      set rs = MyDB.Execute(SQLString3) 
      Var2 =rs(0)
      if err <> 0 then
         result = "_error" & err & "|" & err.description \
                --return error
      else
         if Var1 > Var2 then --if the record has been deleted, \
                --var1 and var2 are different           
            result ="done" --everything OK
         else
            result = "_error" & err & "|" & err.description \
                   --return error
         end if
      end if
   end if
   set rs = nothing
   MyDB.Close
   set MyDB = nothing
   
   --Finally, the VBScript to edit the record:
   Set MyDB = CreateObject("ADODB.Connection")
   on error resume next
      MyDB.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source= PathToDb;\
      User ID=DBUSER;Password=DBPASS;Jet OLEDB:System database \
            = DBVBgroupPath;Jet OLEDB:Database Password=DBaccessPASS"
      SQLString = "Update List SET TheData where ID = TheID" 
      set rs = MyDB.Execute(SQLString)
      if err <> 0 then
         result = "_error|" & err & "|" & err.description
      else
         result = "Fatto"
      end if
      set rs = nothing
      MyDB.Close
      set MyDB = nothing 

TheData is replaced, before running the script, with a string like:

Field1 = '0', Field2 = 'Trevisan', Field3 = '12/6/09', Field4 = '12/6/09', Fieldn= ....

Every Fieldn has to be the exact name and order of the fields on the database document.

Lots of work has been done in order to make Tcalserver work with the two databases. I hope this information can be of help for your needs. I also would like some feedback from Applescript and Vbscript Masters about my use of these scripts in this context. You can send feedback to editor@runrev.com.

And if you want to check how Tcal works or, may be, you need a software like this, please download it freely from:

www.tcal.it (Italian website) or www.managementcalendar.com (English website)

Tcal downloads are fully functional for up to two connected users.

About the Author Roberto Trevisan is an architect working as an Exposition and MultiMedia event Designer in Torino, Italy. He has managed projects for architectural firms working with Fiat, Alfa Romeo, Zegna, Philip Morris, Tim Telephone, Sai and several chainstores. Visit the Tcal website here.