Selecting specific Mbo’s using Relationships and Where Clauses
A very common scenario is that you would like to get an instance of a MboSet with not all records included, but only a subset. Spoken in SQL you would like to apply a where clause to limit the number of records you get as a result in a MboSet. In this article I will show you different ways on how you can achieve this.
- Usage of relationships
A very common pattern is to just use relationships when you initialize your MboSet. I have shown such a pattern in this article. You can easily define the where clause directly in the relationship and you will only get the records based on the where clause.
- Adding a Where-Clause to an existing MboSet
It is quit handy to add a Where-Clause to an existing MboSet. The following code example will show this scenario:
woset = session.getMboSet('WORKORDER') woset.setWhere("WONUM = '2009'") woset.reset() wo = woset.moveFirst() if wo is not None: print "Workorder ",wo.getString("WONUM")
At the beginning you initialize a MboSet returning all Workorder’s in the system. In line 2 you append a Where-Clause to the result set. When you have used a relationship with an existing where clauses in it the new where clause will be appended. The woset.reset() method call in line 3 is required to “execute” the where clause and update the result set in woset. The rest of the script just shows the first record.
- Building a temporary relationship
This is one of my favorite patterns which can help you make life much easier. When you develop your script you will not always have the perfect relationship for navigation defined in the system. So you can either define a new relationship in database configuration or you can do this step directly from your Jython script. You define a new relationship just to be used temporarily in your script with no impact to the rest of the system.
The syntax based on the JavaDoc is:
public MboSetRemote getMboSet(java.lang.String name, java.lang.String objectName, java.lang.String relationship) throws MXException, java.rmi.RemoteException
A real example statement to get all worklog entries for a specific workorder could look like this:
worklogset = wo.getMboSet("$TEMPREL1", "WORKLOG", "RECORDKEY='2009'")
$TEMPREL1 – An unique identifier for the relationship. It needs to be unique and for this reason it is good practice to start the name with a dollar sign.
WORKLOG – The database object which should be queried using any provided where clause.
RECORDKEY=’2009’ – The where clause to be applied to reset the MboSet of interest.