Folks

I'm pretty good at most things, but field validation by calculation is not one of them. I just cannot get my head around it.

Perhaps validation won't work for what I want to do, in which case I'm open to suggestions.

Users enter a code (like "REG") into a key field called "Group". This field is used as one side for a relationship. I'd like to set up a system whereby if the record is not in use (has no related records based on the key field) then the value in the "Group" field can be modified. However if the record has been used, I want the users to be unable to change the value.

i was hoping I could do it with validation: I don't care if the users *try* to edit the field as long as they get a message that the field cannot be changed and it reverts back to the original value.

The field is in a list view layout so I cannot do any fancy layout switch-a-roo type stuff.

Souution is in FMP 7, cross platform but mostly Windows.

Thanks.

Option 1.

Script a button over the field to prevent entry into "Group" if there is a related value.

Option 2.

Create an unstored calculation field to track the modification of the "Group" field. Then use validation on the "Group" field.

not ModField or ( Length(ModField) and Length(Group) )

Option 3.

Validation = IsEmpty( Group ) xor IsValid( GroupRelated::Group )

Option 4.

LookupField = Lookup "Group"

CalculatedValueField = Calculated value, (do not replace) = "Group"

Add a Calculated value option to "Group" (do not replace) = "LookupField"

Validation on "Group"

IsEmpty( Group ) xor ( IsValid( RelatedTable::Group ) and Group = CalculatedValueField )

Option 5.

Only show the field via a toggled relationship in a portal.

Options 1 or 4, I think will satisfy your requirements a little better than the others.

I attached a set of files (fp5, i hope you can use them, Vaughan) to demonstrate Mark's #3 (hope you don't mind the echo, Mark), which i think is the easiest method.

The field is validated with IsEmpty ( relationship::foreignKey ). Thus, entry always fails when that condition is NOT true, regardless of what is entered.

J

vaughan.zip

The trouble with IsEmpty ( relationship::foreignKey ) is when a related record already exists. It gets stuck in a loop until you agree to deletion by reverting the newly created record. If there is no existing right side key, this is a great solution.

If the keyfield is "Group", I think Option 4, or something like it, is better . The validation can be toned down to (IsValid( RelatedTable::Group ) and Group = AutoEnter) with "validate only..." checked.

The calculated value = LookupField is only there to bypass the validation warnings if the value of "Group" is deleted.

I had thought that was what Vaughan was looking for; re-reading his original post now, i think maybe i was wrong. I did not account for the fact that it was the key field he wanted validated. Oops!

I could be wrong, but I don't think you should be using IsValid for this. IsValid will always return 1 unless there is a data type mismatch, the related table cannot be found, or the field has been deleted from the related table.

Thanks folks.

The "button over the field" trick would be good but I want to allow the users to tab between fields.

Another option is to incorporate a custom dialog into the new record creation step to get the initial vale, and just not let them ever change it. If the record has no related children then I'll let them delete it, otherwise they're stuck with it.

Again, my thanks.