Calculated field

I'm relatively new to IJC and have gradually worked my way though most things, but this has me stumped, so time for expert help. I would like to pull out a specific value from multiple options in the "lookup" table below. I want to generate a calculated field in another table that uses the value of 3.45, corresponding to the Compound X / Mouse result.

In an Excel environment, I would use an INDEX / MATCH array formula to differentiate  between the values associated with Compound / Species combinations.  

Compound	Species	Value
Compound X	Rat	1.23
Compound X	Human	2.34
Compound X	Mouse	3.45
Compound X	Rat	4.56

Do I need to explore the world of groovy scripting or SQL related functions or can this be done with calculated field functions.

Any help would be appreciated.

Mike

I'm going to assume that the lack of responses means that this is not easy to do or is not possible. As part of my continuing evolution in Oracle / IJC systems, I've discovered database VIEWS that can be promoted to pseudo tables within IJC. By employing the SQL statement below, I can filter my data to ignore species other than Rat, which creates a suitable "lookup table". This would mean creating views for each species, but as this is a relatively small, static list, that isn't an issue.

SELECT ID, COMPOUND, SPECIES, B_P_RATIO

FROM B_P_RATIO

WHERE SPECIES = 'Rat'

Anyone employed similar tools within an Oracle / IJC system. Would appreciate any pointers / reference documents.

 

Mike

Hello Mike, sorry for the late response. I think you have 2 options here how to solve it

1) your current solution can be simplified with IJC SQL-entities (you can create them at the same place as entities in IJC) , so you will not need to create database views in oracle any more, but otherwise the solution will be very similar (we can call it virtual database views within IJC). Main cons here remains...you will have to create entity for every animal:)

2) the other solution would be not to use calculated field, but e.g. some button script or schema script, where you can query basically everything directly from database. I believe that's how to get over the main cons in 1). But it would than require running schema script or clicking to button. Or you can use also schema-init script and count these values on schema connect/disconnect similarly as in button script or schema script.

BTW: how you decide what animal kind should be queried in your use-case?

 

br

Lukas

Thanks Lukas,

I've applied the SQL statement to an SQL Entity and promoted it to a data tree to use as a "lookup table". I will play with the SQL entities to see how they will work. Don't really mind generating SQL entities for each species as there will be only a limited set.

I have no experience with the button / schema script, but would be interested in seeing examples or reference documents to see how they would work for us, particularly in cases where we have more varied field values (eg species) and the SQL entity approach doesn't suit. Do you have references / examples.

Mike

Dear Mike,

as for the documentation, you will find some theory and examples in the documentation here.  If you have a look at some of the examples there, pieces of code could  (such as how to access a result set in IJC) be used used to create a script of your desired functionality. 

I would also recommend reading through the general Developer's guide and IJC API documentation (Java Docs are accessible at the bottom of the page) to see what is possible to do with Groovy scripting in IJC.

I hope this helps

Wishing all the best

David

Dear Mike,

Very interesting topic - lack of reply might be due to folks being on holiday though :-)

Anyway, I have used promoted database views quite extensively lately in order to create project summary tables, basically doing something similar as you sketch here, by selecting appropriate fields from different database tables and then joining by the compound Batch ID, which is common to all tables containing data for a particular batch. This is on a mySQL system but I'm sure it's equally feasible on Oracle-based systems. I find that it works surprisingly well, and the system is very responsive despite complicated SQL statements that are run on the fly when new data is added.

In mySQL there is a statement called CASE WHEN, which I have found to be very useful to create pivot tables. In your example you could use this in a database view in order to create a table with the Compound ID as row, and then for each species a column with its respective value:

Compound ID	Rat	Human	Mouse
X	2.90	2.34	3.45

Since you have 2 measurements for rat, you would get the average value.

My ambition is to share some of this code with the community but I just came back from holidays, so it may take some time...

Al the best,

Evert

Thanks Evert,

Really pleased to see I'm not alone on this one and that someone has managed to make progress in this area. Since my original post, I've done a lot of reading on views, SQL entities, joins etc and feel a lot more comfortable that there is a solution to this and related challenges.

Would love to see your SQL statements. I'm sure there are minor differences between the MySQL and Oracle systems, but the strategy should be much the same.

Mike