Showing a value latest associated record in datagrid

I’m developing an application for keeping record of ConfigurationItems (CI). Per CI a log (CILog) is created where the actual status and comments can be recorded. The association between CI and CILog is one to many with the direction from CILog to CI. In the overview datagrid all the CI and their actual status (status of latest associated CILog) must be showed. To do this I made an entity called CIOverview derived from ConfigurationItem with an extra field called ‘Status’. This datagrid is filled in the following manner: Create new list of CIOverview Get all CI Iterate CI Get all CILog ordered by date Get the head of the CILog list Create new CIOverview If the first CILog has status: add it to CIOverview. If not give constant value ‘Unkown’ Add new CIOverview to CIOverview list I know this solution is far from optimal (getting multiple lists of all items), but I can’t figure how to do this more efficiently. I know OQL can do sub-queries, but I can’t find any resources about how to apply OQL. Can anybody lead me to a better solution? Thanks in advance!
1 answers

You could add an extra current status field to the CL and use an aftercommit on ClLog to check and set the status of the CL/currentstatus field. Then you just can use a datagrid on CL to show all the info.