Maximo Open Forum

 View Only

  • 1.  Where Clause in Database Configuration

    Posted 02-26-2025 19:22

    I have created the following relationships in Maximo 

    Database Table: WORKORDER

    Relationship: WORKLOGCOMP

    Child: WORKLOG

    Where Clause: class=:woclass and worklogid=(select MAX(worklogid) from worklog where recordkey=:wonum)

    Database Table: WORKLOG

    Relationship: DESCRIPTION  to WORKLOGLDTEXT

    Child: LONGDESCRIPTION

    Where Clause: ldownertable='WORKLOG' and ldownercol='DESCRIPTION' and LDKEY=:WORKLOGID

    I am using a communication template to send out information from the WO and WO LOG table based on an escalation etc.

    However every time I get the mail the work order info from the query above class=:woclass and worklogid=(select MAX(worklogid) from worklog where recordkey=:wonum) always returns the oldest record instead of the most recent record. I have tried two variations of the query and both return the same info in the mail. If I run the queries in Microsoft SQL Server Management Studio I always get the correct and latest version

    class=:woclass and worklogid=(SELECT worklogid FROM worklog WHERE recordkey=:wonum and siteid=:siteid AND createdate = (SELECT MAX(createdate)FROM worklog WHERE recordkey=:wonum AND siteid =:siteid)

    class=:woclass and worklogid=(select top 1 worklogid from worklog where recordkey=:wonum and siteid=:siteid order by createdate desc)

    Any ideas as to why I am getting the oldest info instead of the most recent log entry??

    Appreciate any help you can give in relation to this.

    -Andrew


    #Analytics
    #EverythingMaximo
    #MaximoApplicationSuite

    ------------------------------
    andrew brown
    Intel
    ------------------------------


  • 2.  RE: Where Clause in Database Configuration

    Posted 02-27-2025 09:26
    Edited by Stephen Hume 02-27-2025 09:28

    Andrew here is a query I use in a relationship to get the latest worklog entry for an SR.

    RECORDKEY=:TICKETID AND CLASS=:CLASS AND WORKLOGID IN ( SELECT WORKLOGID FROM WORKLOG WHERE CREATEDATE = ( SELECT MAX(CREATEDATE) FROM WORKLOG WHERE RECORDKEY= :TICKETID AND CLASS=:CLASS ))

    for work order to get the OLDEST entry it can be changed to

    RECORDKEY=:WONUM AND CLASS=:WOCLASS AND WORKLOGID IN ( SELECT WORKLOGID FROM WORKLOG WHERE CREATEDATE = ( SELECT MIN(CREATEDATE) FROM WORKLOG WHERE RECORDKEY= :WONUM AND CLASS=:WOCLASS ))

    I have tested this in my demo Maximo and it is getting the oldest worklog entry.

    If you instead want the most recent worklog entry then change MIN to MAX in the whereclause above



    ------------------------------
    Stephen Hume
    Sheffield Scientific LLC
    ------------------------------

    Original Message


  • 3.  RE: Where Clause in Database Configuration

    Posted 02-27-2025 11:55

    Hi Stephen,

    Thank you for your reply, this is very weird. So just to provide some additional background on the triggers etc. we are using this for our building assistance center and the trigger on the notification is when a WO status for BAC is changed to COMP. Within the template it has the following configuration

    in the escalations component the condition is as follows

    changedate > '2022-05-13' and status in ('COMP') and  exists (select 1 from workorder where workorder.wonum = wostatus.wonum and workorder.siteid = wostatus.siteid and workorder.origrecordclass = 'SR')

    The escalation template then has a notifications configuration which pulls in the COMM Template.

    So when I change a WO to COMP it will trigger the above and I get the mail as designed. The mail I get has the following info configured in the COMM template 

    Completion Log : 
    Below are the details of the last worklog entry:
    Summary Description: :WORKORDER.WORKLOG.description
    Summary Details: :WORKORDER.WORKLOG.description_longdescription
    In the email I do get the values for description and long description from the log in the closed WO. So if I have the following log entries 
    I should get the last log entry dated today for example the AMBROWN item. But no matter how I switch it up I always get the last entry so for the example above I end up with the EFRAI10X the DSP item. 
    In the WORKORDER Databased configuration I have a relationship for WORKLOGCOMP and in the where clause I did have the following 

    class=:woclass and worklogid=(select MAX(worklogid) from worklog where recordkey=:wonum)  and then I changed it to 

    RECORDKEY=:WONUM AND CLASS=:WOCLASS AND WORKLOGID IN ( SELECT WORKLOGID FROM WORKLOG WHERE CREATEDATE = ( SELECT MAX(CREATEDATE) FROM WORKLOG WHERE RECORDKEY= :WONUM AND CLASS=:WOCLASS )) 

    I have tried both queries in the Maximo DB via Microsoft SQL Server Management and each item I get the correct answer i.e. the AMBROWN entry but when I try to trigger it via closing a WO. I always get the oldest record and I cant figure out if there is some other configuration that is reverting the result.



    ------------------------------
    andrew brown
    Intel
    ------------------------------

    Original Message


  • 4.  RE: Where Clause in Database Configuration

    Posted 02-27-2025 09:56
    Hi Andrew,
     
    If I understand correctly, you are using a communication template and escalation for the WORKORDER object. I don't see the configuration of the communication template itself, but I would suggest applying the template to the relevant work order and checking what data is displayed (Select Action → Create → Communication). Then, I would modify the relationship and observe how it affects the message.
     
    At first glance, the relationship looks correct. However, I would avoid specifying a relationship for the LONGDESCRIPTION object and instead use the existing field in WORKLOG: WORKLOG.DESCRIPTION_LONGDESCRIPTION.
     
    Let me know what you find!


    ------------------------------
    Aleksandr Stegniyenko
    ------------------------------

    Original Message


  • 5.  RE: Where Clause in Database Configuration

    Posted 02-27-2025 12:07

    Hi Aleksandr,

    Not 100% sure if you are referring in the relationship component of the Database configuration when you mention this 

    However, I would avoid specifying a relationship for the LONGDESCRIPTION object and instead use the existing field in WORKLOG: WORKLOG.DESCRIPTION_LONGDESCRIPTION. if so then I don't think this is possible as WORKLOG: WORKLOG.DESCRIPTION_LONGDESCRIPTION is not a valid table. If you are referring to the COMM template then I can add that in but my real problem is in regard to what is being returned in the mail. Its always the wrong entry



    ------------------------------
    andrew brown
    Intel
    ------------------------------

    Original Message


  • 6.  RE: Where Clause in Database Configuration

    Posted 02-27-2025 12:34

    Hi Adnrew, 

    The relationship looks correct, which is also confirmed by executing the query in the database. I would possibly add siteid to the relationship since the uniqueness of a work order is determined by both wonum and siteid. Based on the comments above, you are using WOSTATUS as the primary object for escalation. However, according to the logic you described, wouldn't it be possible to use the WORKORDER table instead?
     
    Does your business process allow a work order to be moved to COMP status more than once? If not, then you can modify the communication template and escalation to use the WORKORDER object. After that, you will be able to apply this communication template in the Work Order application and test it through Select Action → Create → Communication.
     
    My comment regarding DESCRIPTION_LONGDESCRIPTION was related to COMMTEMPLATE. As you mentioned, you created a relationship to longdescription, but I don't see much sense in that-you can directly use the DESCRIPTION_LONGDESCRIPTION field instead. Thank you.


    ------------------------------
    Aleksandr Stegniyenko
    ------------------------------

    Original Message


Related Content