Understanding How Using Excel, CSV, and RESTful Services Works

You can use Excel, CSV, and RESTful Services to create a BP record or line item with Line Item Data Picker. When you export the CSV template, the Line Item Data Picker appears, and the direction is set to Input or Both on the Integration tab in uDesigner.

Note:

You cannot modify the BP Data Picker alone if it has an associated Line Item Data Picker value. You must provide both the BP Data Picker value and the BP Line Item Data Picker value to modify either one.

When you use Bulk Create or Bulk Update, you start by exporting the BP design structure to a Microsoft Excel or CSV file and then populating the required fields. If you leave a Data Picker Data Element (DE) field blank that is configured to auto-populate, the system completes the field when you run a Bulk Create. However, it does not complete the field when you run a Bulk Update. To reduce confusion and keep Bulk Create and Bulk Update consistent, 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.

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 the 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 Microsoft 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.

Validations Performed on Line Item Data Picker Values

When you create a record or line items through CSV import, or when a CSV import or REST input request includes Line Item Data Picker values, the system performs the following validations:

  • Verifies that the associated data picker has a selected record. If no record is selected, the system displays an error message.
  • Verifies that the selected record includes the line item. If the line item does not belong to the selected record, the system displays an error message.
  • Verifies that the line item exists. If it exists, the system runs the query conditions that are defined for the Line Item Data Picker. If it does not exist, the system displays an error message.
  • Verifies that duplicate work packages are not included. Work package names are case-sensitive.

The system identifies the specific record or line item that contains an error in the following cases:

  • A form validation rule fails.
  • A DE in the upper form is invalid, such as a DE that must be unique.

    Note:

    DEs with leading and trailing spaces are treated as identical. For example, "EAM" and "EAM ", where the second value includes a trailing space, are treated as the same value.
  • Records in the input file or request contain the same unique DE value.
  • Multiple records or line items exist for a specific value.

The above Line Item Data Pickers validations are also supported in the following REST services:

  • Get BP Record List
  • Get BP Record
  • Get BP Record with attachments
  • Create BP Record
  • Create BP Record with lineitems
  • Update BP Record

    Note:

    You cannot create or update a non-workflow BP record if the input request file contains a status value of Inactive. If you submit a request that contains an Inactive status, the system sends an error message through email notification.

Validations Performed on User Picker Values

When you create a record or line items through CSV import, REST services, or Bulk Update, and the CSV import or input request includes User Picker values, the system performs the following checks:

  • The BP record is created successfully if a single user with the specified first and last name exists in a project with an Active or On-hold status.
  • The BP record is created successfully if multiple users have the same first and last name, but only one user has an Active or On-hold status and all other users have an Inactive status.
  • The BP record is not created if multiple users have the same first and last name and their statuses include Active, On-hold, or Inactive.

Validations Performed on BP Picker Values

If provided, the BP Picker value must meet the configured query conditions. Otherwise, the system displays an Invalid value or multiple records found message. If multiple values are selected, the system determines whether a match for the same values exists in the query.