The Excel Batch task can be used to launch requests based on the individual data rows within an Excel spreadsheet. When a process with the Excel Batch task is launched the requester will be presented with a file attachment question.
Select Configuration > Configure Task to access the window below:
NOTE: THE FOLLOWING MUST BE CONFIGURED ON THE SETTINGS TAB:
To continue, click "Save."
Next edit the Mappings tab.
This tab will show a list of questions selected in the "Form task in launched request to receive data" option on the Settings tab.
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:
For most Excel Batch processes, the start and stop range of any field will be the same. If more than one cell 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 request" from the field titled "Auto-Complete this task with pre-fill data."
In our training example, we have a spreadsheet that is a list of employees and changes. We want to load this file and use it to trigger a process that handles Employee Change requests. The first form of this process has questions assigned to receive information for the changes. The process can then route future tasks to specific users based on the type of change requested.
The Excel Batch process is a simple process that contains only this plugin as shown:
The data in the excel file that will be uploaded is below:
|Employee Name||Current Position||New Position||New Department||New Supervisor||New Location||Benefits Change?||Salary Change?|
|Kent Garbee||Account Manager||Account Director||Sales||Joe Green||Chicago||no||yes|
|Cole Buchman||Director of Sales||VP of Sales||Sales||Will Smith||Dallas||yes||yes|
|John Smith||Support Rep||Support Rep||Support||Kathy Bates||New York||no||no|
|Elena Osuna||Level I Accountant||Level II Accountant||Finance||John Isod||Chicago||no||yes|
|June Taylor||Accounts Payable Rep||Accounts Payable Manager||Finance||John Isod||Chicago||yes||yes|
The configuration for this task is as follows:
You can see that the EmployeeChanges spreadsheet has been uploaded and we have configured the process that we want to be launched, as well as the form task within the process that will receive each row of data. Also, 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 to be prefilled:
As noted above, this example should use row 1 instead of row 2 when configured. And the Value(s) icons are no longer displayed, however the value you targeted will appear in a pop-up during configuration.
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 "Benefits Change" as my field and enter "Yes" as the Value.
In other words, this will now route all requests that include a benefits change that are created as a result of the Excel Batch process to Chet Lemon.