Excel Sheet Mapping Data: Telling the Agent What to Sync

Modified on Tue, 19 Aug at 6:05 PM

Data mapping is a critical step that defines how information from PortPro TMS fields (such as Load Number, Container Status, and Customer Name) corresponds to specific columns in the selected Excel worksheet. Accurate mapping ensures that each data element is placed in the correct location.

Understanding Data Mapping

Data mapping functions as a set of instructions for the agent. For example, the "Load ID" field from the TMS could be configured to populate Column A of the Excel sheet, or a column labeled "Shipment Identifier".

Using the AI-Assisted Mapping Feature

To simplify the process, the Excel Spreadsheet AI Agent may offer an AI-assisted mapping feature. This could include:

Automatic Suggestions – Based on existing column headers in the selected Excel worksheet, the agent may automatically suggest potential mappings to relevant TMS fields.
Interactive Chat – Some versions may include an interactive chat interface where mapping requirements can be described, and the AI will configure them accordingly.

All AI-generated suggestions should be reviewed carefully. Manual adjustments can be made to ensure mappings align perfectly with operational requirements.

The Crucial Role of the Primary Key

During the mapping process, one column in the Excel worksheet must be designated as the Primary Key—the most critical component of mapping setup.

What is a Primary Key?

A Primary Key column contains a unique value for each row of data. Common choices include Load Number, Container ID, or another unique reference number from PortPro.

Why it is crucial?

The agent uses the Primary Key to:

Uniquely identify each row in the Excel sheet.
Accurately update existing rows when corresponding TMS data changes.
Correctly add new rows for new TMS records without creating duplicates.
Remove rows from Excel when the corresponding TMS record is deleted.

Important: The column selected as the Primary Key must contain unique values for every record intended for synchronization. Duplicate Primary Key values can cause synchronization errors or incorrect updates.

Reviewing and Confirming Mappings

After all field mappings are defined and the Primary Key is set, the configuration should be reviewed thoroughly.
Confirm that each TMS field is mapped to the correct Excel column and that the Primary Key is properly designated.
Once verified, save the mappings to activate the data synchronization process.

Mappable Data:

Load #	A unique reference number assigned to the load
Customer	The name of the customer who requested the load
Branch	The branch of the customer who requested the load
Pick Up Location	The location where the load will be picked up from
Delivery Location	The location where the load will be delivered to
Return Location	The location where the load will be returned to
Hook Chassis Location	The location where the hook chassis will be located
Terminate Chassis Location	The location where the chassis will be terminated
Freight Hold	Freight status for the load
Custom Hold	Custom hold status
Carrier Hold	The carrier hold status of the load
Broker Hold	The broker hold status of the load
Vessel ETA	Information about the vessel transporting the load
Last Free Day	The last day the load can be stored without incurring additional charges
Discharged Date	The date when the load was discharged
Outgate Date	The date when the load exited the gate
Empty Date	The day the container became empty
Ingate Date	The date when the load entered the gate
Ready to Return Date	The date when the load is ready for return
Loaded Date	The date when the load was loaded
ERD	The date when the container is available for return
Cut Off	The date when the load is due to be picked up
Date Returned	The date when the load was returned
Container #	The unique identifier for the container used in the load
Container Size	The size of the container used in the load
Container Type	The type of container used in the load
Container Owner	The owner of the container used in the load
Chassis #	The unique identifier for the chassis used in the load
Chassis Size	The size of the chassis used in the load
Chassis Type	The type of chassis used in the load
Chassis Owner	The owner of the chassis used in the load
Genset #	The unique identifier for the genset used in the load
Temperature	The temperature of the container
Route	The route of the load
SCAC	The SCAC of the load
Tare Weight	The tare weight of the load
Hazmat	Whether the load is a hazardous material
Overweight	Whether the load is overweight
Overheight	Whether the load is overheight
Liquor	Whether the load contains liquor
Hot	Whether the load is urgent
Genset	The genset of the load
Street Turn	The street turn of the load
House Bill Of Lading	The house bill of lading of the load
Booking #	The booking number of the load
Reference #	The secondary reference number of the load
Vessel Name	The name of the vessel transporting the load
Voyage	The voyage number of the load
Purchase Order #	The purchase order number of the load
Shipment #	The shipment number of the load
Pick Up #	The pick up number of the load
Appointment #	The appointment number of the load
Return #	The return number of the load
Reservation #	The reservation number of the load
Seal #	The seal number of the load
Master Bill Of Lading	The master bill of lading of the load
Delivery Reference #	The delivery reference number of the load
Ready for Pickup	Whether the load is ready for pickup
Gray Chassis #	The unique identifier for the gray chassis used in the load
Gray Chassis Size	The size of the gray chassis used in the load
Gray Chassis Type	The type of gray chassis used in the load
Gray Chassis Owner	The owner of the gray chassis used in the load
Status	Current load status (e.g. dispatched, enroute to deliver)
Load Type	Type of the load (IMPORT, EXPORT, ROAD, BILL ONLY)
Billing Date	The date when the load was billed
Available Date	The date when the load is available
Pick Up Apt From	The start time of the load pick up appointment
Pick Up Apt To	The end time of the load pick up appointment
Delivery Apt From	The start time of the load delivery appointment
Delivery Apt To	The end time of the load delivery appointment
Return Apt From	The start time of the load return appointment
Return Apt To	The end time of the load return appointment
Per Diem Free Day	Last Date to return the container to port for free
Trailer #	The unique identifier for the Trailer used in the load
Trailer Size	The size of the trailer used in the load
Trailer Type	The type of trailer used in the load
Delivered To Unload Date	The date when the load was delivered to the unload location
Load Completed Date	The date when the load was completed
Current Location	The last known location of the load
Lift Off Reference #	The lift off reference number of the load
Pieces	The commodity and the number of pieces
Delivery Arrival Time	The time of the delivery arrival for the load
Driver Notes	The notes for the driver of the load
Yard Notes	The notes for the yard of the load
Load Notes	The notes for the load