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 Name	Name (Text)
Last Vendor	Lookup (Account)
Category	Picklist
Distribution UOM	Picklist
High Qty. Threshold	Number
Low Qty. Threshold	Number
Last URL	URL
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 Fields:

Inventory Number	Name (Auto Number)
Item	Master-Detail (Item)
PO Line Item	Mater-Detail (PO Line Item)
Vendor	Lookup (Account)
Status	Picklist
Gift-In-Kind	Checkbox
Unit Cost	Currency
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)
Vendor	Lookup (Account
Status	Picklist
Date Received	Date
Gift-In-Kind	Checkbox
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)
Item	Lookup (Item)
Vendor	Lookup (Account)
Status	Picklist
URL	URL
Purchase UOM	Picklist
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) TEXT(Item__r.Distribution_UOM__c)
Gift-In-Kind	Cross Object Formula (Purchase Order) Purchase_Order__r.Gift_In_Kind__c
Total Cost	Formula 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 Number	Name (Auto Number)
Comments	Text Area
Status	Picklist
Date Completed	Date
Delivery Location	Picklist
Department	Picklist
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)
Item	Master-Detail (Item)
Status	Picklist
Quantity Delivered	Number
Quantity Requested	Number
Avg. Distribution Unit Cost	Formula Item__r.Average_Distribution_Unit_Cost__c
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) 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!