comparing two fields

[mecon96]

Hello there, please help.

I have two text fields:

text1= 5 14 15

text2= 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15

I need to get the result, text3 = 1 2 3 4 6 7 8 9 10 11 12 13

I use wordcount, loop, middleword and substitute, but not getting the answer I require. Please can someone help?

Many thanks.

[mecon96]

OK what I really want to do is, I am creating an IP inventory database. For all the existing records in the database, I need to create a script that will find me the 'available' IPs (let's say we have 1-254 available in the same network to start with). The result will go in a selfpointing relationship portal, so you can click a button next to the 'available' number to creat a new record. Just got a bit stuck.

Thanks in advance.

[QuinTech]

Hi, mecon, and welcome to FM Forums!

Thanks for the follow-up post. Makes it much more clear what you need. In your case, i would make one record for each IP address, and have (at least) four fields: Octet1, Octet2, Octet3, Octet4. Each a number field.

Actually, i'd have more fields: One that calculates Octet1 & "." & Octet2, one that calculates Octet1 & "." & Octet2 & "." & Octet3, and one that calculates Octet1 & "." & Octet2 & "." & Octet3 & "." & Octet4. You can then use each of those in relationships to find the next available address at any octet. For example, to find the next available address on a subnet mask 255.255.255.0:

Relate the full IP address to itself. Make the relationship sorted, ascending, by Octet4. Then the calc field (Max(relationshipName::Octet4)+1) will give you the next available number at the fourth position. Then, of course,

(Octet1&"."&Octet2&"."&Octet3&"."&(Max(relationshipName::Octet4)+1))

will give you the next available address.

This doesn't account for numbers that will run over 254, but i'll leave that to you.

HTH,

Jerry

[mecon96]

Hi Jerry, thank you very much for the post.

I understand what you have suggested above. However, the problem is a little bit more complicated...

Some of our IP numbers are already taken (e.g. 200+ are used for servers and 1-100 DHCP and the rest allocated for static use).

What I would like to do is quickly find what is available within each of those category (i.e not already on the db). Present them in a portal (if possible) or a list of some kind, so a selection can be made quickly to create a new record.

mecon

[QuinTech]

Hmmm... the first thing that springs to mind:

Make three fields: z_one (calc number field always equal to 1); addrTaken (number field whose value will be either 1 or null); and addrNotTaken (calc number field equal to (Case(addrTaken,0,1)) ).

Make a relationship from z_one to addrNotTaken and display the results in a portal. Any record where addrTaken <> 1 will then show in this portal. If you also show addrTaken in the portal, then entering a 1 there will make that address disappear from the portal the next time it is refreshed.

J

[mecon96]

Many thanks for the tips, this should get me started...

Just one more question: the above method relies on existing records, is there any way to show the 'NotTaken' addresses even if the records have not been created?

Cheers.

[QuinTech]

There probably is, but i think the best bet is to do it the way i recommend. Or, if not that way, at least having one record for each possible IP address. I can't think of any good, logical ways to do this without having one record per address. I don't know if this has more to do with being conditioned to always have one record per object, or with the limitations of my brain.

Lest you despair, thinking you will have to sit around for four hours typing "1.1.1.1", "1.1.1.2", ... "254.254.254.254", i'll point out you can use the replace contents function. First make sure you have ... ACK! I've just realized what a fantastically large number of records this is going to be. Can you limit it to a certain subnet? Then you could have, say, Octet1 always equal to 192 and Octet2 always equal to 168. This would pare down the number of records necessary from 4,162,314,256 to 64,516. Much more manageable.

So: Make sure you have 64516 records. In Octet3, Replace Contents with:

Int ( Status ( CurrentRecordNumber ) / 256 )

This will assign 0 to the first 256 records, 1 to the next 256, etc. Then, in Octet4, replace contents with:

Mod ( Status ( CurrentRecordNumber ) - 1 ) / 256

Which gives you 0, 1, 2, ... , 255, 0, 1, 2, ... , 255, ...

You will then have a full range of IP addresses for the subnet 192.168.0.0. You can use similar tricks with the Status ( CurrentRecordNumber ) function to rule out batches of addresses.

J