Natural Solutions Blog

Weighty Decisions

Natural Solutions - Wednesday, June 15, 2016

Do you want to weigh all of your items, but don't have time to actually weigh them?  You may want to weigh each item so you can determine the actual shipping charges at order-entry-time.

If you ship with UPS or FedEx, you weigh every box that goes out the door. And then that weight gets sent back to Natural Order (assuming you have correctly configured your ODBC connection). 

So that means that if you have an order with a single item in 1 box, then you basically now know the weight of that SKU (plus dunnage).

To accomplish our objective, you can use Cyberquery to get all shipping documents with a shipped quantity of 1, and can then  lookup the item's shipped weight.



So, as Part 1, you will write a Cyberquery report that sums up the quantity shipped per shipping document number - if the quantity shipped is 1, then that is one of our scenarios where a single item was shipped by itself. 

1. Do a SUM report with the domain set to LINE_ITEM_DISPOSITION

2. Drag Quantity_Shipped into Record Selection, set it to be IS GREATER THAN... 0

3. Drag Quantity_Shipped into SUM

4. Drag Shipping_Document_Number into BY

Now make it output a HOLD FILE so we can further process it in Part 2

5. Switch to TECHIE MODE (the keyboard icon, labeled Select Language Editor)

6. Add the following to the end of the line that says sum/domain="line_item_disposition"


7. Save the query, name it SKU_SHIPMENTS_PART_1

8. Run the query - instead of a report it will create a HOLDFILE



Now for Part 2 we will only select those shipments where a quantity of 1 was shipped, and we'll lookup the SKU and tracking info's weight:

1. Click the White Page icon (New), and then paste the following into the empty Enquiry

    file sku_shipments_hold_to_LID = 
        access line_item_disposition,
        set shipping_document_number = sku_shipments_holdfile:shipping_document_number,
        using second index, one to one
    file sku_shipments_hold_to_tracknbr =
        access shipper_tracking_number,
        set shipping_document_number = sku_shipments_holdfile:shipping_document_number,
        using third index, one to one

     sku_shipments_holdfile:quantity_shipped = 1


sorted by sku_shipments_hold_to_LID:SKU /newline

The above query is quite simple, but it creates new relationships so it can lookup the SKU and the weight for the hold file data.

2. Save the query, name it SKU_SHIPMENTS_PART_2

3. Run the query - it will then list all of the shipped weights for the SKUs listed - Ta Dah!

Note that it is possible some shipments will not have weights - that means you did not use the manifesting system to mark the items as shipped, or you may not have had your ODBC connection from your manifest system setup to send back weight.

DISCLAIMER: this report is an illustrative sample, and is not a part of your maintenance - your scenarios may be different depending on setup and history.