Locating numbers in a text field?

[nelliott]

Let me explain. I want to try and extract postcodes (UK equivilent of Zip Codes from a CV field (resume field!) so that I can automatically populate a Postcode field. The only way I can think how to do this is if FM Pro can locate the first instance in which a number is next to a letter (with no spaces inbetween) and then extract from each side to take the whole postcode. Examples of postcodes are B91 3PH, WA14 4UA, M1 2GH etc.

Is this possible or does anybody have any other ideas on how these can be extracted?

Any help would be much appreciated

Cheers

Nick

[Lee Smith]

Hi Nick,

I'm pretty sure we'll be able to help you find a way. However, there are usually so many variables in this stuff, that when doing something like this it would be better to see the actual raw data. If there is a problem with confidentiality, just change the vital information (i.e. names, phone #, etc. but not the structure, meaning do not remove any spaces, "", . etc.) before sending it.

Otherwise, check out every parsing tip file you can get your hands on. Start by doing a search for parse, or parsing at these two URL's www.databasepros.com and www.fmfiles.com.

HTH

Lee

[Vaughan]

Sorry Nick, I don't like your chances... it's not a FMP-specific limitation, it's just that there are too many permutations to program with any decent acuracy. A touch typist with 90% acuracy is pretty bad: it means one word in ten needs retyping.

Best bet is to create a Postcode field and get the users to type the postcodes into it, not into the address field. If you are processing legacy data to separate the postcode information... budget for somebody to manually retype the data.

[RussBaker]

This script will extract a UK postcode from within a text field "CV Text" and place it the text field "Postcode". If there is no postcode in CV Text, you will get an error message in the postcode field (so you can do a find for the errors). You will need to create the extra global fields (they start with a g_) mentioned in it.

The script uses FileMaker's Word functions because they have inbuilt routines to disregard certain characters (like a "." or "," if the postcode appears at the end of a sentence or phrase).

Allow User Abort [ On ]

Freeze Window

Set Field [ Postcode, ""]

Set Field [ g_Word Counter, 1 ]

#

[CobaltSky]

Hi Nick,

I recently added a demo file to my web site which is directly relevant to what you are trying to achieve.

It uses a script to extract alphanumeric strings from a text field.

The script is only ten steps long in total, and requires no additional fields.

Moreover, it will extract not just the first, but all alphanumeric code strings (of more than two characters in length) in the target field of the current record - and list them in an adjacent field.

The technique was originally developed for use with university subject and course codes, but has since been used to isolate part codes, international postcodes, consignment codes and licence plate numbers. The technique could be readily adapted to suit your current need.

If you're interested, the demo can be downloaded from:

http://www.nightwing.com.au/FileMaker/demos/CodeExtractor.zip

The demo download is free and the file is not password protected, so you'll be able to investigate the 'mechanism' if you wish.

[Lee Smith]

Hi Ray, Russ and Nick,

I made a sample file using Russ's script and ran a copy of the text that was in Nick's original post, and then compared the results of that to the sample file offered up by Ray at is site. The biggest difference I noted was that Russ's Script pulled out the first group of characters that looked like a postal code, and Ray's pulled all of the characters except for the one with only 2 characters, "M1" but did so in 3 and 4 character groups which was divided then by a paragraph instead of the original space.

IMO, this just goes to show that this stuff is dependent on consistency at input, and/or the translations between applications.

Thank you both for your tips, I know that they will come in handy at some point in the future, even if I don't have a need for UK postcodes at the moment.

Lee

[CobaltSky]

Hello Lee,

The settings which cause the NightWing CodeExtractor demo to ignore codes under three characters in length, and to return all codes not just the first, can easily be changed so that the result returned would match that returned by Russ' system.

Similarly, Russ' script could be readily extended to provide those two additional features if desired.

So that is not really a significant 'difference'. By way of analogy, you might just as well say that the difference between a cow and a sheep is that one of them is standing under a tree.

Whilst the differences in output are trivial and result from minor differences in the settings, behind the scenes there are quite fundamental differences in the code design and the driving mechanisms. In fact the theory behind the method of analysis of code-strings in the two examples springs from somewhat different principles. Though they may produce a comparable result, they arrive at it by rather different means, and I guess I find that more interesting than the minor differences of configuration.

As far as the results the scripts produce goes, again there are other things that are perhaps more significant considerations than the effect of the settings. For instance, as compared to Russ' script, the NightWing demo could be said to have some advantages and some disadvantages, as follows:

Advantages:

1 - Less than half the number of script steps

2 - No additional fields required

Disadvantages:

1 - Greater complexity of calc/function expressions (eg If [ ] condition)

2 - No mention of cricket in the description

There would likely also be a slight difference in the time taken to process a given block of text, though I haven't tested that.

As I'm sure Russ would agree (if he were awake at this hour...) one of the benefits of a forum such as this is the opportunity to take a look at different approaches to solving a problem...

[Lee Smith]

> As I'm sure Russ would agree (if he were awake at this hour...) one of the

> benefits of a forum such as this is the opportunity to take a look at

> different approaches to solving a problem...

I totally agree with you, and that was my purpose, to compare approaches and to see what made them tick for my own personal growth.

Please keep up the training exercises.

Thanks,

Lee

[RussBaker]

Yep - I agree with all of the above - especially that this is a good place to put your ideas up.

There are several different ways to solve this problem and how you do it (and how much effort you put in to it) will be influenced by your situation.

For example, when I was setting up my script, I considered putting in checks for the length of the first and second part of the extracted postcode. If you had a couple of thousand records, this would be worth doing to give you a result less prone to error. If the first part of a UK postcode is always 2 or 3 characters and the second part is always 3 or 4 characters then a modified script would ignore the A1 cricketers and other possible non-postcodes.

For info - my script checks about 3000 words er minute on an 800MHz iMac.

For all you non Brits and their ex-convict descendants out there... There is something of a healthy rivalry between England and Australia over that strange game of cricket.

[nelliott]

Thanks everyone for your input. It is truely appreciated (apart from the cricket part, but hey I'm sure Michael "Man of the Series" Vaughan would have something to say about that!). Once again FMForums has come up trumps!

Cheers

Nick