Ensure Asset’s serial number uniqueness

Sometimes you need to ensure the uniqueness of a certain database column in the Maximo database. A common example of this scenario is to ensure that asset’s serial numbers are not duplicated. This can prevent the creation of duplicate assets.

In this post I describe two techniques to achieve this using a unique database index or an automation script.

Unique Index

The simplest and most reliable way of achieving this is to define a unique database index on the SERIALNUM field of the ASSET table. However, we must make some considerations first. The asset serial is not unique alone for two reasons:

  1. Typically you enter the manufacturer’s S/N in the Asset serial number field. In theory two different assets with different part numbers can have the same serial number. This means that we typically have to include the ASSET.ITEMNUM field in the unique index.
  2. Maximo creates duplicate rows when moving an asset form one site to another. This means that we have to include the ASSET.SITEID field in the unique index.

Before proceeding it is important to perform a final check. We must ensure there are no existing rows in the ASSET table having index duplicates. This is to prevent errors during the database configuration fhase when Maximo will try to create the database index. This can be performed with an SQL query.

select SERIALNUM, ITEMNUM, SITEID
from ASSET
group by SERIALNUM, ITEMNUM, SITEID
having count(*)>1;

You will probably find some duplicated rows for assets having null values in the SERIALNUM or ITEMNUM. If you are able to solve all the issues (maybe inserting dummy values in duplicated rows) you can proceed creating the index in Database Configuration application.

Warning! Do not create the unique index if the above query returns data. The database configuration process will fail and you will have problems rolling back.

Automation Script

If you are not able to fix those problems you can use a dfferent approach based on a custom automation script to perform the same check. This allows to display a better error message when a user tries to saving an asset with a duplicated SERIALNUM, ITEMNUM, SITEID triplet.

# Script: ASSETSNVALIDATE
# Launch Point: Save - Add/Update - Before Save
# Object: ASSET
# Check for duplicated serial numbers

assetset = mbo.getMboSet("SAMEASSET")
if not assetset.isEmpty():
  params=[mbo.getString("ASSETNUM"), mbo.getString("SERIALNUM")]
  service.error("mxd", "dupassetsn") 

The launch point of the script must be on the ASSET – Before Save event.

A system message mxd.dupassetsn must be defined to correctly display the error to the user.

Asset {0} has the same serial number {1}

One last thing is to create the ASSET.SAMEASSET relationship:

  • Object: ASSET
  • Child Object: ASSET
  • Relationship: SAMEASSET
  • Where: assetnum!=:assetnum and itemnum=:itemnum and serialnum=:serialnum and siteid=:siteid
Ensure Asset’s serial number uniqueness

4 thoughts on “Ensure Asset’s serial number uniqueness

  1. Thanks for posting the Automation Script method. I have been working on this problem since mid last year. Thank you. Works great

  2. Thanks Bruno for the automation script example… meets our clients requirements perfectly

  3. This is very helpful and thanks for posting it.
    What would be the correct way in an autoscript, instead of showing a dialog box, to set one or more values on both the row you’re trying to save/starting from and also on any duplicates found. I tried one method of doing this with an autoscript with an onsave / onupdate

    record a already exists
    you try to add record b with duplicate attribution, but you want to save it with Error not prevent it being saved.
    you want record a also to be marked as a duplicate.

  4. Finishing sentence in above, I tried it with onsave launchpoint but it seemed to go into a loop, a finding b, saving b, then b finding a etc.

Leave a Reply

Your email address will not be published. Required fields are marked *

Scroll to top