Have you ever wondered what’s the difference between ‘Validation Where Clause’ and ‘List Where Clause’ in the Maximo Table Domain definition dialog?
I believe this is one of the most common misused features in Maximo. I have see so many times tables domains defined in the wrong way. Let me show a good example of a built-in table domain.
The ACTIVEUSER domain is used to allow the selection of an active user in the Cron Tasks application. Here are the domain details.
- Domain: ACTIVEUSER
- Object: MAXUSER
- Validation Where Clause: userid=:runasuserid
- List Where Clause: status in (select value from synonymdomain where domainid=’MAXUSERSTATUS’ and maxvalue=’ACTIVE’)
- Error Message Group: signature
- Error Message Key: NotActiveUser
Lets now analyze two key characteristics of this table domain, the validation where clause and the error message.
Error Message
If we open the Cron Tasks application and try to type some wrong value in the ‘Run As User’ field we will get a specific error: Run as User XXXYYYZZZ is not an active User
Removing the error message group/key from the domain definition we get the generic domain validation error: The value XXXYYYZZZ is not valid for Run as User
The error message group/key properties a Table Domain definition allow to display a more specific error message to the user when the entered value is not valid.
To further improve the error message you can use the {1} param to display the invalid value and the {0} param to display the field name the domain is tied to.
Validation Where Clause
The userid=:runasuserid statement in the Validation Where Clause ensures that the value entered in the field (CRONTASKINSTANCE.RUNASUSERID) matches a value in the data source table (MAXUSER.USERID).
To understand the reason why the Validation Where Clause is so important we can simply try to remove it and see what happens. After having modified the domain we can enter any value in the Run as User field !!!
This means that:
The Validation Where Clause must be always set to validate the entered value against the target table domain.
In our example userid is the attribute of the MAXUSER table that the domain is based on. Instead :runasuserid is the attribute of the CRONTASKINSTANCE table that you want to validate.
Nice work got good understanding of the concept in very comprehensive way.
“The List Where Clause is very similar to the List Where Clause but has an additional clause: userid=:runasuserid”
Should it be, The List Where Clause is very similar to the Validation Where Clause but has an additional clause: userid=:runasuserid
Anyway, this is very informative. Thanks!
Thank you for the feedback.
Actually it is: The Validation Where Clause is very similar to the List Where Clause but has an additional clause: userid=:runasuserid
In my testing, according to the sql logger, the List Where Clause gets ANDed with the Validation Where Clause for validation. Therefore, the Validation Where Clause from the example should be *just* userid=:runasuserid — the “and ” on the Validation Where Clause is redundant.
Nice Post…I have learn some new information.thanks for sharing.
As far as I know, Maximo combines the validation and list clauses when validating the entry in the field. So the validation where clause only needs to be “userid=:runasuserid” and the list where clause, “status in ( select value from synonymdomain where domainid=’MAXUSERSTATUS’ and maxvalue=’ACTIVE’)”
Oh great! You are definitely right.
Maximo adds the list clause automatically to the validation clause when validating the value.
I will update the post as soon as I can.
I have updated the post. Thank you again.
Wonderful blog! Do you have any tips for aspiring writers? I’m planning to start my own blog soon but I’m a little lost on everything. Would you advise starting with a free platform like WordPress or go for a paid option? There are so many choices out there that I’m completely confused .. Any recommendations? Thanks!|
Can we check Application name in validation where clause like “appname=’asset'” ?
Thank you so much for sharing this wonderful post with us.