If you cannot select the desired field for a SalesLogix Template using standard field selection and local joins, you can use SQL to select exactly what is wanted. This will normally only be necessary when a double reference to the same table is required, such as trying to include the name of the Manager of a User. The procedure to use to accomplish this double reference to the USERINFO table follows.
1. Add a new template or edit an existing template.
2. Position the cursor in the template where you want to add the “User Manager” field.
3. To to the “Insert Special Field” menu and select the “Text” SQL option.
4. In the “Field Identifier” field add a unique name, “User Manger” in this example, and click the ellipses button.
5. Enter the SQL statement that will select the desired field and click OK to save. Here is the script for our example:
select u2.username from userinfo u1
inner join usersecurity s1 on u1.userid=s1.userid
inner join userinfo u2 on s1.managerid=u2.userid
6. The new field will now appear highlighted. Click OK to add the field to the template.