Update 27 Feb 2023: this article has been superseded by JSON Currency Exchange Rates revisited.
Update 11 Apr 2021: as per comments below, demo has been updated to use a new service: openrates.io.
Welcome back for another exciting round of JSON exploration. Last time we discussed a JSON-related bug fix in FM 19, as well as a free package tracking service that returns results in JSON format. Today we’re going to look at a free currency exchange rate service.
Demo file: json-exchange-rates-via-openrates.zip
I threw together this FileMaker front end to pull current and historical currency exchange rates from OpenRates.io, a free service which provides rates published by the European Central Bank. Rates are updated once per business day at 16:00 (4 PM) Central European Time.
When you click Get Rates, the demo queries…
https://api.openratesapi.io/[criteria]
…via Insert From URL, and populates $$json with the result (which is then displayed via the “json” field).
Some Notes re: the Demo
Click the “x” icons to clear fields.
When fields are clear, default values will be used — e.g., if dates are empty you will get rates from the most recent business day, and if none of the currency boxes are checked, you will get rates for all currencies.
If a base currency is not specified, it will default to EUR (euro).
Checking the Format box will apply JSONFormatElements to the result.
When the Format box is checked the JSON keys will appear in alphabetical order, which is mostly okay in terms of human readability except for “start_at”, which logically belongs between “base” and “end_at”. Checking the Sort box remedies this annoyance via simple substitution. (Note: “start_at” and “end_at” only appear when a date range has been entered, otherwise there is a single “date” key.)
Changes are not applied automatically — click Get Rates to refresh.
Preview of Coming Attractions
Next time we’ll explore a number of different ways to render a found set as a JSON object.