Indexing - "Unique" Value

[Betsy]

I have a multifield index and I want to set its Unique property to True or
False based on the value in another field. How is this possible?

 

[solex]

Betsy,

It is not possible to set and unset a unique index as the index properties
will always apply to the entire table, and doing so may cause the
referential integrity to fail on some records.

A couple of suggestions:

(1) create 3 tables, the first table contains all of the fields that are not
included in the "unique index" and the flag field that determines whether
you need to apply a unique index. The other two tables will contain the
index fields one table will have the unique property set for the index and
the other will not.

(2) make your application enforce the integrity rules based on the flag
field. Therfore you form will have the logic to determine if the fields
should be unique or not. The table will not have the unique index set.

Dan

 

[Betsy]

Thanks.

In regards to #2, I want the combination of the two fields to be unique
based on the flag field - can I do this in the form?

 

[solex]

Sure,
In the beforeUpdate and beforeInsert events check the value of the flag
field. If it is set and the fields are filled in then do nothing if not
then set cancel = true and the user will not be allowed to save the changes.

Dan