FileMaker: Getting Live Exchange Rates

This technique uses Applescript and cUrl to get the current exchange rate between two currencies. The results are returned to a field within FileMaker.

For this we’re using a database called PRODUCTS and a temporary global field called Temp 001, so change these accordingly for your database.

The Perform Applescript calculated code:

Let ( [
temp_f = Substitute ( GetFieldName ( PRODUCTS::Temp 001 ) ; "PRODUCTS::" ; "" );
from_rate = "USD";
to_rate = "JPY"
];
"
set the_results to do shell script \"curl 'http://rate-exchange.appspot.com/currency?from=" & from_rate & "&to=" & to_rate & "'\"¶
set the_rate to word 4 of the_results¶
tell application \"" & FMVersion & "\"¶
tell database \"PRODUCTS\"¶
set rID to (current record's ID) as integer¶
set cell \"" & temp_f & "\" of record ID rID to the_rate¶
end tell¶
end tell¶
"
)

By using the GetFieldName function instead of just adding the field name in the Applescript code we’re able to maintain this script even if the field name changes in the future. You can use a similar method for the from_rate and to_rate if needed.

Another point to note is in the Applescript code, where we first get the current record’s ID before setting the cell. This is important because without calling a record ID of the cell, it will put the data in the first record’s field. For global fields this doesn’t matter, but if you want to use this code for grabbing rates, each rate in a different record, then it will be needed.