Excel Batch Plugin

The Excel Batch Plugin can be used to launch requests based on the individual data rows within an Excel spreadsheet.

Note: When a process with the Excel Batch Plugin is launched the requester will be presented with a file attachment question.

Plugin Configuration

Select Configuration > Configure Task to access the window below:

THE FOLLOWING MUST BE CONFIGURED ON THE SETTING TABS:

• Sample Spreadsheet in Format to be Uploaded: Attach a sample or template of the spreadsheet that will be uploaded as part of the request.
• Process to Launch: Select the process that should be launched for each row of data.
• Form task in launched request to receive data: Select the form task in the process being launched that you want to pass data into.  Normally this will be the first form task in the process.
• Requester of launched process: Allows you to set therequester of the request launched by the Excel Batch Plugin Task based on the following choices:
  • This requests requester - Sets the requester of the launched request to the requester of the parent request that contains the Excel Batch Plugin Task.
  • This requests client - Sets the requester of the launched request to the client (on behalf of) of the parent request that contains the Excel Batch Plugin Task.
  • Select user - Allows the selection of a specific user to be the requester of the launched request . (This is set via the optional setting explained below.)
• Client of launched process: Allows you to set the client (on behalf of) of the request launched by the Excel Batch Pluginbased on the following choices:
  • This requests client - Sets the requester of the launched request to the client (on behalf of) of the parent request that contains the Excel Batch Plugin Task.
  • This requests requester - Sets the requester of the launched request to the requester of the parent request that contains the Excel Batch Plugin Task.
  • Select user - Allows the selection of a specific user to be the client of the launched request . (This is set via the optional setting explained below.)
• Parent view options: Defines the visibility of data between the Excel Batch (parent) and subsequent launched (child) requests.  View options include:
  • Child and parent requests can not see each other's details - The request detail of the request containing the Excel Batch (parent) Plugin Task will not contain a link to any launched child request, and the link back to the parent request will not be visible in the request detail of any child requests.
  • Parent can see child request's details, but child can not see parent's - The request detail of the request containing the Excel Batch (parent) Plugin Task will contain a link to each launched child request, but the link back to the parent request will not be visible in the request detail of any child requests.
  • Child can see parent request's details, but parent can not see child's - The request detail of the request containing the Excel Batch (parent) Plugin Task will not contain a link to any launched child request, but the link back to the parent request will be visible in the request detail of any child requests.
  • Parent and child requests can see each other's details - The request detail of the request containing the Excel Batch (parent) Plugin Task will contain a link to any launched child request, and the link back to the parent request will be visible in the request detail of any child requests.
• Child view options- Defines whether or not the request detail link and data what will be visible between the launched (child) requests.
  • Siblings can not see this launched request - This prevents launched (child) requests from seeing data from one another.
  • Siblings can see this launched request - This allows launched (child) requests to see data from one another.

ADDITIONAL SETTINGS:

• Skip first row of spreadsheet: Checking this box allows you to skip launching a new request for the 1st row of data in the file when it is processed.  This is useful when the file uploaded to the task contains a header row.  (Note: Checking this box "Yes" does not actually change the Start Range of the data mappings discussed below.)
• Name of launched request: Allows you to provide an alternative name for the launched requests.  If left empty, the name of each new request will be the name of the process launched by the Excel Batch Plugin Task.
• Set requester to the following user - If "Select User" is chosen for "Requester of launched process" above, this field will allow you to define the user.
• Set client to the following user - If "Select User" is chosen for "Client of launched process" above, this field will allow you to define the user.

To continue, click "Save".  You will now see the "Mappings" tab.  This tab will show a list of questions from the from selected in the "Form task in launched request to receive data" option from the "Settings" tab.  This screenshot is from the training example used in this configuration overview:

To complete the configuration, you will need to provide mapping information for each question that you wish to populate with data from the uploaded excel file.  This includes:

• Sheet: This defines the sheet within the excel spreadsheet file from the 'Sample Spreadsheet in Format to be Uploaded' option of the "Settings" tab where the data range is located.
• Start Range: This defines the first cell of the spreadsheet data range that you are mapping to the given question label on the form.
• End Range: This defines the last cell of the spreadsheet data range that you are mapping to the given question label on the form.

For most Excel Batch processes, the start and stop range of any field will be the same. If more than one cell is is defined between the Start and End range, the question will be populated with a comma separated list of values from the spreadsheet. Review the training example below for further explanation.

Click "Close" to complete the configuration process.

Note: Once you have configured the Excel Batch, you should review the 'Form Settings" tab of the form task configured as the "Form task in launched request to receive data" to determine if and how you want the form being populated to be completed.  Specifically, you can set the task to auto-complete when it is triggered by the Excel Batch process. To do this, go to the Form Task for this process and select Configuration > Configure Task.

Select "When process is launched as a child process" from the field titled "Auto-Complete this task with pre-fill data".

Training Example

In our training example, we have a spreadsheet that is a log of IT-related errors.  We want to load this file and use it to trigger a process that handles Error Tickets. The first form of this process has questions assigned to receive information on the error.  The process can then route future tasks to specific users based on the type of error reported. The Excel Batch process is a simple process that contains only this plugin as shown:

The excel file that will be loaded is below:

The configuration for this task is as follows:

You can see that the Sample Error Log Spreadsheet has been uploaded and we have configured the process that we want launched, as well as the form task within the process that will receive each row of data.  In addition, because my spreadsheet has a header row in it, I've selected to skip the first row of results.

Once I click "Save" the Mappings tab is made visible.

I configure each question that I want prefilled - even though I want the system to skip the first line of my spreadsheet, I still use A1 as my start and end ranges.  This states that I want one row's worth of data from column A.

To validate that I've selected the right column, I can click on the "Values" Icon to view the data from my template file:

My header from the spreadsheet is shown as the preview data which tells me I have the correct column and validates the fact that I should, in fact, skip the first line of data when I process the spreadsheet in a real request.

Click 'Close' when you have completed the mappings.

In addition to processing and creating the individual requests based on the spreadsheet, I also want to route the second task within my child or launched requests based on the data in the spreadsheet.

Begin by adding each recipient to the Approval Task like you would with any standard Approval Task:

I then add a rule for each that uses Data as the Source.  In this case I select the form that the excel data will be mapped to and choose "Error Type" as my field and Enter "Web404" as the Value.  
In other words, this will now route all Web404 error types that are created as a result of the Excel Batch process to Lois Lane.

Click "Close" to complete the rules and again to exit the Recipient screen.