TABLE OF CONTENTS

Security

Decisions to make before starting

Adding Fields in SQL Server Management Studio

Insert or Delete a field

Change the field order

The Hidden field

Pull-Down Menus

When done adding a field or fields


The Supplemental and District Supplemental pages are based on the SUP  and DSD tables. Users can add fields directly to the table in SQL and they will appear on the Supplemental or District Supplemental pages in the order they show in SSMS (SQL Server Management Studio) Design mode.

 

The pages are found under Student Data.


Note: Not all Hosted plans include the ability to create/edit tables. Please create a Support ticket for help making these changes if you do not have this access.


Security

Only SQL users can directly modify the SUP and DSD table. For viewing, entering and changing data using the Supplemental and District Supplemental pages, users will need the appropriate permissions to the SUP and DSD tables in User/Group Security.


Note: The following information uses the SUP table as an example, but the DSD table follows the same procedure.


Things to remember

  • Only one person at each site should be responsible for adding fields (columns).
  • All changes should be made through SQL Server Management Studio in a test copy of your SQL database and ONLY placed into the live database when ready to implement.
  • The Supplemental form is created “on the fly” each time you load it.  The more fields in the table the longer it will take the form to load. 
  • You will have to log out and log back in to see the changes and may have to wait for caching to refresh.


Decisions to make before starting:

What kind of data will be entered in the new field?  

These are the available SQL data types supported by Aeries:


Data TypeDescriptionLength
varchar (n)Text maximum 255
varchar(MAX)Textmaximum 8000
nvarchar (n)Text and Unicodemaximum 255
nvarchar(MAX)Text and Unicodemaximum 8000
smallintInteger5 digits up to 32,767
intInteger10 digits up to 2,147,483,647
moneyInteger15 digits, 4 decimals
bitTrue/False (1/0)1
datetimedate and time. Time is not supported8-  mm/dd/yyyy


NOTE:   Other data types are NOT supported.


What size will the field need to be?  

You should use the smallest possible field size since smaller data

sizes can be processed faster and require less memory.


•    If the data type is set to varchar or nvarchar, enter a number from 1 to 255 or (MAX)


What will be the field Description (caption)?  

The Description will be the label that appears on the Supplemental form.  To avoid confusion, do not use a Description that is already being used on the Supplemental form.

  • You can change the Description/label name at any time. However, if you currently have data in the table you would want to make sure the Description matches the data/datatype


What column name (code) will I give the new field?  

The field name will be the code used when you query information from this field.  Use a 2-4-letter name.  A number can be used in the field name. 

  • Do NOT use ID or PID as a column name as Aeries treats those differently

 

NOTE: The following information applies to Client version only.

 A field name in the SUP table MUST start with a letter.  DO NOT use a Number.  If the first character is a number, problems will occur with Aeries and Access queries.  Also, field names should be two or three characters and should not be a reserved word (that would confuse Query), like “OR”, “AND” or “BY”.   The Macro "Check If Table or Field Name is Already in Use" will indicate if a field name is a reserved word.


Adding Fields in SQL Server Management Studio


Before making modifications to the table you may have to enable saving table changes in SSMS when data already exists


Tools > Options > Designers > Table and Database Designers > Prevent saving changes that require table re-creation

Uncheck the box.




Locate the SUP table in your database and right-click to open the Design mode. 


 


IMPORTANT: Be sure to SAVE after making your changes.


Insert or Delete a field

Start by placing your cursor in the last blank Column Name and enter the column/field name.


 


  • Type your Column Name in upper case letters.  This is the field name that will appear in Query.
  • Tab to the Data Type column. Use the pull down arrow to select which type the field will be (remember to select a type supported by Aeries!).
  • Tab to the Allow Nulls column.  De-select "Allow Nulls" for all data types except datetime.


The Column Properties box displays at the lower half of the screen.  This will look a little different for each Data Type. Below are samples of the Column Properties for each Data Type.


VARCHAR or NVARCHAR


  • Allow Nulls should be set to No.
  • Default Value or Binding – Enter two single quotes ('')
  • Length – Change to the size needed for the data that will be entered.
  • Description – Whatever is typed here displays as the field heading on the Supplemental form.  Try to fit the heading to the size of the field if possible.
  • Leave all other options as the default settings.     


MONEY, INT, SMALLINT, BIT



  • Allow Nulls should be set to No.
  • Default Value or Binding – Enter ((0))

Note - Bit fields can also default to True ((1))

  • Description – Whatever is typed here displays as the field heading on the Supplemental form. Try to fit the heading to the size of the field if possible.
  • Leave all other options as the default settings.     


DATETIME



  • Allow Nulls should be set to Yes.
  • Default Value or Binding – leave blank.
  • Description – Whatever is typed here will appear as the field heading on the Supplemental form. 
  • Leave all other options as the default settings.  

     

DELETE a Field


Click on the triangle next to the column name, right-click and choose Delete Column




Change the field order


While in the Design form you can move fields by first highlighting the row(s).  Next with the mouse pointer

pointing to the black triangle to the left of the field, hold down the left mouse button and drag the row(s) to the new position.  A solid black line will show where you are moving the field to.



Cut, copy and pasting fields

You can also use the Cut or Copy and Paste method to move fields.  When using Cut you will get the message asking if you want to delete the field(s).  If you say Yes and don’t Paste them in another location they will be deleted!  There is an Undo, but it only restores a certain number of actions. 


The Hidden field

You can define Text fields (varchar, nvarchar) with the Description "Hidden".  Fields with this Description will not

show on the Supplemental form, but will allow you to space fields at certain lengths from each other or force a field(s) to

go down to the next line.  You define this field just like any other text field.  You may need to experiment with

this field to get the spaces right. You can have more than one Hidden field, but the Field Name must be

different (suggestion: H1, H2, etc.). 


Pull-Down Menus

If you want any fields to have a drop down list, use the Update Code Table to enter a list of values for that field (SUP.xxx). After entering data, fields in the Supplemental form will automatically have the drop down the next time the page is visited.

Note:  Adding pull down lists may cause your fields to be shifted over and you may need to go back into the Design form to make adjustments.


Note:  Date fields do not allow pull-down menus.


When done adding a field or fields:

  • IMPORTANT: Be sure to SAVE after making your changes.
  • Check all entries carefully!
  • Log into Aeries.
  • Go to the Supplemental page and check the new field or fields you just added. Enter some data to be sure it will accept it.