Using the Excel Template to Create BP Records and BP Line Items in Bulk

Creating business process (BP) records in bulk through Microsoft Excel templates is available for all BPs that support bulk creation of records and line items.

An Excel file or template supports formulas and special characters, and it contains multiple worksheets.

Note:

  • A CSV file or template does not support formulas or special characters, and it does not have multiple worksheets. To export a CSV template, select Actions, and then select Export CSV Template.
  • When you use Bulk Create or Bulk Update, first export the BP design structure to an Excel or CSV file, and then populate the required fields. The BP design determines the number of header record (H) fields that appear in the file. If you leave a Data Picker data element (DE) field blank and the field is configured to auto-populate, the system completes the field during Bulk Create. The system does not complete the field during Bulk Update. To reduce confusion and maintain consistency for both Bulk Create and Bulk Update, the system uses the Excel or CSV file to populate the Data Picker field if the field is excluded from the Integration Form for the BP design.
  • If the BP design supports it, you can enter the filename of an applicable attachment in the Document Picker DE column. Do not enter filenames of regular attachments in this column.
  • The picker supports integration with Excel and CSV templates for project/shell-level record imports because these imports support attachments. Master Log and Bulk Update imports do not support attachments, so they do not support the picker. The picker also supports the Create BP Record with Attachment (V2) and Update BP Record with Attachment (V2) REST services at the project/shell level.

From the Actions, you can select Export Microsoft Excel Template to create a single BP record or multiple BP records in bulk that include formulas, special characters, and multiple worksheets.

Note:

This option is available to all users who have View access to the log.

To create line items in a single BP record or multiple BP records that include formulas, special characters, and multiple worksheets, select More Actions, and then select Export Line Item Template.

Due Date Behavior

The behavior of the due_date field depends on the Workflow Settings for the BP.

If the Override Workflow Due Date field is set to Yes, you can edit the Due Date during creation. A Due Date that you add or update through file import also applies.

If the Override Workflow Due Date field is set to No, you cannot edit the Due Date during creation. You also cannot edit the Due Date after work starts or after the work moves to the next step in the workflow process.

When you create or update workflow BP records through file import from CSV or Excel, the system sets the Due Date based on the Workflow Settings. It ignores the Due Date in the input file.

When you create workflow BP records through a REST API, the system sets the Due Date based on the Workflow Settings and ignores the Due Date in the input request. When you update workflow BP records through a REST API, you cannot change the Due Date.

Supported Microsoft Excel File Formats

The supported Excel file formats are Excel workbooks with the .xlsx and .xlsm extensions.

Note:

  • Strict Open XML Spreadsheets with the .xlsx extension are not supported.
  • Google spreadsheets with the .xlsx extension are not supported.

Supported Microsoft Excel Versions

The supported Microsoft Excel versions are Excel 2010 (v14.0) and later.

Supported Microsoft Excel Functions

The supported Excel functions are: ABS, ACOS, ACOSH, ADDRESS, AND, AREAS, ASIN, ASINH, ATAN, ATAN2, ATANH, AVEDEV, AVERAGE, BIN2DEC, CEILING, CHAR, CHOOSE, CLEAN, CODE, COLUMN, COLUMNS, COMBIN, COMPLEX, CONCATENATE, COS, COSH, COUNT, COUNTA, COUNTBLANK, COUNTIF, COUNTIFS, DATE, DAY, DAYS360, DEC2BIN, DEC2HEX, DEGREES, DELTA, DEVSQ, DGET, DMAX, DMIN, DOLLAR, DSUM, EDATE, EOMONTH, ERROR.TYPE, EVEN, EXACT, EXP, FACT, FACTDOUBLE, FALSE, FIND, FIXED, FLOOR, FREQUENCY, FV, GEOMEAN, HEX2DEC, HLOOKUP, HOUR, HYPERLINK, IF, IFERROR, IMAGINARY, IMREAL, INDEX, INDIRECT, INT, INTERCEPT, IPMT, IRR, ISBLANK, ISERR, ISERROR, ISEVEN, ISLOGICAL, ISNA, ISNONTEXT, ISNUMBER, ISODD, ISREF, ISTEXT, LARGE, LEFT, LEN, LN, LOG, LOG10, LOOKUP, LOWER, MATCH, MAX, MAXA, MDETERM, MEDIAN, MID, MIN, MINA, MINUTE, MINVERSE, MIRR, MMULT, MOD, MODE, MONTH, MROUND, NA, NETWORKDAYS, NOT, NOW, NPER, NPV, OCT2DEC, ODD, OFFSET, OR, PERCENTILE, PI, PMT, POISSON, POWER, PPMT, PRODUCT, PROPER, PV, QUOTIENT, RADIANS, RAND, RANDBETWEEN, RANK, RATE, REPLACE, REPT, RIGHT, ROMAN, ROUND, ROUNDDOWN, ROUNDUP, ROW, ROWS, SEARCH, SECOND, SIGN, SIN, SINH, SLOPE, SMALL, SQRT, STDEV, SUBSTITUTE, SUBTOTAL, SUM, SUMIF, SUMIFS, SUMPRODUCT, SUMSQ, SUMX2MY2, SUMX2PY2, SUMXMY2, T, TAN, TANH, TEXT, TIME, TODAY, TRANSPOSE, TREND, TRIM, TRUE, TRUNC, UPPER, VALUE, VAR, VARP, VLOOKUP, WEEKDAY, WEEKNUM, WORKDAY, YEAR, YEARFRAC.

The following topics explain how to use Excel to create a single BP record or multiple BP records and line items that contain formulas, special characters, and multiple worksheets.