Blog

Now that iCal is launching from within FileMaker we're going to move on from just compiling a simple launching script to actually getting iCal to work with the text fiel that we are exporting from Billings.

Lets see how in Billings we need to export the time slips for the range we are working with. Go into the left pane (under projects) and click on the "All Slips" icon. Once the slips show up on the main window select the range that need exported and select File > Export > Export Slips to Text

I have not seem that Billings has an Apple Event for this. To check this you can launch the Script Editor and drag the application icon (from the Applications folder) to the dock ONTO the Script Editor icon to reveal the dictionary. Alternatively with Script Debugger you can explore the dictionary in a more robust manner.

Once this has happened you should have a new text file in the export location, it will have a filename with the following naming constraint: "project name - working slips.txt". If you open this up you will see the following format for these items, a tab delimited file with the first row named after the fields from the SQLLite database table that Billings uses as the column names.

I'll share a little trick here that I use to manipulate text all the time with BBEdit. You can use this whenever you have a need to transpose data from a horizontal to a vertical format or vice versa.

Cut and paste the first row of the timeslips export file to a new BBEdit document, You can do a find and replace on the tab characters as shown above and you can move them to a vertical notation as shown below:

Project
Name
Kind
Category
Nature
Create Date
Start Date
Due Date
Complete Date
Quantity
Rate
Round Time
SubTotal
Markup
Discount
Total
Comment

Alternatively if you want to create the table for FIleMaker that the solution will need to use, drag and drop the .txt file on filemaker, make a new database and then copy and paste the table (if you have FileMaker Advanced) into your existing solution or use the new file as your starting point.

Notice that I've added a _Published flag to differentiate the extra field I created from the ones matching my solution. You can also see immediately that these text fields are holding some dates that are formatted in an ANSI standard that is a little different than what FIleMaker Pro uses for it's date and time fields. Im going to create some extra calculation fields to handle this. I want to, however, try to find some open source custom functions that will help with parsing the dates. To do tis I turn to Brian Dunnings' site for a list of custom functions.

I quickly found what I wanted here with this one:

http://www.briandunning.com/cf/611

Timestamp ( GetAsDate ( Middle ( DateTime ; 6 ; 2 ) & "/" & Middle ( DateTime ; 9 ; 2 ) & "/" & Left ( DateTime ; 4 ) ) ; GetAsTime ( RightWords ( DateTime ; 1 ) ) )

It seems to work great so I set up the rest of the fields I need:

Go ahead and complete this by creating an import script to import the text file, adding in all the usual stuff that you need for an import by deleting the records in this table (which we're going to use as a temp table or a staging table) so that you have a clean set each time, and adding another script step to delete the first row since this will be the header field names.

In the next example we'll look at how to continue with the mashup and automate it.