Designing User Forms

User forms allow authorised users to customise and extend the Synergetic Database Management System. The User Form Designer should only be used when the required functionality is not supplied in the current version of Synergetic.
Note: We recommend that you consult with Synergetic Management Systems prior to creating a new user form to ensure that Synergetic Management Systems are not currently developing similar forms. Ownership of a user form lies with your organisation and as such Synergetic Management Systems accepts no responsibility for data or design issues that arise from their in-house creation.

Fig 1. Student Boarding Details form
This tutorial presents the creation of a Student Boarding Details user form (see Figure 1). This form allows organisations to record useful information for each boarding student. Information such as boarding house and room number, bicycle and vehicle details and permission details can be recorded. Whilst the instructions and the coding used in this tutorial are specific to the creation of this form, the processes and logic used can be followed to create new user forms.
The creation of a user form can be divided into two main steps:

1. Creation of user database table (back-end).
2. Creation of the user form (front-end).

Note: A careful and detailed conceptual design should be conducted before any physical design and coding is commenced.
Creation of user database table (back-end)
Before a user form can be defined, a user database table must be first created and security rights must be granted for it. This is done by following the steps detailed below in this section. SQL statements should be typed into and executed in Microsoft SQL Management Studio or other appropriate ODBC linked query programs.
Note: Caution must always be taken to run the correct script on the correct database. Synergetic has a separate database for community, finance and media!
SQL CREATE TABLE statements must be designed and constructed for all tables required by the user form. For the Student Boarding Details form, you need to create a primary table to contain all the relevant data fields to be stored by the database and accessed by the form.
The CREATE TABLE statement involves the following steps:

• Naming the database table. All User tables must be prefixed with the letter u.
• All database fields must be named and described with appropriate data types and data lengths. A list of MS SQL Server data types are supplied in this document.
• Creation of column constraints can be defined for each database field. CONSTRAINT is an optional keyword indicating the beginning of a PRIMARY KEY, NOT NULL, UNIQUE, FOREIGN KEY or CHECK constraint definition. Constraints are special properties that enforce data integrity and create special types of indexes for the table and its columns.
• Indexation. Primary Key and Alternate Key can be placed on table fields to make queries run more efficiently. A primary key must be created for each user table.
• The GRANT statement must be executed to create an entry in the security system that allows a user in the current database to work with data in the newly created table.

A user lookup table, uluPermissionBeach, is used by the Student Boarding Details user form to provide users with a drop-down list of different permission types. The syntax for the creation of the table is:
_{CREATE TABLE dbo.uluPermissionBeach (
Code VARCHAR(5) NOT NULL CONSTRAINT DF_uluPermissionBeach_Code DEFAULT (''),
Description VARCHAR(30) NOT NULL CONSTRAINT DF_uluPermissionBeach_Description DEFAULT (''),
CONSTRAINT PK_uluPermissionBeach PRIMARY KEY NONCLUSTERED (Code)
)}
A user table, uStudentBoarding, defines database fields to support the fields displayed on the form in Figure 1. The syntax for the creation of the table is:
_{CREATE TABLE dbo.uStudentBoarding (
ID INT NOT NULL,
CommentCreatedDate DATETIME NULL CONSTRAINT DF_uStudentBoarding_CommentCreatedDate DEFAULT (GETDATE()),
CommentCreatedBy VARCHAR(30) NOT NULL,
House VARCHAR(20) NOT NULL CONSTRAINT DF_uStudentBoarding_House DEFAULT (''),
Room VARCHAR(10) NOT NULL CONSTRAINT DF_uStudentBoarding_Room DEFAULT (''),
BicycleBrand VARCHAR(50) NOT NULL CONSTRAINT DF_uStudentBoarding_BicycleBrand DEFAULT (''),
BicycleColour VARCHAR(40) NOT NULL CONSTRAINT DF_uStudentBoarding_BicycleColour DEFAULT (''),
BicycleModel VARCHAR(50) NOT NULL CONSTRAINT DF_uStudentBoarding_BicycleModel DEFAULT (''),
BicycleSize VARCHAR(30) NOT NULL CONSTRAINT DF_uStudentBoarding_BicycleSize DEFAULT (''),
BicycleHelmet VARCHAR(40) NOT NULL CONSTRAINT DF_uStudentBoarding_BicycleHelmet DEFAULT (''),
BicycleSafetyChain VARCHAR(40) NOT NULL CONSTRAINT DF_uStudentBoarding_BicycleSafetyChain DEFAULT (''),
BicycleInsurance VARCHAR(40) NOT NULL CONSTRAINT DF_uStudentBoarding_BicycleInsurance DEFAULT (''),
VehicleLicenseNumber VARCHAR(20) NOT NULL CONSTRAINT DF_uStudentBoarding_VehicleLicenseNumber DEFAULT (''),
VehicleLicenceExpiryDate DATETIME NULL,
VehicleLicenseConditions VARCHAR(30) NOT NULL CONSTRAINT DF_uStudentBoarding_VehicleLicenseConditions DEFAULT (''),
VehicleLicencePlate VARCHAR(10) NOT NULL CONSTRAINT DF_uStudentBoarding_VehicleLicencePlate DEFAULT (''),
VehicleLicenseInsurance VARCHAR(50) NOT NULL CONSTRAINT DF_uStudentBoarding_VehicleLicenseInsurance DEFAULT (''),
PermissionBeach VARCHAR(30) NOT NULL CONSTRAINT DF_uStudentBoarding_PermissionBeach DEFAULT (''),
PermissionActivities TEXT NULL,
CONSTRAINT PK_uStudentBoarding PRIMARY KEY CLUSTERED(ID)
)
GO}
The following stored procedure, sp_bindefault, is used to bind the username of the current Synergetic user to the database field, CommentCreatedBy.
_{EXEC sp_bindefault N'[dbo].[SynergyCurrentUser]', N'[uStudentBoarding].[CommentCreatedBy]'
GO}
The user lookup table, uluPermissionBeach, is populated using the following explicit SQL Insert statements.
_{INSERT INTO uluPermissionBeach (Code, Description)
VALUES ('',' Not Selected')
INSERT INTO uluPermissionBeach (Code, Description)
VALUES ('ACC','Accompanied')
INSERT INTO uluPermissionBeach (Code, Description)
VALUES ('UNA','Unaccompanied')
INSERT INTO uluPermissionBeach (Code, Description)
VALUES ('NP','Not Permitted')}
Finally, the Student Boarding Details form is declared as a Synergetic resource in the ConfigResources table. This step can also be done in the Report Selector window in the User/Report Form Designer of Synergetic.
_{INSERT INTO ConfigResources
(ResourceType, Module, Resource1, Description)
VALUES ('UF','STU','STUBOARD','Student Boarding Details')}

Creation of the user form/interface (front-end)
The creation of the user form is performed in the System module of Synergetic.
If you executed the INSERT INTO ConfigResources statement (see previous) you are able to access the STUBOARD resource on the Report Selector window. Alternatively, you can create this resource on the Report Selector window by completing the Report, Module, Description and ResourceType definitions and clicking save.

Note: The resource type User Form must be selected!

Once this resource exists in the Report Selector, you can click on the Load Source button or double click on the STUBOARD report code in the Report Selector grid, to load the User Form Designer window.
In the User Form Designer window, you are required to enter code to build the User Form/Interface for the database table created earlier.
This is completed in two parts:

• Defining the properties of the user form.
• Defining the control objects and properties for each database field.

Defining the properties of the user form
For the Student Boarding Details user form, the following properties must be defined:

• Query is used to define the database table or function used for the form.
• QueryLink & QueryLinkInSynergy are used to join the user table to a Synergetic base table.
• Height & Width are used to specify the height and width of the form.
• Position property is used to set the position of the user form on the screen when it is opened.
• ShowButton... properties are used to control the buttons which appear at the bottom of the form. For example, ADD, DELETE and APPLY. Note that OK and CANCEL are the default.

Note: Other properties may be defined also. Please Contact Synergetic Management Systems for a list of these.

Note: The correct QueryLink field varies depending on the module being queried. For example, the Students module uses ID, whereas the Debtors module uses DebtorID. For a full list of QueryLink fields, see the UserForm Link Field RefModule table

The code for the Student Boarding Details user form is detailed below:
_{[FORM]
Query="uStudentBoarding"
QueryLink="ID"
QueryLinkInSynergy="ID"
*DesignPixelPerInch=
Width="630"
Height="415"
Position="poScreenCenter"
Caption="Student Boarding Details"
ShowButtonApply}
Note: You can define a database function that returns a table in the Query property. A database function can take additional information in the form of parameters filled out on the user form. You must list each parameter the function requires in the Query property using the following format: "functionName('FirstParam;SecondParam;ThirdParam')" where functionName is the name of the function, and FirstParam, SecondParam and ThirdParam are the names of parameters passed into the function.
Note: The parameters are contained within single quotes and each parameter is separated from the next by a semicolon. Use the QueryLink property to pass a single value to the function, such as the Synergetic ID.
 

Defining the control objects and properties for each database field
The second step involves adding control objects to the form defined in step 1. Please refer to Figure 1 for a guide to how this user form should eventually look.

Note: Each of the field objects must be added sequentially to the form, with its position on the form described relative to its predecessor. The syntax for each code must follow the format below, with no spaces between properties and their values. All values must be enclosed with " " (double quotes).

For each field object the following properties must be defined.

A complete list of object properties that can be used in a user form is listed at the end of this topic.
The following code is used to add objects to the Student Boarding Details user form, as seen in Figure 1.
_{[FIELDS]
Panel,LeftRelRight="15",Caption="",TopRel="5",Width="510",Height="0"
Panel,LeftRelRight="15",Caption="",TopRel="0",Width="0",Height="430"
Panel,LeftRelRight="525",Caption="",TopRel="0",Width="0",Height="430"
Label,Caption="Location Details", LeftRelRight="15",TopRel="5",FontStyle="fsBold"
DBEdit,Datafield="House",TopRel="25", LeftRel="110",Width="150",LabelCaption="House: "
DBEdit,Datafield="Room",TopRel="0", LeftRel="360",Width="150",LabelCaption="Room: "
Label,Caption="Bicycle Details", LeftRelRight="15",TopRel="35",FontStyle="fsBold"
DBEdit,Datafield="BicycleBrand",TopRel="25", LeftRel="110",LabelCaption="Brand Name: ",Width="400"
DBEdit,Datafield="BicycleModel",TopRel="25", LeftRel="110",LabelCaption="Model: ", Width="150"
DBEdit,Datafield="BicycleColour",TopRel="0", LeftRel="360",LabelCaption="Colour: ",Width="150"
DBEdit,Datafield="BicycleSize",TopRel="25", LeftRel="110",LabelCaption="Size: ",Width="150"
DBEdit,Datafield="BicycleHelmet",TopRel="0", LeftRel="360",LabelCaption="Helmet: ", Width="150"
DBEdit,Datafield="BicycleSafetyChain",TopRel="25", LeftRel="110",LabelCaption="Safety Chain: ",Width="150"
DBEdit,Datafield="BicycleInsurance",TopRel="0", LeftRel="360",LabelCaption="Insurance Cover: ", Width="150"
Label,Caption="Vehicle Details", LeftRelRight="15",TopRel="35",FontStyle="fsBold"
DBEdit,Datafield="VehicleLicenseNumber",TopRel="25", LeftRel="110",LabelCaption="License Number: ",Width="150"
wwDBDateTimePicker,Datafield="VehicleLicenceExpiryDate",TopRel="0", Left="360",LabelCaption="Expiry Date: ",Width="150"
DBEdit,Datafield="VehicleLicenseConditions",TopRel="25", LeftRel="110",LabelCaption="License Conditions:", Width="150"
DBEdit,Datafield="VehicleLicencePlate",TopRel="0", LeftRel="360",LabelCaption="License Plate: ", Width="150"
DBEdit,Datafield="VehicleLicenseInsurance",TopRel="25", LeftRel="110",LabelCaption="Vehicle Insurance: ", Width="150"
Label,Caption="Permission Details", LeftRelRight="15",TopRel="35",FontStyle="fsBold"
DBLookupComboBox,Datafield="PermissionBeach",TopRel="25", LeftRel="110",LabelCaption="Beach: ", LookupTable="uluPermissionBeach"
DBMemo,Datafield="PermissionActivities",TopRel="25", LeftRel="110", Width="400", Height="45", LabelCaption="Weekly Activies: "}
Once syntactically correct and complete, the new user form can be tested using the Test button on the User Form Designer. Following this, security rights should be administered to the appropriate users of your new user form. This is done using Group/User Security Maintenance.
Congratulations, you have just completed your first user form!
Student Discipline form example
The following is an example of a user-defined form.

Fig 2. Student Discipline form

Code for Student Discipline
_{[FORM]
Query="uStudentDiscipline"
QueryLink="ID"
QueryLinkInSynergy="ID"
QueryOrderBy="IncidentDate"
QueryPositionLast
*DesignPixelPerInch=
Width="490"
Height="300"
Position="poScreenCenter"
Caption="Student Discipline"
ShowButtonAdd
ShowButtonDelete
ShowButtonApply
[FIELDS]
wwDBGrid,Top="10",Left="10",width="470",height="130",selected="IncidentDate|18",selected="SchoolStaffCode|5",selected="Subject|20",selected="Behaviour|20",ReadOnly
wwDBDateTimePicker,DataField="IncidentDate",Left="80",TopRel="135",Width="140",Enabled="True", LabelCaption="Incident Date:",FirstEdit
label,caption="R.Arrow for time",Left="221",TopRel="2"
wwDBLookupCombo,DataField="SchoolStaffCode",Left="380",TopRel="-2",Width="100",LabelCaption="Staff Code:",LookupQuery="Select distinct SchoolStaffCode;MailNamePrimary from Staff JOIN Community on Staff.ID = Community.ID WHERE SchoolStaffCode <> '' ORDER BY SchoolStaffCode "
DBEdit,DataField="Subject",Height="100",Width="300",Left="80",TopRel="25",LabelCaption="Subject:"
DBEdit,DataField="Behaviour",Height="100",Width="400",Left="80",TopRel="25",LabelCaption="Behaviour:"
DBEdit,DataField="ActionTaken",Height="100",Width="400",Left="80",TopRel="25",LabelCaption="Action Taken:"
DBMemo,DataField="Comments",Height="100",Width="400",Left="80",TopRel="25",Height="60",LabelCaption="Comments:"
DBMemo,DataField="Outcome",Height="100",Width="400",Left="80",TopRel="65",Height="60",LabelCaption="Outcome:"
DBEdit,DataField="CommentCreatedDate",Left="80",TopRel="65",Enabled="False", LabelCaption="Created Date:"
DBEdit,DataField="CommentCreatedBy",Left="300",TopRel="0",Enabled="False", LabelCaption="Created By:"}

Scripts for Student Discipline form
_{CREATE TABLE dbo.uStudentDiscipline               (
Seq int IDENTITY(1,1) NOT NULL,
  ID int NOT NULL,
  CommentCreatedDate datetime NULL CONSTRAINT DF_uStudentDiscipline_CommentCreatedDate DEFAULT (getdate()),
  CommentCreatedBy varchar(30) NOT NULL,
  IncidentDate datetime NULL CONSTRAINT DF_uStudentDiscipline_IncidentDate DEFAULT (getdate()),
  SchoolStaffCode varchar(5) NOT NULL CONSTRAINT DF_uStudentDiscipline_SchoolStaffCode DEFAULT (''),
  Subject varchar(50) NOT NULL CONSTRAINT DF_uStudentDiscipline_Subject DEFAULT (''),
  Behaviour varchar(100) NOT NULL CONSTRAINT DF_uStudentDiscipline_Behaviour DEFAULT (''),
  ActionTaken varchar(100) NOT NULL CONSTRAINT DF_uStudentDiscipline_ActionTaken DEFAULT (''),
  Comments text NULL,
  Outcome text NULL,
CONSTRAINT PK_uStudentDiscipline PRIMARY KEY (Seq)
  ) ON [PRIMARY]
TEXTIMAGE_ON [PRIMARY]
GO
setuser N'dbo'
GO
EXEC sp_bindefault N'[dbo].[SynergyCurrentUser]', N'[uStudentDiscipline].[CommentCreatedBy]'
GO
setuser
GO}

Head of House Comments form example

Fig 2. Head of House Comments form

Code for Head of House Comments
_{[FORM]
Query="uStuHeadOfHouseComments"
QueryLink="ID"
QueryLinkInSynergy="ID"
QueryOrderBy="CommentCreatedDate"
QueryPositionLast
*DesignPixelPerInch=
Width="490"
Height="300"
Position="poScreenCenter"
Caption="Heads of House Comments"
ShowButtonAdd
ShowButtonDelete
ShowButtonApply
[FIELDS]
wwDBGrid,Top="10",Left="10",width="470",height="100",selected="CommentCreatedDate|18",selected="TypeComment|20",selected="SchoolStaffCode|5",selected="CommentCreatedBy|10",ReadOnly
DBEdit,DataField="CommentCreatedDate",Left="80",TopRel="110",Enabled="False", LabelCaption="Created Date:"
DBEdit,DataField="CommentCreatedBy",Left="300",TopRel="0",Enabled="False", LabelCaption="Created By:"
DBMemo,DataField="Comments",Height="100",Width="400",Left="80",TopRel="30",Height="100",LabelCaption="Comments:",FirstEdit
DBLookupComboBox,DataField="TypeComment",Left="80",TopRel="110",LabelCaption="Type Comment:",LookupTable="uluTypeComment"
wwDBLookupCombo,DataField="SchoolStaffCode",Left="300",TopRel="0",LabelCaption="Staff Code:",LookupQuery="Select distinct SchoolStaffCode;Community.MailNamePrimary from Staff JOIN Community on Staff.ID = Community.ID WHERE SchoolStaffCode <> '' ORDER BY SchoolStaffCode "
*DBNavigator,TopRel="30",Left="10"}

Scripts for Head of House Comments
_{CREATE TABLE [dbo].[uluTypeComment] (
Seq int IDENTITY(1,1) NOT NULL,
  [Code] [varchar] (5) NOT NULL ,
  [Description] [varchar] (30) NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[uStuHeadOfHouseComments] (
  [ID] [int] NOT NULL ,
  [CommentCreatedDate] [datetime] NULL ,
  [CommentCreatedBy] [varchar] (30) NOT NULL ,
  [TypeComment] [varchar] (10) NOT NULL ,
  [SchoolStaffCode] [varchar] (5) NOT NULL ,
[Comments] [text] NULL,
CONSTRAINT PK_uStuHeadOfHouseComments PRIMARY KEY (Seq)
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
ALTER TABLE [dbo].[uluTypeComment] WITH NOCHECK ADD
  CONSTRAINT [DF_uluTypeComment_Code] DEFAULT ('') FOR [Code],
  CONSTRAINT [DF_uluTypeComment_Description] DEFAULT ('') FOR [Description],
  CONSTRAINT [PK_uluTypeComment] PRIMARY KEY  NONCLUSTERED
  (
    [Code]
             )  ON [PRIMARY]
GO
ALTER TABLE [dbo].[uStuHeadOfHouseComments] WITH NOCHECK ADD
             CONSTRAINT [DF_uStuHouseMasterComments_CreatedDate] DEFAULT (getdate()) FOR [CommentCreatedDate],
             CONSTRAINT [DF_uStuHouseMasterComments_TypeComment] DEFAULT ('') FOR [TypeComment],
             CONSTRAINT [DF_uStuHouseMasterComments_SchoolStaffCode] DEFAULT ('') FOR [SchoolStaffCode]
GO
EXEC sp_bindefault N'[dbo].[SynergyCurrentUser]', N'[uStuHeadofHouseComments].[CommentCreatedBy]'
GO
insert into uluTypeComment values ('',' Not Selected')
insert into uluTypeComment values ('G','Good')
insert into uluTypeComment values ('O','Observation')
insert into uluTypeComment values ('R','Recommendation')
insert into uluTypeComment values ('N','Negative')
GO
insert into ConfigResources
(ResourceType,Module,Resource1,Description)
values ('UF','STU','STUHOUSE','Head of House Comments')
GO}

Microsoft SQL Server data types
In Microsoft® SQL Server™, each column, local variable, expression and parameter has a data type. Some of the setup of system-supplied data types are shown below.

User forms definitions
_{[FORM] section
  Left
  Top
  Height
  Width
  Caption
  Position=
    'poDesigned'
    'poDefault'
    'poDefaultPosOnly'
    'poDefaultSizeOnly'
    'poScreenCenter'
  FormStyle=
    'fsMDIChild'
    'fsNormal'
    'fsMDIForm'
  Query
  QueryLink
  QueryLinkInSynergy
  QueryOrderBy
  QueryPositionLast
  ShowButtonAdd
  ShowButtonDelete
  ShowButtonApply
  ButtonAddLeft=(pixel)
  ButtonAddTop=(pixel)
  ButtonAddTabOrder=(order)
  ButtonDeleteLeft=(pixel)
  ButtonDeleteTop=(pixel)
  ButtonDeleteTabOrder=order}

[FIELDS] section objects
  _{Label
  Edit
  Memo
  CheckBox
  DBEdit
  DBText
  DBMemo
  DBCheckBox
  DBNavigator
  DBLookupComboBox
  GroupBox
  Panel
  Button
  wwDBDateTimePicker
  wwDBGrid
  wwDBNavigator
  wwDBEdit
  wwDBComboBox
  wwDBSpinEdit
  wwDBComboDlg
  wwDBLookupCombo
  wwDBLookupComboDlg
  wwKeyCombo
  wwIncrementalSearch
  wwDBRichEdit
  wwDBMonthCalendar}

Properties of Objects
  _{Left
  Name
  Top
  LeftRel
  LeftRelRight
  TopRel
  Height
  Width
  Caption
  Checked
  FontStyle
  Enabled
  DataField
  Position
  Query
  QueryLink
  QueryLinkInSynergy
  QueryOrderBy
  QueryPositionLast
  Level
  LabelCaption
  Alignment
  Align
  LookupTable
  LookupQuery
  LookupField
  FirstEdit
  ListFieldIndex
  ListField
  KeyField
  Style
  ItemsAdd
  Selected
  DropDownWidth
  ReadOnly
  Anchors}