I would very much like to take advantage of the new calculated field column type in IJC (5.4 beta 2, Oracle Enterprise 11g w/ Cartridge). I have a structure table with a relationship to an assay data table based on a non-ID column. This works fine for forms, but I would like to be able to view the data and structures in a grid. It seems that using the calculated column may be an ideal way to go, however, I'm not sure what Expression syntax would be appropriate. My attempt to select just the variable (assay result) results in a series of the same error as it fails to pull the appropriate values:
INFO [SQL_LOGGER]: #SQL: MasterDetailSelect SQL: SELECT DISTINCT CHEMAXON.XC_ASSAYS."ID" FROM CHEMAXON.XC_ASSAYS WHERE CHEMAXON.XC_ASSAYS."ID_XC_ASSAY" IN (SELECT CHEMAXON.STRUCTURES."ID_STRUCTURES" FROM CHEMAXON.STRUCTURES WHERE CHEMAXON.STRUCTURES."ID" = ?)
SEVERE [org.openide.util.RequestProcessor]: Error in RequestProcessor com.im.ijc.widgets.table.VertexStateDataCache$FetchRunner
java.lang.IllegalArgumentException: Must specify IDs to retrieve
Any suggestions on proper syntax to fetch the IDs when the relationship is not based on ID?
Yes, using a calculated field is a good approach to this.
The calculated fields feature has been undergoing extensive development recently, and the beta 2 release was not complete. We will hopefully be doing a build tomorrow that contains the final calc field functionality (plus documentation) where this should definitely work. Its possible that you might need to wait for that build.
To do this you basically have two options:
1. add the data from the assay table as a calculated field in the structure table. As the assay table will be in a one-to-many relationship to the structure table you will need to aggregate the assay values in some way e.g. averages or concatenation.
2. Add the structure field to the assay table as a caclulated field. As this relationship will be many-to-one there is no need for aggregation. The structure will need to be added as a text field and then displayed using the structure renderer.
Both apporaches are posible. I attach the documentation page that will be present in the next build for you to refer to. Much of this might work in the beta 2 build, but you might need to wait for the next build for full functionality.
Splendid! I will wait for the next version and then give it a try.