Employee Using List
Overview
Template by Deepa | January 20, 2016

This document provides the details for creating forms through ClaySys Appforms to manage employee details stored in a SharePoint List. Employee Details are displayed in a grid format using Search grid as well as in detailed format using form DataSource. Clicking on Select button in the grid displays  the details of the employee below the grid. Clicking on Edit button in the grid opens a popup window where all Employee Details can be edited and saved back to the list. A new employee can be added using the same form and inserted into the list. Buttons are provided to filter the grid based on Job Status – Salaried/Commission/Contract.

Prerequisite(s)
  • sharepoint 2013
Data Structure

SharePoint lists consist of rows and columns that store data in a similar fashion to a traditional relational database management system such as SQL Server. However, a benefit of lists is that SharePoint includes Web Parts that provide simple methods for managing the data. If the data was stored in a database, it would require custom user interface components to access it and manipulate it. Also, specialized skills are required to design, implement, and maintain a custom database. Another advantage of using lists is that custom workflow and event handlers can easily be registered to them. By default the following columns are available in sharepoint list, ID, Title, Modified, Created, Modified By, Created By, Attachments. Apart from these default list columns , user can add more columns depending on the requirement.

SharePoint List
List NameColumn NameData Type

ClsysTemp_EmpJobType

Stores Job Type – Salaried/Contract etc. Related to Employee Details table through JobTypeId

JobTypeIdNumber
JobTypeSingle line of text
SharePoint List
List NameColumn NameData Type

ClSysTmplt_Employee

Stores Employee Details

EmployeeIdNumber
FirstNameSingle line of text
LastNameSingle line of text
DesignationSingle line of text
BirthDateDate and Time
HireDateSingle line of text
AddressSingle line of text
PhoneSingle line of text
EmailIdSingle line of text
JobTypeIdSingle line of text
AttachmentsFrom Default Columns of SharePoint List
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 Sharepoint list 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
NameFunctionality
MainForm Form Design Rule Design Has set of buttons for filtering the grid and other data operations. Has an ExternalForm Control which loads Employee Details form
EmployeeDetails Form Design Rule Design Shows List of all employees in a grid format with details of each employee listed below the grid.
EditEmployee Form Design Rule Design Used for updating Employee Details or Adding New Employee Details
MainForm
MainForm Form Design
01

Create a NewForm in the Employees Folder and open it for editing.

02
FormProperties

Click anywhere on the form to get the form properties loaded in the Properties window. Change the Name of the form to MainForm. Set the Width of the form to 1200 and Height to 1000.

03
Image Button

From the Standard tab of the toolbox on the left drag an Image control and place it in the first row, first column. Set the control name as ImgLogo and set the width to 210 and height to 69.

04
Button New

In the columns adjacent to the Image buttons, drag and drop 10 buttons from the Toolbox. Set the control name to btnNew for the first one and Text as New. Set Width as 69 and Height as 23. To align the button with the bottom of the Image set Margin of btnNew as 0,48,0,0.

05

Continue similarly for the rest of the buttons.

06
Column Span

Click the cell right beneath New and in the properties of Selected Cell Style set Column Span to 12.

07
External Form

From Toolbox-> Group, choose External Form and drop it into the above cell.

08
properties of the External Form

Set the properties of the External Form as shown here.

09
To set the NavigateURL

To set the NavigateURL, click on the ellipsis to open the dialogbox given below. Unselect the Expand All checkbox so that only the folder names are displayed. Choose the EmployeeDetails form and click on the Add Button. Click OK.

10
SubForm

From ToolBox -> Group, select SubForm and drop it in the cell below Image Logo. Set the properties as given below.

11
subform

Onto the subform, add a label for Job Status and four buttons for filtering the Employee Details grid based on the job status. Set the properties as shown below.

12
Sub Form for hidden control

Add a subform in the cell adjacent to the last button in the top row and set its Visibility to false by unchecking the checkbox Visible in the Properties window. Add a textbox to the subform and name it hdntxtJobTypeIdMain. The value of this will be used for filtering the grid in the EmployeeDetails form.

MainForm Rule Design
01
Rule Name : Load_AllJobs | Trigger(s) : btnAllJobs
Rule 1

Triggers the change event for the hidden textbox hdntxtJobTypeIdMain, which sets an external form value in EmployeeDetails Form to filter the data in the grid based on Job Type.

02
Rule Name : Load_SalariedJobs | Trigger(s) : btnSalariedJobs
Rule 2

Same as above.

03
Rule Name : Load_CommissionJobs | Trigger(s) : btnCommissionJobs
Rule 3

Same as above.

04
Rule Name : Load_ContractJobs | Trigger(s) : btnContractJobs
Rule 4

Same as above.

05
Rule Name : Set_ExternalFormJobTypeId | Trigger(s) : hdntxtJobTypeIdMain
Rule 5

Set the Value of the control hdntxtJobTypeId in EmployeeDetails form to the hidden text form value in the Main form which was set in the above rules.

06
Rule Name : Call_NewEmployeeForm | Trigger(s) : btnNew
Rule 6

Load the form EditEmployee to add new employee details.
Set value of hdntxtEmpIdDetails in EditEmployee form to 0.

EmployeeDetails
EmployeeDetails Form Design
01
Form Properties

Create a NewForm in the folder containing the Main Form. Open the form for edit and set the form properties as given below.

02
Search

Click on the first cell and set Column Span to 12. Add a Search grid from Toolbox ->Data to this cell and set the properties as given below.

03

Click on the cell right beneath the datagrid and set column span to 12. We will copy the first grid here once it is fully configured. Two grids are used because for SharePoint data the filtering of data in the grid will throw an error when the form is loaded first without a value for the search criteria. So the first grid- used during form load- shows all data; and the second grid is shown when data is to be filtered based on button click from MainForm.

04

Here are the steps for Displaying Employee Details for the row selected in the grid.Set the Column span of the cell below second grid to 4 and add a label called lblEmpName. Similarly Add a label beneath it and name it lblDesignation.

05
Image control Properties

For the cell beneath this, set the column span to 3 and add an Image control from Toolbox->Standard. St the properties as shown below.

06
SubformDetails

Add a subform to the cell to the right of the image control and add label controls as shown below. Name the subform as subfrmDetails, and name the labels for Address, Phone and Email appropriately.

07
Subform for hidden controls

In the cell to the right of this add another subform to hold the hidden controls used in the form. Name this as subfrmHiddenCtrls. Add six textboxes and name them as given here.

08
search Grid Properties

Following are the steps for Configuring the Datasources for all the controls. Click on grid1 to get the Properties window for the search Grid. In the properties window select SharePoint from the Dropdown for DataSource.

09
New DataSource Configuration

Click on the grid to get the popup menu, choose DataSources tab and click on New DataSource Configuration.

10
Available lists

A new Popup window opens showing the Available lists in SharePoint. Choose your Employee table from the list and click Next.

11
Search Result columns

Select the Result columns that are to be displayed on the grid, make sure you include the EmployeeId column. Click Next.

12
Search Result Configuration screen

Click Next until you reach the Search Result Configuration screen. The columns you had chosen would be shown there,
You can modify the Column Header to give a user friendly name to the column.
If EmployeeID column is to be hidden, click on the checkbox to Hide it.
For Date columns change the default Type chosen as DateTime to Date if only date is to be displayed.

13
composite columns

We need to create two composite columns now, to show the edit and select buttons for each row. In the Add Composite Column section give Column Header as Edit, Result Column as Composite Column and click on the Add Column button. Repeat the same for creating Select button.

14
Configure composite column

To configure the Action for new composite column, click on the Configure Column button.

15
Configure Column Links

In Configure Column Links, choose Link Button for Column Type. You can choose a Link Button image from existing images.

16
navigation

Choosing Link Button opens another section below, where you can choose the form to navigate to when the button is clicked.
Enter the folder name where the form is present and search to get that folder alone.
Choose the form to be opened, in this case the EditEmployee form. Choosing this form makes this available in the Form Name drop down below.Choose the field to be mapped in the EditEmployee form from the Field Name drop down. In this case txtEmpId.
In the Result Field choose EmployeeId.
Click the + button to add this form to the box below.
Click Ok.

17
Configuring LinkButton

In a similar Manner Configure the LinkButton for Select Column, but since we do not navigate to another form;
Choose the same form EmployeeDetails from the list of forms.

18
Configuring LinkButton

Choose the hidden Control hdntxtEmpId from the Fields dropdown.
Choose EmployeeId for the Result field.
Add the form with the + button.
Click Ok.

19
Column display Order

Change the Column display Order to show Edit and Select buttons at the start of the row by setting the order as 2 and 3 for Edit and Select buttons respectively. Change the order for columns starting from FirstName onwards to be displayed after the Link buttons.

20

Click on Finish button, to come out of the Configure Search Grid option.

21
Preview

Preview the form to make sure the link buttons are configured and data is getting loaded into the grid.

22
Paste

Steps for Configuring the second Search Grid

23
Edit DataSource Configuration

In the properties window, change the second Search grid name to SrchEmpByJobType, since this grid will show the employee list filtered based on the Job Type.
Click on the Grid choose the DataSources tab and you’ll notice that it shows Edit DataSource Configuration, since the datasource configuration of the first grid has been copied here.

24
Create DataSource

Steps for Setting the DataSource for the controls displaying Employee Details.

25
DataSource Type

Give DataSource Name as dsEmpDetails, choose SharePoint as the DataSource Type and click Ok.

26
Employee table

Choose the Employee table from the list and click Next.

27
Commands

In Commands dropdown, default will be Select, click on Add button.

28
Query Criteria

In the query criteria mapping choose EmployeeId Equals hdntxtEmpId.

29
Query Value Mapping

In Query Value Mapping section, we need to map the result fields to the respective controls on the form. Since we need to Display FirstName and LastName together, map these to two hidden textboxes and then in FormLoad and hdntxtEmpId change add rule to assign the value of lblEmpName as hdntxtFirstName + hdntxtLastName.

30
Add Trigger

Map Attachments column of Employee List to the imagecontrol (imgEmpPic here) on the form. Click Finish to get back to the Commands Configured part and click on Add Trigger.

31
Trigger

Select FormLoad and hdntxtEmpId as the triggers and click on Create Rule. This would set textchange in hdntxtEmpId and form load events as the trigger for loading the DataSource dsEmpDetails.

EmployeeDetails Rule Design
01
Rule Name : On_FormLoad | Trigger(s) : Form Load
Rule 1

Height of second grid is set to zero and first grid to 250. The hdntxtEmpId control value is set to the EmployeeId from the first row, first column of Grid1.

02
Rule Name : Select_EmployeeDetails | Trigger(s) : hdntxtEmpId
Rule 2

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

03
Rule Name : Call_EditEmployeeForm | Trigger(s) : hdntxtEmpIdEdit
Rule 3

When the Edit button on the grid is clicked, the employeeId of the selected row is set to the hidden text hdntxtEmpIdEdit. This triggers changing the Employee Details below the grid and also brings up a popup window where the Employee Details can be edited. LoadForm function is called on EditEmployee form and SetFormValue function is used to set the value of hdntxtEmpIdDetails in EditEmployee form to the EmployeeId of the selected row.

04
Rule Name : Grid1_ChangeHeaderStyle | Trigger(s) : srchEmpDetails
Rule 4

If the default Color of the Header of the search grid2 is to be changed.

05
Rule Name : Grid2_ChangeHeaderStyle | Trigger(s) : srchEmpByJobTyp
Rule 5

If the default Color of the Header of the search grid2 is to be changed.

06
Rule Name : Select_JobType | Trigger(s) : hdntxtJobTypeId
Rule 6

Based on the button click on the MainForm for filtering Employees by Job Status, the grid displays are swapped between grid1 and grid2.

EditEmployee
EditEmployee Form Design
01

Create a NewForm in the Employees Folder and open it for editing.

02
Form Properties

Click anywhere on the form to get the form properties loaded in the Properties window. Change the Name of the form to EditEmployee. Set the properties of the form as given below. Enable Close Button should be unchecked and IsPopup should be checked.

03
Controls in the form

Add UI controls to the form as shown in the image below. The controls used are Label, TextBox, DateTimePicker, FileUpload, ComboBox and Buttons all from Toolbox->Standard tab.

04
TextBox control properties

For entering the Address increase the size of the TextBox control and check the AcceptReturn property in the properties window.

05
DateTimePicker control Properties

For DateTimePicker control, date format can be set from the properties window as shown below.

06
Subform for hiddenControls

Add a subform in cell next to txtAddress and name it as subfrmHdnCtrls. Set the visibility of the form to False by unchecking Visibility in the Properties Window. Add a textbox to the subform and name it hdntxtEmpId.

07

Steps for Configuring DataSource for these controls. In the DataSources tab of the form, click on Create DataSource. Give DataSource Name as dsEmpEdit, choose SharePoint as the DataSource Type and click Ok.

08
Available List

Choose the Employee List and click Next.

09
Map the controls

Commands window is opened; by default Select would be chosen in the dropdown. Click Add Button. Map the controls on the form to appropriate DataSource column from the list.

10
Map the Attachments

Map the Attachments column to the File Upload Control. And in the Query Criteria Mapping, select EmployeeId Equals txtEmpId. Please note that hdntxtEmpId is value is set from the calling form(EmployeeDetails here) and when the form loads, details for that EmployeeId are loaded onto the mapped controls.

11
Add Trigger

Click Ok to complete configuring the Select Command. The command configuration .Click on Add Trigger and select txtEmpId from the list of controls and click Create Rule. window will show that no triggers are associated with the Command.

12
Insert and Update

The command window will show txtEmpId as the associated trigger.Continue to configure the Insert and Update commands in a similar manner. Just the associated triggers will change to btnInsert and btnUpdate.

13
Close Button Functionality

For configuring the Close button functionality, click on the button and select the Rules tab. Click on Create Rule which will take you to the Rules panel and attach the close button as the trigger for the rule.

14

Change the Rule Name to Close_Form. RightClick on the rule name and choose Add Action from the popup. In the Action script write the expression CloseTab().
Couple of checks are added to make sure that a New Employee Record is only inserted not updated and viceversa. In the calling form(MainForm) when New button is clicked, the value passed for hdntxtEmpIdDetails is 0. In the trigger of hdntxtEmpId add a decision to check for this and disable the Update button if true. In case it is greater than 0 disable the Insert button.

15
DataSource property

Steps for Configuring the DataSource for ComboBox – cmbJobType. The dropdown items for the Combobox have to be configured separately through its DataSource property. Click on cmbJobType and select DataSource tab.

16
New DataSource

Click on New DataSource which opens the below dialogbox. Choose the DataSource as SharePoint.

17
Select JobType

Select the JobType Table and click Next.

18
Value Member and Display Member

Click Next in the Criteria Builder window.
In the Next window, select JobTypeId as the Value Member and JobType as the Display Member.

19
Add trigger

In the window that comes up next click Add trigger and select hdntxtEmpIdDetails and click create rule. This would close the Add Trigger Window. Click Finish to close the combobox datasource configuration window.

20
Validation

Steps for Adding Validation to the Controls
If the Datasource requires that certain fields are mandatory, you can configure the related controls on the form to show an error message if data is not for this field. EditEmployee form has validation configured for all textbox controls. To configure this first select the control by clicking on it. In the Properties window -> Validation, check IsRequired.

21
Regular Expression

You can also set regular expression fields to check if it’s a valid EmailId or Phone number etc.

22
Error message

When Insert or Update button is pressed on the EditEmployee form, if the required fields are not entered, or the data is not in the expected format an error message is shown to the user as shown below.

EditEmployee Rule Design
01
Rule Name : FirstLoad_CheckNewRecord | Trigger(s) : hdntxtEmpIdDetails
Rule 1

If the value of hdntxtEmpIdDetails is greater than 0, it is an existing Employee’s record to be loaded. Disable the Insert button and set the value of txtEmpId. If hdntxtEmpIdDetails value is 0 it is a New Record to be added, disable the Update button.

Load JobType dropdown.

02
Rule Name : Load_EmployeeDetails | Trigger(s) : txtEmpId
Rule 2

Execute Select on DataSource dsEmpEdit. This rule is automatically created when the datasource is configured. Add a decision to check if txtEmpid > 0 in case it is changed.

03
Rule Name : Update_Record | Trigger(s) : btnUpdate
Rule 3

Automatically created when the datasource is configured for Update Command Trigger. Rule Name changed later.

04
Rule Name : Insert_Record | Trigger(s) : btnInsert
Rule 4

Automatically created when the datasource is configured for Insert Command Trigger. Rule Name changed later.

05
Rule Name : Close_Form | Trigger(s) : btnClose
Rule 5

Close the form.
To be Implemented later: Refresh the EmployeeDetails form if any change has been made.

Template Summary

This Template  allows to view Employee Details in a grid format using Search grid as well as in detailed format using form DataSource. Clicking on Select button in the grid displays  the details of the employee below the grid. Clicking on Edit button in the grid opens a popup window where all Employee Details can be edited and saved back to the list. A new employee can be added using the same form and inserted into the list.

How To

A how-to is an informal, often short, description of how to accomplish a specific task. A how-to is usually meant to help non-experts, may leave out details that are only important to experts, and may also be greatly simplified from an overall discussion of the topic.

  • Add Web Part

    01

    Click on Site Settings Wheel in the upper right corner of your site and then select “Site Contents Page”.

    02
    SitePages

    Select site pages or simply pages library on site content page.

    03
    Wiki Page

    Click on “new Wiki page” to add new site page.

    04
    New Page

    Give the New page name into the pop-up window and click ‘Create’ to add new page.

    05
    ClaySys HTML WebPart

    To add a new web part, click on the ‘Insert’ tab and then click on the ‘Web Part’ button. Then select ClaySys -> ClaySys HTML Web Part.

    06
    Edit The WebPart

    Click ‘Add’ button to add the Web Part. Click on the ‘edit the Webpart’ link to edit the page.

    07
    WebPart Configuration

    A new pop-up window appears and here we can configure the ClaySys AppForms by clicking on the ‘Configure’ button.

    08
    Selecting Form

    Pop-up appears with listed published forms. We can select the appropriate form by clicking on the form and add the form by clicking on the ‘Select’ button.

    09
    WebPart

    Once the configuration is finished, click on the ‘Apply’ button to see the changes. Click ‘OK’ button.

    10
    WebPart

    Now you can see the web part page you just created, as shown in the following image. Click ‘Save’ button to save the changes.

    11
    Title

    The Forms added to the WebPart may contain "Claysys HTML WebPart" as Default title. The title can be changed by editing the Title in the "Appearance" after configuring the form.

  • Import Tenant

    01
    Backup and Restore

    Click on the “Back Up and Restore” on the left side of the AppForm Designer window. Then click on ‘Import Site’ -> Browse the Tenant and click Load.

    02
    Mapping Site

    Select the ‘Zip File’ and ‘Site Name’ and click ‘Next’.

    03
    Datasource Mapping

    Click ‘Next’ button.

    04

    Click on ’Import’ button . Once it done you are free to work with the form in the new site.

    List Mapping
    Import Tenant
  • Upload List Template

    01

    On the right corner of the window, click on settings.

    02
    List Template

    Click on 'Site settings' from the drop down menu , then we can see the following window appearing.

    03
    List Template Gallery

    Click on 'List template' option as shown in the above image. And we can see 'List Template Gallery'. Click on 'Files' at the leftmost corner as shown in the following image.

    04
    Uploading Document

    Click on 'Upload Document'.

    05
    Add Template

    Following Pop up will appear where we can browse a list template stored in our computer and click 'OK' button.

    06
    Template Naming

    Following Pop up will appear where we can change the name of the List Template and enter the Description as required and click on 'Save button'.

    07
    Adding an App

    Now go to the Required sub site, click 'Settings' on the right corner (as done in step '1'). Then click on 'Site Contents'. Following Screen appears in which click on 'add an app'.

    08
    Apps

    Following screen will appear where we can type the uploaded template name and click search.

    09
    Uploaded Template

    Then you can see the Uploaded template ,Click on it .

    10
    List Naming

    On clicking it ,we will get following Pop up . Enter the name for the List template and click 'Create' button.

Downloads
No downloads has been associated with this template.

Deepa

Leave a Reply