How to add a custum message to a table constraint

  • 18 November 2020
  • 1 reply

Badge +4

I've created a constraint to a table (check if date from is before date until). The moment I trigger this the following message is shown:

Unfortunately I see no way of changing this message to a custom message that explains clearer what is wrong.

I also want to show this my highlighting the involved fields or placing the message beside one of the fields.

Any ideas to make this happen are welcome.

1 reply

Userlevel 5
Badge +15

Hi Roy,

Edit: Vote here for this idea:

-----Original message

That is indeed currently not possible. Check contraints are created on tables and show the [translated] database error message.

To show a custom message you can use the Default procedure and/or Triggers. In there you can determine when to give the message to the user and adding more info. The function tsf_send_message is a good way to go as it can show a translated message and it can show more information.

In the project I work on, we use it in the default procedure as we always want to notify the user immediately if some value is not permitted. In code, the Default looks something like this:

if @date_from > @date_until
declare @error_text varchar(500)
select @error_text = null --Optional, see docs for possibilities

exec tsf_send_message 'date_from_past_date_until', @error_text, 0

select @date_from = null

*Note, comparing @date_from and @date_until only works if both have the Datetime data type as Datetime2 cannot be compared with each other.

In the error text you can show any value you like, such as the value of the date from column. In the translation of the Message, use {date_from} to place the value in that spot. {0} can be used to place text or a translation of a column for example.



You could also lead the user to the field that is incorrect by using the @cursor_to_col_id = 'date_from’. To highlight the field, you could use the Conditional layout by comparing the date_from column with the date_until column. If then the date_from column has a bigger value, it will be visible. 

If you would like Messages in combination with Constraints, feel free to create a topic in the Ideation section. I like the idea and will certainly vote for it.

Kind regards,
Mark Jongeling