How To
Summary
IBM Food Trust™ Spreadsheet Converter translates Microsoft® Excel® files into XML format for submission to the Connector API. This tool enables you to easily generate valid IBM Food Trust messages from Excel™ files. This service converts content directly from the spreadsheet file to the IBM Food Trust message type in XML format. The template files also contain each XML field that is available for the message type.
Objective
Steps
Overview
Download a sample Excel spreadsheet (or a blank template) below, and review the worksheets and the required formats. Your input file to the Converter API may contain multiple worksheets—including one for facilities (locations) and one for products. The products and facilities worksheets describe your Master data, and provide a single source of attributes and values for reuse. The worksheets contain drop-down lists to select values that the Converter API requires.
Sample spreadsheets
Click any link to view or save the sample spreadsheet (.xlsx) file:
Blank spreadsheet templates
Click any link to save the blank spreadsheet template file; select Excel Workbook (.xslx) as the extension:
Spreadsheet documentation
The Excel spreadsheets below (.xlsx files) provide field-by-field details for each spreadsheet, including detailed field descriptions, required formats, examples, and requirements. Each table entry contains the collection of documentation files for each IBM Food Trust message type.
- First, populate all required Level 1 cells in the first row. The first trade item description, quantity, and quantity UoM (unit of measure) cells (all Level 2) will be populated in the first row.
- To populate the second trade item, create a new row, do NOT populate the Level 1 cells, and populate the Level 2 trade item description, quantity, and quantity UoM cells.
- Create another row and populate the Level 2 cells for the third trade item.
- Adding a new row and populating the required Level 1 cells creates a new aggregation event.
You can view this Level usage in the sample spreadsheets above.
Excel spreadsheet fields
Click any link below to view or save the Excel spreadsheet field-level help file:
Entitlement mode
All spreadsheets have an Entitlement mode worksheet, which lets you specify which of your supply chain partners can view the data. To change the entitlement mode, select a new value from the drop-down list. The entitlement mode applies to the entire XML file that is generated from a spreadsheet. A mode of none indicates that no data entitlement will be specified for the generated XML. When the XML is submitted to the IBM Food Trust API without an entitlement mode, the default data access control for the user's organization is applied. See data entitlement and data access control for details.
Explicit Entitled Org Ids
The Entitlement Mode worksheet also includes a column named Explicit Entitled Org Ids, which lets you explicitly specify which organizations are entitled to the mode selected in the Entitlement Mode dropdown list. Simply place each organization's Org Id on a separate row in the Explicit Entitled Org Ids column.
Custom bizSteps
All event spreadsheets include a hidden tab with selections for the supported bizSteps, such as packing and shipping. Support for custom bizSteps is also provided, in the format http://<client-controlled-domain-name/path/biz_step_name>. To enable selection and upload of a custom bizStep, it must first be added to the event spreadsheet hidden tab, as follows:
- Open the applicable event spreadsheet, such as Aggregation.xls.
- Right-click on any tab at the bottom of the spreadsheet.
- Select Unhide and then bizSteps.
- Add the custom bizStep URL to the bottom (first empty row) of the bizSteps list, in the format
http://<client-controlled-domain-name/path/biz_step_name>. - Once added and saved, users will be able to select the custom bizStep (URL) in the event spreadsheet.
Usage
Modify your downloaded sample (or create it programmatically) and submit it to the spreadsheet Converter API. If you have multiple events to convert to XML, enter the additional events in their corresponding worksheets and submit them to the API.
Swagger
The Spreadsheet Converter API is available on Swagger.
Endpoint prerequisite
To submit data, you must include the following Authorization header:
|
Header
|
Value
|
|---|---|
| Authorization | Bearer <token> |
API responses
One of three responses is typically returned by calling a Converter API endpoint:
|
Response
|
Meaning
|
|---|---|
| 200 | Success |
| 400 | Error in Spreadsheet File |
| 401 | User Unauthorized |
Error messages
401: User Unauthorized - Indicates a problem with the caller's authorization token, such as a missing Authorization Header, or an expired token (valid for three hours after assignment). This message can also result from specifying a token for the incorrect zone (integration or production).
400: Bad Data - Indicates a problem with the submitted data, with a detailed description.
All errors are returned in the response body separated by a new line. Here is an example with multiple errors:
<BadRequestResponse>
<message>
[Aggregations][2, 9]: BizTransaction may not be null when associated BizTransaction Type is populated.
[Aggregations][2, 11]: Trade Item Description may not be null when associated fields are populated.
Aggregation starting with Event ID urn:uuid:6926712e-599f-4c4e-b6e9-8dd888c906bd must have a Trade Item Description populated with Lot and/or Serial populated.</message>
</BadRequestResponse>
|
Errors (fatal)
|
Description
|
Message format
|
|---|---|---|
| Unrecognized Workbook | IBM Food Trust Converter is unable to recognize the workbook format. You will receive this error if you attempt to submit a spreadsheet from the deprecated IBM Food Trust Basic Converter. | Uploaded File Error: Could not recognize Spread Sheet Format. |
| Missing Sheets | The workbook is missing a required worksheet | The following required sheets are missing. [sheet_names] |
| Unrecognized Sheets | The workbook is missing a required worksheet | The following sheets are not recognized. [sheet_names] |
| Missing Header | A worksheet is missing a header entry (column) | <sheet_name> sheet has missing headers [header_names] |
| Missing Field | The sheet specified is missing a value | [sheet_name][row, column]: <column_name> may not be null. |
| Two Missing Fields | The sheet specified requires that at least one of two values must be populated | [sheet_name][row, column]: <column_name_1> and <column_name_2> may not both be null. |
| Multiple Missing Fields | The sheet specified requires multiple associated values must be populated | <sheet_name> starting with <primary_key_column> <primary_key_column> must have a <column_name_1> populated with <column_name_2> and/or <column_name_3>. |
Related Information
Document Location
Worldwide
Was this topic helpful?
Document Information
Modified date:
13 June 2023
UID
ibm11137040