Applescript, Filemaker, and Dates problem

Scripting Forums AppleScript | Mac OS X
,
#1

Hello,

This is my first post here, and I apologise if this has already been covered (I had a look, but couldn’t find what I was after). The problem I have is this: My Filemaker Pro (8) database has two fields of relevance here (a Month field, and a year field, text and number respectively) from these two fields, I need to populate the contents of a date field with an actual date (i’ve picked the 15th/mm/yyyy, as it’s the middle of the month) so that I can generate a schedule based on it.

I’ve written an Applescript which gives the user a list of months, then a list of years (and these then fill the appropriate fields in Filemaker, but the actual date field refuses to fill (even though Applescript reports no errors). It just runs, the Month and Year fields fill ok, but there’s nothing in the date field. Anyway, here’s the script (I hope you can help):

set options_A to {“January”, “February”, “March”, “April”, “May”, “June”, “July”, “August”, “September”, “October”, “November”, “December”}
set theMonth to (choose from list options_A with prompt “UK availability month” without multiple selections allowed) as text
set options1_ to {“2006”, “2007”, “2008”, “2009”, “2010”, “2011”, “2012”, “2013”, “2014”, “2015”, “2016”, “2017”, “2018”, “2019”, “2020”}
set theYear to (choose from list options1_ with prompt “UK availability year” without multiple selections allowed) as number
set theDay to 15 as integer
tell application “FileMaker Pro”
set field “UK availability month” of current record of front window to theMonth
set field “UK availability year” of current record of front window to theYear
if theMonth = January then
set aMOnth to 1 as integer
if theMonth = February then
set aMOnth to 2 as integer
if theMonth = March then
set aMOnth to 3 as integer
if theMonth = April then
set aMOnth to 4 as integer
if theMonth = May then
set aMOnth to 5 as integer
if theMonth = June then
set aMOnth to 6 as integer
if theMonth = July then
set aMOnth to 7 as integer
if theMonth = August then
set aMOnth to 8 as integer
if theMonth = September then
set aMOnth to 9 as integer
if theMonth = October then
set aMOnth to 10 as integer
if theMonth = November then
set aMOnth to 11 as integer
if theMonth = December then
set aMOnth to 12 as integer
set theDate to {theDay, aMOnth, theYear} as date
set field “Finished book date” of current record of front window to theDate
end if
end if
end if
end if
end if
end if
end if
end if
end if
end if
end if
end if
end tell

Thanks in advance,

Kev.

#2

Hi Kev,

your if - then chain is wrong. The final statement can never happen.
It’ much easier to get the month’s number with a repeat block


set options_A to {"January", "February", "March", "April", "May", "June", "July", "August", "September", "October", "November", "December"}
set theMonth to (choose from list options_A with prompt "UK availability month" without multiple selections allowed) as text
set options1_ to {"2006", "2007", "2008", "2009", "2010", "2011", "2012", "2013", "2014", "2015", "2016", "2017", "2018", "2019", "2020"}
set theYear to (choose from list options1_ with prompt "UK availability year" without multiple selections allowed) as number
set theDay to 15 as integer
repeat with x from 1 to 12
	if theMonth is item x of options_A then
		set theMonth to x
		exit repeat
	end if
end repeat
tell application "FileMaker Pro"
	set field "UK availability month" of current record of front window to theMonth
	set field "UK availability year" of current record of front window to theYear
	set theDate to {theDay, theMonth, theYear} as date
	set field "Finished book date" of current record of front window to theDate
end tell

PS: “without multiple selections allowed” is not necessary because that’s the default setting

#3

Hi, and thanks for such a speedy reply. However, I get the following error message when I run your script: Can’t make {15, 9, 2019} into type date. Also, the month field is set to the numeric month value (8 instead of August, for example). That’s not a big problem, however.

Cheers,

Kev.

#4

I don’t have Filemaker 8. and my Filemaker 6 refuses to calculate dates.
One solution is to calculate the date outside the Filemaker tell block.
Using the “current date” makes the scipt independent of the system date settings

set options_A to {"January", "February", "March", "April", "May", "June", "July", "August", "September", "October", "November", "December"}
set theMonth to (choose from list options_A with prompt "UK availability month") as text
set options1_ to {"2006", "2007", "2008", "2009", "2010", "2011", "2012", "2013", "2014", "2015", "2016", "2017", "2018", "2019", "2020"}
set theYear to (choose from list options1_ with prompt "UK availability year") as number
set theDay to 15
repeat with x from 1 to 12
	if theMonth is item x of options_A then
		set theMonth to x
		exit repeat
	end if
end repeat
set theDate to date (short date string of (current date))
set year of theDate to theYear
set month of theDate to theMonth
set day of theDate to theDay

tell application "FileMaker Pro"
	set field "UK availability month" of current record of front window to theMonth
	set field "UK availability year" of current record of front window to theYear
	set field "Finished book date" of current record of front window to theDate
end tell
#5

Hi Stefan,

Won’t using the current date enter that in the date field rather than the date derived from the month and year fields? Sorry if I’m missing something obvious here. Also, I tried the script you sent, but got the following message: FileMaker Pro got an error: Object or property is the wrong type.

The filemaker date field is set up as a date field, with the format as dd/mm/yyyy, so it should work.

Regards, and thanks again,

Kev.

#6

I see, Filemaker’s date format in a date field is a string not AppleScript’s date format.

then try this:

set theDate to (theDay as string) & "/" & (theMonth as string) & "/" & (theYear as string)

To coerce a string to a date in Applescript I always use the “current date” command
and then set day, month und year to their values. Then the script works everywhere
regardless what date format is set in the system preferences

#7

Hello

This one works on my machine.
In my International System Preference, the date format is dd/mm/yyyy.

set options_A to {"January", "February", "March", "April", "May", "June", "July", "August", "September", "October", "November", "December"}
set theMonth to (choose from list options_A with prompt "UK availability month") as text
set options1_ to {"2006", "2007", "2008", "2009", "2010", "2011", "2012", "2013", "2014", "2015", "2016", "2017", "2018", "2019", "2020"}
set theYear to (choose from list options1_ with prompt "UK availability year") as number
set theDay to 15
repeat with x from 1 to 12
	if theMonth is item x of options_A then
		set theMonth to x
		exit repeat
	end if
end repeat
set theDate to date (short date string of (current date))
set year of theDate to theYear
set month of theDate to theMonth
set day of theDate to theDay

(* build the date as dd/mm/yyyy accordingly to system settings *)
set theDateFM to (short date string of theDate)

tell application "FileMaker Pro"
	set field "UK availability month" of current record of front window to theMonth
	set field "UK availability year" of current record of front window to theYear
	set field "Finished book date" of current record of front window to theDateFM
end tell

Yvan KOENIG (from FRANCE dimanche 29 octobre 2006 14:17:26)

#8

Hello Yvan,

Thank you very much, that’s solved it.

Best regards,

Kevin.

#9

Hello again,

Although the script works perfectly on my system at home (OSX Tiger), when I try it at work (OSX Panther) it won’t! When it hits the line in the script : “set month of theDate to theMonth” I get the error “Can’t make 8 - or whatever month I select “ into the expected type”. I read on this site that there are system date issues with Panther, and I downloaded a couple of OSAXs but none have worked. I set the Date and time format in the International Panes in System Preferences to those on my Tiger system, but still no go. The OSAXs I tried are Date Format OSAX and Date/Time OSAX.

I’m well out of my depth with OSAX files, and would greatly appreciate any help.

Cheers,

Kev.

#10

to set a date’s month using an integer is a new feature in Tiger.
actually you can remove the whole repeat block, it should work anyway

#11

This is because Panther and earlier only accept months that were AppleScript’s month constants, not integers.
i.e.:

month of (current date)
--> October

Anyway, give this a go (expanding on the others’ above):

set theDay to 15

set theMonth to choose from list monthList() with prompt "UK availability month:"
if theMonth is false then error number -128 -- User pressed the Cancel button, so bail out by using the proper cancel error!
set theMonth to run script theMonth -- Convert the text to an actual month by treating it as if you were typed into a Script Editor document

set theYear to choose from list yearList(year of (current date), 9) with prompt "UK availability year:"
if theYear is false then error number -128

set theDate to (current date)
tell theDate
	set year to theYear
	set its month to theMonth
	set day to theDay
	set time to 0
	
	set theDateFM to short date string
end tell

tell application "FileMaker Pro"
	tell current record of front window
		set field "UK availability month" to theMonth
		set field "UK availability year" to theYear
		set field "Finished book date" to theDateFM
	end tell
end tell

on monthList()
	set janDate to current date
	tell janDate to set {day, its month} to {29, January}
	set monthList to {}
	repeat 12 times
		set end of monthList to month of janDate as Unicode text
		set janDate to janDate + 30 * days
	end repeat
	return monthList
end monthList

on yearList(initialYear, additionalYears)
	set yearList to {}
	repeat with i from initialYear to (initialYear + additionalYears)
		set end of yearList to i
	end repeat
	return yearList
end yearList
#12

Thank you so much, (and everyone who has helped) that works perfectly.

Cheers,

Kev

#13

Oooops, no. Now Filemaker’s doing something funny. mmmmm … need to think on this one.

#14

no, that’s OK. Script runs fine :slight_smile: Thanks again to all who helped.

Kev.