Enforcing uniqueness when using computed primary keys

[Jonathon]

For a given project, there are many unique tickets. I have valid_if logic to enforce unique a unique [ticket_no], with a helpful error message:

Previously, I was using uniqueid() as a pkey column ([id]) for each ticket. To facilitate better sqlserver indexing / organization, I want to use a compound/computed key based upon [project_id] and [ticket_no].

When I do this, having a duplicate [ticket_no] will also violate the uniqueness requirement of the rows primary key. This is fine - however, the error message generated from this overrides the more helpful error message applied to the [ticket_no] column. With the computed column, no error message will display under [ticket_no] at all. Upon trying to save, the default (and less user-friendly) “There is already a row with the key ____” message is displayed at the top of the form:

Note that error messages for other columns will still display. This leads me to believe that what I want to do is possible; however, I will need to adjust my formula. What I would typically use in this scenario is:

NOT(IN([_THIS], SELECT(tickets[ticket_no], NOT(IN([id], LIST([_THISROW].[id]))))))

The above formula won’t work, as the [id] column changes dynamically to reflect the ticket_no.

[Jonathon · Accepted solution]

Solving my own problem instead of deleting, in case someone else runs into this.

With a computed column, enforcing uniqueness on the id to allow custom error messages, you can just modify the formula to reference the _RowNumber instead of the primary key (which does not work, as it is effectively filtering itself out as if you were editing the row)

NOT(IN([_THIS], SELECT(tickets[ticket_no], NOT(IN([_RowNumber], LIST([_THISROW].[_RowNumber]))))))

In other words, the documented pattern for enforcing a uniqueness constraint does not hold for a computed pkey when you are enforcing uniqueness on one of the related computed columns. If that makes sense.

[Steve_Fuller]

Jonathon:

NOT(IN([_THIS], SELECT(tickets[ticket_no], NOT(IN([_RowNumber], LIST([_THISROW].[_RowNumber]))))))

Many thanks for keeping the post active!

It works perfectly more me too!