Custom Joins that extend the Datamodel
Custom Joins allow you to extend the Datamodel with new custom relations that have not been defined in the Master version. If a new version of the Datamodel is installed the Custom Joins that have been defined and stored in the SharperLight database will be merged with the new version on start-up.
Custom Joins very useful in linking Tables together based on certain criteria that is unique to the Clients environment. By using Custom Joins one can avoid using much slower Sub-Queries and have your join logic included in the main SQL statement. The description and extended description can be set to help give the user more information about how your Join works and how it is different to simular Joins that may exist in the Selection Tree.
Once a Custom Join has been defined it will appear wherever the Table it has been defined under appears even when the Table appears a Sub-Table under another Table.
The basic steps for creating Custom Joins are as follows
1) Ensure you have permissions to Add Custom Joins and Fields in Site Setup
2) Start Query Builder and select the root Table or Find the Target Table under a Folder
3) Select a Field where you would like to anchor the new Field Join in the Selection List
4) Right Click and Select Customize – Add Join
5) Set the Target Table of the Join in the Property Grid
6) Define the SQL Join logic in the Property Grid
7) Set the Code, Description and Extended Descriptions
8) Save
9) Test Join in Query Builder
10) If the new Join is also required in the Web Channel then SharperLight Service will need to be restarted so that it will be loaded
Check permissions in Site Setup
In Query Builder Select the location where the Join will be inserted and Right Click select Customize – Add Join
In the Custom Join Window you will need to at least set the Join to Table and SQL Join, tho the Code and Descriptions usually need attention too. The Code uniquely identities the Join when there are more than one Join already present to the target Table. In these cases Code is usually set as a number for example 2 , 3 , 4 etc.
Select the Join To Table Lookup button in the Property Grid and tick the Target Table
Select SQL Join Lookup button in the Property Grid and Select the Columns to link up one by one using the From Table side then the Table to Join to side. The SQL will be automatically generated when you select new columns. Once you are happy with the SQL you can either select OK or select Add where upon you can further customize the SQL Join. The {_Join.Alias} and{_Table.Alias} tags are important and are set by the engine at runtime to uniquely identify the Tables. E.g. J001 or J002 etc.
Set the Code, Description and Extended Description Properties if required
Save the Custom Join by select OK and then Test it in Query Builder. Notice that The Custom Join appears as Blue text so that you can identity what is a Custom Join and then Edit it again to fix any errors.
Preview a Test Query and select the Information Icon – Detailed Query Information to view the SQL generated by the engine. If you have selected fields from your Custom Join you should see the Join appear in the SQL statement. If you cannot see the SQL statement please check your permissions in Site Setup as this is also control by these settings and can be suppressed
