FileMaker 14 Group

Getting Live Exchange Rates in FileMaker

Getting live data from outside sources is often a requirement of FileMaker solutions. In this post I’ll explain how we can do a simple import of JSON data from a URL. I updated this post to include two solutions, one using AppleScript and the other utilizing a new (easy) function in the latest editions of FileMaker to do most of the work.

Setup

For this we’re using a database called PRODUCTS. Create three fields: a text field rateFrom, a text field rateTo, a number field xRate, and a temporary global field called IMPORTED_DATA.

We’ll be getting our rates from ABC which outputs in JSON format. Because FileMaker doesn’t have a built-in function for this we’ll need to make a custom function.

From The Soliant Consulting Blog
fn_ParseJSON ( JSON ; node ; instance )

Let ( [

CodeClean = Substitute ( JSON ; ["\"" ; ""]; ["["; ""]; ["]"; ""]; ["{"; ""]; ["}"; ""]; [",";"¶"]; ["dogs:"; "¶"]  ); //Clean up the code
start = Position( CodeClean; node & ":";1; instance); //Find where the node is
vCount = PatternCount( Left( CodeClean; start); "¶") +1;  //Find the value position
Result = Substitute( GetValue ( CodeClean; vCount ); node & ":"; "")//get the value and remove the node name
];

Trim( Result)

)

Next, create a script called Get Exchange Rate.

FileMaker 12 And Above

Starting with FileMaker 12 there’s a new function call Import rom URL. We’ll be using this to get our data.

For FileMaker 12 and above use the Import From URL function with the target field set to PRODUCTS::IMPORTED_DATA and the following calculation:

"http://api.fixer.io/latest?base=" & PRODUCTS::rateFrom & "&symbols=" & PRODUCTS::rateTo

FileMaker 11 And Below

In older versions of FileMaker there was no built-in was to get data from a URL. 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 FileMaker 11 and below use this Perform Applescript calculated code:

Let ( [
temp_f = Substitute ( GetFieldName ( PRODUCTS::IMPORTED_DATA ) ; "PRODUCTS::" ; "" );
from_rate = PRODUCTS::rateFrom;
to_rate = PRODUCTS::rateTo
];
"
set the_results to do shell script "curl 'http://api.fixer.io/latest?base=" & from_rate & "&symbols=" & 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¶
"
)

Note: 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.

Note 2: The Applescript code uses the current record’s ID before setting the cell. We need to call the record ID of the cell to put data into the active record. For a global field like PRODUCTS::IMPORTED_DATA this doesn’t matter, but its just good practice to use.

Getting The Data

In a new record set PRODUCTS::rateFrom to “USD” and PRODUCTS::rateTo to “JPY”.

Run the script Get Exchange Rate. You should now have JSON data in PRODUCTS::IMPORTED_DATA. To parse this, create a new script step with the Set Field fuction with the target set to xRate and a calculation of:

fn_ParseJSON ( PRODUCTS::IMPORTED_DATA ; "rates" ; 1 )

Congrats, you have the exchange rate!

One thought on “Getting Live Exchange Rates in FileMaker”

Leave a Reply

Your email address will not be published. Required fields are marked *