applies to version: 8.2.x; author: Marcin Wiktor
BPS offers users the option to import data to an Item list from an Excel spreadsheet, as well as exporting rows of data from an Item list to an .xls file.
In this article we will demonstrate how to employ these two functions, show a sample configuration for importing data, explain mapping headers to columns, as well as dealing with rows containing erroneous data (like text in an integer number field).
1. Configuring the Import data function.
The first step is to specify the step on which we want our users to be able to import data, and configure the Item list settings for it.
This must be done separately for each step, it is impossible to simultaneously allow importing for every step all throughout the workflow.
To do this, go to the Step edit menu, select the “Forms” tab and in the “Item list” section of the screen click the advanced options button (Marked as 2 on the screenshot).
It is worthwhile to mention that changing these setting for specific steps doesn’t break up the inheritance of global settings for the form.
Next, mark the “Allow Excel import” checkbox and save the process.
To give an example, here is an item list comprised of 3 columns – Name (single line of text), Quantity (floating-point number) and Comment (multiple lines of text).
Name and quantity are both required fields, the comment column will be optional.
Initially, the .xls file we want to import will look something like this:
Next, we want to configure the import function on our Item list. Click the Item list Form field, then click the Configure button in the bottom right. Go to the “Import” (second-last) tab.
In the Initial column field, leave the default value “A”.
Because the first row of our Excel spreadsheet doesn’t contain data or column headers, only a caption which reads “Demand sheet”, change the Initial row field from 1 to 2.
In Criterion for selection of columns, leave the default value, which is “headers”. This way, spreadsheet entries will be assigned over to the Item list columns based on headers (ex. Name, Comment), instead of letters (ex. A or C). Thanks to this, if we ever insert an extra column before the “Comment” one (ex. Unit price), or if we arrange them differently, we won’t have to reconfigure the settings, because headers remain the same.
“Column’s letter” should only be used if our spreadsheet doesn’t have a row containing headers for columns – in such a case, identifying columns by their letters is the only option.
Import type defines how the system will behave if it encounters erroneous entries.
For the purpose of this demonstration, we have added 2 new rows to our spreadsheet which contain data of the wrong type.
In row 7, the quantity is spelled out, instead of being the expected integer number.
Row 8 doesn’t have anything in the quantity column, even though it is a required field.
If we select the “Stop importing on validation error” option, no rows will be imported. We will get a popup message, stating that there is invalid data in rows 7 and 8.
“Skip invalid rows” will display the same error message as above, but rows 3-6 will be imported correctly. Rows 7 and 8 will be skipped.
The last option – “Import Invalid cells as blank”, will also display the same message, but rows 7 and 8 will be imported regardless. Invalid data will be turned to blank values.
Require values – for this field select “Use step configuration” instead of “require all columns”. This will make it so that fields are required (or not) according to the configuration in the Fields matrix. As a result, rows with an empty “Comment” column will not be treated as invalid, because we didn’t set the “Comment” field as required in the Fields matrix.
If we select “require all columns” instead, rows 3, 4 and 5 will be considered invalid, because their comment field is empty.
In our example, the only option in the „Format” column is “Default”. The „Format” setting is mainly used when our Form field is a Picker field type.
For Picker fields it allows us to choose whether the value we import from a spreadsheet cell will be treated as an ID or as a Name.
The three “Format” options for Picker fields:
- Default – Default value formatting (depends on the Item list column type),
- ID – Treats the column/header as an ID,
- Name – treats the column/header as a display name.
For the settings in the screenshot above, importing our file will generate a pop-up message about errors in rows 7 and 8, but because we allowed invalid values to be imported as empty fields, they will import as blank in the “Quantity” column. The remainder will be imported like so:
2. Configuring the export to Excel function
Allowing Workflow users to export data from Item lists to .xls files is extremely easy.
Go to the step edit menu, select the “Forms” tab, and in the “Item list” section of the screen click the advanced options button, select the “Allow Excel export” check box. Save the process.
The Export button will only be visible in Edit mode through clicking the “Edit” option for the document in question. It is important to keep this in mind when we want our users to export data on one of the end steps, where normal users generally only have the option to view documents, not edit them.
After exporting data from the Item list used in the above example we get the following effect: