Use lookup queries for troubleshooting in TIER
Overview
Use lookup queries to see which SQL queries are being loaded at every step of the TIER form.
Details
Lookup queries are useful for troubleshooting forms that will not open, listviews that do not populate, or any other function in TIER that utilizes the TIER Lookup control.
When a document is opened in TIER a number of lookups will execute select statements assigned to them in the lookup properties. For example, when you open the Client facesheet the lookups on the facesheet will query a number of tables, such as FD__Clients. However, when the lookup reaches the table it may find that a field requested in the query isn't in that table, or it's possible the table name was incorrectly written into the SQL portion of the lookup during the design process.
One result of this issue is that when the form is opened it quickly closes without user interaction. Since this issue typically occurs because of a 'bad' lookup query statement; one way to quickly identify the offending lookup is to turn on the Show Lookup Queries option in your workstation properties.
Tips
- Running lookup queries will open a View Memo with every query that is being called, so only turn this feature on right before you need it.
- Each lookup query can be copied and pasted into the SQL Query Analyzer to evaluate the data that is returned. If you are receiving too much data, or too little data, or no data, using lookup queries allows you to test the results of each query.
- You can save each lookup query as you go; selecting Close will move you to the next lookup. The last lookup to execute before the form crashes is the offending lookup. You can take that query and run it in the SQL Query Analyzer to help determine where the problem might be.The problem might simply be incorrect SQL syntax or possibly an improperly qualified table name or even an attempt to look for a field that doesn't exist in that table.
Steps
- From the TIER treeview, right-click on This Workstation and select Properties.
- On the Properties menu, select the Security/Debug tab and click the Show Lookup Queries check box. Click OK.
Once the Lookup Queries have been made available, the lookups behind all of the list views will show the SQL queries running in the order executed.
Example 1
Search for a client with the last name of Test. This query will look for any clients with that last name.
Close the ViewMemo box and the next query will appear. This query searches for accounting information for all clients with a last name of Test. This query will continue to run to load payment and balance information.
All of this processing occurs before you see the listview of clients that match the search type.
Example 2
Perhaps you are creating a new listview in a form which displays the staffkey, full name, and hire date for currently employed staff. You have created the lookup (LU_StaffHireDate), which joins the two tables FD__Staff and FD__Hiredates and filters out terminated and non-active staff.
After creating the TierLookup and the TierListView controls, you test your form and receive the following error message.
Turning on lookup queries shows this result.
Looking at the SQL statement you can see that staffkey is selected, but there are two staffkeys to choose from. Do you want to display the staffkey from FD__Staff or the staffkey from FD__ Hiredates? In this instance we want the staff key from the FD__Staff table, so you would open the form in Design View and make that change.
select FD__Staff.staffkey, fullname, Hiredate
from FD__Staff inner join FD__Hiredates
on FD__Staff.staffkey = FD Hiredates.staffkey
where TerminationDate is null and HireStatus = 'A'
