Creating SQL tables

To create SQL tables, use the Sql Tables form.
  1. In the Sql Tables form, execute Filter-In-Place and initiate a new table.
  2. Verify that the Schema is dbo.
    Note: We do not currently support the building of IDOs over tables in schemas other than dbo at this time.
  3. Specify the Table Name.
  4. Optionally, to include a SiteRef column, which allows for different data for different sites, select the Multi-Site option.
    Note: If you select this option, these additional requirements apply:
    • All primary and unique keys for the table, and any non-unique indexes must 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.

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

    • Build an IDO over this view to prevent one site’s data from being seen in other sites.
  5. Save the record.
  6. Click Columns.
  7. In the Sql Columns form, add columns for the new table and define metadata for the columns.
    This metadata can include specifications such as the data type, length, and default value (as applicable to the data type).
  8. Save the columns and close the Sql Columns form.
  9. Back in the Sql Tables form, to open the Sql Tables Constraint form and define one or more primary keys or other constraints for the table, click New Constraint.
    See Specifying Primary Keys and Other Constraints for a Table.
  10. To save the constraint and return it to the Sql Tables form, click Finish.

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 modify the columns and other attributes.

Note:  This application requires certain columns on tables that it uses. To add those required columns to your table, import the table into your database. You can then filter for it in the Sql Tables form, and click Update Current Table, which action adds those required columns.
Related topics