Self Help - Excel Connector - Get Cells Values Method - Use Case
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
- Excel connector: Get Cell Values method.
- Excel file for retrieving information related to asset tags -
See the description
Procedure
Description of the Excel file
The Excel file, Asset, contains two sheets.
- The BD sheet contains the list of assets and associated information.
- 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
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 retrieve information in the Asset Excel file
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.
See the detailed procedure
2. Add a simple input field.
- Reference
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.
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. See the description
1. Download the Asset Excel file to your workstation.
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.
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.
2. Select the Get 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 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.
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.
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. 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.
2. Click Finish.
The condition indicating a failed process will appear in the procedure.
3. Insert 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 output variable.
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.
- 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.
- 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)
Phase 4: Check that the procedure works correctly
1. Run the procedure.
2. Enter an asset tag.
3. Check that the relevant message appears if the process fails.
4. Check that the information on the asset is correctly displayed if the process is successful.
List of files to download
- (procedure Getting information about an asset)
- (Asset)