How to Manage Program Supplies (Inventory) on Salesforce – Part 2: The Data Map

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.

Photo by Hans-Peter Gauster on Unsplash

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

Notable Challenges

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:

  1. The Purchase Unit of Measure (UOM) vs. the Distribution UOM 
  2. 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.  

Custom Objects:

  • 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

Standard Objects:

  • 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 Fields:

Item NameName (Text)
Last VendorLookup (Account)
CategoryPicklist
Distribution UOMPicklist
High Qty. ThresholdNumber
Low Qty. ThresholdNumber
Last URLURL
Restock Quantity NeededFormula
High_Qty_Threshold__c – Quantity_In_Stock__c
Average Distribution Unit CostFormula
Sum_Unit_Costs_of_In_Stock_Inventory__c / Count_In_Stock_Inventory_Records__c
Quantity In-StockRoll-up COUNT (Inventory)
Sum Unit Costs of In-Stock InventoryRoll-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 Fields:

Inventory NumberName (Auto Number)
ItemMaster-Detail (Item)
PO Line ItemMater-Detail (PO Line Item)
Vendor Lookup (Account)
StatusPicklist
Gift-In-KindCheckbox
Unit CostCurrency
Distribution UOMCross 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 NumberName (Auto Number)
VendorLookup (Account
StatusPicklist
Date ReceivedDate
Gift-In-KindCheckbox
Total CostRoll-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 NumberName (Auto Number)
Purchase OrderMaster-Detail (Purchase Order)
ItemLookup (Item)
VendorLookup (Account)
StatusPicklist
URLURL
Purchase UOMPicklist
Purchase Units ReceivedNumber
Purchase Units OrderedNumber
Purchase Unit PriceCurrency
Distribution Units per Purchase UnitNumber
Cost per Distribution UnitFormula
Total_Cost__c / Distribution_Units_Ordered__c
Distribution Units OrderedFormula
Purchase_Units_Ordered__c * Distribution_Units_per_Purchase_Unit__c
Distribution Units ReceivedFormula
Purchase_Units_Received__c * Distribution_Units_per_Purchase_Unit__c
Distribution UOMCross Object Formula (Item)
TEXT(Item__r.Distribution_UOM__c)
Gift-In-KindCross Object Formula (Purchase Order)
Purchase_Order__r.Gift_In_Kind__c
Total CostFormula
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 Fields:

Requisition NumberName (Auto Number)
CommentsText Area
StatusPicklist
Date CompletedDate
Delivery LocationPicklist
DepartmentPicklist
Count of Requisition Line ItemsRoll-up COUNT (Requisition Line Items)
Count of Line Items DeliveredRoll-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 NumberName (Auto Number)
Requisition NumberMaster-Detail (Requisition)
ItemMaster-Detail (Item)
StatusPicklist
Quantity DeliveredNumber
Quantity RequestedNumber
Avg. Distribution Unit CostFormula
Item__r.Average_Distribution_Unit_Cost__c
Total Cost of Quantity RequestedFormula
Avg_Distribution_Unit_Cost__c * Quantity_Requested__c
Total Cost of Quantity DeliveredFormula
Avg_Distribution_Unit_Cost__c * Quantity_Delivered__c
Distribution UOMCross-Object Formula (Item)
TEXT(Item__r.Distribution_UOM__c)

Next up

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.

Follow along in your inbox!

3 Comments

  1. Hi –
    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?

    Like

    1. Hi Ann,

      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.

      Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.