Table of Contents

Define Custom Tables in Aeries

Define Fields

Custom Table Security

Access the Custom Page

Custom Tables without Aeries Navigation Location


Aeries allows customers to create custom student-related tables in the Aeries SQL database and provides an interface for data from those tables to be displayed in the program. Aeries supports one-to-one and one-to-many student-related tables. A one-to-one student related table has one record per student (similar to supplemental, district supplemental, etc.), whereas one-to-many tables can have multiple records per student (similar to counseling, visitations, retentions, etc.). This article will explain how to create and update custom tables.


Custom tables for other purposes may also be defined in Aeries. See the section 'Custom Tables without an Aeries Navigation Location' below.


Create the Table in SQL

First, the new table must be created by the Aeries database administrator. Detailed instructions on creating tables are not provided here as a qualified DBA is expected to be familiar with the steps, but sample SQL scripts are provided. There are, however, some important guidelines that must be followed for the table to be supported by the Aeries application.

  • The table name must be 4 characters in length. It must also not begin with a number and follow other Microsoft recommendations. The table name should not be the same as any 4 character SQL keywords such as 'TRAN' or 'DROP'. It is recommended that table names contain only A-Z characters for consistency.
  • The primary key of the table must be either (ID) or (SC,SN) for a one-to-one table. If the primary key is (ID), the table should have a foreign key relationship to the IDN table. If the primary key is (SC,SN), the table should have a foreign key relationship to the STU table.
  • The primary key of the table must be either (ID,SQ) or (SC,SN,SQ) for a one-to-many table. If the primary key is (ID,SQ), the table should have a foreign key relationship to the IDN table. If the primary key is (SC,SN,SQ), the table should have a foreign key relationship to the STU table.
  • For the table to work properly with Aeries Query, and potentially other areas of Aeries, column names must be 2-4 characters in length.
  • The table must have a column named DEL of type bit.
  • It is recommended that the table have a column named DTS of type datetime; however, this column is optional. If included, the last updated date/time will be displayed on the custom table page. If the DTS column is present, the table should have a trigger to update the DTS column whenever a row is inserted or updated.
  • Every column except datetime columns should be non-nullable and have an appropriate default value*: 0 for bit and all numeric types, '' (empty string) for all character string types. *Datetime columns cannot have a default value specified.
  • SQL Views may be used in place of tables; however, the same guidelines for tables above should be applied, including having a DEL column. Note that although a SQL View does not have a primary key or foreign keys, the columns that would normally make up the primary key must be included in the View, and the appropriate options should still be selected on the Define Custom Tables page as if there were a primary key and foreign keys.
  • The columns names ID, SC, SN, SQ, DEL, and DTS are reserved names and cannot be used for anything other than their programmed purpose. For instance, if a table is ID based, other fields such as SC and SN cannot be used. Alternative names such as SCL instead of SC can be utilized.


It may be helpful to use the "Script as" feature of SQL Server Management Studio to generate creation scripts for the table, primary key, foreign key constraint, default constraints, DTS trigger, etc.

  • For IDN-related tables, use the LAC table as a one-to-one example or the FRE table as a one-to-many example
  • For STU-related tables, use the SSD table as a one-to-one example or the ATN table as a one-to-many example


The following data types are compatible with custom tables:

  • character string types (varchar or nvarchar)
  • numeric types (tinyint, smallint, int, bigint, or money)
  • datetime
  • bit


Below are sample scripts provided for illustrative purposes only. The scripts encompass all of the guidelines described above.


This script creates a one-to-one table related to STU with primary key SC,SN.

CREATE TABLE [dbo].[XTBL](
  [SC] [smallint] NOT NULL DEFAULT (0),
  [SN] [int] NOT NULL DEFAULT (0),
  [C1] [int] NOT NULL DEFAULT (0),
  [C2] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL DEFAULT (''),
  [C3] [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL DEFAULT (''),
  [C4] [datetime] NULL,
  [C5] [bit] NOT NULL DEFAULT (0),
  [C6] [varchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL DEFAULT (''),
  [C7] [varchar](3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL DEFAULT (''),
  [C8] [datetime] NULL,
  [C9] [money] NOT NULL DEFAULT (0),
  [DEL] [bit] NOT NULL DEFAULT (0),
  [DTS] [datetime] NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[XTBL] WITH NOCHECK ADD 
  CONSTRAINT [PrimaryKey_XTBL] PRIMARY KEY  CLUSTERED 
  (
    [SC],
    [SN]
  )  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[XTBL]  WITH CHECK ADD  CONSTRAINT [FK_XTBL_STU] FOREIGN KEY([SC], [SN])
REFERENCES [dbo].[STU] ([SC], [SN])
ON UPDATE CASCADE
ON DELETE CASCADE
GO

ALTER TABLE [dbo].[XTBL] CHECK CONSTRAINT [FK_XTBL_STU]
GO

CREATE TRIGGER [dbo].[XTBL_update_DTS] ON [dbo].[XTBL] FOR UPDATE, INSERT AS IF EXISTS(SELECT INSERTED.DTS, DELETED.DTS FROM INSERTED, DELETED WHERE INSERTED.DTS=DELETED.DTS) OR (NOT EXISTS(SELECT DELETED.DTS FROM DELETED) AND EXISTS(SELECT INSERTED.DTS FROM INSERTED WHERE INSERTED.DTS IS NULL)) UPDATE XTBL SET XTBL.DTS = GetDate() FROM XTBL, INSERTED WHERE XTBL.SC = INSERTED.SC AND XTBL.SN = INSERTED.SN
GO


This script creates a one-to-one table related to IDN with primary key ID.

CREATE TABLE [dbo].[XTBL](
  [ID] [int] NOT NULL DEFAULT (0),
  [C1] [int] NOT NULL DEFAULT (0),
  [C2] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL DEFAULT (''),
  [C3] [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL DEFAULT (''),
  [C4] [datetime] NULL,
  [C5] [bit] NOT NULL DEFAULT (0),
  [C6] [varchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL DEFAULT (''),
  [C7] [varchar](3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL DEFAULT (''),
  [C8] [datetime] NULL,
  [C9] [money] NOT NULL DEFAULT (0),
  [DEL] [bit] NOT NULL DEFAULT (0),
  [DTS] [datetime] NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[XTBL] WITH NOCHECK ADD 
  CONSTRAINT [PrimaryKey_XTBL] PRIMARY KEY  CLUSTERED 
  (
    [ID]
  )  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[XTBL]  WITH CHECK ADD  CONSTRAINT [FK_XTBL_IDN] FOREIGN KEY([ID])
REFERENCES [dbo].[IDN] ([ID])
ON UPDATE CASCADE
ON DELETE CASCADE
GO

ALTER TABLE [dbo].[XTBL] CHECK CONSTRAINT [FK_XTBL_IDN]
GO

CREATE TRIGGER [dbo].[XTBL_update_DTS] ON [dbo].[XTBL] FOR UPDATE, INSERT AS IF EXISTS(SELECT INSERTED.DTS, DELETED.DTS FROM INSERTED, DELETED WHERE INSERTED.DTS=DELETED.DTS) OR (NOT EXISTS(SELECT DELETED.DTS FROM DELETED) AND EXISTS(SELECT INSERTED.DTS FROM INSERTED WHERE INSERTED.DTS IS NULL)) UPDATE XTBL SET XTBL.DTS = GetDate() FROM XTBL, INSERTED WHERE XTBL.ID = INSERTED.ID
GO


This script creates a one-to-many table related to IDN with primary key ID.

CREATE TABLE [dbo].[XTBL](
  [ID] [int] NOT NULL DEFAULT (0),
  [SQ] [smallint] NOT NULL DEFAULT (0),
  [C1] [int] NOT NULL DEFAULT (0),
  [C2] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL DEFAULT (''),
  [C3] [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL DEFAULT (''),
  [C4] [datetime] NULL,
  [C5] [bit] NOT NULL DEFAULT (0),
  [C6] [varchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL DEFAULT (''),
  [C7] [varchar](3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL DEFAULT (''),
  [C8] [datetime] NULL,
  [C9] [money] NOT NULL DEFAULT (0),
  [DEL] [bit] NOT NULL DEFAULT (0),
  [DTS] [datetime] NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[XTBL] WITH NOCHECK ADD 
  CONSTRAINT [PrimaryKey_XTBL] PRIMARY KEY  CLUSTERED 
  (
    [ID],
  [SQ]
  )  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[XTBL]  WITH CHECK ADD  CONSTRAINT [FK_XTBL_IDN] FOREIGN KEY([ID])
REFERENCES [dbo].[IDN] ([ID])
ON UPDATE CASCADE
ON DELETE CASCADE
GO

ALTER TABLE [dbo].[XTBL] CHECK CONSTRAINT [FK_XTBL_IDN]
GO

CREATE TRIGGER [dbo].[XTBL_update_DTS] ON [dbo].[XTBL] FOR UPDATE, INSERT AS IF EXISTS(SELECT INSERTED.DTS, DELETED.DTS FROM INSERTED, DELETED WHERE INSERTED.DTS=DELETED.DTS) OR (NOT EXISTS(SELECT DELETED.DTS FROM DELETED) AND EXISTS(SELECT INSERTED.DTS FROM INSERTED WHERE INSERTED.DTS IS NULL)) UPDATE XTBL SET XTBL.DTS = GetDate() FROM XTBL, INSERTED WHERE XTBL.ID = INSERTED.ID AND XTBL.SQ = INSERTED.SQ
GO




Define Custom Tables in Aeries  ↑

The remainder of the setup is done in Aeries using the Define Custom Tables page. The page is located under School Info | Configurations in the navigation menu.


Only an Aeries Administrator can access the Define Custom Tables page.


After loading the page, click Add to get started.


The following form will display. Fill in all fields, then click Save.


  • Table Code - Enter the name of the table that was created in SQL (e.g., "XTBL").
  • Name - Enter the display name for the table (e.g., "My First Custom Table"). This name will display in the navigation menu and as the page title when the custom page is viewed in Aeries.
  • Is SQL View? - Check this box to designate this custom table as a SQL View, which will cause the custom form to be read-only.
  • Aeries Navigation Location - Select the node where the table should be located on the navigation menu.Typically, this should be either Student Data or a sub-node of Student Data.
  • Primary Key Fields - Select either ID or SC,SN for one-to-one tables, or ID,SQ or SC,SN,SQ for one-to-many tables depending on the primary key of the table.
  • Parent Table - Select either IDN or STU depending on the foreign key relationship of the table. Tip: ID-based tables must always be related to IDN in the database itself; however, entering STU as the parent table on this form is acceptable for ID-based tables, and will allow users to query the table without the need to include IDN in Aeries queries.
  • Parent Fields - Select the appropriate choice based on the foreign key relationship of the table.
  • Relationship Type - Select either One-to-One or One-to-Many
  • Order By - Enter a custom sort order for the table. The value entered should be the SQL syntax that would come after the ORDER BY keyword


The saved custom table definition is saved in the Custom Table Definitions (CTD) table in the database. A list of all custom tables that have been defined using this page will display in the drop-down list at the top.


Remember, the table must already be created in SQL before adding the Custom Table Definition to Aeries. An error message will display if the table does not exist.


Define Fields   ↑

After the table definition has been created, next the fields need to be defined. Click Add to add fields. Fields can be defined and line separators can be added to create new lines when student records are viewed or edited on the custom table page.


The following field types are available

  • Field Code - Enter the column name from SQL.
  • Title - Enter the display name for the field. This name will display as the field label when the custom page is viewed in Aeries. Note: The CTF.NM field has a maximum of 255 characters but due to SQL limitations, if the field title exceeds 128 characters it will not be able to be queried. 
  • Type - Enter the type of data. The options are described below.
  • Display Width - Enter a numeric value representing the number of pixels wide the field should be when displayed in Edit Mode. A rule-of-thumb for this value is that each character takes up about 8 or 9 pixels.
  • Max Length - For character string fields, enter the maximum length of the column in SQL. (e.g., for nvarchar(50), enter 50). For Memo fields (e.g., nvarchar(max) in SQL), enter the number of rows of text to display in Edit Mode. Note: For numeric data types, Max Length is not required. However, if it is specified, then the field will honor this as a maximum input length, NOT a maximum numeric value. (e.g., to allow only numbers up to 3 digits, enter 3 for the Max Length).


The Type field can have the following values:

  • Text – for varchar and nvarchar fields with a limited length
  • DropDown – for varchar and nvarchar fields that will have drop-down list values from the COD table
  • Date – for datetime fields where the Date part should be displayed and entered. Aeries will display a date picker when in Edit Mode.
  • Time – for datetime fields where the Time part should be displayed and entered. Aeries will display a time picker when in Edit Mode.
  • Memo – for SQL nvarchar(max) fields. Aeries will display a multi-line text input in Edit Mode, using the Max Length field to determine the number of rows to display.
  • Number – for all numeric data types
  • Boolean – for bit fields. This will display a checkbox in edit mode, or Yes/No in read mode.
  • Phone – for varchar and nvarchar fields that should store a phone number. Aeries will display a phone number input mask (___) ___-____ when in Edit Mode.


Click Add Line Separator to add a NEW LINE placeholder. Each NEW LINE placeholder will force the next field to display on a new line when the custom page is viewed in Aeries.


Repeat these steps until all needed fields and all desired new lines have been added. The field definitions are stored in the Custom Table Fields (CTF) table in the database.


Click Sort Fields to display an interface that allows drag-and-drop sorting of existing fields and line separators. When the desired sort order is achieved, click Save Sort Order to save the changes.



The column must already exist in the database before a Custom Table Field can be added using this page. An error message will display if the column does not exist.


Custom Table Security   ↑

Once the custom table has been defined, it will display as an available security area on the Permissions page for Users, Groups, and Portal Groups under the heading Custom Tables. The Read, Update, Insert, and Delete permissions are applicable to custom tables.


Access the Custom Page

Now that the custom table and its fields have been defined, the custom page will appear in the navigation menu. In the example below, the Student Data | Guidance node was selected. Notice that the custom page displays at the bottom of the list for that node.


Below is an example of a custom page in Read Mode. This example illustrates a one-to-many custom table (multiple records are allowed). One-to-one tables display similarly, except only a single record is allowed to be added or edited. Only fields with data populated display.



Below is an example of Edit Mode. The user clicks the pencil icon and an edit window displays. This is similar behavior to other pages in Aeries.



Custom Tables without an Aeries Navigation Location   ↑


Custom tables may be created for other purposes that do not need to appear in the Aeries Navigation menu. These tables will not have the same limitations as tables that will be accessible by users.  These table names may be up to 50 characters and columns do not need to be defined. The Aeries Navigation Location should be left blank. The table will roll forward, but not be visible within Aeries. Also, a DEL column is not required for these tables. When selecting a blank Navigation Location, none of the subsequent options will be required.