Hello

I would like to restrict the insert of records into a dataset based on what values have historically been used for a given field. In particular, I am trying to prevent reuse of patient ids in the Demographics dataset. For example:

In the demographics dataset, a patient is assigned an id of 12345. Some time later, the patient is removed from the study and deleted from the demographics dataset. The patient id 12345 is no longer in the demographics dataset, but we want to prevent this id from being used again.

I have some postgres triggers setup to create a patient id history table, and a trigger applied to the demographics dataset to check against the history table when a new row is inserted. If the patient ID is found in the history table the insert is aborted, but I am unable to bubble up an error message to the UI to alert the user what has gone wrong.

I was looking into the trigger scripts documentation and I've been able to setup a beforeInsert trigger. I see how I can use the Labkey.Query API to check against the history table using the "row" parameter of the beforeInsert function, but I don't see a way to prevent the insert, or to provide a message to the user in the UI.

Is there a way I can prevent a record from being inserted as I've described and alert the user in the case that they are re-using an old patient id?

Thanks!

Sev