SharePoint

Employee Management Using SharePoint List

Vinod Tharakan | Last Updated on : May 28, 2019

Overview

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.

Employee2

Data Structure in SharePoint
Lists Used

List Name Functionality
ClsysTemplt_Employee Stores Employee Details
ClsysTemp_JobType Stores Job Type – Salaried/Contract etc. Related to Employee Details table through JobTypeId

List Details
Employees List

Column Name Data Type
EmployeeId Number
FirstName Single line of text
LastName Single line of text
Designation Single line of text
BirthDate Date and Time
HireDate Single line of text
Address Single line of text
Phone Single line of text
EmailId Single line of text
JobTypeId Single line of text
Attachments From Default Columns of SharePoint List

JobType

Column Name Data Type
JobTypeId Number
JobType Single line of text

Creating and Configuring the Forms

List of Forms used

Form Name Functionality
MainForm Has set of buttons for filtering the grid and other data operations. Has an ExternalForm Control which loads Employee Details form
EmployeeDetails Shows List of all employees in a grid format with details of each employee listed below the grid.
EditEmployee Used for updating Employee Details or Adding New Employee Details

The order of creation of forms would be EditEmployee, EmployeeDetails, and then MainForm. This is because some of the features like edit click on the search grid of EmployeeDetails is configured to open EditEmployee form and New button click from Mainform also opens the EditEmployee form. The best approach would be to complete the form design of all forms before starting on the rules.

Form Design – Main Form

  1. Create a New Form in the Employees Folder and open it for editing.
  2. Click anywhere on the form to get the form properties loaded in the Properties window. Change the Name of the form to Main Form. Set the Width of the form to 1200 and Height to 1000. EmployeeListImage2
  3. 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. EmployeeListImage3
  4. In the columns adjacent to the Image buttons, drag and drop 1 buttons from the Toolbox. Set the control name to btnAddnewemployee.
  5. Click the cell right beneath New and in the properties of Selected Cell Style set Column Span to 12.  EmployeeListImage5
  6. From Toolbox-> Group, choose External Form and drop it into the above cell.
  7. Set the properties of the External Form as shown here.  Employee7
  8. 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.   Employee8
  9. From ToolBox -> Group, select SubForm and drop it in the cell below Image Logo. Set the properties as given below.   Employee9
  10. 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.   Employee10
  11. 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.   Employee11

Rules for MainForm

Rule No Rule Name Trigger Functionality
1 Load_AllJobs btnAllJobs 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.
2 Load_SalariedJobs btnSalariedJobs Same as above.
3 Load_CommissionJobs btnCommissionJobs Same as above.
4 Load_ContractJobs btnContractJobs Same as above.
5 Set_ExternalFormJobTypeId hdntxtJobTypeIdMain 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.
6 Call_NewEmployeeForm btnNew Load the form EditEmployee to add new employee details.

 

Set value of hdntxtEmpIdDetails in EditEmployee form to 0.

Rule 1: Load_AllJobs
employee12

Rule 2: Load_SalariedJobs
Employee13

Rule 3: Load_CommisionJobs
employee14

Rule4: Load_ContractJobs
employee15

Rule  5: Set_ExternalFormJobTypeId
employee19

Rule 6: Call_NewEmployeeForm
Employee17

Form Design – EmployeeDetails

  1. Create a NewForm in the folder containing the Main Form. Open the form for edit and set the form properties as given below.   EmployeeListImage12
  2. 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.  EmployeeListImage13

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.

Displaying Employee Details for the row selected in the grid.

  1. 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.
  2. 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.   EmployeeListImage14
  3. 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.   EmployeeListImage15
  4. 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.   EmployeeListImage16

Configuring the Datasources for all the controls.

  1. Click on grid1 to get the Properties window for the search Grid. In the properties window select SharePoint from the Dropdown for DataSourceEmployeeListImage17
  2. Click on the grid to get the popup menu, choose DataSources tab and click on New DataSource Configuration.

EmployeeListImage18

  1. A new Popup window opens showing the Available lists in SharePoint. Choose your Employee table from the list and click Next.   EmployeeListImage19
  2. Select the Result columns that are to be displayed on the grid, make sure you include the EmployeeId column. Click Next.   EmployeeListImage20
  3. Click Next until you reach the Search Result Configuration screen. The columns you had chosen would be shown there,
    a.You can modify the Column Header to give a user friendly name to the column.
    b.If EmployeeID column is to be hidden, click on the checkbox to Hide it.
    c.For Date columns change the default Type chosen as DateTime to Date if only date is to be displayed.   EmployeeListImage21
  4. 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.  EmployeeListImage22
  5. To configure the Action for new composite column, click on the Configure Column button.  EmployeeListImage23
  6. In Configure Column Links, choose Link Button for Column Type.  You can choose a Link Button image from existing images.  EmployeeListImage24
  7. Choosing Link Button opens another section below, where you can choose the form to navigate to when the button is clicked.

a. Enter the folder name where the form is present and search to get that folder alone.
b. 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.   EmployeeListImage25
c. Choose the field to be mapped in the EditEmployee form from the Field Name drop down. In this case txtEmpId.
d. In the Result Field choose EmployeeId.
e. Click the + button to add this form to the box below.
f. Click Ok.   EmployeeListImage26

8. In a similar Manner Configure the LinkButton for Select Column, but since we do not navigate to another form;
a. Choose the same form EmployeeDetails from the list of forms. EmployeeListImage27
b. Choose the hidden Control hdntxtEmpId from the Fields dropdown.
c. Choose EmployeeId for the Result field.
d. Add the form with the + button.
e. Click OkEmployeeListImage28

9. 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.   EmployeeListImage29
10.Click on Finish button, to come out of the Configure Search Grid option.
11.Preview the form to make sure the link buttons are configured and data is getting loaded into the grid.   EmployeeListImage30

Configuring the second Search Grid

  1. Right click on the first grid, Copy it and paste into the cell right underneath it, the column span of which was set to 12 earlier.   EmployeeListImage31
  2. 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.
  3. 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.   EmployeeListImage32

Setting the DataSource for the controls displaying Employee Details.

  1. In the DataSources tab of the form, click on Create DataSource.  EmployeeListImage33 
  2. Give DataSource Name as dsEmpDetails, choose SharePoint as the DataSource Type and click Ok.  EmployeeListImage34
  3. Choose the Employee table from the list and click Next.  EmployeeListImage35
  4. In Commands dropdown, default will be Select, click on Add button.  EmployeeListImage36
  5. In the query criteria mapping choose EmployeeId Equals hdntxtEmpId.   EmployeeListImage37
  6. 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. EmployeeListImage38
  7. Map Attachments column of Employee List to the imagecontrol (imgEmpPic here) on the form.   EmployeeListImage39
  8. Click Finish to get back to the Commands Configured part and click on Add Trigger.  EmployeeListImage40
  9. 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.   EmployeeListImage41

Adding Rules to the form

The rules created during Grid and DataSource configuration would be already added to the Rules Designer. Apart from that, these are the rules to be added to the form.

Rule No. RuleName Trigger Functionality
1 On_FormLoad Form Load 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.
2 Select_EmployeeDetails hdntxtEmpId When a row is selected on the grid, corresponding details like Image, Address etc are displayed in the controls below the grid.
3 Call_EditEmployeeForm hdntxtEmpIdEdit 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.
4 Grid1_ChangeHeaderStyle srchEmpDetails If the default Color of the Header of the search grid2 is to be changed.
5 Grid2_ChangeHeaderStyle srchEmpByJobType If the default Color of the Header of the search grid2 is to be changed.
6 Select_JobType hdntxtJobTypeId Based on the button click on the MainForm for filtering Employees by Job Status, the grid displays are swapped between grid1 and grid2.

Rule 1: On_FormLoad
EmployeeListImage42

Rule 2 : Select_EmployeeDetails
EmployeeListImage43

Rule 3: Call_EditEmployeeForm
EmployeeListImage44

Rule 4: Grid1_ChangeHeaderStyle

EmployeeListImage45

Rule 5: Grid2_ChangeHeaderStyle
EmployeeListImage46

Rule 6: Select_JobType

EmployeeListImage47

Form Design – Edit Employee

  1. Create a NewForm in the Employees Folder and open it for editing.
  2. 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.   EmployeeListImage48
  3. 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.   employee18
  4. For entering the Address increase the size of the TextBox control and check the AcceptReturn property in the properties window.
  5. For DateTimePicker control, date format can be set from the properties window as shown below.   EmployeeListImage51
  6. 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.   EmployeeListImage52

Configure DataSource for these controls

  1. In the DataSources tab of the form, click on Create DataSource.   EmployeeListImage53 
  2. Give DataSource Name as dsEmpEdit, choose SharePoint as the DataSource Type and click Ok. EmployeeListImage54
  3. Choose the Employee List and click Next.   EmployeeListImage55
  4. Commands window is opened; by default Select would be chosen in the dropdown. Click Add Button.   EmployeeListImage56
  5. Map the controls on the form to appropriate DataSource column from the list. EmployeeListImage57
  6. 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.   EmployeeListImage58
  7. Click Ok to complete configuring the Select Command. The command configuration window will show that no triggers are associated with the Command.   EmployeeListImage59
  8. Click on Add Trigger and select txtEmpId from the list of controls and click Create Rule. EmployeeListImage60
  9. The command window will show txtEmpId as the associated trigger. EmployeeListImage61
  10. Continue to configure the Insert and Update commands in a similar manner. Just the associated triggers will change to btnInsert and btnUpdate.   EmployeeListImage62 EmployeeListImage63
  11. 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.   EmployeeListImage64
  12. 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().
  13. 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.

Configuring the DataSource for ComboBox – cmbJobType

  1. The dropdown items for the Combobox have to be configured separately through its DataSource property. Click on cmbJobType and select DataSource tab. EmployeeListImage65
  2. Click on New DataSource which opens the below dialogbox. Choose the DataSource as SharePoint.   EmployeeListImage66
  3. Select the JobType Table and click Next. EmployeeListImage67
  4. Click Next in the Criteria Builder window.
  5. In the Next window, select JobTypeId as the Value Member and JobType as the Display Member.   EmployeeListImage68
  6. 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. EmployeeListImage69

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.

  1. To configure this first select the control by clicking on it. In the Properties window -> Validation, check IsRequired.   EmployeeListImage70
  2. You can also set regular expression fields to check if it’s a valid EmailId or Phone number etc.   EmployeeListImage71 EmployeeListImage72
  3. 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. EmployeeListImage73

Rules for form EditEmployee

No RuleName Trigger Functionality
1 FirstLoad_CheckNewRecord hdntxtEmpIdDetails 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.

2 Load_EmployeeDetails txtEmpId 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.
3 Update_Record btnUpdate Automatically created when the datasource is configured for Update Command Trigger. Rule Name changed later.
4 Insert_Record btnInsert Automatically created when the datasource is configured for Insert Command Trigger. Rule Name changed later.
5 Close_Form btnClose Close the form.

 

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

Rule 1: FirstLoad_CheckNewRecord
employee20

Rule 2: Load_EmployeeDetails
EmployeeListImage75

Rule 3: Update_Record
EmployeeListImage76

Rule 4: Insert_Record
EmployeeListImage77

Rule 5: Close_Form
EmployeeListImage78

How to use this template

a) Uploading List Template

Following lists are using for creating the template form.

  • ClsysTemp_EmpJobType
  • ClSysTmplt_Employee

Please do the following steps for uploading list templates

  • Click on settings  at the right corner of the window
  • Click on ‘Site settings’ from the drop down menu, we can see the following window appearing.   EmployeeListImage80
  • Click on ‘List template’ option as shown in the above image. And we can see ‘List Template Gallery’. Click on ‘Files’ at the left most corner as shown in the following image. EmployeeListImage81
  • Click on ‘Upload Document’   EmployeeListImage82
  • Following Pop-up will appear where we can browse a list template stored in our computer and click ‘OK’ button.   EmployeeListImage83
  • 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’.   EmployeeListImage84
  • Now go to the required sub site, click ‘Settings’ on the right corner.  Then click on ‘Site Contents’.  Following Screen appears in which click on ‘add an app’.   EmployeeListImage85
  • Following screen will appear where we can type the uploaded template name and click search.   EmployeeListImage86
  • Then you can see the Uploaded template, Click on it. EmployeeListImage87
  • On clicking it, we will get following Pop up. Enter the name for the List template and click ‘Create’ button.   EmployeeListImage88

b) Uploading Tenant

  • Click on the ‘Backup and Restore’ on the left side of the App form Designer window .Then click on ‘Import Site’ -> Browse the Tenant and click Load. EmployeeListImage89
  • Select the ‘Zip File’ and ‘Site Name’ and click ‘Next’.   EmployeeListImage90
  • Click ‘Next’ button.

EmployeeListImage91

  • Click on ’Import’ button

EmployeeListImage92

  • Backup restored successfully EmployeeListImage93

c)  Create a Web Part Page

Forms can be embedded on any SharePoint page by using the ClaySys AppForms Web Part, to create a web part page, follow these steps:-

  • Click on Site Settings Wheel in the upper right corner of your site and then select “Site Contents Page”.   EmployeeListImage94
  • Select “Site Pages” or simply pages library on Site Contents Page. EmployeeListImage95
  • Click on “New Wiki Page” to add new site page.   EmployeeListImage96
  • Give the New page name into the pop-up window and click ‘Create’ to add new page. EmployeeListImage97
  • 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.
  • Click ‘Add’ button to add the WebPart.  EmployeeListImage98
  • Click on the ‘edit the Webpart’ link to edit the page.   EmployeeListImage99
  • A new pop-up window appears and here we can configure the Claysys Appforms by clicking on the ‘Configure’ button EmployeeListImage100
  • Pop-up appears with listed published forms. We can select the appropriate form by click on the form and add the form by clicking on the ‘Select’ button.   EmployeeListImage101
  • Once the configuration is finished, click on the ‘Apply’ button to see the changes. Click ‘OK’ to continue. EmployeeListImage102
  • Click ‘Save’ button to save the changes.
  • Below figure shows the form after WebPart configuration. Employee2
Vinod TharakanFounder & Managing Director of ClaySys
0 Comments

Leave a Reply

Your email address will not be published. Required fields are marked *

Employee Management Using SharePoint List

2019-05-28 14:12:59