Employee Management Portal Using SQL

Overview

When working for a large company, it’s hard to know everyone’s name, job title, room location and contact information such as phone number or email address. This employee template form is a solution to this, that’s capable of searching a database of employee names. When an employee is found, his or her name is displayed in a hyperlink that provides further information when clicked. In this template form, this template form not only manages the employee details, it is also lists the tasks and Annual Evaluations of each employee.

Prerequisite(s)

- SharePoint 2013

Microsoft SQL 2014

Data Structure

Microsoft SQL Server is a relational database management system developed by Microsoft. As a database server, it is a software product with the primary function of storing and retrieving data as requested by other software applications which may run either on the same computer or on another computer across a network (including the Internet).

SQL Table
Table Name	Column Name	Data Type
EmployeeMaster EmployeeMaster Table is used to store the details of employees.	Created	date
	Modified	date
	ModifiedBy	nvarchar(50)
	Photo	varbinary(MAX)
	IsDelete	nvarchar(10)
	CreatedBy	nvarchar(50)
	SkypeID	nvarchar(50)
	HireDate	date
	Department	nvarchar(50)
	Status	nvarchar(50)
	ZipCode	int
	HomeTelephone	nvarchar(50)
	MobileNumber	nvarchar(50)
	Email	nvarchar(100)
	Address	nvarchar(MAX)
	City	nvarchar(300)
	State	nvarchar(50)
	FirstName	nvarchar(50)
	LastName	nvarchar(50)
	Prefix	nvarchar(50)
	Title	nvarchar(50)
	ID	int

SQL Table
Table Name	Column Name	Data Type
Employee_TaskMaster Employee_TaskMaster Table is used to store the task assigned to each employee.	IsDelete	bit
	CreatedBy	nvarchar(50)
	Created	date
	ModifiedBy	nvarchar(50)
	Modified	date
	Subject	nvarchar(MAX)
	TaskDetails	nvarchar(MAX)
	TaskCompletionPercentage	nvarchar(50)
	StartDate	date
	DueDate	date
	Priority	nvarchar(30)
	Status	nvarchar(30)
	ID	int
	EmployeeID	int
	TaskOwner	nvarchar(50)
	AssignedTo	nvarchar(50)

SQL Table
Table Name	Column Name	Data Type
Employee_Evaluations_Table Employee_Evaluations_Table is used to store the review details of each employee.	ModifiedBy	nvarchar(50)
	IsDelete	bit
	Created	date
	CreatedBy	nvarchar(50)
	Modified	date
	ID	int
	Subject	nvarchar(MAX)
	Evaluation	nvarchar(MAX)
	EmployeeID	int

Design And Configuration

In ClaySys AppForms, Employee is used to know employee name, job title, room location and contact information such as phone number or email address. This template can be used for large companies to store employee details. Here, we are using DB as SQL for storing the employee details. A user can search for an employee and he can edit as well. Using this employee template, a user can Create New Employee, Delete/Update Employee details, Print details, assign task to employees and Sort the employee details based on the status.

List Of Forms Used
Name	Functionality
EmployeeForm Form Design Rule Design	‘EmployeeForm’ is used to insert the new employee details.
Dashboard Rule Design	‘Dashboard’ form is used to display the details of employees saved.
EvaluationsPopup Rule Design	‘EvaluationsPopup’ form is used to insert/update the review of performance of each employee to the Evaluation table.
TasksPopup Rule Design	‘TasksPopup’ form is used to Insert/Update task which is assigned to each employee.
EmpSearch Rule Design	‘EmpSearch’ form is used to display employee details in a grid.
CardView Rule Design	‘CardView’ form is used to display employee details in a card view format.
CardViewFrm Rule Design	‘CardViewFrm’ form contains an external form which is used to call ‘frmEmpCardForm’.
frmEmpCardForm Rule Design	‘frmEmpCardForm’ contains three external form which is used to display employee details in card format.
frmEmployeeCard Rule Design	‘frmEmployeeCard’ contains the information of each employee.

EmployeeForm

EmployeeForm Form Design

Create Form

Folders are used to store and organize forms that have a logical association or relationship. To create a new form, click ‘New Folder’ button on the toolbar of the designer. On clicking the New Folder option, it will create a new folder and the name will be in the edit mode. You can type in the new name for the folder.

Rename Form

Then click on the ‘New Form’ link on the toolbar. Give an appropriate name for the form. Click ‘Save’ to save the form.

Publish Form

You can drag and drop controls to the form and format the styles easily. ‘Check-In Form’ to save the changes and publish to get the completed form for the end user to use. A form must be checked-out to make modifications while all aspects of the form can be examined while checked-in.

EmployeeForm Rule Design

Rule Name : SAVE | Trigger(s) : btnSave

Rule – Save

Insert/Update values to the Employee master table.

Rule Name : Selection | Trigger(s) : hdnID

Rule – Selection

Corresponding hdnID’s values Select from the Employee master table.

Rule Name : CancelRule | Trigger(s) : btnCancel

Rule – CancelRule

This rule used to Close the form.

Rule Name : ValidationsRule | Trigger(s) : btnSave

Rule – ValidationsRule

This rule used to check the validation.

Dashboard

Dashboard Rule Design

Rule Name : FORMLOAD | Trigger(s) : Formload

Rule – FORMLOAD

Sets the External Form Control value.

Rule Name : Load_ALL | Trigger(s) : hlbtnALL

Rule – Load_ALL

SetFormValue function is used to set the value to refresh the employee search, it loads all values from the employee table.

Rule Name : TaskRefresh | Trigger(s) : hdnTaskRefresh1

Rule – TaskRefresh

SetFormValue function is used to refresh the Task grid.

Rule Name : EvalRefresh | Trigger(s) : hdnEvalRefresh1

Rule – EvalRefresh

Set FormValue function is used to refresh the Evaluation grid.

Rule Name : DeleteEmp | Trigger(s) : btnDelete

Rule – DeleteEmp

This rule used to delete the employee details.

Rule Name : ExportToExcel | Trigger(s) : btnExport

Rule – ExportToExcel

Set FormValue function is used to save the search values to an Excel sheet.

Rule Name : Salaried | Trigger(s) : hlbtnSalaried

Rule – Salaried

Set FormValue function is used to set the value to refresh the employee search, it loads Salaried employees from the employee table.

Rule Name : Commision | Trigger(s) : hlbtnCommision

Rule – Commision

SetFormValue function is used to set the value to refresh the employee search, it loads Commision employees from the employee table.

Rule Name : Contract | Trigger(s) : hlbtnContract

Rule – Contract

SetFormValue function is used to set the value to refresh the employee search, it loads Contract employees from the employee table.

Rule Name : printForm | Trigger(s) : btnPrint

Rule – printForm

This rule used to execute PrintPage(), it prints the form.

Rule Name : Meeting | Trigger(s) : btnMeeting

Rule – Meeting

It is used to display a messagebox.

EvaluationsPopup

EvaluationsPopup Rule Design

Rule Name : Save | Trigger(s) : btnSave

Rule – Save

Insert/Update values to the Evaluation table.

Rule Name : ValidationsRule | Trigger(s) : btnSave

Rule – ValidationsRule

This rule is used to check the validation.

Rule Name : On_hdnID | Trigger(s) : hdnID

Rule – On_hdnID

Corresponding hdnID’s values get Selected from the Evaluation table.

Rule Name : Cancel | Trigger(s) : btnCancel

Rule – Cancel

This rule is used to close the form.

TasksPopup

TasksPopup Rule Design

Rule Name : SaveRule | Trigger(s) : btnSave

Rule – SaveRule

Insert/Update values to the Task table.

Rule Name : ValidationsRule | Trigger(s) : btnSave

Rule – ValidationsRule

This rule used to check the validation.

Rule Name : SelectionRule | Trigger(s) : hdnID

Rule – SelectionRule

Corresponding hdnID’s values select from the Task table.

Rule Name : Cancel | Trigger(s) : btnCancel

Rule – Cancel

This rule used to close the form.

Rule Name : FormLoadRule | Trigger(s) : FormLoad

Rule – FormLoadRule

This rule is used to load form as default.

Rule Name : on_cmbAssigned | Trigger(s) : cmbAssignedTo , cmbOwnerName

Rule – on_cmbAssigned

This rule is used to validate if both the Owner and Assigned To are the same.

EmpSearch

EmpSearch Rule Design

Rule Name : On_hdnSelectID | Trigger(s) : hdnSelectID

Rule – On_hdnSelectID

When a row is selected on the grid, corresponding details like Image, Address etc are displayed in the controls below the grid.

Rule Name : EvalRefresh | Trigger(s) : hdnEvalRefresh

Rule – EvalRefresh

Refreshes the Evaluation grid, when you Insert/Update to the Evaluation table.

Rule Name : TaskRefresh | Trigger(s) : hdnTaskRefresh

Rule – TaskRefresh

Refreshes the Task grid, when you Insert/Update to the Task table.

Rule Name : On_hdnTaskDel | Trigger(s) : hdnTaskDel

Rule – On_hdnTaskDel

This rule is used to delete the selected Task details.

Rule Name : On_hdnEvalDel | Trigger(s) : hdnEvalDel

Rule – On_hdnEvalDel

This rule is used to delete the selected Evaluation details.

Rule Name : EmpRefresh | Trigger(s) : hdnRefresh

Rule – EmpRefresh

Refreshes the Employee grid, when you Insert/Update to the Employee table.

Rule Name : ExportToExcel | Trigger(s) : hdnExportToExcel

Rule – ExportToExcel

This is used to save the employee search values to an Excel sheet.

CardView

CardView Rule Design

Rule Name : FORMLOAD | Trigger(s) : Formload

Rule – FORMLOAD

Sets the External Form Control value.

Rule Name : Load_ALL | Trigger(s) : hlbtnALL

Rule – Load_ALL

SetFormValue function is used to set the value to refresh the employee search, its loads all values from the employee table.

Rule Name : TaskRefresh | Trigger(s) : hdnTaskRefresh1

Rule – TaskRefresh

SetFormValue function is used to refresh the Task grid.

Rule Name : EvalRefresh | Trigger(s) : hdnEvalRefresh1

Rule – EvalRefresh

SetFormValue function is used to refresh the Evaluation grid.

Rule Name : DeleteEmp | Trigger(s) : btnDelete

Rule – DeleteEmp

This rule is used to delete the employee details.

Rule Name : hdnExportToExcel | Trigger(s) : btnExport

Rule – ExportToExcel

SetFormValue function is used for saving the search values to an Excel sheet.

Rule Name : Salaried | Trigger(s) : hlbtnSalaried

Rule – Salaried

SetFormValue function is used to set the value to refresh the employee search, it loads Salaried employees from the employee table.

Rule Name : Commision | Trigger(s) : hlbtnCommision

Rule – Commision

SetFormValue function is used to set the value to refresh the employee search, it loads commision employees from the employee table.

Rule Name : Contract | Trigger(s) : hlbtnContract

Rule – Contract

SetFormValue function is used to set the value to refresh the employee search, it loads Contract employees from the employee table.

Rule Name : Print | Trigger(s) : btnPrint

Rule – Print

This rule is used to execute PrintPage(), it prints the form.

Rule Name : Meeting | Trigger(s) : btnMeeting

Rule – Meeting

It is used to display a messagebox.

CardViewFrm

CardViewFrm Rule Design

Rule Name : On_hdnSelectID | Trigger(s) : hdnSelectID

Rule – On_hdnSelectID

When a row is selected on the grid, corresponding details like image, address etc are displayed in the controls below the grid.

Rule Name : EvalRefresh | Trigger(s) : hdnEvalRefresh

Rule – EvalRefresh

Refreshes the Evaluation grid, when you Insert/Update to the Evaluation table.

Rule Name : TaskRefresh | Trigger(s) : hdnTaskRefresh

Rule – TaskRefresh

Refreshes the Task grid, when you Insert/Update to the Task table.

Rule Name : On_hdnTaskDel | Trigger(s) : hdnTaskDel

Rule – On_hdnTaskDel

This rule is used to delete the selected Task details.

Rule Name : On_hdnEvalDel | Trigger(s) : hdnEvalDel

Rule – On_hdnEvalDel

This rule is used to delete the selected Evaluation details.

Rule Name : EmpRefresh | Trigger(s) : hdnRefresh

Rule – EmpRefresh

Refreshes the Employee grid, when you Insert/Update to the Employee table.

Rule Name : ExportToExcel | Trigger(s) : hdnExportToExcel

Rule – ExportToExcel

This is used to save the employee search values to an Excel sheet.

Rule Name : EvaluationEdit | Trigger(s) : hdnEvalEditID

Rule – EvaluationEdit

When a row is selected on the Evaluation grid, corresponding details will be shown in a popup which needs to be edited.

Rule Name : TaskEdit | Trigger(s) : hdnTaskEditID

Rule – TaskEdit

When a row is selected on the Task grid, corresponding details will be shown in a popup which needs to be edited.

Rule Name : On_FormLoad | Trigger(s) : Formload

Rule – On_FormLoad

Loads the form as default.

Rule Name : FilterType | Trigger(s) : hdnFilterType

Rule – FilterType

SetformValue to the external form hidden control.

frmEmpCardForm

frmEmpCardForm Rule Design

Rule Name : Selection | Trigger(s) : hdnSelectID

Rule – Selection

Corresponding hdnID’s values are selected from the Employee master table.

Rule Name : editRule | Trigger(s) : hlbtnEdit

Rule – editRule

This rule is for edit. A popup will be shown and selected corresponding ID’s values can be selected.

Rule Name : SelectionRule | Trigger(s) : hlbtnSelect

Rule – SelectionRule

SetformValue to the external form’s hidden control.

frmEmployeeCard

frmEmployeeCard Rule Design

Rule Name : FormLoad | Trigger(s) : FormLoad

Rule – FormLoad

Fills the hidden search control and sets the count of external forms and loads it.

Rule Name : SelectRepeat | Trigger(s) : hdnRefresh

Rule – SelectRepeat

Sets each external form’s values and selects corresponding values.

Rule Name : ExportToExcel | Trigger(s) : hdnExportToExcel

Rule – ExportToExcel

This is used to save the hidden employee search values to an Excel sheet.

Rule Name : FilterRule | Trigger(s) : hdnFilterType

Rule – FilterRule

This rule loads form as default like FormLoad.

Template Summary

The employee template form can be used for storing employee details. This is also capable of searching a database of employee names in an organization. When an employee is found, his or her name is displayed in a hyperlink that provides further information when clicked. This template form not only manages the employee details, it also lists the tasks and Annual Evaluations of each employee.