Maximo

Maximo

Come for answers, stay for best practices. All we're missing is you.

 View Only
Back to discussions
Expand all | Collapse all

Batch-delete records via Excel application import/export

  • 1.  Batch-delete records via Excel application import/export

    Posted Mon August 02, 2021 08:26 PM
    Edited by System Admin Wed March 22, 2023 11:46 AM
    MAM 7.6.1.2:

    If I recall, Maximo admins can use MxLoader to batch-delete records.

    Similarly, is it possible for users to delete records via Excel application import/export?

    Example:
    There isn't a OOB way to batch-delete route stops from a route (other than deleting the actual route, which we don't always want to do). As an alternative, it would be helpful if power users (who are already using import/export to create/update route stops) could batch-delete route stops using the same tool.

    Thanks.
    #AssetandFacilitiesManagement
    #Maximo


  • 2.  RE: Batch-delete records via Excel application import/export

    Posted Mon August 02, 2021 08:32 PM
    This should be as simple as updating the Option/Action during Import:



    ------------------------------
    Tim Ferrill
    Solutions Consultant
    Intelligent Technology Solutions
    tferrill@webuildits.com
    www.webuildits.com
    @tferrill/@webuildits
    ------------------------------

    Original Message


  • 3.  RE: Batch-delete records via Excel application import/export

    Posted Tue August 03, 2021 09:07 AM
    Thanks. I wasn't aware of that option until you mentioned it.


    I did some testing, and it seems like the DELETE option will delete the entire route, not just the route stops. Any idea if the DELETE option can be used to only delete the child records (route stops) and not the parent record (route)?

    Here's what my object structure looks like. Let me know if other details would help.


    Thanks.
    Original Message


  • 4.  RE: Batch-delete records via Excel application import/export

    Posted Tue August 03, 2021 09:50 AM
    I'm going from memory as I can't find any documentation on this right at the moment, but I think you can add 'action="Delete"' to the ROUTE_STOP nodes if you're using XML.

    So this:
    <ROUTE_STOP>

    Becomes this:
    <ROUTE_STOP Action="Delete">

    I don't know of a way to do that with a flat file.



    ------------------------------
    Tim Ferrill
    Solutions Consultant
    Intelligent Technology Solutions
    tferrill@webuildits.com
    www.webuildits.com
    @tferrill/@webuildits
    ------------------------------

    Original Message


  • 5.  RE: Batch-delete records via Excel application import/export

    Posted Fri September 03, 2021 08:34 AM
    @Colin Aitchison pointed out that this can be done with MxLoader or Excel application import. The key is to create an object structure that is only for the route stops (don't include routes) -- so that only the route stops are deleted, not the parent route.

    Original Message


  • 6.  RE: Batch-delete records via Excel application import/export

    Posted Tue September 07, 2021 08:53 AM
    For route stops in particular, if you're able to define the criteria in some reliable way, I prefer enhancing routes to make them "dynamic" and having them maintain their own route stops. What I've done in the past was added two attributes for a SQL where clause (one for ASSETS one for LOCATIONS) that would then evaluate the list against that where clause and remove any route stops that no longer match and add any route stops that match that are not in the list. For example, if you want all the fire extinguishers in a particular location (or location hierarchy), you should be able to write a SQL statement to get those. That list might not change too often but if you have to replace a fire extinguisher for example, it's easy to forget to add it into the route.

    Creating a separate object structure will work most of the time but there are times where Maximo requires a certain parent/child relationship or will throw an error. This is because there are times where they get the "owner" record to update it and if there is no owner or the owner isn't what they're expecting, you may get an error. 

    Otherwise you need to go the XML route that Tim mentioned or make a webservice call (REST or SOAP) to delete child objects without deleting the header.

    ------------------------------
    Steven Shull
    ------------------------------

    Original Message


  • 7.  RE: Batch-delete records via Excel application import/export

    Posted Thu September 09, 2021 12:22 AM
    Edited by System Admin Wed March 22, 2023 11:50 AM
    Thanks Steven. That's a good approach.

    FYI - I borrowed your wording and posted it as an IBM Idea (RFE) here: Dynamic routes (route stops based on SQL where clause) .
    Feel free to let me know if there's anything I should change.
    #Maximo
    #AssetandFacilitiesManagement


  • 8.  RE: Batch-delete records via Excel application import/export

    Posted Mon September 13, 2021 05:53 PM

    Ha I'm working on exactly this at the moment and I too decided to call it Dynamic Routes.

    In my case I'm extending the application/xml design to have a tick box and a build route button.

    I specify the location and asset classification to include and when I press build route it clears the routestops and rebuilds them with all assets that fit the criteria I've provided.  It sorts by building name, then by level descending and then by room ascending, and spaces them 5 apart in the sequence numbers.

    At some stage in the future I'd love for the route to be empty even, and this process be run at the PM WO generation process stage, so it's always pulling the most current list of applicable assets.



    ------------------------------
    Richard Jackson
    Asset Information and Systems Manager
    Massey University
    Palmerston North
    New Zealand
    ------------------------------

    Original Message


  • 9.  RE: Batch-delete records via Excel application import/export

    Posted Mon September 13, 2021 08:55 PM
    A related RFE here:
    Dynamic routes (route stops based on SQL where clause)

    And an old Community post here:
    Dynamic Routes?
    Original Message


  • 10.  RE: Batch-delete records via Excel application import/export

    Posted Mon September 13, 2021 09:11 PM

    Yeah in the first instance we're looking to deploy as a one off manual button.

    Users will still be able to add other Assets, or remove any they don't want included after the list is populated etc

    Will see how this is received by end users and potentially develop from there.



    ------------------------------
    Richard Jackson
    Asset Information and Systems Manager
    Massey University
    Palmerston North
    New Zealand
    ------------------------------

    Original Message


  • 11.  RE: Batch-delete records via Excel application import/export

    Posted Mon September 13, 2021 09:19 PM
    Someone sent me these automation scripts recently. I haven't had a chance to test them, but I thought I'd share them anyway:


    Script 1:
    Replaces route stops with new route stops -- via a saved asset query
    from psdi.mbo import Mbo
    from psdi.mbo import MboSet
    from psdi.security import UserInfo
    from psdi.server import MXServer

    mxserver = MXServer.getMXServer()
    ui = mxserver.getSystemUserInfo()
    route = mbo.getString("ROUTE")

    #Script will execute when the user selects a saved query from a field in the route and hits save
    if (route is not None and route!=""):
    #Change: DESCRIPTION field to the field with the saved query name
    savedQuery = mbo.getString("DESCRIPTION")
    if (savedQuery is not None and savedQuery!=""):
    querySet=mxserver.getMboSet("QUERY",ui)
    #Change the app name as required
    querySet.setWhere("clausename='"+savedQuery+"' and app='ASSET'")
    querySet.reset()
    if (querySet is not None and querySet.getMbo(0) is not None):
    queryMbo = querySet.getMbo(0)
    whereClause=queryMbo.getString("CLAUSE")
    assetSet=mbo.getMboSet("ALLASSETS")
    assetSet.setWhere(whereClause)
    assetSet.reset()
    assetMbo=assetSet.moveFirst()
    newRSSet=mbo.getMboSet("ROUTE_STOP")
    if (newRSSet is not None):
    newRSSet.deleteAll()
    while(assetMbo):
    assetnum=assetMbo.getString("ASSETNUM")
    newmbo=newRSSet.add()
    #Add the fields for Route_Stop as per the requirement
    newmbo.setValue("ASSETNUM",assetnum,11)
    newmbo.setValue("ASSETLOCSITEID",assetMbo.getString("SITEID"),11)
    newmbo.setValue("ASSETLOCORGID",assetMbo.getString("ORGID"),11)
    newmbo.setValue("ASSETDESCRIPTION",assetMbo.getString("DESCRIPTION"),11)
    newmbo.setValue("ASSETDESCRIPTION_LONGDESCRIPTION",assetMbo.getString("DESCRIPTION_LONGDESCRIPTION"),11)
    #newmbo.getThisMboSet().save()
    assetMbo=assetSet.moveNext()


    Script 2: 
    Delete all the stops in route (not related to the script above)
    from psdi.mbo import MboRemote
    from psdi.mbo import Mbo
    from psdi.mbo import MboSetRemote
    from psdi.mbo import MboSet

    routeStopSet = mbo.getMboSet("ROUTE_STOP")
    if (not routeStopSet.isEmpty()) :
    routeStopSet.deleteAll(2L)
    mbo.getThisMboSet().save()

    ​
    Original Message


  • 12.  RE: Batch-delete records via Excel application import/export

    Posted Mon September 13, 2021 09:25 PM
    Thanks, much appreciated.

    Will help with the ground work for sure.

    Cheers from New Zealand.

    ------------------------------
    Richard Jackson
    Asset Information and Systems Manager
    Massey University
    Palmerston North
    New Zealand
    ------------------------------

    Original Message