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.
As mentioned some where here on other article,
this code can be used instead of :-
# remove this
# woset = session.getMboSet(‘WORKORDER’)
#—– put this ———–
from psdi.server import MXServer
from psdi.iface.mic import MicService
# ——— Initialize some stuff
mxServer = MXServer.getMXServer()
micService = MicService(mxServer)
micService.init()
userInfo = micService.getNewUserInfo()
woset = mxServer.getMboSet(‘WORKORDER’, userInfo)
.
.
.
Good comment, thanks! The way of initialisation of the base MboSet always depends on the context where your script runs in. In the context of a launch point you basically have a Mbo to build all other stuff on. If you run it independently you may initialise your first MboSet the way you showed.
Hi Matthias,
Nice info. Thank You.
I have a question for which looking for info :
I have a non-persistent field(Check Box) and a button. Action(Script code) runs on Button Click. During user activity on UI, User check this non-persistent flag. I want to read this value(Checked = 1) on Button click(in Script).
Any good way to read this?
I tried thru MBOSet iteration also using relations where checkbox = 1 .. but no luck. Actual changed value for this non-persistent flag is not accessible. I am getting only its default value which is check box = not checked.