Create a dynamic view

Dynamic views are essentially joins which replicate SQL views. You can use dynamic views in statements to access your business data.

1. On the Navigation toolbar, select Data Functions .
2. In the Data Functions pane, select a connection.

   The join matching options are displayed.

3. If you want the system to create joins automatically when possible, select either of the following check boxes:
   • Use Foreign Key Matching — if each table comprising the connection includes columns that are designated as foreign key columns, use the columns to create a join linking the tables.
   • Use Name Based Matching — if each table comprising the connection includes columns with the same name, use the columns to create a join linking the tables.
4. Click Create New, and choose one of the following:
   • View — create a virtual table using columns from one or more tables.
   • Union — return results from two or more queries, removing duplicate records.
   • Except — compare results from two or more queries, returning records only when they are available from the first query but not from the other queries.
   • Intersect — compare results from two or more queries, returning records only when they are available from all of the queries.

   The view is added to the connection.

5. In the view properties, provide values for the following:

   For these view types	Do this
   View and union	Provide the following values: Name — type a name for the view. Table Joins — (Views only) If you need to create table joins for use with the view, select the Create Joins button and create the necessary joins. Columns — specify one or more columns. If necessary, also adjust the following properties: Conditions — specify any conditions that must be met to return a value. Sort By — filter the data to narrow the scope of the query. Distinct Records — return unique records only. Only The First __ Records — return a specified number of records only. See Statement Editor Functions for information on the available functions and how to create statements. If your view has joins defined within it, see Access a visual join from the Statement Editor.
   Except and intersect	Provide the following values: Name — type a name for the view. Data Sources — specify the number of data sources that you want to include in the view. Columns — specify the columns for each data source. If necessary, on the Advanced tab, also adjust the following properties for each data source: Conditions — specify any conditions that must be met to return a value. Sort By — filter the data to narrow the scope of the query. Distinct Records — return unique records only. Only The First __ Records — return a specified number of records only. See Statement Editor Functions for information on the available functions and how to create statements.

6. Click the Preview tab to quickly validate that your dynamic view was set up correctly. You can view the results as a data table (the Data tab), or as SQL code (the SQL tab).
   • If your view uses parameters, enter the appropriate parameter values in the "Set Initial Parameter Values" dialog when prompted.
7. If necessary, return to the Configure tab to update your view.
8. Click Save.