Use spreadsheets
IBM Food Trust provides the following Microsoft® Excel spreadsheet templates to facilitate uploading data in XML format. Each template is unique, so you must use the template specific to the data type:
- Products
- Facilities
- Purchase Orders
- Despatch Advice
- Receive Advice
- Events (Commission, Decommission, Observation, Transformation, Aggregation, Disaggregation)
Each row in a file denotes a new asset.
Usage notes
Use the following procedure to upload data in a spreadsheet template:
- Save the correct template file for your data type from the convert spreadsheets page.
- Open the template file in Microsoft® Excel and save it as an Excel Workbook: (File -> Save As -> File Format: Excel Workbook (.xslx). An Excel Workbook is a collection of one or more worksheets (spreadsheets).
- To populate data across worksheets, fill them out in the order prescribed by the worksheet titles. For Purchase Orders, for example, enter the data in "Step 1 - Products" first, followed by "Step 2 - Facilities" and "Step 3 - Purchase Orders". Note that some templates only consist of one worksheet.
Products
| Products Sheet Header | Description | Format |
|---|---|---|
| Item Name | The name or description of the product; this value is used in subsequent worksheets | |
| Company Prefix* | Company Prefix used to convert from GS1 to EPC formatting | 4-13+ digits |
| GTIN or IBM Food Trust Product ID | Item GTIN or IBM Food Trust Product Identifier | GTIN: 1234567891011 IFTPI: 0032334.ftpi123 |
* This field can be omitted from any workbook that does not have an Events worksheet.
Facilities
| Facilities Sheet Header | Description | Format |
|---|---|---|
| Organization | Owning Organization Name | |
| Facility Name | Name of facility; this value is available in subsequent worksheets | |
| Role | Facility Role | |
| City | City that the Facility is based in | |
| State | State that the Facility is based in | |
| Company Prefix* | Company prefix used to convert from GS1 to EPC formatting | 4-13+ digits |
| GLN or IBM Food Trust Location ID | Facility GLN or IBM Food Trust Location Identifier | GLN: 1234567891011 IFTLI: 0032334.ftpi123 |
* This field can be omitted from any workbook that does not have an Events worksheet.
Purchase Orders
| Purchase Orders Header | Description | Format |
|---|---|---|
| Reference | A reference identifier for this Order; leave this field blank to continue the previous reference. | |
| Buyer Location | The facility of the buyer; this value is populated from the Facilities worksheet. | |
| Seller Location | The facility of the seller; this value is populated from the Facilities worksheet. | |
| Purchase Date and Time | The date and time of the purchase. | yyyy-mm-ddThh:mm:ss.mmmZ |
| Requested Delivery Date | The requested delivery date (and time). | yyyy-mm-ddThh:mm:ss.mmmZ |
| Line Item Number* | Auto-Generated line number for an item; this value should not be changed. | |
| Products | The product for this line number; each value is populated from the Products worksheet. | |
| Quantity | The quantity purchased of each product. | |
| UoM | Unit of measurement used for the Quantity. |
* The line number increments for each line of a Reference; entering a new Reference resets the line number to 1.
Despatch Advice
| Despatch Advice Header | Description | Format |
|---|---|---|
| Reference | A Reference identifier for this Despatch Advice; leave this field blank to continue the previous Reference | |
| Despatch Date and Time | The Date and Time of the Despatch | yyyy-mm-ddThh:mm:ss.mmmZ |
| Ship From (Single Only) | The Facility the item is shipping from; this value is populated from the Facilities worksheet. | |
| Ship To (Single Only) | The Facility the item is shipping to; this value is populated from the Facilities worksheet. | |
| Line Item Number* | Auto-Generated line number for an item; this value should not be changed. | |
| Purchase order Reference (Scenario Only) | Populated with all Reference identifiers entered in the Purchase Orders worksheet. | |
| Purchase order Line Number (Scenario Only) | Populated with line numbers for the selected Reference identifiers entered in the Purchase Orders worksheet. | |
| Despatched Item (Scenario) | Auto-Generated Product name from the selected Reference identifier and Line number | |
| Despatched Item (Single) | Product despatched in this advice | |
| Despatched Quantity | Quantity despatched for this Item and line number | |
| Despatch UoM | Unit of Measurement for this Quantity | |
| Shipper Company Prefix | Company Prefix of the Shipper | 4-13+ digits |
| Shipper Identifier | SSCC** of the shipper | <Company Prefix>.<Serial Reference> i.e.
5012345.0678901234 |
* The line number increments for each line of a Reference; entering a new Reference resets the
line number to 1.
** Serial Shipping Container Code
Receiving Advice
| Receiving Advice Header | Description | Format |
|---|---|---|
| Reference | A Reference identifier for this Receiving Advice; leave this field blank to continue the previous Reference. | |
| Received Date and Time | Date and Time Received | yyyy-mm-ddThh:mm:ss.mmmZ |
| Origin (Single Only) | Origin of item; this value is populated from the Facilities worksheet. | |
| Received Location (Single Only) | Location item was received; this value is populated from the Facilities worksheet. | |
| Despatch Advice Reference (Scenario Only) | Populated with References from the Despatch Advice worksheet. | |
| Despatch Advice Line Number (Scenario Only) | Populated with line numbers for the selected Reference identifiers entered in the Despatch Advice worksheet. | |
| Received Product (Scenario) | Auto-Generated Product name from the selected Reference identifier and Line number | |
| Received Product (Single) | Product received in this advice | |
| Quantity Received | Quantity received for this item and line number | |
| Quantity UoM | Unit of Measurement used for the Quantity |
Events
When you upload EPCIS events to IBM Food Trust the event data rules apply.
| Events Header | Description | Format |
|---|---|---|
| Action | Event Action, menu of available actions:
|
|
| Event ID | A unique ID for the event. Enter a unique ID. Using UUIDs is recommended. | |
| Event Location | Location of event; values are populated from the Facilities worksheet. | |
| Event Date and Time | Date and Time of event | yyyy-mm-ddThh:mm:ss.mmmZ |
| Products | Products involved in this event; values are populated from the Products worksheet. | |
| Quantity | Quantity of product | |
| UoM | Unit of Measurement used for the Quantity | |
| Lot | Lot number used for this product | |
| Products After Transformation | Products created after a Transformation event; values are populated from the Products worksheet. | |
| After Quantity | Quantity of transformed product. | |
| After UoM | Unit of Measurement used for the After Quantity. | |
| After Lot | Lot number used for the transformed product | |
| Purchase Order Reference (Scenario Only) | Purchase Order to reference in this event; values are populated from the Purchase Orders worksheet. | |
| Despatch Advice Reference (Scenario Only) | Despatch Advice to reference in this event; values are populated from the Despatch Advice worksheet. | |
| Receiving Advice Reference (Scenario Only) | Receiving Advice to reference in this event; values are populated from the Receiving Advice worksheet. |