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 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 the 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

Last modified: 15/10/2013 12:52:28 PM

© 2013 Synergetic Management Systems. Published 15 October 2013.