How do I search within a range?

[kllrwlf]

I'm currently using FM6 (I'll upgrade when I have the funds) :/

I have 2 databases.

Database-A has 2 fields with numbers already populated in Field 1. Field 2 is to be automatically populated by Database-B, Field 3.

Example:

Record 1, Field 1= 14

Record 2, Field 1= 6

Record 3, Field 1= 28

Database-B has 3 Fields. Field 1 is the first number in range, Field 2 is the end of the range, Field 3 is the result

Example:

Record 1, Field 1= 1

Record 1, Field 2= 10

Record 1, Field 3= First

Record 2, Field 1= 11

Record 2, Field 2= 20

Record 2, Field 3= Second

Record 3, Field 1= 21

Record 3, Field 2= 30

Record 3, Field 3= Third

So the result in Database-A, Field 2 should be:

Record 1, Field 2= Second

Record 2, Field 2= First

Record 3, Field 2= Third

Is this possible?

(hope this makes sense)

[Tim W]

Hi,

Database A and Database B are related how?

Are the ranges, going to be static? 1-10 always = first, 11-20 always = second, etc.?

If so, I would just make a calculation and you don't need a second database.

Like:

Case(field1 >0 and field1 <11, "first", field1 >10 and field1<21, "second",......etc)

see case function for examples.

HTH,

Tim

[kllrwlf]

Thank you for the reply. :

The ranges are not in order.

This was actually to have a IP Address to Country shown.

I have a database with ranges in IP Number format and would like the name of the Country returned.

Database-A has the IP Address:

ex. 23.34.21.53

which is converted to an IP Number format= 388109621

Database-B has the IP Number ranges:

ex. One Record might have:

23.34.0.0 - 23.34.256.256 in IP Number format= 388104192 - 388169984 which is for USA.

So I would like Database-A with 388109621 to somehow check with Database-B to see if 388109621 falls into any range of all the records and spit out the one that those, in this case "USA".

BTW, if anyone has a IP to Country Filemaker database or some sort, that would probably be easier. :/

[Tim W]

Hi,

So what you really need is a table lookup solution?

You could use a script to loop through DB B and return the first match, but it would need to be triggered somehow.

How many Records in DB A?

Would one pass through DB A, accessing the match from DB B, do the trick? If so, you could trigger a script to do the following.

Start at record 1 DB A

loop thru DB B until match, insert field2 in db A

Loop thru DB A on record at a time executing the lookup to fill in field 2.

Ideas??

Tim

[comment]

This is very easy in version 7 and higher. In previous versions, you need to work a little harder, seeing as there are likely to be be gaps between your ranges.

Define two relationships, one based on IPnumber = IPnumberLow, the other as IPnumber = IPnumberHigh.

Define two fields that lookup the country from the ranges file - the first field as "lookup next lower" from the first relationship, the other as "lookup next higher" from the second relationship.*

If the two fields produce the same country, you have a winner. If not, the number falls in a gap not covered in your database.

---

(*) I am doing this from memory, so it could well be the other way around.

[kllrwlf]

Can lookups work with ranges?

Database-A has let's say "5" and is looking for records in Database-B that has ranges (field 1="1", Field 2="10"), so "5" is not actually in Database-B. Only one record with the 2 fields that represents the range.

[comment]

Yes, lookups work with ranges - that's what the option 'If no exact match then: copy next lower/higher value' is for.

[Tim W]

Hi Comment,

He is on version 6. Can he do it as you suggest under that version?

Tim

[comment]

He is on version 6. Can he do it as you suggest under that version?

Yes, of course. If it were v.7 or higher, it could be done simply by a single range relationship, using multiple criteria.