Category: FileMaker

Today’s vexing problem in FileMaker was in calculating bi-weekly intervals (as in a payroll period) without using a lookup table. For example, given a payroll period of:

Start	End
11 Jan 2009	24 Jan 2009
25 Jan 2009	7 Feb 2009
8 Feb 2009	21 Feb 2009
22 Feb 2009	7 Mar 2009

I wanted to be able to calculate the last and current pay period for any given date. After some digging around FMForums and more trial-and-error on my part, the formulas are:

To find the last pay period:

START = 14 * Div ( givenDate - 14 ; 14 )
END = 14 * Div ( givenDate - 14 ; 14 ) + 13

And to find the current pay period:

START = 14 * Div ( givenDate ) ; 14 )
END = 14 * Div ( givenDate ) ; 14 ) + 13

This calculation will find the date range between today and whatever the previous Monday was. Best used as part of a navigation script that takes you to a set of recent/current records.

Get ( CurrentDate ) - Case (
DayOfWeek ( Get ( CurrentDate ) ) = "2" ; 0 ;
DayOfWeek ( Get ( CurrentDate ) ) = "3" ; 1 ;
DayOfWeek ( Get ( CurrentDate ) ) = "4" ; 2 ;
DayOfWeek ( Get ( CurrentDate ) ) = "5" ; 3 ;
DayOfWeek ( Get ( CurrentDate ) ) = "6" ; 4 ;
DayOfWeek ( Get ( CurrentDate ) ) = "7" ; 5 ;
DayOfWeek ( Get ( CurrentDate ) ) = "1" ; 6 ;
0 ) & "..." & Get ( CurrentDate )

Posting this here for future reference in case I needed it again.

For the past couple of days I’ve been bonking myself on the head repeatedly trying to figure out what would seem to be a simple FileMaker operation. I want to create an alphanumeric serial number that increments as follows:

A1
A2
A3
…
A998
A999
B1
B2
B3

…and so on until it rolls over at Z999 and resets back to A1. Resetting is OK – there’s no need to keep a permanent archive as once these references have passed out of the larger tracking system they just get deleted.

In a sudden flash of post-ice coffee-enhanced thought, I came up with the solution. Make sure that the serial field is set to auto-enter a serial number on commit. Set “Next value” to A1 and “increment by” to 1. Create a “New Record” script that looks like this:

New Record/Request
If [ Int(SerialField) > 998 ]
Set Next Serial Value [ SerialField ;
Let ( [
alphabet="ABCDEFGHIJKLMNOPQRSTUVWXYZ" ;
CurrentLetter = Left( SerialField ; 1 ) ;
CurrentLetterPosition = Position ( alphabet ; CurrentLetter ;
1 ; 1 ) + 1 ] ;
Middle ( alphabet ; CurrentLetterPosition ; 1 ) & 1 )
]
End If
Commit Records/Requests

It’s an obvious solution in retrospect and I can’t believe it had been vexing me for so long.

I was once told that AppleScript is a read-only language and after much caffeine and gnashing of teeth this morning, I do not disagree at all. All I want to do is paste the name of a selected file into a FileMaker database. Sounds easy, right? Here’s the syntax:

tell application "Finder"
	set theFile to selection
	set TheName to name of (theFile as alias)
end tell
tell application "FileMaker Pro Advanced"
	go to database "Test_Import"
	go to layout "Test_Import"
	set data field "i_file_name" to TheName
	do script "Import Excel file"
end tell