Workbook Integration Guide

The integration is bi-directional – there is a pre-population of data when downloading a workbook from the software to provide to the inspector. Then there is a set of required fields needed by the system in order to re-upload the completed file back into the software.

 

Pre-population

Workbooks are pre-populated when downloading a workbook ready to provide to an inspector.
Named cells in the Workbook Template are populated as follows:
Fill in each of the following cells with data from the Job Order.

Field Name	Value
FOF_File_No	Reference number
FOF_Office	Office name
FOF_Type_Of_Operation	Job order type name
FOF_Voyage_No	Voyage number
FOF_Port	Port
FOF_Terminal_1	Terminal
FOF_Coordinator_Name	Coordinator display name
FOF_Date	Date Format: m-d-Y

If there are inspection subjects (vessels, barges, tanks etc), populate the following named fields as described:

Field Name	Value
FOF_Object_Type	Inspection subject type code
FOF_Object_Name	Display name of the inspection subject
FOF_Object_Name_Orig	Display name of the inspection subject

If the inspection subjects contain a barge or vessel loop through them all, populating the below named cells with the display name(s):

Field Name	Value	Notes
FOF_Barge_(suffixed with an index starting at 1)	Display Name	Field names increment to represent number of cargoes. Begin counting from 1. Example field names: FOF_Barge_1 FOF_Barge_2
FOF_Barge_(infixed with an index starting at 1)_Orig	Display Name	Field names increment to represent number of cargoes. Begin counting from 1. Example field names: FOF_Barge_1_Orig FOF_Barge_2_Orig

Otherwise, if the inspection subject is a shore tank populate the following named fields with the display name:

Field Name	Value
FOF_Object_Name	Display Name
FOF_Object_Name_Orig	Display Name

Populate the destination (or load) port from the set of ports. If there are destination or load ports, set the following named cell:

Field Name	Value
FOF_Destination_Port	Display name

Populate the products table. For each cargo, set a named field suffixed with the index of the cargo started from 0 with the cargo name:

Field Name	Value	Notes
FOF_Product_(suffixed with an index starting at 1)	Cargo name	Field names increment to represent index number of cargo. Begin counting from 1. Example field names: FOF_Product_1 FOF_Product_2
FOF_Product_(infixed with an index starting at 1)_Orig	Cargo name	Field names increment to represent index number of cargo. Begin counting from 1. Example field names: FOF_Product_1_Orig FOF_Product_2_Orig
FOF_VCF_Table_(suffixed with an index starting at 1)	Contains the VCF table name	Example field name: FOF_VCF_TABLE_1

Populate the customers table. The contact for each customer is the primary contact - if one exists - or the first contact listed. For every client, list the contact in the following named cells:

Field Name	Value	Notes
FOF_Client_Company_(suffixed with an index starting at 1)	Legal entity name	Example field name: FOF_Client_Company_1
FOF_Client_Contact_(suffixed with an index starting at 1)	Primary contact display name	Example field name: FOF_Client_Contact_1
FOF_Client_Ref_(suffixed with an index starting at 1)	Client reference number	Example field name: FOF_Client_Ref_1

Populate the hidden list of offices. For each office, populate the following named cells:

Field Name	Value	Notes
FOF_OfficeName_(suffixed with an index starting at 1)	Office name	Example field names: FOF_OfficeName_1 FOF_OfficeName_2
FOF_OfficeAddress_(suffixed with an index starting at 1)	Office contact	Example field names: FOF_OfficeAddress_1 FOF_OfficeAddress_2

For the remaining offices fields, set the named cells to null

To ensure pre-populated data is carried through the workbook, ensure "ForceFullCalculation" is set to true for the workbook in the VisualBasic editor window.

Uploading

In order to be able to upload a completed workbook back into the software, the following set of minimum requirements must be met:

• Field Workbook Templates can only be uploaded with the following mime-types:
  1. application/vnd.ms-excel.sheet.macroEnabled.12
  2. application/vnd.openxmlformats-officedocument.spreadsheetml.sheet
• Field Workbook Templates can only be uploaded with the following file suffixes:
  1. .xlsm
  2. .xlsx
• Maximum file size allowed for upload is 30MB
• To ensure that the Field Workbook Template is parseable, the minimum requirements are:
  1. A sheet is present in uploaded completed Workbooks named “Data” (the Data Sheet). This recommended to be a hidden sheet.
  2. The first row (Row 1) of the data sheet contains the following:
     • Column A contains the text “Fill out First”
     • Column B contains the text "Job"
     • Column C contains the text “FileNo”
     • Column G contains a valid (existent) Job Order Number 

Additional integration is possible to allow the system to parse quantity data. These details can be provided upon request.