Jump to content

Can someone help me get my brain around this one?


This topic is 6509 days old. Please don't post here. Open a new topic instead.

Recommended Posts

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?

Link to comment
Share on other sites

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"

)

)

)

Link to comment
Share on other sites

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

Link to comment
Share on other sites

This topic is 6509 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.