A Simple But Effective Custom Inventory App

Inventory is core to nonprofit operations. Here is how we finally started tracking it in Salesforce instead of paper lists and spreadsheets.

Also see the Texas Dreamin Presentation Terry made showing these objects in Lightning Desktop and Mobile pages. (Here is the Texas Dreamin’ 2022 Slide Deck)

Also see detailed reference link to excellent article on inventory for nonprofits: (It explores a lot of complex issues that inspired our simplified version).

Data model:

User Stories:

1. User creates a new item to be tracked and knows it is: Canned Beans tracked by each. We use 2 per food bag and 11 food bags per week, so we use 22 per week. We always need at least 1 week supply and can store at most 6 weeks. They create the new inventory item in Setting Up mode and when ready release it to Active. It shows zero at this point in stock. (Passed)  
2. The user recognizes that we don’t have a current inventory of several canned items and creates a list view of inventory items that filters all containing the word “canned” that are Active. They see that the rollup field Quantity on hand says we have 0 beans (it’s newly tracked), 68 fruits, and 12 tuna. They count the number of canned beans, canned meat, and canned fruit on hand. They can see from the list view that all three are tracked by “each” so they enter the number of cans into the list view in the “inventory update” fields and save: 92 beans (our first tracking data), 42 fruit (apparently we gave out a lot of fruit), 22 tuna (we must have had some donation or purchase). The software creates the necessary inventory move objects with today’s date to make the inventory on hand correct. E.g., An inventory move is created for canned beans of +92 (each is the units automatically) with a type of “inventory update” and today’s date. The canned fruit gets an inventory move of -26 with the same type and date. After saving the list view, the inventory update is blank again and the quantity on hand matches the numbers entered now: 92, 42, and 22. The urgent needed units for canned beans is zero since we have at least 22. The urgent budget price updates using the best price. The stock-up quantity for canned beans updates to 6*22-92 = 40 and the budget updates using the best price.  (passed)
3. The user recognizes that SYM has purchased 144 cans of beans from Amazon. They create a new Inventory move item. They selected canned beans. They select ‘purchase’ for a reason. They enter 144 (each is automatically the units). They select the organization Amazon and leave the contact blank. After the save, they view canned beans, and the quantity on hand is higher by 144 units.  (passed)
4. The user is told that Walmart Stores sell black beans for 10.68 for a case of 12. They think this is lower than our current best price and a valuable source. They enter new Inventory source records. They selected canned beans and the organization is Walmart. They enter the price. The units are automatically each (controlled by the Inventory Object) so they enter 0.89. They enter the dates and note the terms (by the case only and limited to 5 cases online or unlimited in store) The status is Current. Since the previous source record was higher, the new best price will be 0.89 on the canned bean inventory item. The budget will use this price.  (Passed)
5. A user finds out that Walmart has raised the price of beans to 1.10 per can. This isn’t considered a valuable source. We previously had used them as a good source so the used finds the Inventory source object and changed the status to not current. This causes the best price for beans to have no valid source so the best price on the item becomes blank (zero would be OK but blank is better). This blank value means the budget items because zero.  (Passed)
6. A user recognizes we need a new source for canned beans. They think Amazon might be the best source. They create a new Inventory Item for canned beans, set the organization to Amazon and set the status to researching. They eventually determined that the price is 0.69 and they updated this, noting the terms that we must buy 8 cans, and set the status to current. The best price for canned beans updates to 0.69 and the budget prices recalculate. (Passed)
7. A user sees the list view to look for the items needed to prepare for shopping. They can see the quantity and budget for immediate need. (Passed)
8. A user sees the list view to look for the items needed to be stocked up and prepare for shopping. They can see the quantity and budget for stock up needs.  (Passed)
9.  The food bag donations will be increased as many holidays in the last quarter of the year. A user wants to raise the threshold for weekly usage of Canned beans from 2 to 4. 
10. The user decides to get rid of canned oranges and use fresh oranges instead.  When they mark the Canned oranges inventory item as inactive, the attention will be changed to Not Needed.  (passed)
11. The local Walmart donates 100 lbs of sweet potatoes bi-monthly. A user records the contact for Walmart. The inventory is too full to store more food items. A user needs to  find the contact’s email or phone number for not accepting the donation temporarily.  
12. Paper towels are out of inventory for two weeks and the stores have no inventory as well. A user knows that they received a donation of paper products from some donors.  They want to contact them to see if they would like to make a donation. A user can use volunteer hours to search who donated the paper products. 
13. The user decides we will no longer track beans. They set the status to “inactive”. The attention will be set to not needed.    

List views for Users:

SYM Update inventory: show actively tracked items with quantity on hand, update amount, note

SYM Current inventory: show actively tracked items, status, Attention, items

SYM Urgent Shopping List: show actively tracked items with urgent quantity >0, amount needed, price, budget, note, chart to total budget

SYM Stock-up shopping list: show actively tracked items with stock up quantity >0, amount needed, price, budget, note, chart to total budget 

Object: Inventory Item

• Name: 
• Inventory Status: Picklist (Setting Up, Active, Inactive)
• Attention: Picklist (Urgent Need, Stockup Needed, Enough, Not needed)
• Weekly Usage Rate: Number (5,2)
• Unit: Text (50)
• Min Weeks Needed: Number (5,2) default 1
• Max Weeks Hold: Number (5,2) default 13
• Quantity On Hand: rollup from Inventory Move
• Best Price: min rollup of all current inventory source prices
• Immediate need: formula field to have min weeks less current (It’s allowed negative for the design,but thye user stories don’t allow negative and set to zero if it’s negative)
• Immediate Budget: formula of immediate need * buy price (don’t allow negative)
• Stock Up need:  formula field to have max weeks less current  (It’s allowed negative for the design,but thye user stories don’t allow negative and set to zero if it’s negative)
• Stock Up Budget:  formula of Stock up * buy price (don’t allow negative)
• Update Inventory: positive number, two decimals
• Description: Text (255)
• Note:  Text(255)

Object: Inventory Move (master-detail relationship-child object of Inventory Item)

• Inventory Move Name: Auto number
• Quantity : Number (6,2) (positive or negative, two decimal places ) 
• Reason : Picklist (Donation,Scrapped,Traded,Purchase, Inventory update, Initial Amount)
• Note: Text (255)
• Unit Price: Formula (currency) SYM_Total_Purchase_Price__c / SYM_Quantity__c
• Total Price: Currency (6,2)  (validation rule)
• Unit: Formula (text)  SYM_Inventory_Item__r.SYM_Unit__c
• Contact: lookup to contact 
• Account: lookup to account  
• Volunteer Hour: lookup to volunteer hour  

Object: Inventory Source (master-detail relationship-child object of Inventory Item)

• Contact: lookup to contact 
• Account: lookup to account  
• Note: Text (255)
• Price: Currency (6,2)
• Price Start Date: Date 
• Price Status: Picklist  Researching, Current, Not Current, Unknown
• Purchase URL:  URL (255)
• Unit: Formula (text) SYM_Inventory_Item__r.SYM_Unit__c
• Inventory Source Name: Text (80)
• Note: Text (255)

Validation rules:

1. Number_Cannot_be_Negative

Object: Inventory Item

Error Condition Formula: SYM_Update_Inventory__c <0

2. Total_Price_Is_Required

Object: Inventory Move

Error Condition Formula: AND (ISBLANK(SYM_Total_Purchase_Price__c), ISPICKVAL( SYM_Reason__c , “Purchase”))

Automation:

When update inventory is changed to non-zero, create a new inventory move so that the roll-up summary field will match the amount entered. Reset to blank. The reason is to always update inventory.

SYM Inventory App flow:

Other future questions:

Do we need a product category to standardize the names for inventory items? 

Maybe? Later? Naming convention

Price book? Excel? The category has names, sizes, units. 

Price book very hard and will make it about “doing what Salesforce needs”

Do we need a location picklist field? Likely for the future. 

But this could interact with the idea of pools of inventory which we may also need later. (A managed pool at multiple service delivery locations with a warehouse backup pool.) So defer implementation for now.