Integrate FileMaker & WeatherStack using APIs – desegregate double entry.

Getting started with Integrate FileMaker API in 5 Easy Steps.

Like you, I wanted to learn how to use FileMaker’s APIs, so I started researching free APIs. I had a very general idea of how an API worked and decided to make a simple API with the available information I already knew about accessing the API data and then parsing the data into a relevant field. So I started by researching weather APIs and found WeatherStack. WeatherStack has three available plans. The free account is limited but will allow you 1000 calls for no cost. This is a great way to get started learning how to make an API call.

Step 1. Go to Website and Create a Free Account

First, go to https://weatherstack.com and sign up for and API it is Free, you can do this by pressing the “Start Using The API” button located on the main screen.

Once you are registered and logged in to your dashboard, you will receive an API key. This key is used to identify your current plan subscription and usage.

Now you are ready to get started. In your FileMaker database create the following fields.

Step 2. Use an Existing or new FileMaker Database and Create Your Fields

API – Will be used to hold your personal API code generated from weatherstack.com

City – This will contain the city and state you wish to find the weather for.

Master URL – Copy the following into your master URL path.

http://api.weatherstack.com/current?access_key=<API>&query=<City>

URL Calc – Calculation field which will Substitute your data, 

Substitute ( Master URL ; 

[“<City>” ; City];

[“<API>” ; API]

 )

Date = Date

Description  = Text

Temperature  =  Number

FeelsLike = Number

Humdity  =  Number

Pressure  =  Number

Precip = Number

Visibity  = Number

Wind Speed  = Number

Wind Direction  = Text

Wind Degree  = Number

Icon Path  = Text

Icon Container = Container

JSON = Text

Step 3. Create Your Script

Now create a script called “SET JSON”, when the script is performed FileMaker query the Weatherstack API for real-time weather data in a location of your choice.

Your results can be found in your JSON field and will look something like this.

{“request”:{“type”:”City”,”query”:”New York, United States of America”,”language”:”en”,”unit”:”m”},”location”:{“name”:”New York”,”country”:”United States of America”,”region”:”New York”,”lat”:”40.714″,”lon”:”-74.006″,”timezone_id”:”America\/New_York”,”localtime”:”2020-06-15 16:52″,”localtime_epoch”:1592239920,”utc_offset”:”-4.0″},”current”:{“observation_time”:”08:52 PM”,”temperature”:23,”weather_code”:113,”weather_icons”:[“https:\/\/assets.weatherstack.com\/images\/wsymbols01_png_64\/wsymbol_0001_sunny.png”],”weather_descriptions”:[“Sunny”],”wind_speed”:0,”wind_degree”:83,”wind_dir”:”E”,”pressure”:1030,”precip”:0,”humidity”:36,”cloudcover”:0,”feelslike”:24,”uv_index”:8,”visibility”:16,”is_day”:”yes”}}

Now that you have your API data results you can use, FileMaker’s JSONGetElement function to parse your data results.

If you are not familiar with using this function, don’t worry it is not as scary as you might think. Use your data viewer to assist you. Section in the JSON will open and look something like this {“request”: and will close looking like this “}

In this example there are two sections. Request and Current

If you enter JSONGetElement ( Settings::JSON ; “request” ). You will notice FileMaker returns the following results.

{“language”:”en”,”query”:”Mission Viejo, United States of America”,”type”:”City”,”unit”:”m”}

You can then narrow your results by entering in a second element this is done by adding a dot/period between values.

JSONGetElement ( Settings::JSON ; “request.language” )

Which as you can see returns the following results. “en”

To query the second section your request will look like this.

JSONGetElement ( Settings::JSON ; “current” )

{“cloudcover”:25,”feelslike”:25,”humidity”:59,”is_day”:”yes”,”observation_time”:”07:46 PM”,”precip”:0,”pressure”:1015,”temperature”:22,”uv_index”:10,”visibility”:16,”weather_code”:116,”weather_descriptions”:[“Partly cloudy”],”weather_icons”:[“https://assets.weatherstack.com/images/wsymbols01_png_64/wsymbol_0002_sunny_intervals.png”],”wind_degree”:210,”wind_dir”:”SSW”,”wind_speed”:20}

To narrow your results add a section name.

JSONGetElement ( Settings::JSON ; “current.humidity” )

In this example the data only goes down one category from current it is possible the data drills down further, using the a period between values will allow you to drill down to the next section as needed.

Additionally, if this was a 5 day forecast, you might find that the JSON repeats for each day of the week. To get the first repetition you data may look something like this JSONGetElement ( Settings::JSON ; “current[0].humidity” ) zero always will indicate the first repetition.

Now go to your field definition and define each of your field as follows, you will find that some of the data will parse out but will include extra characters you may not want. 

This was true for some field, like the icons path.  Which will be used to pull the weather icon. I also found this to be true for the temperature. The JSON gave me Celsius results when I wanted Fahrenheit. 

It is possible I could have made this change in my API call to correct this, but instead I created a custom function to convert Celsius to Fahrenheit and created an additional field call “temp letter” this field can be used as a setting to flip between Celsius to Fahrenheit and is also used for display purpose to let the user know the weather format used.

Step 4. Add Custom Functions define Fields

From my experience, I have learned that you may need help every once in a while converting your data. An excellent example of this was when I did a Hubspot API, and the data comes over in a UNIX style format that is was not easily converted. The good news is there are plenty of free custom functions to help you out. In this example, I used a custom function called CelsToFahr to convert my data from Celsius to Fahrenheit.

((degreesCelsius/5)*9)+ 32

DEFINE YOUR FIELDS

Date = Let ( $Date = Left ( 

JSONGetElement ( Settings::JSON ; “location.localtime”  ) ; 10 );

Date ( Middle ( $Date ; 6 ; 2 );Right ( $Date ; 2 )

Description  = Substitute ( JSONGetElement (Settings::JSON ; “current.weather_descriptions” ) ;

[ “\”” ; “” ];[“[“;””];[“]”;””])

Humidity =  JSONGetElement (Settings::JSON ; “current.humidity” )

Precip = JSONGetElement (Settings::JSON ; “current.precip” )

Pressure  =  JSONGetElement (Settings::JSON ; “current.pressure” )

Visibity  = JSONGetElement (Settings::JSON ; “current.visibility” )

Wind Speed  = JSONGetElement (Settings::JSON ; “current.wind_speed” )

Wind Direction  = JSONGetElement (Settings::JSON ; “current.wind_dir” )

Wind Degree  = JSONGetElement (Settings::JSON ; “current.wind_degree” )

Icon Path  = Substitute ( JSONGetElement (Settings::JSON ; “current.weather_icons” ); 

[ “\”” ; “” ];[“[“;””];[“]”;””])

Substitute ( JSONGetElement (Settings::JSON ; “current.weather_icons” ); 

[ “\”” ; “” ]; [“[“;””]; [“]”;””])

Temperature  =  If(

temp_letter=”C”;JSONGetElement (Settings::JSON ; “current.temperature” );

CelsToFahr ( JSONGetElement (Settings::JSON ; “current.temperature” ) ))

FeelsLike = If(

temp_letter=”C”;JSONGetElement (Settings::JSON ; “current.feelslike” );

CelsToFahr ( JSONGetElement (Settings::JSON ; “current.feelslike” ) )) Number

Step 5. Test and Run your Script

Now that everything is in place, be sure to enter the city you would like to get the weather for. Then run your SET JSON script and it will call and parse your data.

ADDITIONAL INFORMATION

From the WeatherStack webpage, you can view the API documentation https://weatherstack.com/documentation, this documentation for this demo, however, if you would like to further your skills. You may find this page very insightful.

Good Luck!!

Kitty Kane