Forcing Joins/ Referencing Fields on Other Tables

Method 1

There is nothing to stop you in the object ‘Select’ statement referencing a field or fields that doesn’t exist in this table, but on another one that it is joined to (although you would have to set this up manually).

However, if you then built a chart that only used objects from the one table piDashboard wouldn’t automatically resolve the join to the other table and therefore our object and therefore the chart would fail. The dashboard has no way of knowing that one of the objects references a field held on another table.

Example

This object on the order detail line (POPOrderReturnLine) table is referencing a column on the order header table (POPOrderReturn), specifically DocumentTypeID.

If you were to build a chart that included this object but only looked at the order detail line table then it would fail because it has no notion of how to find POPOrderReturn.DocumentTypeID.

In this scenario you need to ‘force the join’ to POPOrderReturn, and generally the easiest way to do this is to add a filter from POPOrderReturn to your chart. This will then force PiDashboard to resolve the join to POPOrderReturn and it will then be able to find POPOrderReturn.DocumentTypeID. This could be a ‘nonsense’ filter such as Order Status <> ‘Micky Mouse’ or it could be something slightly more sensible such as Order Year > 1980, it doesn’t really matter as long as it’s not going to actually restrict the orders you want; you are only doing it to enforce the join.

Method 2

An alternative way to referencing fields on other tables is by referencing the field you want to use in the following way.

In this example, we have an object called ‘Order Header DTID’ which is an object that exists on a different (joined) table.

If we reference this object using #~Object Name~# it will force the join automatically.

When we use the object name, ORDERS #~, in data table it will force the join automatically.