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.
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 DetailsEmployees 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
- Create a New Form in the Employees Folder and open it for editing.
- 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.
- 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.
- In the columns adjacent to the Image buttons, drag and drop 1 buttons from the Toolbox. Set the control name to btnAddnewemployee.
- Click the cell right beneath New and in the properties of Selected Cell Style set Column Span to 12.
- From Toolbox-> Group, choose External Form and drop it into the above cell.
- Set the properties of the External Form as shown here.
- 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.
- From ToolBox -> Group, select SubForm and drop it in the cell below Image Logo. Set the properties as given below.
- 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.
- 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.
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
Rule 2: Load_SalariedJobs
Rule 3: Load_CommisionJobs
Rule4: Load_ContractJobs
Rule 5: Set_ExternalFormJobTypeId
Rule 6: Call_NewEmployeeForm
Form Design – EmployeeDetails
- Create a NewForm in the folder containing the Main Form. Open the form for edit and set the form properties as given below.
- 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.
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.
- 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.
- 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.
- 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.
- 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.
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.
- Click on the grid to get the popup menu, choose DataSources tab and click on New DataSource Configuration.
- A new Popup window opens showing the Available lists in SharePoint. Choose your Employee table from the list and click Next.
- Select the Result columns that are to be displayed on the grid, make sure you include the EmployeeId column. Click Next.
- 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.
- 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.
- To configure the Action for new composite column, click on the Configure Column button.
- In Configure Column Links, choose Link Button for Column Type. You can choose a Link Button image from existing images.
- 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.
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.
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.
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 Ok.
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.
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.
Configuring the second Search Grid
- 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.
- 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.
Setting the DataSource for the controls displaying Employee Details.
- In the DataSources tab of the form, click on Create DataSource.
- Give DataSource Name as dsEmpDetails, choose SharePoint as the DataSource Type and click Ok.
- Choose the Employee table from the list and click Next.
- In Commands dropdown, default will be Select, click on Add button.
- In the query criteria mapping choose EmployeeId Equals hdntxtEmpId.
- 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.
- 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.
- 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.
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
Rule 2 : Select_EmployeeDetails
Rule 3: Call_EditEmployeeForm
Rule 4: Grid1_ChangeHeaderStyle
Rule 5: Grid2_ChangeHeaderStyle
Rule 6: Select_JobType
Form Design – Edit Employee
- Create a NewForm in the Employees Folder and open it for editing.
- 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.
- 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.
- For entering the Address increase the size of the TextBox control and check the AcceptReturn property in the properties window.
- For DateTimePicker control, date format can be set from the properties window as shown below.
- 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.
Configure 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.
- Choose the Employee List and click Next.
- 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.
- 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.
- Click Ok to complete configuring the Select Command. The command configuration window will show that no triggers are associated with the Command.
- Click on Add Trigger and select txtEmpId from the list of controls and click Create Rule.
- 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.
- 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.
- 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.
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.
- Click on New DataSource which opens the below dialogbox. Choose the DataSource as SharePoint.
- Select the JobType Table and click Next.
- Click Next in the Criteria Builder window.
- In the Next window, select JobTypeId as the Value Member and JobType as the Display Member.
- 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.
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.
- You can also set regular expression fields to check if it’s a valid EmailId or Phone number etc.
- 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.
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
Rule 2: Load_EmployeeDetails
Rule 3: Update_Record
Rule 4: Insert_Record
Rule 5: Close_Form
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.
- 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.
- Click on ‘Upload Document’
- Following Pop-up will appear where we can browse a list template stored in our computer and click ‘OK’ button.
- 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’.
- 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’.
- Following screen will appear where we can type the uploaded template name and click search.
- Then you can see the Uploaded template, Click on it.
- On clicking it, we will get following Pop up. Enter the name for the List template and click ‘Create’ button.
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.
- Select the ‘Zip File’ and ‘Site Name’ and click ‘Next’.
- Click ‘Next’ button.
- Backup restored successfully
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”.
- Select “Site Pages” or simply pages library on Site Contents Page.
- Click on “New Wiki Page” to add new site page.
- Give the New page name into the pop-up window and click ‘Create’ to add new page.
- 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.
- Click on the ‘edit the Webpart’ link to edit the page.
- A new pop-up window appears and here we can configure the Claysys Appforms by clicking on the ‘Configure’ button
- 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.
- Once the configuration is finished, click on the ‘Apply’ button to see the changes. Click ‘OK’ to continue.
- Click ‘Save’ button to save the changes.
- Below figure shows the form after WebPart configuration.