ERP 2.50:Developers Guide/How To Add a Constraint
How to add a constraint
We have added a valid to column in the How to add a column, but the value it has doesn't make sense to be a date prior to the one in valid from. To force the value to be higher it is possible to add a database constraint.
The changes described in this Howto are done in the context of a module. Specifically the module's DB_Prefix is used in naming of the new constraint. The DB_Prefix relates the constraint to the module.
Add constraint to database
To add the constraint execute in database the following clause:
ALTER TABLE AD_ALERTRULE ADD CONSTRAINT HR_SALARY_DATES_CHK CHECK (VALIDTO>VALIDFROM) ENABLE;
Pay special attention to the name of the constraint. It follows the modularity naming rules, so in case it is added in the same module as its table's one no special rule is needed to be followed, but in case it is going to be in a different one it will have to start with EM_DBPREFIX being DBPREFIX the module's DBPrefix. It is also very important to ensure it is within the correct module because the application doesn't validate it. Suppose instead of VALIDTO we created EM_HR2_VALIDTO column in module HR2, HR2 depends on HR1 and not vice versa the check constraint must be in module HR2, other wise it would be referring a column that does not exist in any dependent module.
Add a proper message
Now create the window with this new column and we try to insert a valid to date lower than valid from one we'll get an error. But actually the error is not very user friendly, it would look like:
It would be better it said something like "Date to cannot be lower than date from" to help the user to insert correctly the dates. This is done adding a new message, note that message are translatable, so when the user selects a different language for his/her user interface the message will be displayed in that language.
To create the message go to Application Dictionary || Message, there create a new one. The important fields here are (for more information see the AD_Message table description):
- Search key
- The search key must be exactly the same as the constraint's one, in this case HR_SALARY_DATES_CHK. This is the way it is identified the message.
- Message type
- Depending on the type the UI for the message box will be different (green for success, yellow for warning...), in our case we want a red error message box, so we select Error.
- Message text
- It is the user friendly message that will be displayed inside the message box. So let's enter: Date to cannot be lower than date from.
That's all now we have a message like:
Whenever Application Dictionary or Physical database is modified, it is possible to export that information to xml files, this is the way Openbravo ERP maintains database data as part of its source code files. To do it just execute:
For further explanations read the Development tasks document.
ERP 2.50:Developers Guide/How To Add Columns To a Table | ERP 2.50:Developers Guide/How To Create a Trigger