Self Help - Excel Connector - Set Cells Values Method - Use Case

Last modified on 2023/08/09 14:30

  To help you set up this use case, you can download the relevant documentation and sample project. Open url.png See 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

Procedure
        Excel Connector - Set Cells Values Method - Use case - Procedure.png

Overview

The implementation of the use case is performed in several phases:

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.

     Open url.png See the detailed procedure

2. Add five simple input fields.

  • Date
  • Name
  • Email
  • Description
  • Amount

          Excel Connector - Set Cells Values Method - Use Case - Form.png
 

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.

          Excel Connector - Set Cells Values Method - Use Case - Step Input information.png

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.

  Excel template

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.
        Excel Connector - Set Cells Values Method - Use Case - Step Update and send.png

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.
            Excel Connector - Set Cells Values Method - Use Case - Add Excel connector.png

2. Select the Set Cell Values method.

          Excel Connector - Set Cells Values Method - Use Case - Add Set Cells Values method.png

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

          Excel Connector - Set Cells Values Method - Use Case - Excel connector - Input parameters.png

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.

          Excel Connector - Set Cells Values Method - Use Case - Excel connector - Output parameters.png

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.
            Excel Connector - Set Cells Values Method - Use Case - Add Email connector.png

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.

Best Practice icon.png  Specify the other parameters only if your SMTP server configuration is different from the default configuration on the Self Help server.

          Excel Connector - Set Cells Values Method - Use Case - Email connector - General parameters.png

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.

Caution: You must not modify the general configuration.

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.

          Excel Connector - Set Cells Values Method - Use Case - Email connector - Input parameters.png

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.
            Excel Connector - Set Cells Values Method - Use Case - Email connector - Output parameters.png

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.

          Excel Connector - Set Cells Values Method - Use Case - Method Email connector - Content step description.png

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.
   Open url.png 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.
            Excel Connector - Set Cells Values Method - Use Case - Step Update and send - Add condition.png

2. Click Finish.

The condition indicating a failed process will appear in the procedure.
        Excel Connector - Set Cells Values Method - Use Case - Step Update and send - Condition for Error added.png

3. Create a Page step after the <cond.> branch. Name it error.

          Excel Connector - Set Cells Values Method - Use case - Condition for Error - To display message.png

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.

          Excel Connector - Set Cells Values Method - Use Case - Add condition - error.png
 

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.

          Excel Connector - Set Cells Values Method - Use case - Condition for Success - To display message.png

2. Select the Content tab in the Description pane of the step.

3. Write a message informing users that the process was successful.

          Excel Connector - Set Cells Values Method - Use Case - Add condition - success.png

Phase 5: Check that the procedure works correctly

1. Run the procedure.

2. Enter your expenses.

          Excel Connector - Set Cells Values Method - Use Case - Test procedure - Entry informations.png

3. Check that the relevant message appears if the process fails.

          Excel Connector - Set Cells Values Method - Use case - Test procedure - Error process.png

4. Check that the relevant message appears and that the email is correctly sent if the process is successful.

          Excel Connector - Set Cells Values Method - Use Case - Test procedure - Success.png
         Excel Connector - Set Cells Values Method - Use Case - Test procedure - Expense report attached.png

5. Check the format of the expense report.

          Excel Connector - Set Cells Values Method - Use Case - Test procedure - Expense report result.png

List of files to download

Tags:
Powered by XWiki © EasyVista 2022