Warehousing Storage & Weight Calculation Feature Request

[tech4kstudios]

Workflow

• Size of products are added to each product in the form of length, width and height along with weight.
• Once new inventory quantity is updated, the inventory storage details section would list dimensions, weight, total volume and total weight.
  
• Total volume and weight is also displayed on numerous pages such as the shipment page similar to the mock-up below.
  
• Lastly, the dashboard, stock reports, etc should display the total volume and weight of the products contained within.

Excel Document Currently Used for this Workflow: https://cloud.t4ks.com/s/Kry76pepRTkcGKY

Purpose

The purpose of such a system is to allow warehousing businesses to quickly see the space consumption of the items, thus allowing for use cases like invoicing where storage is calculated based on monthly storage usage charged per cubic metre, see required space when creating shipments, ie. does the shipment weight or volume exceed the allowed limits, is it required to be split into separate shipments, does the shipment method need to be changed due to volume and weight, etc...

Future Use Cases

Furthermore, future feature additions such as automated weight limit notifications can allow for use cases such as:

• Notification or error if size or weight limit exceeds capability of selected storage location, ie. pallet too large or heavy for selected pallet rack location.
• Notification if certain locations require specific machinery, eg. heavy items placed in high locations bring up a notification suggesting "A Forklift may be Required to access this Item", if the heavy item is located on ground floor then notification could say "Pallet Jack or Trolley may be Required as Weight is XX".

To-Do

• Add separate input for Length, Width, Height and Weight on product edit page.
• Perform volume calculation for total inventory (LxWxH)*total_quantity along with total of weight * total_quantity

Remaining to-do points to be added by dev with more knowledge of implementation and requirements than me ;)

[jmiranda]

@tech4kstudios As a first pass, we could implement these as product attributes and extend some of the reporting to include the attributes as well as product attributes that are custom formulas. This would be fine for the first two bullets as well as some reports. However, we wouldn't be able to include volume / weight in the workflows just yet.

The longer term solution would be to add these properties to ProductPackage and allow you to select a specific product package as the default for storage (or at least a default unit of measure: EA, BTL/100, BX/10). We also might want lot numbers to be associated with a ProductPackage so that you could have different volumes / weights if you have multiple suppliers that each send a package with different volume / weight. We'd also want the product package to be associated with a bin location. The only issue there is that I'm not sure how we'd deal with an implementation where different products are co-mingled in a single bin location (see Amazon Prime as an example https://youtu.be/5TL80_8ACPc?t=20). I guess there the bin location wouldn't be associated with a product package but rather with its own volume / weight restrictions and all the items would just need to fit within that space.

Let me know what you think and whether you'd be cool with a short-term solution that uses product attributes. Here's what I'm thinking:

• (Done) Allow user to create product attribute for volume and weight attributes
• Allow user to import/export product attributes for volume and weight
• Allow user to create product attribute custom calculation (total volume = product attribute H * product attribute W * product attribute L * quantity on hand). This might be more difficult, so we could also export the product attributes in reports and then require you to add calculated fields in Excel. At least to get started.
• (Done?) Allow user to mark a product attribute as exportable (included on inventory reports, product exports, etc)
• Allow user to mark a product attribute as displayable (stock card)

[tech4kstudios]

@jmiranda A short term solution like this would be perfect to get started. Would allow my business to make the switch from entirely Excel based to something an order of magnitude better. Then based on the observable workflow, we can make modifications and additions to future passes.

Had a look at the Amazon Prime video linked. Seems like they are dealing with smaller quantities of a large range of products. In our warehouse a significant proportion will involve handling pallets and other bulk goods and will be shipped out in bulk quantities. Although there will be fulfilment, only one or two items will be picked per order, and as we likely won't have anywhere near the same range of items found in warehouses such as Amazon, items with the same barcode/SKU will be placed together.

In terms of volume calculation, we can definitely implement automation of this in future passes. It is not a critical requirement to get started. The rest of the outline looks great!

[jmiranda]

@tech4kstudios I made some progress on this last week. I'll share a video some time this week.

[tech4kstudios]

@jmiranda Thank you heaps, look forward to the video and can't wait to start using it. My business has been expanding a fair bit and using excel is taking a big toll at the moment.

[jmiranda]

Attached a sample XLSX file. This actually needs to be an XLS file when importing into OpenBoxes, but Github does not like XLS for some reason.
ProductAttribute (1).xlsx

[jmiranda]

@tech4kstudios Finally had time for a rambling video to show some progress on this one
https://drive.google.com/file/d/1XJRBm4kNCrYDBHNkhqiTqfO-j4fSJEuP/view

[jmiranda]

Here's a custom query we could run to get the total weight and volume for each product. Slight modifications would be required to aggregate at the bin location or inventory item level. Temperature was added to show a basic example of what we could do with time series data.

select 
    product.product_code,
    product.name as product_name,
    location.name as location_name,
    product_attribute_tmp.product_weight,
    product_attribute_tmp.product_volume,
    product_summary.quantity_on_hand,
    product_weight * product_summary.quantity_on_hand as total_weight,
    product_volume * product_summary.quantity_on_hand as total_volume  
from product_summary 
join (
SELECT 
    product_attribute.product_id as product_id,
    MAX(CASE WHEN attribute.code = 'WEIGHT' THEN product_attribute.value ELSE NULL END) AS product_weight,
    MAX(CASE WHEN attribute.code = 'VOLUME' THEN product_attribute.value ELSE NULL END) AS product_volume,
    AVG(CASE WHEN attribute.code = 'TEMPERATURE' THEN product_attribute.value ELSE NULL END) AS product_temperature
    from product_attribute
    join attribute on product_attribute.attribute_id = attribute.id
    GROUP BY product_attribute.product_id
) as product_attribute_tmp on product_attribute_tmp.product_id = product_summary.product_id
join location on location.id = product_summary.location_id
join product on product.id = product_summary.product_id
order by location_name, product_code

[jmiranda]

@tech4kstudios Did you ever get my Slack messages back in December regarding this feature? If not I'll upload the video and comments here so we can continue the discussion. I had a branch that was more or less ready to be merged ... was just waiting on your feedback.

[jmiranda]

Here's the video demonstrating the new customizable export feature https://www.youtube.com/watch?v=o8Xqf5OAfOM

We're about to get 0.8.15 out the door so I wanted to give you a chance to review to see if this might be a good candidate for the release. Otherwise, we can work on it at some point in the future.