Link Parent-Child records in Maximo

In a previous post I have described how to correctly manage a child table in an application using the Application Designer. However, there are better ways of achieving the same goals so I will analyze all the available options to correctly link records in a parent-child relationship.

If you are a creating custom objects and applications in TPAE you may need at some point to create a child table like the Subassemblies in the Assets application.

Let's pretend we have a parent table called TB1 and a child table called TB2.

TB1: Parent table

TB1ID: Identifier of records in TB1
...

TB2: Child table

TB2ID: Identifier of records in TB2
TB1ID: Reference to the parent record in TB1 table
...

The fundamental concept is to have a field in the child table that points to a specific row in the parent table. In our example such field is TB2.TB1ID.

In order to have the child table to behave correctly is to 'link' in some way the child rows to the parent object. If not doing this the child records will 'disappear' as soon as you save the record.

The known techniques for linking parent-child records are described in the following table.

Technique	Pros	Cons
Using Application Designer	Very simple	Not working with MIF
Setting defaults in APPFIELDDEFAULTS table	Quite simple	Do not manage deletes
Using Java Mbo	Works perfectly	Requires Java customization
Using scripting	Quite simple	None (BTW the example does not manage deletes)
Using Database Configuration	Built in feature	Do not manage deletes Cannot be used once the tables are created

Parent-Child database relationship

Not all the techniques described hereafter requires this configuration but I think it is important to define a relationship from the TB1 table to the TB2 records. In Database Configuration define the following relationship in object TB1:

Relationship: TB2
Child Object: TB2
Where Clause: TB1ID=:TB1ID

Using Application Designer

In the Application Designer you have to use the TB2 relationship defined before to link the child table.
The last important step is to initialize the TB2.TB1ID field on child records. To achieve this, add a Default Value control with the following configuration:

Attribute: TB1ID
From Data Source ID: results_showlist
From Attribute: TB1ID

This will set the 'link' between parent and child tables.

Using APPFIELDDEFAULTS

Another possibility is to default the key values of your child table using the APPFIELDDEFAULTS table.
The following INSERT statement will put a default value in TB2.TB1ID field to link the child records to the parent one.

INSERT INTO APPFIELDDEFAULTS
(APP, DEFAULTVALUE, OBJECTNAME, ATTRIBUTENAME, APPFIELDDEFAULTSID)
('[APPNAME]', ':OWNEROBJECT.TB1ID', 'TB2', 'TB1ID', APPFIELDDEFAULTSSEQ.NEXTVAL );

Using Java

An alternative method is to initialize this link in the child Mbo using Java. Here is how the child Mbo class should look like.

public class Tb2Mbo extends Mbo implements MboRemote
{
  public Tb2Mbo(MboSet ms) throws MXException, RemoteException
  {
    super(ms);
  }

  public void add() throws MXException, RemoteException
  {
    super.add();

    MboRemote ownerMbo = getOwner();
    if(ownerMbo != null)
    {
      // retrieves the TB1ID value from the parent Mbo
      String tb1id = ownerMbo.getString("TB1ID");
      // sets the TB1ID value in the child Mbo
      setValue("TB1ID", tb1id, NOACCESSCHECK|NOVALIDATION_AND_NOACTION);
    }
  }
}

To correctly manage deletes of child records you should also override the delete method of the Tb1Mbo class.

public void delete(long accessModifier) throws MXException, RemoteException
{
  super.delete(accessModifier);

  (((Mbo)this).getMboSet("TB2")).deleteAll();
}

This method has only one limitation (as far as I know). If the primary key of the parent table is updated before saving the record, it will create zombie child records. This is because the changes of TB1.TB1ID fieald are not propagated to child records. This is also a problem when duplicating objects.
To solve this problem you can implement the action() method of the TB1 field class or setting the primary key of the parent table as described in the Using Database Configuration method.

Using Scripting

Adapted from John's comment (thank you)

Create a table and include attributes for: {OWNERTABLE, OWNERID}
Create a script with an Object Launch Point. The launch point needs to be set to fire on Initiate only.

from psdi.mbo import MboConstants
mbo.setValue('TB1ID', mbo.getOwner().getString("TB1ID"), MboConstants.NOACCESSCHECK))

Using Database Configuration

The last technique was suggested by Scott Dickerson. I haven't tested it but it should work.

All you have to do is make sure your parent and child records have the same attribute names, and a unique primary index defined on the child table that's column order matches the unique primary index on the parent record.

Make sure that the field names in the child table match the same field names from the key columns in the parent table. For instance, your child table MYASSETCHILD's field names must exactly match the field names of the parent table, in your case ASSETNUM,SITEID. The MYASSETCHILD field will also need it's own unique key field, let's say MYASSETCHILDID.
So the unique key of the MYASSETCHILD table is ASSETNUM,SITEID,MYASSETCHILDID right?
Now if you set the primarykeycolseq field in the maxattribute table in this order

MYASSETCHILD:ASSETNUM: 1
MYASSETCHILD:SITEID:2
MYASSETCHILD:MYASSETCHILDID:3

(you can set the primarykeycolseq by creating a unique index on these 3 columns and flagging it as the primary index for that table).
As long as the order of the primarykeys in your child table match the order of the primary keys in the parent table, the TPAE framework will automatically set these child attributes whenever new child MBOs are added underneath the parent.

Labels: advanced, application, database, java