Self Help - Excel Connector - Set Cells Values Method - Use Case
- Overview
- Phase 1: Create and configure the form for collecting information
- Phase 2: Configure the Excel connector
- Phase 3: Configure the Email sending connector
- Phase 4: Define the conditions for testing the call to the connectors
- Phase 5: Check that the procedure works correctly
- List of files to download
The use case enables users to enter expenses in a form found in a Self Help procedure. The expenses will automatically be formatted in an Excel template and a report will be sent by email to both the user and the Accounting Department.
Objects used
- Excel connector: Set Cell Values method.
- Email sending connector: Send an email method.
- Expense Report Excel file containing the template for generating expense reports.
Procedure
Overview
The implementation of the use case is performed in several phases:
- Phase 1: Create and configure the form for collecting information
- Phase 2: Configure the Excel connector to format form information in the Excel template
- Phase 3: Configure the Email sending connector to send the expense report in Excel format by email to both the user and the Accounting Department
Phase 1: Create and configure the form for collecting information
Step 1: Create the form
The form enables users to enter information related to their expenses.
1. Create a new form in the Resources folder of your Self Help project. Name it Information.
See the detailed procedure
2. Add five simple input fields.
- Date
- Name
- Description
- Amount
Step 2: Add the form to a procedure step
1. Create a new procedure in your Self Help project. Name it Create my expense report.
2. Create the first step, i.e. a Page step. Name it Input information.
3. Click and drag the Information form to the step.
Phase 2: Configure the Excel connector
Step 1: Add the Excel template to a procedure step
The Excel file, Expense Report, contains the template for generating the expense report in Excel format based on the information entered by users in the form.
1. Download the Expense Report Excel template to your workstation.
2. Add it to the Resources folder of your Self Help project.
3. Create an Action with Switch step after the Input information step. Name it Update and send.
Two branches will be created.
4. Click and drag the Expense Report Excel template to the step.
Step 2: Create a new Connector resource
You create a Connector resource in order to update the Expense Report Excel template based on the information entered by users in the form. This uses the Excel connector and the Set Cell Values method.
1. Create a new Connector resource in the Resources folder of your Self Help project.
- Select the connector, Excel connector.
- Click Next.
2. Select the Set Cell Values method.
3. Click Finish.
The new Connector resource will be added to the Resources folder.
Step 3: Add the resource to a procedure step
1. Click and drag the new Connector resource to the Update and send step and click Next.
The window for configuring input and output parameters for the method will appear.
2. Specify the input parameters of the method and click Next.
- Click the + Add a key and a value for parameter link to add a new key/value row.
- In each row:
- In the left field, specify the cells in the Excel file dedicated to displaying each of the five items of information, i.e. cells in the ER sheet.
- In the right field, specify the fields in the Information form containing the information entered by users.
- Date of expenses: ER!B4: Informations.date
- Requestor name: ER!B7: Informations.name
- Requestor email address: ER!B8: Informations.email
- Description of expenses: ER!A11: Informations.description
- Amount of expenses: ER!C11: Informations.amount
3. Specify the output parameters of the method.
- Select the New variable checkboxes next to the result and error output variables to create them.
- Enter a meaningful name for the variables where the output parameter values will be stored so that users can identify them easily.
- result: result_excel variable
- error: error_excel variable
- Click Finish.
4. Click OK in the Parameters Refactoring window.
The Set Cell Values method of the Excel connector will be associated with the Input and send step. The information entered by users can now be processed in the Excel template.
Phase 3: Configure the Email sending connector
Step 1: Create a new Connector resource
You create a Connector resource in order to send the expense report by email to both the user and the Accounting Department. This uses the Email sending connector and the Send an email method.
1. Create a new Connector resource in the Resources folder of your Self Help project.
- Select the connector, Email sending connector.
- Click Next.
2. Select the Step content box in the general connector configuration.
Note: This option is used to automatically load the text found in the Content tab of the step into the email message and insert the Excel report as an attachment.
3. Select the Send an email method.
4. Click Next and click Finish.
The new Connector resource will be added to the Resources folder.
Step 2: Add the resource to a procedure step
1. Click and drag the new Connector resource to the Update and send step and click Next.
The window for configuring input and output parameters for the method will appear.
2. Specify the input parameters of the method and click Next.
- From personal: Enter the form variable, Informations.name.
- To: Enter your company's email address surrounded by double quotes.
- Cc: Enter the form variable, Informations.email (user's email address).
- Subject: Enter the text, New expense report.
- Message content: As the Step content option is selected in the general connector configuration, the email message will automatically contain the text found in the Content tab of the step and the Excel report will be added as a file attachment.
3. Specify the output parameters of the method.
- Select the New variable checkboxes next to the result and error output variables to create them.
- Enter a meaningful name for the variables where the output parameter values will be stored so that users can identify them easily.
- result: result_mail variable
- error: error_mail variable
- Click Finish.
4. Click OK in the Parameters Refactoring window.
The Send an email method of the Email sending connector will be associated with the Update and send step. The Excel file containing the expense report can now be sent by email to both the user and the Accounting Department.
Step 3: Write the message to be sent in the email
1. Select the Content tab in the Description pane of the Update and send step.
2. Write the message to be sent in the email with the file attachment.
Phase 4: Define the conditions for testing the call to the connectors
You must add a condition to the error output variables of the two methods, Set Cell Values (variable error_excel) and Send an email (variable error_mail). If one of the variables is not null, this means that the call to the connectors failed.
See the description of return codes
Step 1: Add a condition to check if the call to the connectors failed
1. Define the condition that will show a failed process, indicated by a non-null value in one of the error output variables.
- Double-click the <cond.> branch of the Update and send step.
The window for defining the condition will appear.
- Select the error_exceloutput variable.
- Select the is not null operator.
- Click the + Add a condition line link to add a new row.
- Select the error_mail output variable.
- Select the is not null operator.
- Indicate that at least one of the conditions must be fulfilled by selecting At least one condition must be true (OR) from the list at the top of the window.
2. Click Finish.
The condition indicating a failed process will appear in the procedure.
3. Create a Page step after the <cond.> branch. Name it error.
4. Select the Content tab in the Description pane of the step.
5. Write a message informing users that the process failed and display the error stored in the error_excel and error_mail output variables.
Step 2: Process the results if the call to the connector was successful
1. Create a Page step after the default branch. Name it Success.
2. Select the Content tab in the Description pane of the step.
3. Write a message informing users that the process was successful.
Phase 5: Check that the procedure works correctly
1. Run the procedure.
2. Enter your expenses.
3. Check that the relevant message appears if the process fails.
4. Check that the relevant message appears and that the email is correctly sent if the process is successful.
5. Check the format of the expense report.
List of files to download
- (procedure Create my expense report)
- (Expense Report)