How do I set up a frame and lens inventory system for prescription ordering and store-level inventory tracking?

Here’s a practical way to set up a frame and lens inventory system that supports both prescription ordering and store-level tracking.

1) Define what you need to track

Separate the system into 3 inventory buckets:

A. Frames

Track each frame by:

• Brand
• Model
• Color
• Size
• SKU / barcode
• Vendor / supplier
• Cost
• Retail price
• Location (store, back room, lab, display, incoming)
• Status (available, on hold, sold, returned, damaged, reordered)

B. Lenses

Track lenses by:

• Lens type (single vision, bifocal, progressive, etc.)
• Material (CR-39, poly, Trivex, high-index)
• Coating (AR, blue light, scratch-resistant, photochromic)
• Power range / prescription category if needed
• Supplier SKU
• Cost
• Standard turnaround time
• Status (stocked, ordered, received, used, defective)

C. Order components

For each prescription order, track:

• Patient/order ID
• Frame selected
• Prescription details
• Lens type and options
• Lab submission status
• Order date
• Expected delivery date
• Completion date
• Remake/return reason if applicable

---

2) Use unique identifiers

Every item and order should have a unique ID.

Examples:

• FRAME-000123
• LENS-SSV-0456
• ORD-2026-00189

If possible, also use:

• Barcodes or QR codes on frames, lens stock bins, and order paperwork
• A scannable label for faster receiving and checkout

---

3) Set up inventory locations

Create location fields so you always know where items are.

Example locations:

• Main floor display
• Frame drawer A
• Back stock
• Lab
• Receiving
• Vendor return area
• Repair/damaged shelf
• On hold for patient

This is important for store-level inventory, because “in stock” is not enough—you need to know where it is.

---

4) Create a database or spreadsheet structure

You can start in Excel/Google Sheets, but a database or inventory software is better long-term.

Suggested tables

Frames table

• Frame ID
• Brand
• Model
• Color
• Size
• SKU
• Supplier
• Cost
• Retail
• Quantity on hand
• Reorder point
• Location
• Status

Lens table

• Lens ID
• Lens category
• Material
• Coating
• Supplier SKU
• Cost
• Quantity on hand
• Reorder point
• Location
• Status

Orders table

• Order ID
• Patient ID
• Order date
• Frame ID
• Lens ID
• Prescription summary
• Lab vendor
• Order status
• Due date
• Pickup/completion date

Transactions table

Track every movement:

• Transaction ID
• Item ID
• Quantity change
• Type: receive, sell, adjust, transfer, return, damage, reorder
• Date/time
• User
• Notes

This transaction log is what makes inventory auditable.

---

5) Set reorder points and par levels

For each frame and lens item, set:

• Par level = ideal amount to keep on hand
• Reorder point = minimum amount before ordering more

Example:

• Keep 8 of a popular frame model
• Reorder when it drops to 3

For lenses:

• Keep fast-moving items in stock
• Order custom/specialty items as needed

Use historical sales data to decide reorder levels.

---

6) Build the prescription-to-inventory workflow

A good workflow looks like this:

When a frame is selected

1. Scan/select frame
2. Mark frame as reserved/on hold
3. Link frame to patient/order
4. Deduct from available inventory if sold
5. If only reserved, keep separate from available stock

When lenses are ordered

1. Enter prescription
2. Choose lens type/options
3. Check stock if lenses are stocked
4. If not stocked, create vendor/lab order
5. Update order status to “ordered”
6. When received, mark as “received” and link to order

When order is completed

1. Convert frame/lens to “sold/dispensed”
2. Remove from inventory
3. Close order
4. Record completion date

---

7) Track store-level status separately from sales status

A frame can be:

• Physically in store
• Available for sale
• Reserved
• Sold but not picked up
• Returned
• Damaged

This avoids confusion between physical presence and ownership status.

---

8) Include receiving and adjustment procedures

Whenever stock arrives:

• Compare packing slip to purchase order
• Verify quantities and SKU
• Scan items into inventory
• Assign location
• Mark as available

For adjustments:

• Require reason codes:
  • damage
  • shrinkage
  • theft
  • counting error
  • vendor return
  • sample/demo use

---

9) Perform cycle counts

Do regular counts to keep records accurate.

Recommended:

• Weekly for high-value frames or fast-moving items
• Monthly for all stock
• Quarterly full physical audit

Compare:

• System quantity vs actual quantity
• Investigate differences
• Record adjustments

---

10) Use software if possible

Options:

• Spreadsheet for small stores or pilot setup
• POS + inventory system if you already have a retail platform
• Dedicated optical practice management software with inventory module
• Custom database if you need specialized prescription and lab tracking

Look for:

• Barcode scanning
• SKU management
• Multiple locations
• Purchase orders
• Vendor management
• Order tracking
• Reporting
• User permissions

---

11) Helpful reports to generate

At minimum, track:

• On-hand inventory by location
• Low-stock report
• Overstock/slow-moving frames
• Frames sold by brand/model/color
• Lens usage by type/material/coating
• Open orders by due date
• Inventory shrinkage/adjustments
• Margin by item or category

---

12) Recommended starter setup

If you want a simple but effective system, start with:

Frame master file

Columns:

• Frame ID
• Brand
• Model
• Color
• Size
• SKU
• Vendor
• Cost
• Retail
• Location
• Qty on hand
• Qty reserved
• Reorder point
• Status

Lens inventory file

Columns:

• Lens ID
• Type
• Material
• Coating
• SKU
• Vendor
• Cost
• Qty on hand
• Reorder point
• Status

Order file

Columns:

• Order ID
• Patient name/ID
• Frame ID
• Lens ID
• Rx details
• Order status
• Dates
• Notes

Transaction log

Columns:

• Date
• Item ID
• Transaction type
• Quantity
• User
• Reference order
• Notes

---

If you want, I can also provide:

1. a Google Sheets template layout,
2. a database schema, or
3. a workflow diagram for the ordering/inventory process.