In Part 1 of this series, I explained the overall reasons why an organization might need a clever way to manage supplies/inventory on Salesforce. In this post, we’ll take a look at what requirements a robust system would address as well as the ensuing ramifications for our data map.
For the benefit of everyone, please feel free to ask questions in the comments below.
Processes to Smooth, Questions to Answer
The needs of each organization will be a little different, but for the sake of being as robust as possible, we’re going to design for a situation in which:
- Supplies are both purchased and donated
- Staff members often request items from inventory to use in service of the organization’s mission
Here are the requirements we’re going to use as our guide for this project:
- An accurate cost (per good accounting practices) needs to be attributed to a staff member’s department when they request supplies
- We need a way to handle different order UOM’s, purchase prices, and vendors
Challenge 1: Purchase vs. Distribution
Tracking procurement and use/distribution of inventory can be challenging due to the difference between how goods are purchased and how they are used in an organization. As our organizations attempt to minimize costs, we will buy whatever is on sale—a 12-pack of all-purpose cleaner one week and a 36-pack the next week. To make matters worse, we will procure a box of 12 widgets, but a social worker may only request/use one at a time, and therefore we need only attribute 1/12th of the cost to their department.
For these reasons, we will need to make a few important distinctions:
- The Purchase Unit of Measure (UOM) vs. the Distribution UOM
- The Average Distribution Unit Cost calculated using the Purchase Cost and the number of Distribution Units contained in the Purchase Unit (e.g., the number of bottles in the crate).
Challenge 2: First-In, First-Out
An important accounting concept also has a bearing on our solution design. When we calculate the average unit cost of an item—mainly to attribute that cost to the requestor’s department—we need to ensure we’re using the costs of the items sitting in storage, not simply the average cost of an item for the whole year (i.e., across all POs). According to the principle of first-in, first-out, we will distribute/use the oldest items first. This means that when we calculate the average unit cost for an item, we want to calculate the average cost from the most recent POs.
Challenge 3: Junction Objects & Line Items
In our solution, a Purchase Order record could represent an instance in which we’re recording the ordering/receipt of supplies, whether it be from a vendor or donor. But how do we associate instances of individual items with that Purchase Order? Should we use custom fields on the Purchase Order such as Item 1, Quantity 1, UOM 1, etc.? Yuck!
Instead, we’ll create another custom object—Purchase Order Line Item—in a master-detail relationship with the Purchase Order object (the Purchase Order object being the master).
An important concept underlying our data map is the junction object. For those of you familiar with Sales Cloud applications, you may recognize this concept from objects such as Opportunity, Product, and Opportunity Line Item (a.k.a. Opportunity Product). For example, in this project, we’ll make a junction object (Purchase Order Line Item) between our Purchase Order object and our Item object.
The Data Map
The data map for our custom inventory system consists of six custom objects, each with several custom relationships, fields, formulas, and roll-ups.
- Item – this object serves as the master list of different types of items/products in our inventory.
- Inventory – this object represents the individual units sitting in storage.
- Purchase Order (PO) – a user (probably someone managing the inventory, purchasing supplies, or receiving donations) creates a Purchase Order and adds PO Line Items to track an order made with a vendor or record the addition of items to inventory.
- Purchase Order Line Item – added as detail records to a Purchase Order instance to represent the list of items involved in the PO.
- Requisition – a user creates a Requisition and adds Requisition Line Items to request supplies from inventory or otherwise record the use (depletion) of inventory items.
- Requisition Line Item – added as detail records to a Requisition instance to compose the list of items involved in the Requisition
- Account – used to associate a PO with a specific vendor or donor. Bonus: Later in the series we’ll provide some automation so that a “Recent Vendor” field on the Item record is updated with whoever we last obtained the Item.
The Item Object
This object serves as the master list of different types of items/products in our inventory. I named it “Item” instead of “Product” so as not to conflict with the standard Product objects in Salesforce, but I’m sure other names like “Supplies” would work as well.
|Item Name||Name (Text)|
|Last Vendor||Lookup (Account)|
|High Qty. Threshold||Number|
|Low Qty. Threshold||Number|
|Restock Quantity Needed||Formula|
High_Qty_Threshold__c – Quantity_In_Stock__c
|Average Distribution Unit Cost||Formula|
Sum_Unit_Costs_of_In_Stock_Inventory__c / Count_In_Stock_Inventory_Records__c
|Quantity In-Stock||Roll-up COUNT (Inventory)|
|Sum Unit Costs of In-Stock Inventory||Roll-up SUM (Inventory)|
|Count In-Stock Inventory Records||Roll-up COUNT (Inventory)|
The Inventory Object
This object represents the individual units in our system, and as such, each record represents one actual unit of an item that passed through our inventory system. Other names for this object could be “Units in Inventory” or “Inventory Items” depending on what makes sense to you. Each record represents an actual piece of product that we either bought or received as a donation.
|Inventory Number||Name (Auto Number)|
|PO Line Item||Mater-Detail (PO Line Item)|
|Distribution UOM||Cross Object Formula (Item)|
TEXT( Item__r.Distribution_UOM__c )
The Purchase Order Object
Acts as a container for all the items being purchased from a vendor or received from a donor. A Purchase Order record represents an instance of procurement. Primarily, it will be the inventory managers who create Purchase Orders (and PO Line Items, below) in order to record the replenishment of inventory.
Purchase Order Fields:
|Purchase Order Number||Name (Auto Number)|
|Total Cost||Roll-up SUM (PO Line Items)|
The Purchase Order Line Item Object
To add items to a PO, the user adds PO Line Items to a PO record via the Master-Detail relationship that the objects share. The PO Line Item Object serves as a junction object between Purchase Order and Item.
PO Line Item Fields:
|PO Line Item Number||Name (Auto Number)|
|Purchase Order||Master-Detail (Purchase Order)|
|Purchase Units Received||Number|
|Purchase Units Ordered||Number|
|Purchase Unit Price||Currency|
|Distribution Units per Purchase Unit||Number|
|Cost per Distribution Unit||Formula|
Total_Cost__c / Distribution_Units_Ordered__c
|Distribution Units Ordered||Formula|
Purchase_Units_Ordered__c * Distribution_Units_per_Purchase_Unit__c
|Distribution Units Received||Formula|
Purchase_Units_Received__c * Distribution_Units_per_Purchase_Unit__c
|Distribution UOM||Cross Object Formula (Item)|
|Gift-In-Kind||Cross Object Formula (Purchase Order)|
Purchase_Unit_Price__c * Purchase_Units_Ordered__c
The Requisition Object
Acts as a container for all the items being taken out of inventory for use. A Requisition record represents an instance of distribution/use. These records (along with Requisition Line Items, below) will be created by staff to request supplies and by the inventory managers to otherwise record the use of supplies by the organization.
|Requisition Number||Name (Auto Number)|
|Count of Requisition Line Items||Roll-up COUNT (Requisition Line Items)|
|Count of Line Items Delivered||Roll-up COUNT (Requisition Line Items)|
The Requisition Line Item Object
To add items to a Requisition, the user adds Requisition Line Items to a Requisition record via the Master-Detail relationship that the objects share. The Requisition Line Item Object serves as a junction object between Requisition and Item.
Requisition Line Item Fields:
|Requisition Line Item Number||Name (Auto Number)|
|Requisition Number||Master-Detail (Requisition)|
|Avg. Distribution Unit Cost||Formula|
|Total Cost of Quantity Requested||Formula|
Avg_Distribution_Unit_Cost__c * Quantity_Requested__c
|Total Cost of Quantity Delivered||Formula|
Avg_Distribution_Unit_Cost__c * Quantity_Delivered__c
|Distribution UOM||Cross-Object Formula (Item)|
Now that we have an understanding of the general data map for our Program Supplies app, in the next part of this series, we’ll tackle some vital automation:
- We’ll use Flow to automatically created Inventory records based on the quantity obtained through a Purchase Order (representing an addition to Inventory/items in storage).
- We’ll also ask Flow to update the status of the right number of Inventory records to “Distributed” when a Requisition is completed (representing the depletion/use of items in storage). This will remove the Inventory records from our In-Stock calculations.
I am trying to recreate this. We already use Products/Orders/Order Products/WorkOrders/and Work Order Line items. This accounts for usage of products, but doesn’t count inventory or decrement it in any way yet,
I thought I could use Products as a comparable object to your “Items” object, but a Master Detail is not available from my new Inventory Object to Product. Is there something i need to set up somewhere to allow that relationship?
So pleased that you’re trying to recreate this! I’m here to help in any way I can. But unfortunately you’re correct about not being able to have Product as master in a master-detail relationship. You can up-vote the idea for that functionality here: https://success.salesforce.com/ideaView?id=0873A000000E3oOQAS.
What you could do instead is use a lookup relationship (to Product) on your Inventory object and use apex or Flow to mimic the roll-up summary.