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

Last modified on 2023/08/09 14:24

  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 an asset tag in a form found in a Self Help procedure. The value will then be sent to an Excel file containing asset tags and processed. The model, manufacturer and cost of the asset corresponding to the asset tag will be retrieved and displayed.

Objects used

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

Description of the Excel file

The Excel file, Asset, contains two sheets.

  • The BD sheet contains the list of assets and associated information.
        Excel Connector - Get Cells Values Method - Use case - Excel file - Sheet BD.png
  • The Search sheet contains:
    • Cell A1 where an asset tag can be entered
    • A range of cells, A2:D2, containing formulas for retrieving and displaying the information associated with the asset tag
          Excel Connector - Get Cells Values Method - Use case - Excel file - Sheet Search.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 the asset tag.

1. Create a new form in the Resources folder of your Self Help project. Name it Reference.

     Open url.png See the detailed procedure

2. Add a simple input field.

  • Reference

          Excel Connector - Get 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 Enter asset tag.

2. Create the first step, i.e. a Page step. Name it Get information on asset.

3. Click and drag the Reference form to the step.

          Excel Connector - Get Cells Values Method - Use case - Step Input information.png

Phase 2: Configure the Excel connector

Step 1: Add the Asset Excel file to a procedure step

The Asset Excel file contains a list of assets and associated information. It enables you to search for a given asset tag using formulas. Open url.png See the description

1. Download the Asset Excel file to your workstation.

  Asset Excel file

2. Add it to the Resources folder of your Self Help project.

3. Create an Action with Switch step after the Get information on asset step. Name it Get information.

Two branches will be created.
    Excel Connector - Get Cells Values Method - Use case - Step Get the information.png

4. Click and drag the Asset Excel file to the step.
 

Step 2: Create a new Connector resource

You create a Connector resource in order to send the value entered by users in the form to the Asset Excel file and retrieve the information associated with the asset tag. This uses the Excel connector and the Get 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 - Get Cells Values Method - Use case - Add Excel connector.png

2. Select the Get Cell Values method.

          Excel Connector - Get Cells Values Method - Use case - Add Get 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 Get information 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.

  • In the Result cells field, enter the cells in the Excel file dedicated to retrieving information on the asset: "Search!B2;Search!C2;Search!D2".
  • Click the + Add a key and a value for parameter link to add a new key/value row.
  • In this row:
    • In the left field, specify the cell in the Excel file dedicated to the asset tag entered, i.e. cell A1 in the Search sheet: Search!A1.
    • In the right field, specify the field in the Reference form containing the asset tag entered by users: Reference.reference.

          Excel Connector - Get 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.
  • Click Finish.

          Excel Connector - Get Cells Values Method - Use case - Excel connector - Output parameters.png

4. Click OK in the Parameters Refactoring window.

The Get Cell Values method of the Excel connector will be associated with the Get information step. The asset tag entered by users can now be retrieved and processed in the Asset Excel file.

Phase 3: Define the conditions for testing the call to the connector

You must add a condition to the error output variable of the Get Cell Values method. If the variable is not null, this means that the call to the connector failed.
Open url.png See the description of return codes

Step 1: Add a condition to check if the call to the connector 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 Get information step.
    The window for defining the condition will appear.
  • Select the error output variable.
  • Select the is not null operator.
        Excel Connector - Get Cells Values Method - Use case - Step Get the information - Add condition.png

2. Click Finish.

The condition indicating a failed process will appear in the procedure.
    Excel Connector - Get Cells Values Method - Use case - Step Get the information - Condition for Error added.png

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

          Excel Connector - Get 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 output variable.

          Excel Connector - Get 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 - Get 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.

  • Insert a table to display the results.
  • Enter the information to be displayed on the asset in the first column, i.e. asset tag, model, manufacturer, cost.
        Excel Connector - Get Cells Values Method - Use case - Add condition - success - Information to display.png
  • Enter the expression corresponding to the value to be displayed in the second column.
    • Asset tag: Reference.reference (value entered by users in the form)
    • Model: result["Search!B2"] (value found in cell B2 in the Search sheet of the Excel file)
    • Manufacturer: result["Search!C2"] (value found in cell C2 in the Search sheet of the Excel file)
    • Cost: result["Search!D2"] (value found in cell D2 in the Search sheet of the Excel file)
          Excel Connector - Get Cells Values Method - Use case - Add condition - success - Values of information to display.png

Phase 4: Check that the procedure works correctly

1. Run the procedure.

2. Enter an asset tag.

          Excel Connector - Get Cells Values Method - Use case - Test procedure - Entry informations.png

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

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

4. Check that the information on the asset is correctly displayed if the process is successful.

          Excel Connector - Get Cells Values Method - Use case - Test procedure - Success.png

List of files to download

Tags:
Powered by XWiki © EasyVista 2022