Recursive Traversal of Relationships

Is it currently possible to access data in tables that are indirectly related to the parent table?





For example if I have three tables, Assay, Batch, and Structure. Assay and Batch are related through BatchUID, and Batch and Structure are related through StructureUID, is there any way to bring up data in the Structure table from a view rooted at the Assay table level via this indirect relationship? Otherwise, common keys are duplicated throughout and can (and do) lead to internal inconstancies.





Thanks,


Orion

Essentially this should be possible.


At the database level the types of relationships between tables is pretty well unrestricted.


To build a view (e.g. form) displaying data you need to assemble a DataTree that is a hierarchical data structure that uses the relationships in the database. The hierarchy can be as many levels deep as you want, but there are currently some limitations that we do intend to remove in future versions. The key limitations are that a table can only appear once in the DataTree which prevents recursive data structures.





For instance the following are currently OK:





A -> B





A -> B -> C





A -> B -> C -> D -> ...





A -> B


-> C





A -> B


-> C -> D








But the following are not currently allowed





A -> B -> A





A -> B -> D


-> C -> D








In your case (the A -> B -> C example) you can create this Data Tree and then create a form for it. The form would typically contain:





1. The structure (A) and any of its fields





2. A table widget containing fields from the batch table (B). The data in this table would update according to the currently selected structure





3. A table widget containing fields from the assay table. The data in this table would update according to the currently selected row in the batch table.








In principle this works to an infinite number of levels, but in reality more than 3 are probably not too useful from a practical point of view.








Tim

Thanks Tim,





I thought this was implemented as you described, but I can't seem to access data through more than one relationship either in queries or form views. (e.g. the hierarchy A -> B -> C does not work for me.) Perhaps I've set up some aspect of the relationships wrong.





For example, I've noticed that relationships set up in the 'Edit Schema' pane do not get functionally implemented, while relationships added through the 'Edit Data Tree' pane do. In any case, we will certainly be avoiding circular relationships. Will follow up with you to see what are doing wrong/right on our end.





Many Thanks,


Orion

You have probably not created the DataTree correctly. Open the editor for the DataTree and see what you have. You should have A at the top, B as a child of A and C as a child of B.





Remember, you need to different things to create a child element in a DataTree:


1. The relationship that defines the relationship between the 2 tables in the database and can be added in the schema editor.





2. An edge in the DataTree that uses the relationship you defined in step 1, and is added in the DataTree editor.





There are shortcuts that provide simplified ways of doing both steps together, but if you are having difficulties then I suggest you go back to basics and do it in the 2-step process I outlined.





Tim