Overview

This tutorial will introduce you to what is required in order to build a relational form given some suitable data sources. A relational form contains at least two entities (but usually many more) in a parent-child relationship. The parent entity is normally a structures table of molecules with an associated primary key field defined. The child table might be any source where by there are multiple data points for each primary key entry (or molecule). The child table must contain a field which acts as foreign key, or reference to it's parent primary key. In this way, it is possible to build a simple form in Instant JChem around this simple relational data model. In this tutorial we use some small portion of ChemBl data to show the functionality. We also use the local derby database for the purposes of demonstration, the Oracle / MySQL approaches are essentially similar using the Instant JChem interface.

Contents:

• Create Project & Schema connection

• Create a new Data tree in the schema & Entities then import the data

  • Create Data tree and parent Structures entity

  • Create child Activities entity

• Establish a foreign key between entities

  • Create relationship for the foreign key

  • Create an edge for the relational form

• Create a new form view for the Data tree

  • Add a new form to your data tree

  • Bind widgets to your parent and child entities

Create Project & Schema connection

First create a new project container. Use File -> New Project... menu entry or appropriate icon in the toolbar (shortcut - Ctrl+Shift+N). Create a new project and choose IJC Project (empty). Next name your project and select finish. Next, right click in the project window and select new schema, choose Embedded Derby for this example and finally name your schema.

Create a new Data tree in the schema & Entities then import the data

Next we should need to create a data tree with a structures table and there are two approaches to this. You can either Right click on the schema node and a menu will appear. Then select New Data tree and structure entity (table). Alternatively, you could complete the same result with two operations in the schema editor with the same result. Create a structures entity in the entities tab by right clicking New Structure entity (table). Then at the data tree level, promote it, using New Data tree from entity ... X. Using the preferred method, create a data tree with a root node named "Structures".

Next we can create a standard entity for the child data. Right click in the entities tab of the schema editor and select New standard entity (table). Create a standard entity named "Activities". You should now see two entities and one data tree now.

Finally, you can import data into each entity. This is completed at the entity level for both. In the entities tab, right click on the Structures entity using the Import file into X ... and select the SDF file. You can also import the CSV file into the Activities entity in exactly the same way. Here you need to be careful, since the default primary key name for a standard entity table is always 'ID' and in this case it is the same name in the file! The recommended course of action here is to create a new field for the source primary key prior to import and in this case we suggest you could name it 'source_primary_key' to distinguish this field from the local auto generated primary key 'ID'. You can then map the source ID field to source_primary_key field during the import. Please see screen shots below which indicate the steps required. (This will be useful to consider before the import map & merge tutorial but is not essential for here).

The source ID needs a field. First add new field for source ID...

You need to change the Display name as there is already one ID field in the database containing the values generated by IJC. Select Next to start import.

You will notice that the Activities entity is greyed out. This is because at this point only the Structures entity is part of the data tree. The next section will discuss the integration of Activities into the data tree.

Establish a foreign key between entities

Next, we need to create a relationship between the two entities, that is based upon the "molregno" field which exists in both entities. These fields do not have to be named the same but the reference key must contain values that are found in the primary key. Right click on either entity and select New relationship.... In the dialog, select:

• Relationship Type: New Many-to-One Relationship

• Name: generated value is usually sufficient and reflects references

• Type: Many-to-One

• From: Activities; Field: molregno - This is the many child data points

• To: Structures; Field: molregno - This is the parent molecule
  click Next

• select checkbox next to the Activities datatree - this will promote the relationship
  You can see your new relationship in the schema editor, under relationships, for both entities.
  
  
  You can now see your data tree has changed somewhat. You can see the root node now indicates it has dependents and you can see the child table lower in the hierarchy denoted 1:n to indicate the relationship type. If you look in entities you will see Activities is no longer greyed out - it is actively part of the data tree 'Structures'.
  

Create a new form view for the Data tree

From the projects editor window, right click on the data tree node and select New view.... You can choose an Empty form view and name it accordingly. If you select a Grid view you will find it displays all the fields in the root node. Next, drag a Molecule pane widget on the form. It will bind automatically to the Structures field in the Structures entity. Next, choose other fields from this entity to bind, like formula and molecular weight, a Single line text field widget is ideal for these. We can now add our child data to a form. Due to the nature of the relationship, a table widget lends itself perfectly for this task. Select a Table widget and bind it to the entity Activities (you could choose specific fields by expanding). Select Browse mode and admire the beauty of your new relational form!

Congratulations

Congratulations! You have just created a simple relational form by learning :

• How to create project & schema

• How to create data tree (Structures) and add to it a child entity (Activities)

• How to create a foreign key using a relationship and edge

• How to create a new relational form view and bind entities and fields