It's not clear to me what your FileMaker table contains - I am assuming that it has the contents of list 1 (all 50 Excel files), is this correct?
The time taken for the search seems excessively slow to me; I would check to see if indexing has been turned on for the field(s) you are checking with the search.
My suggestion is to add another table to your database file, tblAuthorities, which you can create by importing the file shown in your list 2 image. In your relationship graph, create a relationship between your two tables by dragging the authority ID field from your postcodes table to the authority ID field in tblAuthorities.
Note that I am assuming your postcodes table has data in the following format:
postcode ?? ?????? ?????? AuthorityID
------------- ----- ----------- --------- ------------------
RH1 1AD 10 528367 150863 E920000001
RH1 1AD 10 528367 150863 E190000002
RH1 1AD 10 528367 150863 E180000008
RH1 1AD 10 528367 150863 E100000030
RH1 1AD 10 528367 150863 E070000211
RH1 1AD 10 528367 150863 E050007340
Also note that I am Australian, don't understand the UK postcode system, and may have things confused - a description of the fields shown in the list 1 file would probably add some clarity.
1 of 1 people found this helpful
Daniel, My first question has to be: Is that the only source of data available for your mission ?
I ask because, as I imagine you know, there are only 121 Postal Area which get sub divided into Post Towns and further broken down into Post Districts.
You don't really say what the "Authority" controls but I'm guessing the governing council. The post code system in its totality is for sorting mail onto the delivery trucks. The "Authority" surely doesn't break down to the door step. I suspect that if you parse the Postal Code (the one you would write on an envelope) down to the first level (121) and second level (about 21 per) you would have a manageable method to identify the "Authority".
You obviously also have the name of the town so you could parse that out as you did with the postal code. Those two fields concatenated with an underscore might very well serve as a key for a lookup for the Authority.
Either way or any way I think there is a better data source out there for you.
Lastly, you could get/post from a website like:
That one will cough up the answer on the fly as you enter a property into your system. Notice that I used one of your sample postal codes in the url (street and town is stronger).
Hope this helps (a Yank who works in the UK on occasion)
Whilst the scale is bigger than in Australia, the problem is the same. I don't see a problem with 1.7 million records merged so long as you are only seeing them via a relationship.
Each region in Australia which shares the same postcode can have many towns and one is the central 'authority'. WHilst this might be tolerable for mail sorting it is a nightmare for mapping or things like Facebook where they define where you live by the main town not the town you actually live in.
I wish there were more postcodes as potentially our mail would be quicker in Regional Australia. When I ordered my passport it was returned to my 'local postoffice' but really the main regional one. They sat on it for 2 weeks then returned it to the Immigration department because the notification I got was sent to a non-existant address... a non-existant street in the main town not the real street in the town where I live.
This all works fine in the city where sometimes a suburb has more than one postcode but when you are talking about several hundred square km for one postcode... it makes it pretty rediculous.
Thank you all for the responses,
I had thought about ‘parsing’ (you learn something everyday) the first 3 or 4 digits in the post code however the problem with that is local authorities (district or borough councils) have areas that do not match with post code areas. For example postcodes that start with RH* * can be in either Molevalley district or Reigate and Banstead district.
I will look for a different source of data.
I have thought about setting up a script that pastes a post code into a web site like the one you suggest or http://www.planningportal.gov.uk/wps/portal/genpub_LocalInformation?docRef=LocalInformation&scope=202&langid=0 however this seems like a clunky way out and ideally we would like the process to happen in the background rather than in the foremost browser window.
Thanks for the response.
Correct the filemaker table contains all 50 excel files worth.
The below is what the table looks like:
The speed of a find etc has completely changed since o originally imported that data, Perhaps it was a hardware problem when I was experimenting with it before?
What you suggest is what I was going to try befire I was confronted with the slow down problems I will work on it today