In a complex data dictionary, there may be multiple ways to get from one table to another. For example, in the Sage 300 accounting system:
- OESHDT is related to OESHHD and ICCATG
- OESHHD is related to ICITEM
- ICCATG is related to ICITEM
If a report contains only fields from OESHDT and ICITEM, the correct path is OESHDT - OESHHD - ICITEM. However, Stonefield Query may instead choose OESHDT - ICCATG - ICITEM, which won't give the correct results.
The join weight property of a relationship can help you choose the optimum path, but since that's only used for a specific relationship, it can be complicated getting the correct set of join weights to give the desired path under all conditions. That's where join trees help: a join tree allows you to define exactly what set of relationships Stonefield Query should use to get from one table to another.
To create a join tree, choose the Create Join Tree function from the Objects menu, the shortcut menu, or from the drop-down list for the Create button. Join trees appear in their own panel in Studio rather than in the Databases panel.
In the Tables in this join tree list, choose the two tables that you're interested in specifying how to join. Double-click a table to jump to that table in the databases TreeView. The Relations in this join tree list shows all relationships for those two tables. Turn on the check mark for those relationships you want used in the join tree. Double-click a relationship to jump to that relationship in the databases TreeView.
In the image above, you can see that the OESHDT - OESHHD and OESHHD - ICITEM relationships were chosen for the OESHDT - ICITEM join tree. That means when Stonefield Query encounters fields from OESHDT and ICITEM in a report, it'll automatically use OESHDT - OESHHD - ICITEM for the joins for the SQL statement for the report. The Name property is automatically assigned as "Table1,Table2" and is simply used for information purposes.