Unique calculation

[darrel]

Hi All,

I have a calculation field that I use as a key for a relationship. It is made up of the concatenation of three other fields.

I would like it to be unique but am not sure how to go about it ... Is there any way of validating a calculation field as unique?

Any suggestions appreciated, I've tried a number of different ways but all have failed

TIA

Darrel

[-Queue-]

Use a self-join based on the calc (assuming you can index it) and create a number field that auto-enters a 1 and strictly validates by the calculation Count( self::field ) < 1, with a message telling your users that the record already exists. Also uncheck the 'Validate only if field has been modified' box. Place this field on any layouts where the user can create a new record, and hide it (change its text color to match the layout's, disallow entry, etc).

[darrel]

Thanks Queue,

This works extremely well for stopping users creating exactly the same record. My only problem now is that when I go to edit an existing record I get the same validation message as if I were creating a new one! Any thoughts ...?

Cheers

Darrel

[-Queue-]

Ahh, okay, do your three other fields exist already, or are they user-entered?

[Razumovsky]

I would suggest doing this via a script. The define fields validation system is very limited in my experience.

Have you considered creating a "edit" layout that has a "finished editing" button that scans the record for inconsistencies such as this before returning to a view layout?

I use this system frequently, as it allows for a great deal of specificity in error checking.

-Raz

[-Queue-]

Continuing on Raz's thought process, I was going to suggest you have a creation layout and modification layout. Then you wouldn't need the number validation field, and you could have the 'continue' button script on the creation layout (which only consists of your required fields for concatentaion) first Go to Field[] to exit the record and then check that Count(self::field) = 1.

If Count(self::field) > 1

Show Message["This record is not unique. Do you want to remove it or edit the fields?"]

If Status(CurrentMessageChoice) = 1 {assuming Remove is the default option}

Delete Record[No dialog]

Else

Halt Script

End If

End If

Go to Layout[modification]

[darrel]

Thanks Raz and Queue,

I'm slowing coming to the same conclusion .. that the validation would be better off being scripted. I can still use the script step

If Count(self::field) > 1

I hadn't thought of doing it like that - thanks heaps for that one

Cheers

Darrel