Build a SQL Where Clause Search

Build SQL Where Clause searches to find records or compare data across multiple hubs, applications, and databases.

To build a SQL Where Clause search:

1. Open the Search dialog box:
   • From the Saved Search control () at the top of the active hub or application form, click to display the Saved Searches and Record Selection panes. At the bottom of the Saved Searches pane, click + New Search to display the New Search dialog box.
   • From a lookup list, click Search. The New Search dialog box displays and includes a list of the records related to the lookup. Click the More Search Options link to open the New Search dialog box with the Search Criteria grid.
   • From the Reporting application, click the Reports tab and then click on a report row in the Reports grid. The Saved Searches list appears. Click and select the Search option from the Searches list. The New Search dialog box displays and includes a list of the records related to the report.
   • From the Reporting application, click the Favorites tab and then select a favorite report in the list to run it.
2. Select the SQL Where Clause fields and tokens:
   1. To make options for SQL Where Clause searches available, select the Advanced Settings toggle.
   2. On the Actions bar, click Other Actions and then select SQL Where Clause.
   3. On the SQL Where Clause dialog box, click Select Search and then select a SQL Where Clause search.
      The SQL Where Clause statement displays in the text field.
   4. Edit the SQL Where Clause statement using tokens and clauses.
   5. Click the Check SQL action to run and validate the SQL Where Clause syntax.
      If the statement contains invalid syntax, you receive an error message that briefly describes the problem.
3. If you do not see a list of matching results, click Show Preview to display all records matching the criteria in the Search Results grid.
   These records also display when you use the Select Searches List on the main form for a hub record or in the Records or Saved Searches list on the Reports tab or form for a specific report.
4. To refine search results, select one of two actions:

   Goal	Description
   Include all records that match the search criteria	In the Search Results list, click the Apply All button. Although all the record checkboxes display as cleared, all records in the Search Results list are automatically included in the search.
   Select a subset of records	Select each record that you want included in the search and then click Apply Selected (#) to include only those records that match the search criteria. Records for which you do not select a corresponding checkbox are not included in the record selection lists of the hub or application associated with the records.

   All records applied to the search are also available for export to a comma-separated values (.CSV) file.
5. To change the display of the Search Results grid, click one of the following options on the toolbar of the Search Results grid:

   Option	Use this option to:
   	Expand the grid to full screen, so that you can more easily view long lists of search results.
   	Export all or a subset of search results to a comma-separated values ( .CSV) file. To include or exclude other records, select a new subset of records and apply them to the search.
   	Filter the search results based on the values that you select in one or more columns.

6. Save or cancel your work:
   • To save a validated SQL Where Clause search, on the Actions bar click Save Options and then complete the Save Options dialog box. Choose Save to update an existing search, or Save As New Search to save a new search. Vantagepoint adds the search to the designated folder in the Select Searches list.
   • To cancel, click Cancel. Any changes that you made are reverted and the original expressions used in the SQL Where Clause search are restored.
7. To allow other users access to this search, click in the Save For field.
   Depending on your search security rights, you can save for everyone (all security roles), your role, or specific security roles. If you do not have the security rights to save for others, then you can save searches only for yourself.

   CAUTION: If you share a SQL Where Search with a user who does not have the required security role access, then that user can run the SQL Where Clause search, but cannot edit it.
8. To add the search to one of the folder directories in the Select Searches list, click in the Add This Search To field and complete the Folder Options dialog box.
   You can select an existing folder directory from the list or click + New Folder to create a new folder directory for the search.
9. To execute the search, click Apply All or Apply Selected (#).
   You are returned to the main form of the application and the search records are included in the Find field of the appropriate hub or application.

   Depending on how you accessed the Search dialog box, you may be able to select either a single record or multiple records from the search list to return either one record or a subset of records.