Decimal value to IP address

[westsec09]

Need help in converting numerical value to text (IP address). FM database has an ODBC connection to an external data source. IP address field in the external data source is a numercial equivalent to the decimal value calculation of an IP address.

Edited May 28, 2008 by Guest

[comment]

I know I have done this before, just can't figure out where. Hm...

Anyway, try:

Let ( [

B = Mod ( SerialIP ; 256 ) ;

G = Div ( Mod ( SerialIP ; 256^2 ) ; 256 ) ;

R = Div ( Mod ( SerialIP ; 256^3 ) ; 256^2 ) ;

W = Div ( Mod ( SerialIP ; 256^4 ) ; 256^3 )

] ;

W & "." & R & "." & G & "." & B

)

[westsec09]

Thanks!! I'll give it a try.

[westsec09]

comment - it worked! although it's missing the 3rd and 4th octet's decimal points

[westsec09]

comment - nevermind my last entry. I was setting the field to use number instead of text. It's working now!! Thank you so much!

[comment]

I know I have done this before

Thanks to LaRetta who found it:

http://fmforums.com/forum/showpost.php?post/214528/

Keywords for next time: IPv4 address extract octet RGB()

[westsec09]

sorry one last question: i have the ip address field but it appears because it is a calculation, i am not able to setup a relationship to a separte table in order to do a lookup based on the same ip address.

[comment]

I'm afraid I don't follow your setup. In general, you cannot base a relationship on an UNSTORED calculation in the 'child' table. If your calculation is referencing fields from another table, then it must be unstored.

The solution is probably to make an inverse calculation in the 'parent' table - but we need more specific details regarding your tables and relationships.

[westsec09]

Thank you. I just learned the hard way regarding calcuations. Hopefully I can explain things:

The calculation field was created from an external data source. Is there a way to send the calculation results (decimal number to IP address) as a text field in the same table so that i can reference the result? The only options in the layout is to create calculation or summary fields. Or is there another way?

[comment]

I'm afraid you will have to be a lot more specific than that. What do you mean by "The calculation field was created from an external data source"? Created where - in another table? How is that table related to the external data source? What needs to be looked up from where? And so on.

[westsec09]

I appreciate your patience and the quick posts.

Yes the calculation field is one table but is from an ODBC connection. I would like to create a relationship with another table where i manually entered IP addresses (as text) into. this table would lookup description, etc from the ODBC connected table.

[comment]

Do you mean the calculation is a 'supplemental field' in the ODBC table?

[westsec09]

yes. is there a way to send the results to a text field in another table or a way to reference the result in a relationship?

[comment]

I don't understand what you mean by "send the results to a text field in another table", or how this would help. If you calculate the result for ANY record in the ODBC table, how are you going to pick the record from which to send - and more importantly, how will you get back to the same record?

I believe you have two options here: either import the data from the ODBC table into a Filemaker table, then do the calculation there. This will enable the calculation field to be stored, so you will be able to use it to establish a relationship and ultimately do a lookup.

The second option is to do an inverse calculation in the other table and base the relationship on matching the result directly to the decimal IP in the ODBC source.

[westsec09]

I'm starting to do option#1. However, I'm getting an ODBC error: Query was empty.

I created a new Table with the same fields as the ODBC source. I created a button for the import script step.

Go to Layout (NEW TABLE)

Import Records [No dialog; DSN; Add;Windows ANSI]

[westsec09]

wow please disregard my last. i think i may have gotten it. thanks for your help!!