Intermediate 25 min read

Query Low-Stock Items

By the end of this lesson, you'll be able to:

Query stock records below their reorder threshold
Traverse a parent relationship to include the product's name in the results
Sort low-stock results by how far below threshold they are

Prerequisites: "Track Inventory Levels"

Filtering to stock below threshold

public List<Warehouse_Stock__c> getLowStockItems() {
    return [
        SELECT Id, Product__c, Warehouse_Name__c, Quantity_On_Hand__c, Reorder_Threshold__c
        FROM Warehouse_Stock__c
        WHERE Quantity_On_Hand__c < Reorder_Threshold__c
    ];
}

public List<Warehouse_Stock__c> getLowStockItems() { — answers "what needs reordering?"
return [ — opens the query.
SELECT Id, Product__c, Warehouse_Name__c, Quantity_On_Hand__c, Reorder_Threshold__c — the fields needed.
FROM Warehouse_Stock__c — the object.
WHERE Quantity_On_Hand__c < Reorder_Threshold__c — comparing two fields on the same object directly in WHERE is exactly Module 20's comparison-operator filtering, now comparing one field against another instead of against a fixed value.
]; — closes the query.
} — closes the method.

Including the product name via a parent relationship

public List<Warehouse_Stock__c> getLowStockItemsWithProductName() {
    return [
        SELECT Id, Product__r.Name__c, Warehouse_Name__c, Quantity_On_Hand__c
        FROM Warehouse_Stock__c
        WHERE Quantity_On_Hand__c < Reorder_Threshold__c
    ];
}

public List<Warehouse_Stock__c> getLowStockItemsWithProductName() { — the same low-stock query, now with the product's name included.
return [ — opens the query.
SELECT Id, Product__r.Name__c, Warehouse_Name__c, Quantity_On_Hand__c — Product__r.Name__c reaches through the relationship to the parent Product__c's name — this is Module 18's and Module 20's parent-relationship traversal directly applied. Note Product__r (with __r, the relationship suffix), not Product__c (the raw lookup field itself).
FROM Warehouse_Stock__c — the object.
WHERE Quantity_On_Hand__c < Reorder_Threshold__c — the same filter as before.
]; — closes the query.
} — closes the method.

Sorting by how urgent the shortage is

public List<Warehouse_Stock__c> getLowStockItemsSortedByUrgency() {
    return [
        SELECT Id, Product__r.Name__c, Warehouse_Name__c, Quantity_On_Hand__c, Reorder_Threshold__c
        FROM Warehouse_Stock__c
        WHERE Quantity_On_Hand__c < Reorder_Threshold__c
        ORDER BY Quantity_On_Hand__c ASC
    ];
}

public List<Warehouse_Stock__c> getLowStockItemsSortedByUrgency() { — builds on the previous method, now sorted.
return [ — opens the query.
SELECT Id, Product__r.Name__c, Warehouse_Name__c, Quantity_On_Hand__c, Reorder_Threshold__c — the fields needed, including the threshold for context.
FROM Warehouse_Stock__c — the object.
WHERE Quantity_On_Hand__c < Reorder_Threshold__c — the same filter.
ORDER BY Quantity_On_Hand__c ASC — sorting ascending puts the most critically low items first — a manager scanning this list top-to-bottom sees the most urgent reorders before less pressing ones.
]; — closes the query.
} — closes the method.

Exercise

Write a method getOutOfStockItems() returning Warehouse_Stock__c records where Quantity_On_Hand__c equals exactly 0.

Show hint

WHERE Quantity_On_Hand__c = 0

Salesforce: ○ Not connected Connect

APEX

Execution Results

Run your code to see the result.

Query Low-Stock Items Quiz

1. What does Product__r.Name__c refer to, compared to Product__c?

The Name__c field on the related parent Product__c record, reached via the relationship The same value as Product__c, just formatted differently A field that doesn't exist The Id of the Warehouse_Stock__c record itself

My Notes

Questions about this lesson

No questions yet — be the first to ask.

Summary

A warehouse manager's most common question is "what needs reordering?" — this lesson answers it directly with a filtered, sorted query.

❬ Previous Next ❭