Microsoft Excel Format for Creating Line Items for BP Records

You can create line items through the Microsoft Excel > Export Line Item Template option within the Menu Options toolbar.

Note: This option is not available in the Bidder portal. A bidder can import or update existing lines that are part of the Requestor package through CSV import only.

You can see Export Line Item Template option only if the integration interface has been defined for the BP record. If the BP record does not have a defined integration interface, then the option will not be made available. In addition, this options will be available when the BP record is in Edit mode. The Export Line Item Template option will be seen whether the Upper Form property allows the addition of line items, or not. Finally, this option will not be available in the End step of a Workflow BP.

MS Excel Template File

When you export your template file, the name of the exported template file will have the following format: <BPName>_Line Items_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_Line Items_Template.xlsx.

MS Excel Template File Worksheets

The exported template file will have the following worksheets:

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 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 Generic Line Item BPs

For Cost BPs of Summary Payment Application SOV type

The first row will have the following static informational text: "Line Item Tabs along with the respective detail form fields"

The rows that follow will list all of the line item tabs.

All of the required fields will have the asterisk symbol (*).

After the D row, a CA row will be seen. This row will have the asterisk symbol (*) for all of the required fields of the cost allocation details.

<Other User-Defined> worksheet

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:

Creating Line Items by way of Importing MS Excel Template Data

To create line items 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.

The sub-options within the Import option will only be seen if the Upper Form properties allow the addition of line items.

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 all required fields for the creation of a line item have been met successfully.

In case of any failures, the import will fail, and the same file that was used for importing the data will be changed to contain information about the errors. After the initial check has been passed, the system will process the file further.

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."

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, the system 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:

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.

After a successful import, the line items get created. Subsequently, the line item log will get refreshed with the newly created line items.

Audit Log

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


Last Published Monday, August 28, 2023