relationship between tables

Hi to all,


Hi have imported four different files each of which is a table in my ijc schema (oracle connection).


the first one is a structure table from sdf file, the others are standard tables from txt files.


those four tables should be linked together by a common field named ojectid.


During the import from file, ijc application creates its own PK in each table.


how can i get a custom view linking my four tables? objectid is not a pk in any ijc tables.


The problem is that when i link the standard tables to structure table in a many to one relationship using such common field, ijc considers its own PK in the structure table in order to reference the Fks on the other tables.


I do not know if i have been clean in my post........sorry


Cheers,


Pasquale

When you create a relationship you can specify the fields used at either end of the relationship. In the "one" end of a many-to-one relationship this does not have to be the primary key, but frequently is.





However in the current relesed version there is a limitation that a unique constraint has to exist on the database column, and this IJC version does not provide a way to add this constraint. Currently you will need to go into oracle and manually add a unique constraint on the appropriate column before adding the relationship.





The next IJC version that should be ready soon includes index management and also allows the unique constraint to be added as part of creating the relationship.





Once you have the relationships defined you can build a Data Tree that defines the data hierachy and utilises those relationsips and then create forms to view the data.





Tim

Hi Tim,


I did as you told me and i added a unique constraint on the field at the one side of the one to many. then a recreated such relationships and built the edges, but still doesn't work. It still gets the default ijc PK key on the structure table instead of the proper field.

I will look at this.


Just to confirm, you are saying that the relationship is created correctly (with the correct fields at each end) but the data that is retrieved is ignoring the field at the 'one' end of the relationship and using the primary key value instead?





Tim

exaclty!


Pasquale

This problem should now be fixed. Can you load the latest updates and give it a try.


Please let us know if you see any other similar problems.





Thanks





Tim

It works fine thank you very much!!


Another little tip.. I am testing ijc on a terminal server in order to check how it works on a multi users environment with a single installation. The question is, how could i update ijc and keep the updating to all users? how works manual update with nmb module files?

I'm not sure I fully understand. But I hope this response is of some help.


When a user updates from the update center (or using locally downloaded .nbm files) by default the updates are loaded for just that user. They will have a directory in which the updates are stored (on Windows this is something like C:\Documents and Settings\<user>\Application Data\.instantjchem\ijc_2_0_dev note: the last part of this will differ between major releases of IJC). In this dir you will find various directories (update, update_tracking and modules) which contain the updated modules and information. If you ever want to reset IJC back to the version that was installed just delete these 3 directories.





When you upate on Step 4 there is a checkbox on the right hand side of the screen where you select the modules for updating that has the heading of "Global". If you check this then those updated modules will over-write the originals (in C:\Program Files\.... ), and should be available to all users. But you can only roll back be uninstalling and reinstalling.





Please test this carefull first!








Also note that in the next major release the autoupdate wizard will change significantly.





Tim

Hello Tim,


i tried to set global during uploading, but it seems that doesn't work.


Any way, how can i download .nbm modules and get them available to all users?


Pasquale

The updates (for the 2.0M3 release) are available from here:


http://www.chemaxon.com/instantjchem/updatecenter/ijc_2_0/


either indiviudaully or as a zip file with the complete set for each update.


You could use the zip file to set up your own update center if you preferred your users to update from there. This would give you more control over the timing of updates. An update center is just a location on a web server so is easy to set up.





Of course, users by default will se these updates on chemaxon.com automatically so can update IJC for themselves. You would only need to get hold of the .nbm files or set up your own update center if you wanted some behaviour other than the default





Note: this location will change between different versions of IJC to ensure that the updates don't get mixed up.





Tim

Last question (maybe) on relationship.


For a custom form built by 4 different tables, only the parent table fields can be queried.


I would like to use fields of all other linked tables to filter search results but it is not possible due to such fields are not in the query editor. I tried to build a form centred on one of the linked table to get such result, but if just the main table is a structure table i loose the opportunity to get substructure searches.


Any suggestion?

We are just finishing a re-implementation of the query builders. The old style "standard" query is still available for simple cases, but we now have a new "advanced query" builder that lets you build more complex query expressions, including those containing nested AND and OR operators and those that include queries for relational data (e.g. the case you mention).





This will be shown at the UGM next week (are you coming?) and available shortly after that.





Tim

Hi Tim,


unfortunately, i could not be present to your last UGM.


what about the new advanced query editor? have you got an idea on when it will be available?


Cheers, Pasquale

Very soon. (should be this week).





Tim

Sorry, going to be next week now :-(





Tim

The new version is now ready for download.





Tim

Hi Tim,


Really very nice the new version of IJC. But, i know i am quite boring... some features could be improved in my point of view:





1) now we are able to query the database in the advanced mode as discussed in the past. But, if ad example, I query the database by substructure form using a filter with a field of another linked table , i am not able to export the summarized result in a sdf file. Only the fields of the master table seems that could be exported.


2) how can I select custom rows to be exported? If I. ad example, after a query, need to select not all the results but just a piece, with ctrl and click on the selected row it doesn't work.


3) It seems there are some issues in the export to sdf and open the sdf file with ISIS for Excel

Quote:

i know i am quite boring...

We like all feedback, boring or not !


In fact these are good suggestions, and we have already been thinking about them:

Quote:

i am not able to export the summarized result in a sdf file. Only the fields of the master table seems that could be exported.

This is correct. Export does not yet support relational data. We will be adding this once we have the 2.0 release finished. We plan to be able to export to RDF keeping the relational nature of the data, and to SDF by flattening out the data (e.g. write multiple detail entries as separate lines for the property in the SDF file.

Quote:

how can I select custom rows to be exported?

Currently your only option is to run an "IN LIST" query to retrieve just the IDs you want to see. We will soon be starting to implement proper list management in IJC which will provide a much better way to create and use lists.

Quote:

It seems there are some issues in the export to sdf and open the sdf file with ISIS for Excel

Can you provide more details on this?








Tim

when i try to import into ISIS for excell an sdf generated by IJC i got the following error message:

Quote:

error in reading SDfile data record. Input past end of file