Perform Find on Large number of Data Ranges

[TheFonze73]

Hi All,

this is my first post.

I need to perform a find which includes over 8000 data ranges.

How can I do this without entering each range individually??

I have an all the data ranges in excel already.

Any help would be greatly appreciated

[IdealData]

Can you give some examples of your "data ranges", and a bit more of a clue what is actually stored in your DB.

[Søren Dyhr]

Perhaps this will give you some clues?:

http://www.databasepros.com/FMPro?-DB=resources.fp5&-lay=cgi&-format=list.html&-FIND=+&resource_id=DBPros000651

--sd

[TheFonze73]

Thanks for the replies.

I have a database with about 6 million phone numbers and I am trying to omit the vast majority.

I know the number ranges I want to omit because of the exchanges that they are connected to.

There are about 8,000 number ranges I would like to omit.

I hope that helps.

Thanks

[IdealData]

From the original post

over 8000 data ranges.

Note: datA not datE

Which is why asked for some examples as I'm confused what a "data range" is.

[TheFonze73]

A range of phone numbers.

Sorry if I was unclear with my terminology

[IdealData]

Provide the numbers are all the same format then you should be able to easily identify the exchange part of the numbers, but I'm doubting the success of any part of this due to the large number of records and the obvious need to perform multiple finds.

You could script with a loop to EXTEND the found set on each loop, but this will require 8000 loops (based on your data ranges) and time to search will become a very relevant factor.

Do a sample test to see how quick FM returns a found set for just a few ranges. I'm guessing it will take several seconds...you can do the math.

[TheFonze73]

The amount of time it takes on the search isn't important. Omitting the numbers is important.

I am a FM noob and just wanted to be pointed in the right direction.

What would the script look like?

[IdealData]

Here's a mock up of the file. Use the NUMBERS table to record your phone numbers, use the RANGES table to record your ranges.

Please note that the phone number must be NUMBERS, not TEXT so you must strip any thing outside 0-9. The same applies to the ranges. Please see the sample data in the file.

Untitled.fp7.zip

[TheTominator]

Another way is to use a related table.

All your phone numbers are in one table.

All your "bad" exchanges are in another table.

Create a calculated field in the phone number table to pull out just the exchange.

Relate the exchange to the exchange in the other table. From the vantage point of the table of exchanges, show all related records from the phone numbers table including all matches in the found set. This will give you the phone numbers you don't want.

Show omitted records only to give you the records you do want.

Example file is attached.

ExcludingEchanges_fp7.zip