Text Parsing (IP Address)

[tomg3]

Ok, this might be straightforward, I have been beating my head against my desk for a couple hours now...

Here's the situation:

I have a database which I am making to house our company's systems information. In this database is a text field called "IP" this field contains an IP address for each system in the format 1.2.3.4. (Imported, that's why it's text) I then have 4 other calculation fields (result number) that are meant to separate these so they can be searched / exported. (IP1, IP2, IP3, IP4)

Here is my problem, the IP varies in size, for example, each octet between the "."'s could be anywhere from 1 to 3 numbers.

(111.222.333.444 or 11.222.33.444 and so on)

I got the first calculation field to work properly with:

Left(IP;Position ( IP ;"." ;1 ;1 )-1)

However I can't get it to work for the other three fields.

Help!

[TheTominator]

IP1 = GetValue(Substitute(IP; "."; "¶"); 1)

IP2 = GetValue(Substitute(IP; "."; "¶"); 2)

IP3 = GetValue(Substitute(IP; "."; "¶"); 3)

IP4 = GetValue(Substitute(IP; "."; "¶"); 4)

[tomg3]

I don't know how or why, but that is PERFECT?! Maybe if you have some extra time / are board you can explain why it works...

None the less, thank you very much!

Edited March 25, 2010 by Guest

[TheTominator]

I don't know how or why, but that is PERFECT?! Maybe if you have some extra time / are board you can explain why it works...

The Substitute() makes a list.

The GetValue() pulls out the item from the list.

Substitute(IP; "."; "¶") replaces each occurrence of the "." with a line break. This makes each segment of the IP address appear on a separate line. A FileMaker list is a series of values with each value appearing on a separate line.

GetValue(a_list; 2) will get the second value in a list.

[tomg3]

Awesome! Thank you very much!

[gewald]

The Substitute() makes a list.

The GetValue() pulls out the item from the list.

Substitute(IP; "."; "¶") replaces each occurrence of the "." with a line break. This makes each segment of the IP address appear on a separate line. A FileMaker list is a series of values with each value appearing on a separate line.

GetValue(a_list; 2) will get the second value in a list.

An awesome solution. Thanks for the tip. My situation has an added wrinkle. I'm trying to compare an IP address to a table of IP addresses. Simple when the table uses a static IP address, but some of the entries use a wild card in the third and/or fourth position of the IP address to allow it to cover values from 1-255. Trying to avoid having to create entries in the table for every value as this could lead to 256 * 256 records to be added for more than a dozen entries. Will make the table very complex.

[comment]

I'm trying to compare an IP address to a table of IP addresses.

You could use a find. It's also possible to do a match via a relationship, using calculation fields. Would it be safe assume a wild card cannot be in the third position only?

[gewald]

You could use a find. It's also possible to do a match via a relationship, using calculation fields. Would it be safe assume a wild card cannot be in the third position only?

Yes if in the third, also in the fourth. Otherwise only in the fourth position. The table brings along other data associated with the IP address that is the purpose for the relationship.

I've been spoiled by using Hyperion Brio which allowed wild cards in relationships.

[comment]

Well, if you make a calculation field that takes an IP e.g. "12.34.56.78" and returns "12.34.56.78¶12.34.56.*¶12.34.*.*" it will match both an exact IP and masked ones.

Another option is to calculate the start and end points of a masked IP as decimal numbers and relate to the range.