AnsweredAssumed Answered

Relationship based on postcode data

Question asked by haighd2 on Nov 27, 2013
Latest reply on Nov 28, 2013 by haighd2

Hi I wonder if anyone can help,

 

We have a filemaker solution used to create reports with a subject property address field. I have created a calculated field that picks off the post code of the subject property address so that we have a postcode field.

Screen shot 2013-11-27 at 13.58.35.png

What I am looking for is a way to know which local authority this address is in, based on the post code.

 

I have downloaded a list of all postcodes in the country (List 1 ) which is in roughly 50 separate excel files (csv) I also have a list (list 2) of all local authority areas in the country. List 2 has unique ID numbers for each local authority. List 1 has a collumn with a correspnding code to let you know which authority that aprtiular post code is in.

 

list 1

Screen shot 2013-11-27 at 14.05.10.png

list 2

Screen shot 2013-11-27 at 14.11.05.png

 

I have created a filemaker sheet (file I guess you call it!) which when viewed in table view has two columns (fields) one with postcodes in and one with the relevant code. This creates about 1.7 million records one for each postcode and as a result when you attempt to search the list the system takes about 3 mins per search to return a result. I was intending to have a relationship between this and our filemaker solution. Is there a way of setting up a relational database without having a record for every individual postcode?

 

Generally I need a better system to generate a field in our solution that states the name of the relevant local authority. Open to any suggestions?

 

Any help would be appreciated.

 

Cheers

 

Dom

Outcomes