Connect from Applescript or FileMaker?

[The Swede]

Is there any way I can set field values from an Applescript or from FileMaker?
I'd like to set a book as sold and the sold date from a script.
Is that possible? Perhaps via a workaround?

[Conor]

You can invoke the menu command to mark as sold from AppleScript. It requires that you authorize AppleScript to use Assistive Device Access. Selecting the movie would require the title column to be sorted.

Code: Select all

set old to (path to frontmost application as text)
tell application "Bookpedia" to activate
tell application "System Events" to tell process "Bookpedia"
	-- Select the movie based on title, works only if title sorted
	keystroke "Ready Player One"  -- Variable with Book title
	delay 2.5  -- Wait for Bookpedia to select the movie
	
	-- This requires Assistive Device Access, granted in System Preferences -> Security & Privacy -> Privacy -> Accessibility
	-- Another option is to set a Keyboard Shorcut, also in System Preferences and use that instead
	tell menu bar item "Book" of menu bar 1
		click
		tell menu item "Mark As" of menu 1
			click
			if menu item "Sold" of menu 1 exists then
				click menu item "Sold" of menu 1
			end if
		end tell
	end tell
		
end tell
delay 1
activate application old

Setting the date is a bit more complicated. Although you can mimic key strokes in AppleScript and open up the selling tab, the selection when you hit the tab to highlight might be anywhere (Bookpedia remembers the previous edited field to help users editing many entries, so you would have to rely in that it was the last field selected). The static way of selecting text fields is quite complex as the edit window has been customized to allow user flexibility. Hence the regular AppleScript commands such as "set text field 3 of window 1" will not work it needs to be:

Code: Select all

	-- Open the Selling tab for edit
	keystroke "e" using command down
	keystroke "]" using command down
	keystroke "]" using command down

	-- here the value "4" can change depending on the location of the Sold field, it can be brought up to the top in Preferences -> Fields so that it's always 1.
	set value of text field 4 of scroll area 1 of group 1 of window 1 to "07/07/2014"

I guess a smarter version of the script could find it anywhere in the window based on the title of the field. This could be adapted to also set the checkbox for sold on and you no longer need the above menu command.

Code: Select all

delay 0.5
	
	keystroke "e" using command down
	keystroke "]" using command down
	keystroke "]" using command down
	
	with timeout of 0 seconds
		set tElements to entire contents of window 1
	end timeout
	set num to count of tElements
	repeat with i from 1 to num - 1
		set element to item i of tElements
		if class of element is static text then
			if value of element is "Sold On" then
				set numone to i + 1
				set field to item numone of tElements
				set value of field to "07/07/2015"  -- This would be a variable with your date
			end if
		end if
	end repeat
	
	click button "OK" of window 1

I have also updated the beta version of Bookpedia to set the sold on date to the current date as a helper. So as long as you run the script on the day of the sale you would not need to set the date manually anymore.

[The Swede]

Hi again!

Sorry for the long delay, but I want to tell you all how I did.
You see, I didn't go with the "Keyboard macro"-solution.

Instead I did it this way:
First I bought the ODBC-connector from http://www.actualtech.com for connecting FM12 to Open Source Databases (MySQL, PostgreSQL and SQLite) and made a connection to the Bookpedia database. (Actually to a copy of it. One should never work directly with the database until all development is finished) When prompted for user/password I used my OS X-account that owns the file since SQLite don't use password to protect the database.

Then I made a button in my FM layout that I configured to Execute a SQL-statement. It makes a calculated SQL-statement since it needs to use data from the current post.

Since dates are involved I needed to convert the FM-date to SQLites format which uses some sort of epoch date. That needed a separate function added to FM and I found it here: http://www.briandunning.com/cf/863
And after tinkering with it - that is subtracting 977378400 seconds (some 31 years) from the result it gave the correct date.

The calculated SQL-statement which calls on the datefunction looks like this:
"UPDATE ZENTRY SET ZHASBEENSOLD =\"1\" " & ", ZONSALE =\"" & "0" & "\"" & ", ZSOLDON =" & " \"" & Julian (Timestamp ( Tabell::Orderdatum ; Time ( 0 ; 0 ; 1 ) ))& "\" WHERE ZCUSTOM1 =" & " \"" & Right ( Tabell::OrderArtNr; Length ( Tabell::OrderArtNr ) -Position ( Tabell::OrderArtNr ; " " ; 1 ; 1 ) ) & "\" "

In the field ZCUSTOM1 I have a unique articlenumber composed by partly the ZUIDfield and a location identifier so that I can find the book faster. I also have two trash-characters in the beginning of the FM-field that i "wash away" with the Position-statement.

This SQLstatement sets/un-sets the OnSale-flag and the Sold-flag and sets the SoldOnDate to the date of the order.

I also made another button that resets everything in case I wrongly click the first button.

Perhaps my explanation is understandable even though I use some swedish words in my fieldnames above.

Thank you for an excellent product!

[The Swede]

But I have another question;
Will the changes I did in this way (se above post) sync correctly to PocketPedia or do I need to alter any ChangedOnDate-field somewhere too?

Kind regards
The Swede

[Conor]

That is impressive programing work. Apple uses 2001 as the beginning of time, unlike Unix that uses 1970.

With the new version of Pocketpedia (version 3) you no longer need to update the zDateEdited field to get it to pick up the information. Only should you update the cover then you need to update zDateEdited as that still used to send only covers that have changed.It would be a date like the others that needs to be translated to begin in 2001 but you should be able to use now to get the current date.