Microsoft Excel Format for Creating BP Records

The following applies to all business Processes where BP records can be created, in bulk, by way of CSV import.

Note: The Request for Bid business process (RFB BP) does not support bulk creation of records through CSV.

The following does not apply to managers such as Space Manager, Portfolio Manager, Configurable Managers, Code and Records-based Configurable Managers, and Code-based Configurable Managers that allow the creation of records by way of CSV import.

To create BP records, in bulk, through MS Excel:

In your Shell go to the Logs grouping node (BP Logs) node and select a BP to open the BP log window.
From the toolbar options, click Actions, and click Export Microsoft Excel Template. This option is only available for the users with View permission, to the log.

To be able to download your MS Excel template the integration interface must be defined for the BP record. If the BP record does not have a defined integration interface, then the entire import will be aborted and no BP records or BP line items will get created. In such scenario, Unifier displays the following error message: "Import could not be completed. There are errors in this template file. These errors can be seen in the Import Data Template worksheet. Do you want to download the file and fix the errors? Click 'Yes' to download the file and 'No' to upload a different template file."

MS Excel Template File

When you export your template file, the name of the exported template file will have the following format: <BPName>_Template

Note: The name will always be the source string without spaces.

Example

If the BP name is Daily Reports, then the template file name will be: DailyReports_Template.

MS Excel Template File Worksheets

The exported template file will have the following worksheets:

Instructions worksheet
Imported Data Template worksheet
<Other User-Defined> worksheet

Instructions worksheet

This worksheet is generated automatically, and you cannot change the name of this worksheet. The Instructions worksheet contains all of the relevant information about entering the BP record and line item information data. The content of the instructions worksheet will depend on the type of business process.

Imported Data Template worksheet

This worksheet is generated automatically, and you cannot change the name of this worksheet. Always set by the system as the second worksheet in the template file, this worksheet contains the Unifier data needed for creating BP record and line item.

Note: The name of this worksheet will be translated according to the user's language preference.

The top section of this worksheet is dependent on the business process type.

For the Cost BPs of Summary Payment Application SOV type, the "Enter date below" section will have an additional row to enter the Cost Allocation. In addition to the H & D rows, the CA row will also be seen. This row will have the asterisk symbol (*) for all the required fields of cost allocation details.
For the Simple BPs, the worksheet will have the "Enter data below" section and an H row that contains the column headings.
For Line items type of BPs, the first few rows will list all of the line item tabs. The first line will have the static informational text "Line Item Tabs along with the respective detail form fields." The tabs will be listed one below the other. The hidden tabs will also be displayed, similar to the CSV template files. The "Tab Name" will be the first column followed by the detail form fields. All of the required fields of the line item tab will be marked with an asterisk symbol (*).

<Other User-Defined> worksheet

A user may insert additional sheets for their reference. These sheets will not be processed by Unifier at the time of import.

This worksheet will not be processed by Unifier. An end-user (you) can add multiple worksheets.

Note: The content of this worksheet will be ignored at the time of import.

Entering Data in the MS Excel Template File

The following explains the general rules for entering data in the MS Excel template file.

You need to use the Import Data Template option in order to be able to enter data and ultimately create records, or to create records with line items.

Note: Review the information on the Instructions worksheet to minimize the import errors.

When entering data, you can:

Enter data in all of the fields.
Copy the values of a column from another sheet. This is to state that you can use the MS Excel native features when entering data.
Use Excel formula feature to calculate values in the columns.
Enter UTF-8 character string in the String fields.

For all data types, Unifier will only process the data and not the format, when importing.

Example

A user may have entered -999.99 and the format on the cell may have been (999.99), and the user will see the data in Excel as (999.99). When Unifier processes this data, -999.99 will get processed and not (999.99).

Creating BP Records by way of Importing MS Excel Template

To create BP Records by way of Importing MS Excel Template, click the Import option (Actions > Import), click to select the Data From Microsoft Excel File sub-option, and follow the prompts.

The Import option has the following sub-options:

Note: These options are displayed for users who can create new BP records.

Data From CSV File
Data From Microsoft Excel File

Uploading

When you upload an MS Excel file, in the Upload Microsoft Excel File window (similar to the CSV) you can select the MS Excel file (using the Select File option) and click Next to proceed with your upload. See below for information about adding attachments.

Validating Microsoft Excel File Content

There are multiple levels of validations that take place during the file import process. To begin, the system will check to ensure that the BP record has the required setup for the creation of records through MS Excel in the Auto Creation tab of the BP setup.

The system will also check to ensure that values have been entered in all of the required fields (for the creation of the BP record, along with the line items). In case of any failures, the import will fail and the same file that was used for importing the data will now be changed to contain error information.

After the initial validation ends, the system will process the file further. At this stage, all of the validations pertaining to the system-level rules (as well as user-defined form-level validations) will be performed. If the creation fails, then an email notification will be sent to the users and groups mentioned in the business process setup. This email will contain the reason for the failure.

Errors and Corrections

When an error is encountered in any of the rows, the entire import will be aborted and no BP records, line items, will get created. As mentioned earlier, when the import file contains errors, the system will alert the user with the following message: "Import could not be completed. There are errors in this template file. These errors can be seen in the Import Data Template worksheet. Do you want to download the file and fix the errors? Click 'Yes' to download the file and 'No' to upload a different template file."

Additional Information Regarding Importing an MS Excel Template File

The name of a downloaded file will be the name of the imported MS Excel template file. If you decide to download a file with errors, then the Import Data Template worksheet will have the errors listed, and the structure of displaying the errors will be as follows:

On the first row of the worksheet, an additional column with the following static text will be included: "Following errors were encountered while importing the data from the template. This column must be deleted after fixing all the errors. If this column exists in the file at the time of import, the import will fail."
For errors that are not specific to a row, such as the auto-creation setup not being met, the information will be displayed in the first H row that contains the data.
If a row has multiple errors, then all of the errors will be displayed in the error cell.
Each row that has an error will display the actual error message, in the first column. The following error format is used for the required fields:
"Values are missing in required fields: <Field Label 1>, <Field Label 2>"

Example

Values are missing in required fields: Contract Reference

"Values are missing in required fields: <Field Label 1>, <Field Label 2>, <Field Label 3>"

Example

Values are missing in required fields: Type, Short Description, Asset Type

For other form-level validations, the message will be displayed against the row.

Example

Equipment Units Available cannot be less than 10. This number is required for contingency purposes.

Adding Attachments

Similar to the CSV file import, you can add attachments. You can upload all the referenced attachments in the Add Attachments section. You can use the Upload Microsoft Excel File window (in the 'Add Attachments' step) to add attachments to your BP record, or line item, during your import. The review section on the screen enables you to see what which files are uploaded and which files are remaining.

Example

When you upload the MS Excel file, all of the attachments listed in the MS Excel file will be read by Unifier, and they will get listed in the ‘Files to be uploaded' section. As, and when, you upload the files, the status of the respective file will turn from ‘Waiting for File' to ‘Ready to be uploaded'.

You can perform sort operations on the Status column in the review section.

When you click Upload, Unifier uploads the attachments onto the respective BP record, or line item, as required.

Validating Added Attachments

When you upload your attachments, Unifier performs validations and:

If there is no data in the Attachments column of the MS Excel file that is being uploaded, then Unifier processes the file as a file that does not need have any attachments that need to be uploaded.
If you have specified a name for the Attachment column (either at the BP record level or at the line item level), then Unifier ignores the column headings.

For validating a BP record level MS Excel import:

When creating BP records by way of using MS Excel file import from the BP log, Unifier does not retain the Attachment option that was used in the validation form of the integration interface. If you decide to import the attachments while using MS Excel import, then the system will allow the import action. If there are files that have been referenced in the Attachments column of the MS Excel file, and Unifier cannot find the files in the attachment payload (attachment missing), then the Attachments table will provide the following informational text: "Some files are still waiting to be uploaded. Do you want to proceed without uploading those files?" If you click OK, then Unifier will ignore the missing attachments (from the MS Excel file) and creates BP records, or line items, without any attachments. If you click Cancel, you will be returned back to the 'Upload Attachments' step of the upload process.

If the Status value for some of the files in the review section states Waiting for the file, and you click Upload, then the Attachments table will provide the following informational text: "Some files are still waiting to be uploaded. Do you want to proceed without uploading those files?" If you click OK, then Unifier will ignore the missing attachments (from the MS Excel file) and creates BP records, or line items, without any attachments. If you click Cancel, you will be returned back to the 'Upload Attachments' step of the upload process.

For validating a line item level MS Excel import:

When you proceed with a line item import, from a Line Item tab, if the option Upload documents from My Computer is not selected in the design, then you cannot add attachments, and the Attachments table will provide the following informational text: "Uploading of Attachments is not allowed."

If there are files that have been referenced in the Attachments column of the MS Excel file, and Unifier cannot find the files in the attachment payload (attachment missing), then the Attachments table will provide the following informational text: "Some files are still waiting to be uploaded. Do you want to proceed without uploading those files?" If you click OK, then Unifier will ignore the missing attachments (from the MS Excel file) and creates the line items, without any attachments. If you click Cancel, you will be returned back to the 'Upload Attachments' step of the upload process.

If there are files uploaded in the attachment payload, but the uploaded files are not present in the Files to be uploaded section of the MS Excel, then Unifier will not upload those files. In this scenario, the Status value will not change in the review section, for the files.

Audit Log

Each BP record created through Microsoft Excel import will be audited. The following is the audit attributes:

For Non-Workflow BPs:
- Event
  Creation by way of Microsoft Excel Import
- Action
  Create <BP Name>
For Workflow BPs:
- Event
  Creation by way of Microsoft Excel Import
- Action
  <Action Name>

Last Published Monday, August 28, 2023