Maximo Open Forum

 View Only

 Most recent workflow transaction

  • Administration
  • Analytics
  • Architecture
  • Assets
  • Civil Infrastructure
  • Customizations
  • End User
  • Everything Maximo
  • HSE/Oil and Gas
  • Infrastructure
  • Integrations
  • Inventory
  • IoT
  • Life Science/Calibration
  • Linear
  • Maximo Application Suite
  • Maximo For Aviation
  • Maximo User Groups
  • Mobility
  • Nuclear
  • Procurement
  • Reporting
  • Scheduling
  • Security
  • Service Provider
  • Spatial
  • Transportation
  • Utilities
  • Work Centers
  • Work Management
Eric Godel's profile image
Eric Godel posted 01-20-2023 10:22
HI all,
I am trying to get the WFTRANSACTION.TRANSID field and copy it to a field on my work order when the user routes the the workflow.  I have configured my workflow to run the "SETVALUE" action while routing to the next assignment.  My set value action uses the relationship WFTRANSACTION to get to the workflow transaction table.  My problem is, its always grabbing the oldest wftransaction on the table related to my workorder.  I would like it to grab the most recent workflow transaction, with the goal of grabbing the one in memory while its being created.  I'm not sure what relationship to use in that case, i assumed it may be the out of box WFTRANSACTION relationship, but that one is not working.

Does anyone have any tips to pull this off with set value actions?  Or will I need to create an automation script for it?

Thanks
Eric
Prashant Sharma's profile image
Prashant Sharma
Hi Eric,

OOB WFTRANSCTION where clause for example when WF is on WO will be - ownertable = 'WORKORDER' and ownerid = :workorderid

Now as you can see this clause will return more than 1 value when there is more than 1 WFTRANSACTION happening against a WO and by default Maximo mboset coming from the query will be sorted ascending and hence you are getting the oldest value. You need to change the query to get the latest transaction to achieve this using Relationship.


Eric Godel's profile image
Eric Godel
Hi Prashant,

When ordering the result set like this, I added "order by transdate desc" at the end of the where clause.

Doing this is still giving me only the last workflow transactions that had been put in the database, it is not returning the transactions that are currently being created.  Im assuming I will just need to have an automation script pull this off.
Prashant Sharma's profile image
Prashant Sharma
"order by transdate desc" will just reverse the order of values in the set but it is not going to filter and return only 1 latest value. Use max(), row_number(), etc. functions depending upon the database that you are using with Maximo to write a query that gives you the latest record.
Eg- If I need to find the latest status from Work Order History.

wonum = :wonum and siteid = :siteid 
and wostatusid = (
    select max(wostatusid)
    from wostatus
    where wonum = :wonum and siteid = :siteid
    )


Even if you write an automation script, you need to put a logic of where clause in it and it eventually fires a SQL clause on the Database which you can do using relationship.

Danielle Shaw's profile image
Danielle Shaw
Hi Eric,

Make a new relationship and try this where clause (child table=WFTRANSACTION). It will get the WF transaction with the most recent date for the WO.

ownertable = 'WORKORDER' and ownerid = :workorderid and transdate=(select max(transdate) from wftransaction z where z.ownertable='WORKORDER' and z.ownerid=:workorderid)

This doesn't take into account the WF process or anything, so you might need to specify that if you have multiple processes. I think there is a *chance* that multiple transactions will have the same transdate so if you can narrow it down further, with columns like transtype or nodetype, it might be good.

But you can use this relationship in your setvalue action.
Eric Godel's profile image
Eric Godel
Thank you for your answers Prashant and Danielle.  

I've tried what you have both suggested.  It still returns me the latest record thats committed to the database.  Not the Transid from the workflow transactions that are at that time being created.

I have moved my logic to an escalation that will query for it and fill in the transid on the table, this seems to fix my timing issue and i am not hitting "updated by another user" error by running the automation script.

Thanks!
Eric