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
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.
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 );
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.
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))
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.
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.
I have updated the table but I haven't tried it so I don't have precise instructions about how to do it.
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
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.
I'm sorry but I don't understand your point.
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
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)
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?
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.
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…
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.
Hi,
I think you can do this by setting relationship to person .
Ex:
DBrelationship : supervisordetails – personid = :supervisor
modify xml – supervisordetails.department
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.
Is there any way to use a relationship in a where clause of another relationship in the same table?
Yes but you have to use the SQL syntax.
my experience while using Application Designer – defaultValue method in Maximo 7.5.0.5:
Attribute: TB2ID — must be ChildTable.AttributeName
From Data Source ID: results_showlist — this is not fixed value, need to provide the correct data source ID, in my case – I had to use MAINRECORD
From Attribute: TB1ID
Also, APPFIELDDEFAULTS didn't work for me in Maximo 7.5.0.5
does MBO copy also copies child objects data ?
Ex. I need to copy workorder table in a custom table. while copying the records I am getting error that "relationship does not exits between Child table and custom table"
What Data source value should be used?
I was designing a new application and I set data source ID: results_showlist and it didnt work. Any idea what Data Source should I be using.
Rana – Use MAINRECORD for Data Source ID as mentioned by Sourabh.
I'm using Control Desk 7.6. I had no success with the technique of matching the parent and child primary keys as described in the "Using Database Configuration" section. However you can link a child and parent using default values for the key attributes on the child object. In my case I wanted to create a custom object that would be a child record of tickets. I defined a TICKETUID attribute on the child object type, and defined its default value to be ":&OWNER&.TICKETUID". This evaluates to the TICKETUID value of the ticket that owns the child record. I prefer doing it this way to using the APPFIELDDEFAULTS table because doing the work in Database Configuration makes it independent of which application is creating the child record.
To answer Priyanshu's question, mbo copy will not automatically copy a child object unless support for this is coded into the mbo's class. In Maximo/ICD 7.6 you can implement this using automation scripts. Create a script having no launch point. Define the script's name to be .DUPLICATE. So, for my case of creating custom child records for tickets, I need to create scripts named SR.DUPLICATE, INCIDENT.DUPLICATE, etc. The script will run after the mbo class' copy logic has done all of its processing. The script will have a "mbo" variable pointing at the source of the copy, and a "dupmbo" variable pointing at the result of the copy. Your script will need to create the desired child objects under dupmbo, and copy the desired attribute values to it.
In my 2nd paragraph it should have said to set the script name to "object-name.DUPLICATE".
Hi Bruno,
We are creating an interface to GIS that loads the signs assets to Maximo. There is a related table in GIS that contains Spare Parts and Sign Detail.
We tried to display the sign detail within the Spare Parts section for each row but could not solve the ‘invalid binding’ error.
We created a new table in Maximo for the Sign Detail and we load the spare parts and sign detail information from GIS using the MIF.
We added a section to the Assets Spare Parts tab in Application Designer to display the sign detail. We wanted the sign detail to display the related information from the Spare Parts section. When a different spare part row was selected we wanted the related sign detail to display the associated detail but have not been able to get the associated sign detail to change when the spare part is selected.
Do you have any suggestions on how we can display the sign detail in the spare parts asset tab?