Hi Gerard,

Subscribing to the Royal Mail PAF (post office address file) can be quite expensive; it depends on who you go through to get it. I'm not sure a web based service would be the best option for use with VFP; I'd be interested to see how it worked though.

The PAF itself is a huge file with a lot of columns; these columns are largely useless for the majority of users. If you go for a CD copy then the chances are you will get the raw PAF in a text format; you'll need to import this into a queryable data source and strip out the fields you don't need.

If you're wanting to interface from a VFP app then I'd recommend either subscribing to the file on CD and then importing the columns you want into a DBF (or SQL Server);

or buying an address cleaning package like Capscan's "Matchcode" or Quick Address (QAS). Address cleaning packages usually come with their own licenced version of PAF and some even have API's that allow you to use their built in algorithms when handling PAF. This can be especialy usefgul if you want to format your UK addresses to the royal mail standard.

Matchcode can be found: http://www.capscan.co.uk/matchcode.aspx
Quick Address here: http://www.qas.co.uk/products/
Hopewiser here: http://www.hopewiser.com/

I personally find QAS to be the most expensive of the three although I have been told it has the best API.

Hopewiser used to include an option to create a PAF extract to allow you to select the columns relevant to you and export them to a text file. I think they may have pulledthat product but were working on introducing an API.

The majority of my experience has been with the raw PAF file itself or through Capscan's Matchcode API. The API integrates well in VFP and their developers are incredibly helpful. The last version I used was towards the spring of 2006, there were a few bugs when using it with advanced fuzzy matching options but I dare say they will have ironed these out by now. The documentation of the API is also excellent!

The API was simple to use:

Drop it on a form.
Set .AddressLine(n) (n being 1-7) property with the UK address you want to verify.
Execute the .CrossMatch() method
View the PAF elements in the .PafElement(n) (n being 0-10) property
Execute another method to format the address to a UK standard (which you have some control over in Capscan's settings)
Read the verified address back from the .AddressLine(n) property.

The only drawback is that the user of the app must have a licence to use the API AND either a copy of the PAF file locally or a licence for the client/server version of capscan.

Capscan also takes into account historical postcode changes. Postcodes can change in the UK; sometimes council boundaries move and sometimes new building developments cause an area to change its postcode. Capscan (and I dare say other PAF products) take these changes into account when querying.

I've never come across Arc en Ciel before and so I can't comment on that product.

If all you need is to look up an address on postcode then your best option would be to get the file direct from Royal Mail; I believe there are levels of subscription and you will be able to get the basic columns a lot cheaper than the whole lot.

One final note regarding the PAF contents. A PAF formatted address is usally made up of up to 7 fields - 6 address lines and a postcode. However the standard used by most people in the UK is for 5 address lines and a postcode:

1) Address Line 1
2) Address Line 2
3) Address Line 3
4) Postal Town
5) Postal County
6) Postcode

There is an additional hidden field known as the Delivery Point Suffix - in most cases this two character code points directly to a house or flat; although in some large cities this can reference a building and not the individual flats within.

The address details themselves are held in PAF "Elements", of which there are 11:

1) Organisation (Company Name for Corporate Addresses)
2) Sub Building (e.g. Sugden Annexe)
3) Building Name (e.g. Universal Mills or Dunroamin)
4) Building Number (e.g. 6)
5) Dependent Street (Rarely populated)
6) Street
7) Dependent Locality (An area within a village or hamlet that may have its own name, rarely populated)
8) Locality (e.g. Village name)
9) Postal Town (e.g. Huddersfield or Doncaster)
10) Postal County (e.g. Surrey)
11) Postcode (I recommend looking up UK postcode formats on wikipedia as there are a couple of regular expressions up there that are useful for validating postcode formats - also see note below*) and Delivery Point Suffix

The PAF also optionally comes with Ordnance Survey Grid references (Grid Eastings and Northings); these allow you to actually physically map a postcode to a geographic area. It is important to note that the Eastings and Northings for Northern Ireland are not to the same grid as the mainland UK and so a formula has to be applied to these to make them match up to the same grid. Unfortunately I can't remember the formula at this time but a quick google will help if you really need it.

If I were looking to introduce PAF at my current place of employment then I would recommend Capscan for address matching, verification and cleaning; OR the raw PAF file for a simple postcode lookup.

If using the raw file I'd host it in SQL Server (you could use SQL Express if you don't have the full app) as well. Fox is a fantastic app that I love to bits but in my experience it doesn't handle the PAF nearly as well as SQL does.

*A UK postcode is valid if it fits one of the following TYPE patterns:

CN NCC
CNN NCC
CCN NCC
CCNN NCC
CNC NCC
CCNC NCC

Regards,