Connect from Applescript or FileMaker?

Any trouble you encounter with the Pedias, here's the place to ask for help.

Connect from Applescript or FileMaker?

Postby The Swede » Tue Jul 01, 2014 5:05 pm

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?
User avatar
The Swede
Bruji Friend
Bruji Friend
 
Posts: 10
Joined: Sat Aug 26, 2006 8:19 am
Location: Sweden

Re: Connect from Applescript or FileMaker?

Postby Conor » Wed Jul 02, 2014 4:43 am

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.
User avatar
Conor
Top Dog
 
Posts: 5272
Joined: Sat Jul 03, 2004 12:58 pm

Re: Connect from Applescript or FileMaker?

Postby The Swede » Thu Oct 02, 2014 2:51 pm

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!
User avatar
The Swede
Bruji Friend
Bruji Friend
 
Posts: 10
Joined: Sat Aug 26, 2006 8:19 am
Location: Sweden

Re: Connect from Applescript or FileMaker?

Postby The Swede » Thu Oct 02, 2014 2:54 pm

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
User avatar
The Swede
Bruji Friend
Bruji Friend
 
Posts: 10
Joined: Sat Aug 26, 2006 8:19 am
Location: Sweden

Re: Connect from Applescript or FileMaker?

Postby Conor » Fri Oct 03, 2014 7:39 pm

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.
User avatar
Conor
Top Dog
 
Posts: 5272
Joined: Sat Jul 03, 2004 12:58 pm


Return to Support