Maximo Open Forum

 View Only
  • 1.  Autoscript: Populate field with value from SQL query

    Posted 11-07-2021 16:35
    Edited by User 1972 11-21-2021 16:15
    MAM 7.6.1.2;  Oracle 19c:

    I want to use an automation script to update a custom CLASSSTRUCTURE.USEWITHCONCAT field.

    As an experiment, I want to use an SQL query to get the value that will be used to populate the field.

    select 
        listagg(objectname,', ') within group(order by objectname) as usewith
    from 
        maximo.classusewith
    where
        classstructureid = '68070'
    group by 
        classstructureid

    Source


    Question:
    Is there a way to use the value from the SQL query to populate a field (via an automation script)?
    (I want to avoid creating a view. I find views to be a pain to set up in Maximo.)

    #Administration


  • 2.  RE: Autoscript: Populate field with value from SQL query

    Posted 11-09-2021 22:36
    you should be able to use dbshortcut in a script.

    Also if you create a view in a database, once it exists you can create the same view in maximo by using the same name.

    ---------------------------------
    Jeff Kish
    interloc solutions
    ---------------------------------





  • 3.  RE: Autoscript: Populate field with value from SQL query

    Posted 11-21-2021 23:38
    Edited by User 1972 12-01-2021 23:01
    I got part of the way there:

    #Class SqlFormat: https://bportaluri.com/wp-content/MaximoJavaDocs76/psdi/mbo/SqlFormat.html
    #...bind values from an Mbo to a SQL statement. eg. Convert a statement like 'WHERE X=:assetnum to 'WHERE X='11430''. 
    #The user may also do manual replacements by placing numeric bind variables in the string and using the 
    #setXXXX(col, val) methods to substitute values. The advantage of this is that it takes care of DB specific calls.
    
    #This script works when using the Execute Script action in the Automation Scripts application
    from psdi.server import MXServer
    from psdi.mbo import DBShortcut,SqlFormat
    mxServer = MXServer.getMXServer()
    dbShortcut=DBShortcut()
    dbShortcut.connect(mxServer.getSystemUserInfo().getConnectionKey())
    
    sql = """
    select 
        listagg(objectname,', ') within group(order by objectname) as usewith
    from 
        classusewith
    where
        classstructureid = :1     --Bind var #1
    group by 
        classstructureid
    """
    sqfQuery=SqlFormat(sql)
    sqfQuery.setObject(1, "ALN", "68070")
    resultSet=dbShortcut.executeQuery(sqfQuery.format())
    
    resultSet.next()
    print resultSet.getString(1)
    
    resultSet.close()
    dbShortcut.close()​



    That theoretically works -- as a test script in the EXECUTE window.


    But then I realized that there might be a logistical issue.

    In the Classifications application, we can open a given CLASSSTRUCTURE record and edit the related USE WITH records. But editing the USE WITH records doesn't actually trigger an edit the CLASSSTRUCTURE record. So I think the script needs to be on the CLASSUSEWITH object, not on CLASSSTRUCTURE.

    However, I'm wondering if that wouldn't work. I would need the script to fire after an edit to CLASSSUSEWITH is committed, so that the edit is visible to the db (visible to my SQL query in the script above). I couldn't figure out how to set up the launch point on the script so that it fired after commit.  I'm not sure if the "After Commit" option in object launch points is known to be finicky. Or if it's just me. But I didn't get it working.


    ------------------------------
    User 1972
    ------------------------------



  • 4.  RE: Autoscript: Populate field with value from SQL query

    Posted 11-10-2021 00:36
    Hi User 1972,

    Yes, you should be able to, not sure of the code.  But I have learned something new and that is exciting.  That is the listagg function.  Simple and effective.

    Doing views, in general, is very simple, so I'd be curious why you think it's hard.  Like Jeff has said, create the view using SQL in the correct schema, then in DB config, simply name the new object the same.  Maximo will then import your work, config, and you're up and running.

    I'd recommend in the view to include the CLASSSTRUCTUREID because you can then create a relationship to it from the CLASSSTRUCTURE object and use it on the list screen as per your use case.

    ------------------------------
    Craig Kokay
    ISW
    Maximo Practice Manager
    eMail: ckokay@isw.net.au
    Phone: +61-411-682-040

    #IBMChampion2021
    ------------------------------



  • 5.  RE: Autoscript: Populate field with value from SQL query

    Posted 11-10-2021 08:48
    Edited by User 1972 11-10-2021 15:59
    Edit:
    I've moved this conversation about "trouble with views" and "attribute Search Type is read-only" to a separate post:
    IMPORTED DB VIEW: HOW TO SET CALCULATED/PERSISTENT TEXT ATTRIBUTE SEARCH TYPE TO WILDCARD?




    Thanks Craig.

    Here's the reason reason I find views to be a pain:

    When I import a view to Maximo using the steps you described, the Search Type of the attributes gets automatically set to NONE by default. And the Search Type is read-only, so I can't set it to what I want (like WILDCARD). It's my understanding that text fields need to have a Search Type of WILDCARD in order for me to filter on the field in the Advanced Search window and in the list view. If the Search Type is NONE, then I don't think I can filter on the field in Maximo.

    I also don't have CREATE VIEW privileges in the database. So creating imported views (directly in the DB) is a challenge.


    ------------------------------
    User 1972
    ------------------------------



  • 6.  RE: Autoscript: Populate field with value from SQL query

    Posted 11-22-2021 09:08
    The best way to create views in Maximo - my opinion - is using DBC scripts. These give you flexibility that you don't have in the Database Configuration application. I'll have to write an article on that one of these days.


    ------------------------------
    Alex Walter
    A3J Group, LLC
    ------------------------------



  • 7.  RE: Autoscript: Populate field with value from SQL query

    Posted 12-01-2021 23:04

    Hi Alex,

    In a reply above, I mentioned:

    "I couldn't figure out how to set up the launch point on the script so that it fired after commit.  I'm not sure if the "After Commit" option in object launch points is known to be finicky. Or if it's just me. But I didn't get it working."

    I have a vague memory from reading one of your posts that it's sometimes necessary to do an "explicit save" when setting a value -- depending on the launch point type.
    Is there any chance that rings a bell? I can't remember where I saw that.

    Thanks.



    ------------------------------
    User 1972
    ------------------------------



  • 8.  RE: Autoscript: Populate field with value from SQL query

    Posted 12-02-2021 10:07
    After Save/After Commit require explicit saves again because the data has already been inserted/updated in the database. I don't like After Save/After Commit for most use cases, especially if you're modifying the same record. You have to ensure you prevent a loop if you save the same record. The database has to work harder as you made an insert/update already and now are making a separate update to set one field. It's like writing:
    UPDATE workorder set description='Test' WHERE wonum='1000' and siteid='BEDFORD';
    UPDATE workorder set assetnum='1234' WHERE wonum='1000' and siteid='BEDFORD';
    instead of:
    UPDATE workorder set description='Test', assetnum='1234' WHERE wonum='1000' and siteid='BEDFORD';

    In the case of After Save, you have an open transaction running that has manipulated the database which can cause blocking in the database while that script is running. So if you have a slow operation (reaching out to an external system for example) I'd be hesitant to go that approach.

    ------------------------------
    Steven Shull
    IBM
    ------------------------------



  • 9.  RE: Autoscript: Populate field with value from SQL query

    Posted 12-20-2021 22:57
    Edited by User 1972 12-20-2021 23:00
    For what it's worth, I couldn't figure out how to do it -- use an automation script on USEWITH to fire an update on CLASSSTRUCTURE.USEWITHCONCAT. I wasn't using the "Populate field with value from SQL query" idea...I was just using normal automation script techniques (mbo, while loop, etc.).

    I got it working if I only added/deleted a single USEWITH in a given edit. But if I added/deleted multiple USEWITHs within an edit session, then I kept getting the "updated by another user" error...there seemed to be conflicts between USEWITH and CLASSSTRUCTURE in that scenario.

    (I had brainstormed with a couple of experienced automation script developers about the issue, and they couldn't figure it out either.)

    I spent way too much time on it, and ultimately had to give up and go back to the VIEW idea. I posted the view SQL here: Classification summary view, including aggregated USE WITH column.