Maximo Open Forum

Christopher Winston posted 07-28-2022 19:30

Work Order Tracking Advanced Search has the Related WO field




That generates SQL: 

((woclass = 'WORKORDER' or woclass = 'ACTIVITY') and historyflag = 0 and siteid = 'BEDFORD' and istask = 0) and (exists (select 1 from dbo.relatedrecord where ((emxrelatedwonum like '%12345678%')) and (siteid = workorder.siteid and recordkey=workorder.wonum and class =workorder.woclass and relatedrecclass in (select value from synonymdomain where domainid ='WOCLASS' and maxvalue in ('CHANGE', 'RELEASE','ACTIVITY', 'WORKORDER')))))

Person Group does not come on the advanced search out of the box, so you will need to add it, and then your users can just use the screen and not have to deal wtih the SQL

Elliott McRobert posted 07-28-2022 19:41

HI, thanks for this. My advance search doesnt have that! WE dont have access to application designer to change it as its a client system. I should be able to work with the query you sent below. thanks so much

Elliott McRobert posted 07-28-2022 20:09

also cant use EXIST clause so didnt work

Christopher Winston posted 07-28-2022 20:37

the emxrelatedwonum attribute is more than likely the issue; it is added to our demo in the related records object in db config, which I am guessing you don't have access to, so let's try something else. 

for an originating record and those related to it:

((woclass = 'WORKORDER' or woclass = 'ACTIVITY') and historyflag = 0 and origrecordid like '%12345678%' and siteid = 'BEDFORD' and istask = 0)

this gets rid of the 'exists'.

Just curious - what database? 

Elliott McRobert posted 07-28-2022 23:48

that didnt work. I have the original record field in my advance search and tried it previously. A ticket has been raised to fix the exist issue so maybe I just have to wait. thanks for your help

Nikolaus Despain posted 08-01-2022 12:14

Why don't you try an embedded query...  If I understand what you are trying to do (find related work orders from the work order screen, where "647670" is the original record [sometimes shown on the screen])...

Enter this Query in the Where Clause (this embedded query will check wonum's against relatedreckey value in relatedrecord table) -


wonum in (select relatedreckey from relatedrecord where workorder.origrecordid = '647670') and siteid = 'cml' and persongroup like 'CTR%'