Workbook Integration Guide

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 NameValue
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 NameValue
FOF_Object_Type
Inspection subject type code
FOF_Object_Name
Display name of the inspection subject
FOF_Object_Name_OrigDisplay 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 NameValueNotes

FOF_Barge_(suffixed with an index starting at 0)

Display NameField names increment to represent number of cargoes. Begin counting from 0.

Example field names:
FOF_Barge_0
FOF_Barge_1

FOF_Barge_(infixed with an index starting at 0)_Orig

Display NameField names increment to represent number of cargoes. Begin counting from 0.

Example field names:
FOF_Barge_0_Orig
FOF_Barge_1_Orig

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

Field NameValue

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 NameValue
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 NameValue Notes
FOF_Product_(suffixed with an index starting at 0)
Cargo name
Field names increment to represent index number of cargo. Begin counting from 0.

Example field names:
FOF_Product_0
FOF_Product_1
FOF_Product_(infixed with an index starting at 0)_Orig
Cargo name
Field names increment to represent index number of cargo. Begin counting from 0.

Example field names: FOF_Product_0_Orig
FOF_Product_1_Orig
FOF_VCF_Table_(suffixed with an index starting at 0)
Contains the VCF table name
Example field name: FOF_VCF_TABLE_0

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 NameValueNotes
FOF_Client_Company_(suffixed with an index starting at 0)
Legal entity name
Example field name: FOF_Client_Company_0

FOF_Client_Contact_(suffixed with an index starting at 0)

Primary contact display name
Example field name: FOF_Client_Contact_0

FOF_Client_Ref_(suffixed with an index starting at 0)
Client reference numberExample field name: FOF_Client_Ref_0

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

Field NameValue
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.

Did you find it helpful? Yes No

Send feedback
Sorry we couldn't be helpful. Help us improve this article with your feedback.