We use an module call iparcel for International Orders.

These orders get immediate tracking information and this updates in the correct tables needed for normal tracking use case, however, we use a middle-ware service called celigo to map order data to our ERP, Netsuite(NS).

Celigo uses sales_flat_order table to gather all records involved with order data , to import via an API call, and tracking is not a default field in this table.

I've managed to figure out how to correctly add the table based on a few fields in iParcels sql scripts, but I can not figure out what model to use to write to these tables.

Here you see where iParcel writes the tracking

 protected function _submitParcel($shipment, $order)
        $api = Mage::helper('iparcel/api');
        $response = $api->submitParcel($shipment);

        // Find the name of the Service Level as defined in the Admin
        $serviceLevels = Mage::helper('iparcel')->getServiceLevels();
        $responseServiceLevelId = $response->ServiceLevels[0][0]->ServiceLevelID;
        $serviceLevelTitle = 'I-Parcel';
        if (array_key_exists($responseServiceLevelId, $serviceLevels)) {
            $serviceLevelTitle = $serviceLevels[$responseServiceLevelId];

        // Add tracking number from submitParcel response

What I am asking is, is there a way to directly update this field right after it writes tracking , so its there before celigo imports the order ?

  • A more efficient way is to modify Celigo to do a table join from sales_flat_shipment_track to sales_flat_order.
    – kiatng
    Commented Sep 13, 2016 at 1:48

1 Answer 1


If you can't change the code in celigo, then execute the following 3 mySQL statements in the database:

ALTER TABLE `sales_flat_order` ADD `track_number` text;  
UPDATE `sales_flat_order` o
INNER JOIN `sales_flat_shipment_track` t
ON o.entity_id = t.order_id
SET o.track_number = t.track_number;
CREATE TRIGGER track_number_insert AFTER INSERT ON `sales_flat_shipment_track` FOR EACH ROW
    UPDATE `sales_flat_order` o INNER JOIN `sales_flat_shipment_track` t ON o.entity_id = t.order_id SET o.track_number = t.track_number;

Note: the above is not tested.

  • This works! Ive been banging my head for 4 days trying to figure this out.
    – Ryan Patt
    Commented Sep 13, 2016 at 17:38
  • could you clarify what the o and t reference to ? just so I know what this code fully means
    – Ryan Patt
    Commented Sep 13, 2016 at 17:50
  • Glad that it works for you. o refers to sales_flat_order, t refers to sales_flat_shipment_track.
    – kiatng
    Commented Sep 14, 2016 at 0:58
  • Also, what are the double dollar signs for ? I tried to look into but did not find anything that explained its use here
    – Ryan Patt
    Commented Sep 14, 2016 at 13:05
  • $$ is the delimiter, you can define other delimiter. See online doc
    – kiatng
    Commented Sep 15, 2016 at 2:52

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.