Page Comparison

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:

...

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 CONSTRAINT DF_uStudentBoarding_CommentCreatedBy DEFAULT (dbo.fnConstraintGetLoginName()),
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 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!

...

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.

...

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 CONSTRAINT DF_uStudentDiscipline_CommentCreatedDate DEFAULT (dbo.fnConstraintGetLoginName()),
  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}

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 ,}

  _{Seq int IDENTITY(1,1) 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
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.

...