how to update a row in IJC

dear support,

 

With IJC 5.3.8, i create one entitie by importing a sdfile (filed structure, and some ID fields).

I add 4 new standard entities and i create realationships between the 5 entities and i update the datatree with the 4 new entities. In the form view, i create in the design mode a table for each new 4 entities.

After, i would like to update each row containing already the structure and the IDs by adding some datas in the tables created from the 4 new entities but I can't. I can only update the field "structure" and "IDs".

 

Thanks for your help.

Hi I'm not totally clear on what you are doing here (maybe a screenshot might help).

But I think you might be wanting to update the ID of fields that are used in relationships between the entities. If so, then IJC prevents these fields from being edited as editing these values can have strange consequences. Technically it could be allowed, but the results could be confusing in some cases. 

Could you clarify exactly that it is you are wanting to do.

Tim

I attached the pdf file with explanation and screenshots.

 

Thanks

In relational datatrees it is important that you insert data in the right order.

For example you have two entities A and B with relationship 1:Many (one row in A is related to many rows in B).
It means that entity B has probably some field (e.g. A_ID) which contains values of related A.ID. Each row in B has the link to one unique row in A. In this case it's necessary to insert row into A first. Then you can select this row in A and insert associated row in B.

It looks that your "assay" entity is the entity which has only relationships 1:Many with other entities (1:Many to assay_result and 1:Many to projet_criblage). So you need to insert row into "assay" entity first. This is not possible in your existing datatree.
According to what I see in the screenshot of schema editor I recommend this:

1. create a new datatree (just with using existing entities) with assay as root entity, two detail entities assay_result and projet_criblage (in schema editor using action "New edge")


2. create a new form with three table widgets.


3. select assay table widget and insert a new row


4. then select this new row and click to assay_result table widget. Now it will be possible to insert row into assay_result which is associated with previously created row in assay. You'll also need ID of molecule from your structures entity (as it looks assay_result has N:1 relationship with structures)


5. the same you can do with the last data entity: projet_criblage

After data are inserted you can switch back to your original form and browse the data.

Let us know if anything is not clear or it doesn't work for you.

Dear support,

 

Thank you for you answer.

It is not really exact when you wrote : it looks that your "assay" entity is the entity which has only relationships 1:Many with other entities (1:Many to assay_result and 1:Many to projet_criblage).

 

I complete my pdfile.

 

the entity projet_criblage has the relationships 1:Many with entity assay

the entity assay has the relationships 1:Many with entity assay_result

and the entity assay_result has the relationships many:1 with entity CNE_pourIJC (table structure)

 

Kiet.

here is the pdfile

Yes, I see it now. I didn't read correctly one n-1 label in your original screenshot.

So now it looks projet_criblage is the starting point. You need to add row to this entity before you are entering values into assay entity. And you need to add row into assay before entering rows into assay_result.

I think this can help:

1. create a 3-level datatree with projet_criblage -> assay -> assay_result


2. create a form for this datatree with three table widgets (each bound to one entity in the order like hierarchy)


3. select projet_criblage table widget and insert row


4. select this new row and then click to assay table widget. Now you should be able to insert row into it


5. select the new row in assay and click to assay_result and insert row. You'll need to know id of appropriate structure from CNE_pourIJC entity.

I tested it in local DB project (see attachment). Each entity has only default ID and FK fields, no extra fields were added, but the structure should be similar to yours.

Hope it will work now.

dear support,

 

I noticed that when i create a relationship between for example table assay and projet_criblage :

assay_projet_criblage is the name of the relationships, the Id (primary key) of the table projet_criblage is not automatically put in the table assay as foreign key.

 

In your project, is the field ID_project_criblage inserted automatically in the table assay after the creation of the relationship assay_projet_criblage? or you added this field? 

In this example I was using function "New detail table" which is simplest way how to create a few tables linked with relationships. I've created root table (entity), then R-click on it in schema editor and used New detail table. This can be used again if you want to create "detail of detail".

You can invoke this action from more places: in project explorer (when create only the first level detail table), or in schema editor in datatrees tab.

dear support, 

 

with your last post, I can have in the entity assay, the fied ID_assay created and the field ID_assay created in the entity assay_result. But I can't have the field ID_CNE_pour IJC.  How could I create this last field automatically by creating a relationship between assay_result and CNE_pourIJC entity?

From Data trees, I started from CNE_pourIJC and I try to create "New detail Table" but I have the error : name "assay result" is already used by other entity.

erratum : with your last post, I can have in the entity assay, the fied ID_assay created 

 

=> the correct sentence is with your last post, I can have in the entity assay, the fied ID_projet_criblage created 

Ok i find the way how to create the wished relationship between CNE_pourIJC entity and assay_result entity :

 

in the assay_result entity, i create a field ID_CNE_pourIJC type varchar and I create a relationship type many to one :

source : entity= assay_result (field : ID_CNE_pourIJC)

target : entity=CNE_pourIJC (field : ID_CNE)

 

now it works 

So i would like to import data from csv file (see test.csv).

 

I have 14 fields in the csv : 

 

projet;responsable;plateforme;ID CNE;ID plaque;Puit;assay;assay_protocole;assay_date;assay_note;assay_result_value;assay_result_unit;

assay_result_type_unit;assay_result_note

 

 

I would like to associate for example the field projet with the field projet_nom from projet_criblage entity

From the entity projet_criblage, i import my csv file =>  14 fields are found but these fields are not named :

 

I have field 1, field 2,.. and I can't map with field in the database : projet_nom, projet-reponsable-projet-plateforme

see the pdfile 

see the pdfile

see the test.csv

sorry for all these posts,

 

ok I have to remove the fields in "Fiels in database", Field 1 to Field 14 after i can map the Field1 in( the Field in file) with projet_nom, Field 2 with projet_responsable and Field 3 with projet_responsable.

 

as the tool doesn't recognize the field name,  4 lines are inserted in the table projet_criblage .Normally only 1 line must be created because the data are the same for the 3 lines (except the 1st line).

Why 4 lines are created ? 

see the screenshot of the last post

I'm not sure I understand the last problems. A few comments:

• In import you can specify that first row contains row headers (so fields will be correctly named then). You need to click to "..." button in the first import screen (next to file type combo)


• Standard import (.sdf, .csv, etc) works only for a single entity. It's not relational, so it can't insert rows into multiple entities. There is also rdf import which can be used for importing relational data and it can create more entities during import.


• I checked the last attached .csv file. It contains 4 rows (the first row is header). I think it should produce 3 rows in database after import (it's necessary to specify the first row contains header names as I mentioned above).

Let us know if this is not answering your questions or there are still other problems.

I think that for the field projet_nom, I can create a unique key, so only one row will be created from the csv file.

Is it possible?

how could I reset to 1 the id index of a column ?

In sql , we delete the sequence and we create  :

create sequence id INCREMENT BY 1 START WITH $valeur MAXVALUE 1.0E28 MINVALUE 1 NOCYCLE CACHE 20 NOORDER

Finally, I achieve to insert for each molecule (640) in IJC, the information available in the csv file.

I have to insert table by table.

Now I have a difficulty : 

- each molecule is associated in my case with 1 lab (field :laboratoire) and with 2 projects (table project_criblage) and each project may be associated with one or 2 assay 

- I have 640 molecule from X different labs.

 

So I run a query : 

laboratoire=UGCN

project_id=2

 

=> result : 4 molecules but the 2 projects (project_id=1 and project_id=2) appear in the results

normally only 1 project with project_id=2 must be obtained and When I export the results in rdfile, I have 4 structures associated with 2 projects.

 

I attach the screenshot of the query result

dear support,

 

any suggestion about my last post?

 

thank you 

Could you please provide a screenshot of ijc datatree opened in schema editor? I think I know how entities are connected each other, but there are many ways how to build the datatree on top of it. Maybe even better is to send us  schema definition (Help -> API examples -> Export IJC schema).

I think this is now problem of how relational queries are executed in Instant JChem. For example: you have two tables A and B with a relationship and then datatree A -> B. Then you can run a query where B.someValue = X. This query returns all rows (from A) which have at least one detail row with B.someValue = X.

If datatree is more complex like in your case (e.g. A->B->C) then it can get something a little less intuitive if you use the detail-detail table for search criteria. For example if your criteria is C.value = X then it will return all rows from A which has at least one detail row from B which has at least one detail row from C with value = X.

You can review the actual hierarchical query definition in Query builder (Windows -> Query builder).

Dear support,

 

 

please find enclosed the schema definition.

 

thank you for your help

I was finally able to import your xml into my local schema so I can see it.

The search problem seems to be exactly what I described in the previous comment. There are two conditions:

project_id=2 AND laboratoire=UGCN

If you use 'ijc' datatree where structures entity (called 'ijc') as master table then the second condition is OK. It finds only structures with laboratoire=UGCN. The other condition works different way as it's in 3rd level of master-detail relationship. The search actually finds rows from ijc where exists at least one row from assay_result for which exists at least one row in assay for each exists at least one row in projet_criblage with project_id=2.

This means the result list is probably correct but if you select a structure in master you can see some rows in details which doesn't have any detail-detail rows with project=1.

In the future we plan to implement a filter in detail tables to show only rows which match with expression.

dear support,

 

When I export the result of a query in csv format, how could I precize the symbol like ";" which separate 2 values?

By default, 2 values are separated by comma (",").

Yes, csv is Comma separated values format, which doesn't have optional separator. You can also use *.tab file format (tab separated file). Currently we don't support other separators in export.

If this is not sufficient, there is a simple work around: You can import the csv file into MS Excel (or Open Office) and then export again. There are settings which allows you to export it with different delimiter.

Hope this helps.

P.S. it's usually better to start a new topic when it's not related to original subject.