May 19, 2013

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 simpleNot working with MIF
Setting defaults in APPFIELDDEFAULTS table Quite simpleDo not manage deletes
Using Java Mbo Works perfectlyRequires Java customization
Using scriptingQuite simpleNone (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
  1. MYASSETCHILD:ASSETNUM: 1
  2. MYASSETCHILD:SITEID:2
  3. 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.

15 comments:

  1. I think you overlooked the option of using automation scripts applied with an Object Launch Point and on the Add action to set the FK and other fields in the child records.

    ReplyDelete
    Replies
    1. I have updated the table but I haven't tried it so I don't have precise instructions about how to do it.

      Delete
  2. I created a technical documentation table that can be used by multiple applications but primarily designed for the Asset and Locations tables.

    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.

    The Script:
    from psdi.mbo import MboConstants
    mbo.setValue('ownertable',mbo.getOwner().getName(),MboConstants.NOACCESSCHECK)
    mbo.setValue('ownerid',mbo.getOwner().getUniqueIDValue(),MboConstants.NOACCESSCHECK)


    Create a relationship between the parent and child using the following as a template(In this his case, the Asset table is the parent):
    ownerid = :assetuid and ownertable = 'ASSET'

    You can delete this post after assimilating these steps into your instructions above. Please reference my name though :)

    Thanks,

    John C. Wilson

    ReplyDelete
  3. Hi Bruno,
    I use your' Basic method using Database Configuration and Application Designer'. I create 2 tables tb1 and tb2(child table) also i use default value same as your comments.Then create new application and i fill child table fields and i save my application but i can not see any data in my child table.After this i go to database and write select * from tb2 and i see my fields values before i save in child table.But key which i use tb1id (tb1id=tb2id) is seems null. In addition when i exit application then go to application i fill child table and save my new record in my child table i see my new values which i fill child table. Also i use select * from tb2 in database in this time i see tb1id is not null. What should i do or i forgot.
    Regards.

    ReplyDelete
    Replies
    1. I'm sorry but I don't understand your point.

      Delete
    2. Hi,
      Sory for my English :(

      I used duplicate menu with Purchase Contract Application and create new Apllication named 'Power Contract App'. Then i created a new table in cotract tab. My new Table name is Payment Details. I created a new attribute same as CONTRACTNUM in payment Details table(CONTRACTNUM UPPER length=8 Same as Object=CONTRACT and Same as Attribute=CONTRACTNUM) .In Database Configuration i defined the following relationship in object PURCHVIEW:
      Relationship: PAYMENTDETAILS
      Child Object: PAYMENTDETAILS
      Where Clause: CONNTRACTNUM=:CONNTRACTNUM. After this i added a Default Value control with the following configuration im My Power Contract App:
      Attribute: CONTRACTNUM
      From Data Source ID: results_showlist
      From Attribute: CONTRACTNUM
      When i created an application i add new values in my child table(Payment Details) at first time they are not visible. When i created a query in my Oracle Db as below:
      'select * from maximo.paymentdetails;'
      i see that only paymentdetails.comtractnum=null, other values are not null. if i go to back my Power Contract app and add new values to Payment details table, they are saved and i can see my new values.
      i run my query again select * from maximo.paymentdetails;
      i see that paymend details.contractnum is saved correctly and add more fiels correctly after that. What i mean here is only the first saving process fails. I added some image about my relation to here
      https://www.ibm.com/developerworks/community/forums/html/topic?id=707f65d4-79b6-44ad-beab-c22886af77fb
      Regards

      Delete
  4. Hello Bruno, I really appreciate your tips. I would like to mention that the only way I could get the linking to function correctly in the presentation (using the App Designer) was to include an index in the child object that replicated the columns identified in the relationship. (maybe just my problem)

    ReplyDelete
  5. Hi Bruno,

    Thanks for the tips.

    In the article you mention:
    "To solve this problem you can implement the action() method of the TB1 field class"

    I was wondering, could you be a bit more specific about this solution? I tried to implement this, but when you are in the action() method and need to discover the child objects to propagate the field change to, the parent field is already updated and the relationship returns an empty mbo set (i.e., it's too late, the children have already become zombies).

    Do you have some working sample code which does not have this problem?

    ReplyDelete
    Replies
    1. Sorry I don't have any sample code.
      I'm not sure itis the best approach but you can try to retrieve the previous value of the attribute as described here.
      http://maximodev.blogspot.it/2013/10/mbo-attribute-current-previous-initial-values.html
      Then you can retrieve the child records using a where clause.

      Delete
    2. Hi,

      Thanks for the reply.

      Getting the previous value of the parent key value is not a problem. The problem is how to use it to get the old related MBOs. The process looks like this:

      1) Add a new parent ("new row")
      2) Add a new child ("new row") - the parent fields are copied to the child, but are empty because they are not filled in in the parent yet; the relation is based on an empty key
      3) Change the parent key field - this breaks the relationship

      Now the action() method gets called. However, Maximo has already updated the key field in the parent, so the existing relationship based on the empty key is no longer valid. Setting the where clause does not work either, because this causes the children to be queried from the database, and the child elements were not persisted yet.

      I examined the entire API of the involved objects, but cannot find a way to recover the non-persistent MBOs which where in the previous related set. And without being able to do that, the children are lost...

      Delete
  6. When I create a work order and fill in the supervisor name, I want to pull in person.department into workorder.department where workorder.supervisor = person.personid

    How would I achieve this through an automation script? I've been struggling with cross-over domain, but someone said automation script is the way to do it.

    ReplyDelete
    Replies
    1. Hi,

      I think you can do this by setting relationship to person .
      Ex:
      DBrelationship : supervisordetails - personid = :supervisor

      modify xml - supervisordetails.department

      Delete
  7. I used duplicate menu with Purchase Contract Application and create new Apllication named 'Power Contract App'. Then i created a new table in cotract tab. My new Table name is Payment Details. I created a new attribute same as CONTRACTNUM in payment Details table(CONTRACTNUM UPPER length=8 Same as Object=CONTRACT and Same as Attribute=CONTRACTNUM) .In Database Configuration i defined the following relationship in object PURCHVIEW:
    Relationship: PAYMENTDETAILS
    Child Object: PAYMENTDETAILS
    Where Clause: CONNTRACTNUM=:CONNTRACTNUM. After this i added a Default Value control with the following configuration im My Power Contract App:
    Attribute: CONTRACTNUM
    From Data Source ID: results_showlist
    From Attribute: CONTRACTNUM
    When i created an application i add new values in my child table(Payment Details) at first time they are not visible. When i created a query in my Oracle Db as below:
    'select * from maximo.paymentdetails;'
    i see that only paymentdetails.comtractnum=null, other values are not null. if i go to back my Power Contract app and add new values to Payment details table, they are saved and i can see my new values.
    i run my query again select * from maximo.paymentdetails;
    i see that paymend details.contractnum is saved correctly and add more fiels correctly after that.

    ReplyDelete
  8. Is there any way to use a relationship in a where clause of another relationship in the same table?

    ReplyDelete