I want to do the following: if someone types in a phonenumber, the city part of the phone number should be in brackets. With 'filter' and 'let' I could get somewhere but:

this is Germany, all phonenumbers have 10 digits. What complicates this challenge is: some cities have 3 digits (Berlin: 030), others have 4 (Essen: 0201) and loads of them have 5 digits.

It would be impossible to have FileMaker determine which is a 3-digit, a 4-digit or a 5-digit phonenumber, but I have a distinct advantage here: I have a list of all the city-numbers in Germany.

I have a feeling that I should be able to have a user type in a 10-digit number, then compare that number to the list I have. The city-numbers have a big advantage: you can never make 4 digits out of 3 and come up with a valid city number, by which I mean that 030 exists, but there is no 0301, 0302, 03001, 03002 etc. So if I find 030, I am sure that this is the city of Berlin.

I can't come up with a smart way of comparing the typed number with the list I have. Something like: read the first three digits, compare to all records in the list. If nothing matches, read the first four, compare to all records. If nothing matches, read the first five, compare to all records.

Does anyone have any ideas on how to do that elegantly?

Have you looked at using Position and putting all the prefixes in a single string/field?. If the "012|022|023|030|045|163|248|300|" is needed many times in the calc, put it in a global text field.

Example:

If( Position("012|022|023|030|045|163|248|300|", Left(PhoneNumber, 3), 1, 1),

"I've got a match for first 3",

If( Position("0212|0322|1223|2130|2645|5163|6248|8333|", Left(PhoneNumber, 4), 1, 1),

"I've got a match for first 4",

If( Position("02142|03322|12253|21360|26345|45163|76248|98333|", Left(PhoneNumber, 5), 1, 1),

"I've got a match for first 5",

"I've got gibberish"

)

)

)

Cool problem. If you have a large number of cities, then you can put them in a table with their city prefix as a match field. Reference this table from your main file with three separate Lookup fields on three relationships; one from the first three digits, one from the first four digits, and one from the first five digits. Whichever one gets populated tells you the length of your city prefix.

See the attachment.

Version: v6.x

Platform: Mac OS X Panther

I knew it was possible, thanks for the pointers!

Ano, it sounds like you're using FM7, you might also want to check out the FilterValues() function.

FilterValues( Left(PhoneNumber,3), "012