MAM 7.6.1.2, Oracle 19c:

I have a custom field/textbox in the Classifications application that is based on a Maximo relationship:


That relationship can return multiple records:


#AssetandFacilitiesManagement
#Maximo

Original Message:
Sent: Wed March 03, 2021 12:32 AM
From: User1971
Subject: Field based on relationship: What if relationship is 1:M?

MAM 7.6.1.2:

I have a custom field in Classifications that is based on a Maximo relationship:


That relationship can return multiple records:

select    *from    maximo.classancestorwhere    classstructureid = '32770'

Question:

How does Maximo handle the 1:M scenario? (aka: one classification record is related to many classancestor records)
 - It seems like Maximo only retrieves the record with the maximum HIERARCHYLEVELS value. But I don't understand why Maximo is behaving that way / if it will always behave that way. 
 - Is it just grabbing the last record that was inserted to the database table?

Thanks.

#AssetandFacilitiesManagement
#Maximo

I believe this will be dependent on the database optimizer itself and how it handles the query when no order by is specified.

I ran into a similar issue a couple of years back, where an automation script suddenly behaved differently in two environments. We were using a relationship with 1-to-many cardinality (specifically it was 1:2).
It used to consistently return the same row in both our test and production environments, before all of a sudden the production environment started returning the "other" row. Did not investigate to pinpoint the exact reason as of why - just enough to realize what was happening and how to fix it. My memory of the specifics is a bit blurry, but I think we got the "other" row because the database server was upgraded and the optimizer decided to do things differently..

In any case, the below link explains this in much greater detail than I am able to: https://stackoverflow.com/questions/26236352/default-row-order-in-select-query-sql-server-2008-vs-sql-2012

And you should absolutely look into creating a new relationship that specifies which exact classancestor record you want; for example by adding a min/max function to the WHERE-clause. I've done that in the past to get a specific description for a status change, e.g. finding the second-to-last status change, or being 100% sure that I am getting the newest work log record for an SR to add to a communication template.

------------------------------
Henrik Christiansen
------------------------------

Original Message:
Sent: Wed March 03, 2021 12:32 AM
From: User1971
Subject: Field based on relationship: What if relationship is 1:M?

MAM 7.6.1.2:

I have a custom field in Classifications that is based on a Maximo relationship:


That relationship can return multiple records:

select    *from    maximo.classancestorwhere    classstructureid = '32770'

Question:

How does Maximo handle the 1:M scenario? (aka: one classification record is related to many classancestor records)
 - It seems like Maximo only retrieves the record with the maximum HIERARCHYLEVELS value. But I don't understand why Maximo is behaving that way / if it will always behave that way. 
 - Is it just grabbing the last record that was inserted to the database table?

Thanks.

#Maximo
#AssetandFacilitiesManagement

It's the same as when you use a 1:M relationship to display a value in a Comm Template: the single value that gets shown in the resulting email is the one that happened to be first back from the database. There may be database reasons why the list of records come back in an order that is consistent across queries. Reasons might be clustered indexes or the order of records on disk or which disk is faster or etc. However, there is no guarantee from anyone about what will come back first. So, as others have indicated, it is good practice to only use relationships that find 0 or 1 value for displaying values in fields or comm templates or for comparisons in expressions.

------------------------------
Blessings,
Jason Uppenborn
Sr. Technical Maximo Consultant
Ontracks Consulting
------------------------------

Original Message:
Sent: Wed March 03, 2021 12:32 AM
From: User1971
Subject: Field based on relationship: What if relationship is 1:M?

MAM 7.6.1.2, Oracle 19c:

I have a custom field in Classifications that is based on a Maximo relationship:


That relationship can return multiple records:

select    *from    maximo.classancestorwhere    classstructureid = '32770'

Question:

How does Maximo handle the 1:M scenario? (aka: one classification record is related to many classancestor records)
 - It seems like Maximo only retrieves the record with the maximum HIERARCHYLEVELS value. But I don't understand why Maximo is behaving that way / if it will always behave that way. 
 - Is it just grabbing the last record that was inserted to the database table?

Thanks.

#AssetandFacilitiesManagement
#Maximo

To get first / last record I use queries like 

{OBJECTID} = (select max({OBJECTID}) from {OBJECT} where ... [original query])
where {OBJECTID} - unique id of object to be queried

------------------------------
Andrey Ilinskiy
------------------------------

Original Message:
Sent: Thu March 04, 2021 10:13 AM
From: Jason Uppenborn
Subject: Field based on relationship: What if relationship is 1:M?

It's the same as when you use a 1:M relationship to display a value in a Comm Template: the single value that gets shown in the resulting email is the one that happened to be first back from the database. There may be database reasons why the list of records come back in an order that is consistent across queries. Reasons might be clustered indexes or the order of records on disk or which disk is faster or etc. However, there is no guarantee from anyone about what will come back first. So, as others have indicated, it is good practice to only use relationships that find 0 or 1 value for displaying values in fields or comm templates or for comparisons in expressions.

------------------------------
Blessings,
Jason Uppenborn
Sr. Technical Maximo Consultant
Ontracks Consulting

Original Message:
Sent: Wed March 03, 2021 12:32 AM
From: User1971
Subject: Field based on relationship: What if relationship is 1:M?

MAM 7.6.1.2, Oracle 19c:

I have a custom field in Classifications that is based on a Maximo relationship:


That relationship can return multiple records:

select    *from    maximo.classancestorwhere    classstructureid = '32770'

Question:

How does Maximo handle the 1:M scenario? (aka: one classification record is related to many classancestor records)
 - It seems like Maximo only retrieves the record with the maximum HIERARCHYLEVELS value. But I don't understand why Maximo is behaving that way / if it will always behave that way. 
 - Is it just grabbing the last record that was inserted to the database table?

Thanks.

#AssetandFacilitiesManagement
#Maximo

Here's a similar method that seems to work too:

classstructureid=:classstructureid
order by hierarchylevels desc
fetch first row only

Source: Maximum value on field in Maximo


The relationship:

#Maximo
#AssetandFacilitiesManagement

I'm new to Maximo variables.

I need to remember that the right-hand side of classstructureid=:classstructureid pertains to the field in the parent table (current record). I tend to think of the parent table (current record) as being on the left-hand side of a "join", not the right-hand side.

So to me, classstructureid=:classstructureid is in the wrong order. But that's not how conditions are usually written in Maximo. It'll just take some getting used to. I also might be thinking about that wrong.

Source: Conditional expression syntax

 

#AssetandFacilitiesManagement
#Maximo

it's not the left or the right, it's with or without the colon. With the colon is the substitution variable from the parent.

------------------------------
Blessings,
Jason Uppenborn
Sr. Technical Maximo Consultant
Ontracks Consulting
------------------------------

Original Message:
Sent: Fri June 18, 2021 11:46 PM
From: User1971
Subject: Field based on relationship: What if relationship is 1:M?

I'm new to Maximo variables in queries.
I need to remember that the right-hand side of classstructureid=:classstructureid pertains to the field in the parent table (current record). I tend to think of the parent table (current record) as being on the left-hand side of a "join", not the right-hand side.

So to me, classstructureid=:classstructureid is in the wrong order. But that's not how conditions are usually written in Maximo. It'll just take some getting used to. I also might be thinking about that wrong.

Source: Conditional expression syntax

 

#Maximo
#AssetandFacilitiesManagement