Address Lookup in FileMaker using JSON
Addresses. I’m guessing we all have them and no doubt so do all of our clients. I think we can safely say that nearly all customer interaction is going to start with who they are, what they want and where they need it. So, addresses. Now if we agree that addresses are useful then we can certainly agree that we want them to be accurate and easy to access, right?
That brings to mind all of those websites which have an address lookup facility - you know the ones where you provide a postcode and you’re immediately shown the full addresses for that postcode. That's really handy and would be very useful in say, a FileMaker Customer database. If this has piqued your interest, read on.
But! Before I continue, here are some interesting facts about postcodes in the UK.
1. There are around 27 million postcodes in operation, though that probably only includes the geographic ones. Non-geographic postcodes are used by certain businesses (such as banks) and a few government departments. Probably the hush hush ones that we don’t know exist…like the National Domestic Extremism and Disorder Intelligence Unit.
2. It took over 15 years for the full introduction of the postcode by the GPO (now known as the Royal Mail). Mind you, back in 1959, England was probably only a collection of 3 villages, a small town and Blackpool Tower, so there were only 7 postcodes to begin with. Okay, I may have trivialised that fact quite a lot but, back in October 1959, when the first postcodes were created, England was a much smaller place. Kind of makes you wonder why it took 15 years to get everyone a postcode.
3. Father Christmas has his own postcode and it actually works - XM4 5HQ. It used to be SAN TA1 but the Royal Mail changed it so make sure, for Christmas 2018, you use the right one otherwise Santa won't get his letters!
4. Postcode data in the UK is not provided free of charge - the Royal Mail licences the data to numerous businesses, at a cost. Which brings us to…
So how do I get that postcode data into my shiny FileMaker database and what will it cost me?
In essence, JSON is a method for sharing data, held in arrays, which can be read easily by people (think of it like a more legible alternative to XML). FileMaker started to take note of JSON in FileMaker 16, with the introduction of several JSON commands but, in the usual fashion, it was introduced quietly and without much real world support. Way to go FileMaker. Even the current online help guides fall woefully short of what the industry is after but still, that’s where guides, such as this one, come in.
I’ve taken the time to write a very basic JSON postcode interrogator database for you to download. Will you be able to plug this into your database? Nope, sorry - at least not without some work. First off, I haven’t written this with that intention - it’s only a vehicle for you to see how JSON can be used to grab address data via a postcode. Secondly, as I mentioned above, postcode address data isn’t free - you need to pay for a license token (ie the database won’t work unless you pay for the service which populates the address data) and I can't provide that token for you. Sorry.
Consequently, to make any use of this database, you’ll need to get hold of a license token from a suitable provider - I used Crafty Clicks. They currently charge around £15 a month for 250 address lookups (about 6p per use) but there are other monthly / annual plans available and other address lookup providers - you may want to shop around. I liked the Crafty Clicks website as the pricing seemed reasonable, they have lots of different pricing plans to suit our needs and the support instructions they’ve provided for JSON data retrieval are really rather good.
You can download the FileMaker JSON Address Lookup database here.
After you’ve downloaded the database and presuming that you do use Crafty Clicks, you’ll need to update the $$Token variable in the Startup script with the access token details that Crafty Clicks will provide (it’ll look like a 20 character hexadecimal string with a hyphen between every block of 5 characters). Make sure that you put your token in quotes otherwise you'll get a "Table Cannot Be Found" error as FileMaker will try to resolve the token as a field. After the $$Token global variable is loaded, you should be able to enter a value into the “Postcode to search for” field on the Contact layout - click out of the field and you’ll be shown a popover with a portal of addresses (click one to select it). That’s all there is to it.
Things to note:
1. There are four scripts but you’ll only be interested in Address Lookup and Select Address. The Close Popover script is my way of ensuring that the address selector popover doesn’t close when it loses focus. That’s a handy piece of code right there so feel free to make use of it in your own work should it prove to be useful.
2. The scripts make use of Virtual Lists which I’ll presume that you are at least aware of - if not, I recommend that do a bit of research and familiarise yourself with the technique. Suffice it to say, I created an AddressLookup table which is used to hold all of the addresses that the address lookup service will provide for the supplied postcode (one address per record).
The AddressLookup table has a number of previously generated records (50 should be sufficient for testing) each of which has a field that acts as a counter (I’ve called this the VListNumber field). I've already populated the VListNumber field with a number from 1 - 50 as it acts as counter for grabbing the JSON data which is provided by the address lookup site. The rest of the fields in the AddressLookup table are calculation fields for each part of the address (line 1, line two, town, etc).
With a little background reading of virtual lists, you'll see exactly what I've done to utilise this very fast method of storing the address data (at least temporarily, which is all we need).
3. I'm storing a single address per contact but if you look closely, you'll see that multiple addresses are supported (though you'll need to make a couple of modifications to do that).
4. When you look at the JSON code in the scripts, remember that JSON data is held in an array. In fact, in this case, it's held in an array in an array (each address is an array and all of the addresses are held in an array based upon the postcode). In the Address Lookup script, you'll see that you need to loop through the postcode array and then use a counter to extract each part of the address you're currently viewing. It's easy enough to understand once you've got your head around it.
5. Yes, you can use the code provided in the supplied database in your own private / commercial FileMaker databases. You see, sometimes stuff is free :)
I hope that's helped you out. If you're after FileMaker database development work, igeek are here to help!