IBM Support

Using spreadsheets to prepare data for upload

How To


Summary

A set of Microsoft® Excel™ templates are provided for convenience, to help in preparing data for uploading it in IBM Food Trust™ in XML format. The following describes the usage as well as the content of the different templates files.

Objective

Use Spreadsheets

IBM Food Trust™ provides the following Microsoft® Excel™ 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)

Note: Each row in an file denotes a new asset.

Usage Notes

Use the following procedure to upload data in an Excel template:

  1. Save the correct template file for your data type from the convert spreadsheets page.
  2. 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).
  3. 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 Sheet

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 Sheet

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 Sheet

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 Sheet

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 Sheet

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 Sheet

Header Description Format
Action Event Action, menu of available actions:
  • Produced
  • Destroyed
  • Packed
  • Unpacked
  • Transformed
  • Observed
  • Purchased
  • Despatched
  • Received
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.

Related Information

Document Location

Worldwide


[{"Business Unit":{"code":"BU059","label":"IBM Software w\/o TPS"},"Product":{"code":"SSNTIG3","label":"IBM Blockchain Transparent Supply"},"Component":"","Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"All Versions","Edition":"","Line of Business":{"code":"LOB59","label":"Sustainability Software"}}]

Document Information

More support for:
IBM Blockchain Transparent Supply

Software version:
All Versions

Document number:
1108353

Modified date:
19 October 2021

UID

ibm11108353

Manage My Notification Subscriptions