WIN xp FMP8 Adv

I have two lists (which are actualy textfiles at the moment)

one containes surnames
the other containes prefixes of surnames (like: van, der, o', vander, mac,
mc)

I want to combine these lists into seperate tables, which can be added to
and altered when needed. Then I want to compare the surname list to the
prefixes list and take away any prefixes from the surname list.

There might be over 10000 records in the surnamestable
and about 50 records in the prefixes table.

So having entered a surname of O'Mally, and O' being in the prefixes list,
the result for this record should be Mally.

I have been twisting my mind how to connect these two table an d try to come
up with some calculation. But failed. Is a scripted loop the only (SLOW)
option?

--
Keep Well,
Ursus

I made a small example that seems to work in FMP6.
If you wish to see it, please give me privately your real address.
Remi-Noel



"ursus.kirk" <secret (AT) nowhere (DOT) com> a écrit dans le message de news:
43906037$0$95883$dbd4b001 (AT) news (DOT) wanadoo.nl...

Do you also want

Vanmally
Dermally
Macmally

to return "Mally?"

What about

Vant --> t
Dern --> n
Macy -> y

Those would seem unintended? Or do you want the stripping of prefixes only
to occur for the names present in the second table?

Bill

"ursus.kirk" <secret (AT) nowhere (DOT) com> wrote

Ursus,

If I understand correctly, you have two tables:

Prefixes
========
van
der
o'
vander
mac
mc

CompleteSurnames
================
O'Mally
MacNichols
McDonald
Smith
VanDerHoff

If a name in the Surnames table contains one of the Prefixes, you want to
split the name into two parts, the prefix and the surname.

In FileMaker 8 the way I have solved this is as follows:

1) Create a new field in Complete Surnames:

TestPrefixes (calculation, test result, 40 repetitions) =
If (Length(Extend(Surname)) < Get (CalculationRepetitionNumber) ; "" ;
Left ( Extend(Surname); Get ( CalculationRepetitionNumber )))

This returns a repeating field that looks like this, for Marriott:

M
Ma
Mar
Marr
Marri
Marrio
Marriot
Marriott

(If names can be longer than 40 chars then simply increase the number of
repetitions)

Repetitions are a good way of simulating loops without scripting.

2) Next, create a relationship between the two tables:

Prefixes::Prefix = CompleteSurnames::TestPrefixes

This will cause a match when any of the items in TestPrefixes matches a
Prefix in the other table.

3) You can then find the prefix that was matched using a portal, or the
calculation:

MatchedPrefix (calculation, text result) =
Prefixes::Prefix

and the suffix by using

Suffix (calculation, text result) =
Right ( Surname; Length ( Surname )-Length ( Prefixes::Prefix))

Note, this will indiscriminately match words like "Macy" and "Dern." You can
use an If statement to control whether a suffix has to be a certain number
of letters before the split occurs.

Example file can be found at http://wjm.org/linked/Names.zip

Bill

"ursus.kirk" <secret (AT) nowhere (DOT) com> wrote

Thanks Bill

But I am not there quite yet.

You have understood correctly about the two tables.

But I want the name
van Meter
to return only Meter.
And the name
Vanmeter
To return Vanmeter

And to complicate things even further O'Mally should return Mally, but it
has no space between the prefix and the suffix. (But this is not very
important to solve)

Ursus

"Bill Marriott" <wjm (AT) wjm (DOT) org> schreef in bericht
news:FdudnQFIwNGnAg3eRVn-rQ (AT) comcast (DOT) com...

Ursus,

The method I outlines and sample file I attached will correctly handle the
"van Meter" and "Vanmeter" situations.

Bill

"ursus.kirk" <secret (AT) nowhere (DOT) com> wrote

Ursus,

The method I outlined and sample file I attached will correctly handle the
"van Meter" and "Vanmeter" situations. You simply need to specify "van "
(with a space) in the prefix table. It even works with the apostrophe in
"O'Mally." Download it and give it a try.

Bill

"ursus.kirk" <secret (AT) nowhere (DOT) com> wrote

Bill,

I did try and had figured I need to add the space after each word.. I didn't
mean to be harsh, I just mist the solution and got frustrated. SIGH. Well
now I'm back on earth, thank you again for your trouble.

Ursus

"Bill Marriott" <wjm (AT) wjm (DOT) org> schreef in bericht
news:1dmdndtkrbTiZgzenZ2dnUVZ_v-dnZ2d (AT) comcast (DOT) com...

Sorry to ask again Bill,

I have found (or rather Remi-Noel has found) an other working solution. But
yours is more sleek. There is however still a bug. We have lots of poeple
with duble prefixes. So
van der Meer
is a valid name, which in my solution should result into Meer.
I have trioed adding "van der " including the space, but Ido understand it
just matches the first connection (being the 'van' bit), returning der Meer
as solution.

You don't have to wring your neck, but if there is a solution I missed I
would like to know.

Ursus


"Bill Marriott" <wjm (AT) wjm (DOT) org> schreef in bericht
news:1dmdndtkrbTiZgzenZ2dnUVZ_v-dnZ2d (AT) comcast (DOT) com...

This is because you have "van " as well as "van der " as prefixes.

I think the easiest solution is to

1) Modify the TestPrefix formula to:

If ( Length(Extend(Surname)) >= Get ( CalculationRepetitionNumber ) ; Left
( Extend(Surname); Length(Extend(Surname))-Get (
CalculationRepetitionNumber )+1); "")

This will reverse the trimming so that your repeating field is

van der Meter
van der Mete
van der Met
van der Me
van der M
van der{space}
van der
van de
van d
van{space}
van
va
v

2) Make sure your Prefix table has all double (and triple) word prefix
variants entered (physically, not by sort) before any single-word prefixes
that are a subset of the double-word prefix. In other words, "van der " must
be appear (in Unsorted mode) before "van " in the table.

This will force the relationship to consider the double-words ahead of any
similar single-word matches.

In the case that you want to later add a double-word prefix when a similar
single-word prefix already exists, you would add the double-word version,
then delete and re-add the single-word variant.

Bill


"ursus.kirk" <secret (AT) nowhere (DOT) com> wrote