IBM Maximo offers many unique configuration features, but one of the most useful are relationships. Despite the usefulness of this option, I met consultants who had trouble defining and using it. In this post, I will explain the basics of relationships and in the next post provide you with some examples.
You can use the relationships in the field definitions and the tables to access the information from other sources than the current database table:
In this example, a new tab with the generated Work Orders list was added to the PM application. The content of the table is from the Work Order Tracking application, except for the Asset Description column, which has its source in the Assets application.
Technically, the relationships are defined in the Database Configuration application, Relationships tab. They are simply SQL Where Clauses that connect the current database table with another one.
NOTE: you can only link 2 tables: parent and child but in the Where Clause many other tables can be referenced.
You can define a relationship either to be used in the field or the table. The first one must return 0 or exactly 1 record. The relationship for tables can return 0 or many records.
NOTE: if you use table relationship in the field, you will get an error, because Maximo cannot display multiple values in a field, please make sure you are using the right relationship.
Below is the example of relationships, that were defined for the WORKORDER table:
The relationship ASSET links WORKORDER and ASSET tables. The Where Clause contains the following condition:
assetnum = :assetnum and siteid = :siteid
Technically it is equivalent to:
asset.assetnum = workorder.assetnum and asset.siteid = workorder.siteid
because Maximo understands the column names without colon as columns in the target table, in this case: ASSET.
The columns preceded with a colon are variables, they will be replaced with current values from the source table, here: WORKORDER. Assuming the asset number is 1056 and the site is DEMO, the condition sent to the database will look like this:
assetnum = ’1056’ and siteid = ’DEMO’
NOTE: you can use variables in your conditions, but it is not mandatory.
As SQL command, the relationship allows you to display the information without copying it to the current table. This can save some space in the database, but the other side of the coin is that you will always reference the same data. Let’s take an example of two Work Orders: one created a year ago and one from yesterday. The standard field presents the asset description of the WO. The Asset Manager has changed the asset description some weeks ago, but when you open both Work Orders, you will see only the current description, because the SQL points to the same record. That means you should use relationships only to present the information which does not need to be kept in history. If you want to store the actual asset description with the Work Order, then you will need to add a new attribute to the WORKORDER table and copy it from the Asset record whenever WO is created.
Once the field relationship is ready, you can access any column from the target table using the so-called “dot notation”:
e.g. to access the Asset Description in the Work Order, you will use:
where ASSET is the above relationship name and DESCRIPTION is a column in the ASSET table.
You can also use multiple relationships to display the content in the field. We have already a relationship: ASSET, which links WORKORDER and ASSET tables. In the ASSET table definition, there is another relationship called MANUFACTURER connecting ASSET and COMPANIES tables. As we have a “chain” of relationships, we can then add a new field to the Work Order with Asset Manufacturer’s name using the following attribute name:
Maximo will process the attribute name from left to right: it will find the relationship: ASSET, this will redirect us to the ASSET table, then, the next relationship will be found: MANUFACTURER, which will guide us to the COMPANIEStable, where we will find the column: NAME.
Think about the flexibility this feature can give you. With the help of relationships, you can access almost any information in the system!
So far, I have explained what the relationship is, two types of relationships, what are the variables, and how to use the relationship in the fields. In the next post, I will present some examples of relationships.
If you like my post, please share it with your network. You may also check my book “IBM Maximo Asset Management. The Consultant’s Guide. Second Edition”.
My blog posts are my own and may not represent my company’s position or opinion. I have done my best to make sure that the materials are correct and error-free, but I am only a human—please test before using them in production.
All product and company names mentioned in this publication may be trademarks or registered trademarks of their respective owners.#Featured-area-1#Featured-area-1-home