July 5, 2013

How to import Failure Codes from Excel into Maximo

This article is outdated! Checkout MxLoader tool.

This entry is part of the Maximo Integration Framework series.

Creating and maintaining the hierarchy of failure codes can be quite complex using the standard Failure Codes application. Unfortunately there is no common solution to import Maximo failure codes. This evening I have worked on creating an Excel spreadsheet that can help in such cases.

This is an experimental tool. Use with care on development environments.
Leave a comment on this page both if you have problem (I'll try to fix them) and if you are able to use it successfully in your environment.


Data Model

Before going on, we need to understand how Maximo stores failure codes and their hierarchy of problems, causes and remedies.
The FAILURELIST table stores the hierarchy of problems, causes and remedies. The PARENT field holds the parent-child relationship while the FAILURECODE field points to another table called FAILURECODE that stores the descriptions of the failure codes in the various languages.
It is important to understand that a specific failure code can be attached to several places of the failure list hierarchy. This makes quite complex to manage this structure.


Export Failure List

A simple way of exporting failure codes and list is through SQL queries.

 
select orgid, failurecode, description, langcode from failurecode;


select f.orgid, f.failurelist classid, f.failurecode class, p.failurelist problemid, p.failurecode problem, c.failurelist causeid, c.failurecode cause, r.failurelist remedyid, r.failurecode remedy
from failurelist f
left outer join failurelist p on p.parent=f.failurelist and p.type='PROBLEM'
left outer join failurelist c on c.parent=p.failurelist and c.type='CAUSE'
left outer join failurelist r on r.parent=c.failurelist and r.type='REMEDY'
where f.parent is null
order by f.failurecode, p.failurecode, c.failurecode, r.failurecode;


Import

Create the following Object Structures.
Object Structure: FLL-FAILURECODE
  • Consumed By: Integration
  • Object: FAILURECODE
Object Structure: FLL-FAILURELIST
  • Consumed By: Integration
  • Object: FAILURELIST

Now download the FailureCodesLoader Excel spreadsheet and open it. Go on the Config sheet and set the correct hostname of your Maximo server. Clicking of the link the browser will open and should display the following message: Servlet is running. Please use HTTP POST to post data.
Now you are ready.

The Failure Codes and Failure List sheets allow to upload data into the two tables described before.
The Failure Codes sheet is straightforward to use. Just fill the worksheet with your failure codes descriptions and click on the little arrow on the toolbar.


This will execute a VBA macro that will call the FLL-FAILURECODE Object Service through an HTTP POST request.

The Failure List sheets represents the the failure codes hierarchy in a flat table with the same structure as exported by the above SQL query.
The Excel macro will handle the necessary logic to manage the failure list hierarchy. Once the objects are created and the hierarchy is populated, the macro will fill the 'ID' columns with the correct values. Once the ID values (greyed cells) are filled, the failure node is created in the hierarchy and shouldn't be changed.

At first it may seem a little tricky at the beginning, but after having played a little bit with the tool, it will be easy to create a complete failure list hierarchy of 1000 codes and more.

16 comments:

  1. Hello Bruno,
    I was trying to use this tool and it gave me an error with the Excel file. Can not compile module1.
    Can you pls check it?

    ReplyDelete
    Replies
    1. Try to download the new file (v0.3).
      It should work now.

      Delete
  2. Nice work I will test it and give you feedback

    ReplyDelete
  3. Hi;

    Please guide , me how to upload without using webservices.

    ReplyDelete
  4. I tried to use the tool and see it updated some of the heirachy data for some of the data i tested.
    It also skipped over some hierachy data with an error of not a valid org which means it not finding the failurecode and trying to add it
    not sure why this behavior.

    ReplyDelete
  5. is there any prerequisite before uploading Failure Code using this excel macro?

    ReplyDelete
  6. Hello Bruno,

    Thank you for the tool, is great!

    However, I'm using it, and it seems that if you have the same failure codes for 2 different orgid, it works perfectly for the failurecode table, but it does not work for the failurelist table. It seems that the orgid column in sheet Failure List is not being used.

    Thank you!

    ReplyDelete
  7. Thanks Bruno, really helpfull

    ReplyDelete
  8. Bruno, my requirement is to use the insert script and insert the records in to failure codes application , could you provide me the insert scrits

    ReplyDelete
  9. it always show
    “null is not a valid organization…….” but orgid have value.

    ReplyDelete
  10. Hi Bruno, I am getting "Error:500: nested exception is: java.lang.NullPointerException" error when I try to load the FailureCodes using this spreadsheet. Can you please tell me if I missing any thing in the spreadsheet?.

    ReplyDelete
  11. Hi All,

    I'm getting an error while executing the tool.

    Error 500:BMXAA5655E - Enterprise service cannot be resloved.

    Im getting this msg "Servlet is running. Please use HTTP POST to post data" but still Im having a doubt is Im pointing correct websevice..

    May I know where I can find the correct Server OS web service address

    ReplyDelete
  12. Hello bruno,
    I need to know haw maximo do to automatically populate the failure class field when creating a new work order from the IHM, in fact when I choose an ASSETNUM for my work order the FAILURECODE is automatically populated.
    I have to reproduce the same behavior for another field.

    ReplyDelete
  13. Have you considered setting field defaults with crossover domains or automation scripts?

    ReplyDelete
  14. Hi all.
    Checkout this: http://maximodev.blogspot.it/2017/03/load-failure-codes-in-maximo.html

    ReplyDelete