Creating SQL Tables

To create a SQL table:

  1. In the SQL Tables form, select Actions > New and specify the table name
  2. Verify that the schema is dbo.
  3. If you want the table to include a SiteRef column, allowing different data for different sites, select the Multi-Site field.
    Note: If you select this option, these additional steps are needed:
    • All primary and unique keys for the table, any non-unique indexes should contain the SiteRef column. See Specifying Primary Keys and Other Constraints for a Table.
    • In the Application Schema Tables Metadata form, select this table and specify a name in the View Name field.

      Then use the View Management form to generate a view over the table. (The standard convention is to name multi-site tables so they end in _mst, and then use the table name without _mst for the view name.)

    • Build an IDO over this view to prevent one site’s data from being seen in other sites.
  4. Save the record.
  5. Click Columns.
  6. In the SQL Columns form, add columns for the new table and define metadata about the columns such as the data type, length, and default value (when applicable to the data type).
  7. Save the columns and return to the SQL Tables form.
  8. Click New Constraint to open the SQL Tables Constraint form and define one or more primary keys or other constraints for the table.

    See Specifying Primary Keys and Other Constraints for a Table.

  9. To save the constraint and return to the SQL Tables form, click OK.

After you create tables or columns, you can create IDOs, IDO extension classes, or events that use the tables and columns. You can also filter for a table in the SQL Tables form, and alter the columns and other attributes.

Note:  This application requires certain columns on tables that it uses. If you import a table into your database, you can filter for it in the SQL Tables form, and then click Update Current Table to add those required columns.
Related topics