Must be Unique Except in one case.

[grumbachr]

Setting up a field to store IP address. I want the field to require a unique value except when its set to the value DHCP.

I'm never really dabbled with Validation other than default options when defining a field. I'd appreciate any help in getting me started in the right direction on this one.

TIA.

[LaRetta]

You might try something like this (attached)

uniqueExcept.zip

[comment]

Or:

uniqueExcept.fp7.zip

[grumbachr]

That is exactly what I need and it works beautifully but could I ask you to help me understand it a little more? The logic is escaping me.

If I get it the first part, IPaddress = "DHCP", FM saying if the field is DHCP it okay and it passes validation.

but the , or IsEmpty (selfjoin::IPaddress), Is where i'm getting lost. The "IsEmty" FM is saying if the field (related field) is emtpy it passes validation.

But I don't see how it fails when they are the same.

Thank you so much for the example.

[LaRetta]

The "IsEmty" FM is saying if the field (related field) is emtpy it passes validation. But I don't see how it fails when they are the same.

Since validation happens before record commit, it will not accept ANY IP except the first time it is created (because it expects NO related records). If there is ANY matching related record, it means that there is another identical IP already existing. And since we are validation ALWAYS and not only when the record is modified, the validation fires first and refuses to accept the duplicate IP.

But I hope you are seriously checking Comment's suggestion because another field holding the DHCP saves creating a selfjoin relationship at all.

[grumbachr]

Yeah I'm looking at both. But really want to understand yours as validation by calculation is still foreign to me.

Although I wouldn't have thought of comments example on my own it was straight forward and easy to understand. Which for me might be the right choice in the end.

Thank you both for taking the time to post examples.