← Al Davies, 250 High Street

Dental office supply inventory with reorder alerts

Track dental supplies in one SharePoint list with a row per item, an on-hand count, a reorder point and a par level. A calculated column flags items at or below the reorder point and sets the order quantity as par minus on hand, and a weekly Power Automate email groups them by vendor. Keep patient information out of this list. Par level is the amount you want on the shelf after an order, and the reorder point is the amount that triggers an order. This guide covers supplies only.

This guide is about the ordering side: par, reorder point, vendor and one weekly email. It does not cover rules for storing, handling or using supplies. For those, follow your own office policy and the manufacturer's label. If you want the basics of a stock list with an in and out log, see "Inventory tracking for a small team without new software". For a different kind of stock list, see "A food bank inventory tracker in SharePoint". For service dates on machines, see "An equipment maintenance log in SharePoint for a clinic or lab". If your supply list is a spreadsheet today, see "Replace a spreadsheet tracker with a SharePoint list" first.

Par level and reorder point: what each one means

These two numbers do different jobs, and people mix them up.

An example with made-up numbers: gloves have a par level of 20 boxes and a reorder point of 8 boxes. When the count falls to 8 or lower, the order quantity is 20 minus the count on hand. If 6 boxes are left, you order 14.

One way to start is to set the reorder point at the amount you use between placing an order and receiving it, plus a small cushion. Set the par level at the reorder point plus the amount you want to buy each time. These are your office's numbers. Nothing in Microsoft 365 sets them for you, and they are our advice, not a Microsoft rule.

Before you start

  1. Pick the supplies worth tracking. One way to start is items that would stop work if they ran out. Skip items nobody would miss for a week.
  2. Pick one storage area to start. Add other areas after the first one works.
  3. Pick one unit for each item (box, pack, each) and write it in its own column.
  4. Pick one owner. In this guide that is the office manager. Pick a backup for days off.
  5. Keep the list to supplies only. Do not add patient names, appointments or any other patient detail. Recall reminders are a separate job and belong in the system your office already uses for patients, not in this list.

Step 1. Create the supply list

Create a blank list in your team site and name it something plain, like "Supply Items". Exact names may differ on your screen. Use the Title column for the item name, for example "Exam gloves, medium".

Add these columns:

If you use a Choice column to rank anything, such as an Ordering priority, put a number in front of each choice, such as 1 Needed this week, 2 Needed this month, 3 Can wait. Our advice, not a Microsoft rule: keep the number prefix so the choices read in your order.

Step 2. Add the two calculated columns

A calculated column works out a value from other columns in the same row. Microsoft's formulas page gives this kind of example for comparing two columns: =IF([Cost]>[Revenue], "Not OK", "OK"). Using the same pattern, create a calculated column named Order status, set the returned type to single line of text, and use:

=IF([On hand]<=[Reorder point],"Reorder","OK")

Create a second calculated column named Order quantity, set the returned type to number, and use:

=IF([On hand]<=[Reorder point],[Par level]-[On hand],0)

Use your own column names inside the square brackets. In a formula, a column is named by its display name, so check that every name matches yours exactly. Microsoft says "The TODAY and ME functions are not supported in calculated columns." These two formulas use no date, so they are fine. It does mean you cannot build a column that says "not counted in 30 days." Use a view or a flow for that.

If the Order quantity comes out negative, the par level is lower than the count on hand. Check that the par level is higher than the reorder point for every item.

Step 3. Make the views

A view is a saved way of looking at the list. Create these:

Our advice, not a Microsoft rule: show Title, Vendor, On hand, Reorder point and Order quantity in the "To order" view. The weekly email is one block of lines, one line per item, each with the vendor, item, quantity and unit.

A note about filters. Microsoft says filters that use [Today] or [Me] need the classic view editor (Edit current view, then Filters). Our advice, not a Microsoft rule: use the classic view editor for blank and "is not equal to" filters too. The "To order" view uses a plain "equals" filter, so you may not need the classic editor for it. Remember that a view is not security. It only changes what shows on screen. People who can open the list can still reach the items a view hides.

Starter rows and column list

Copy these columns into a blank list, then add a few sample rows with made-up numbers and delete them before real use:

Title | Single line of text | the item name

Storage area | Choice | your rooms or shelves

Unit | Choice | each, box, pack

On hand | Number | the shelf count

Reorder point | Number | the order trigger

Par level | Number | the target after an order

Vendor | Single line of text | who you order from

Last counted | Date | the day of the last shelf count

Sample rows:

Instruments and several rooms

Our advice, not a Microsoft rule: instruments you reuse and consumables you use up are different counts, so add the optional Item type column and filter the views by it. For a practice with several operatories, add each operatory as a Storage area choice and use the "By storage area" view to count room by room.

List or inventory software

Our advice, not a Microsoft rule: this list suits an office that wants par levels, a reorder point and one weekly email. Barcode scanning, lot tracking and placing orders with a vendor are jobs for inventory software.

Step 4. Count and update on a set day

The flag is only as good as the number behind it. One way to start:

  1. Pick one day a week for a shelf count.
  2. The owner opens "By storage area" and types the real count into On hand.
  3. The owner fills in Last counted.
  4. The flag and the order quantity update on their own when the row is saved.

Our advice, not a Microsoft rule: a weekly count is a good start for cheap supplies. For items that move fast, count twice a week or ask staff to update On hand when they open the last box. Pick the habit your team will keep.

Step 5. Send the office manager one summary email

A scheduled flow in Power Automate can read the list and send one email. Microsoft's page on scheduled flows says a scheduled cloud flow runs on a recurrence, for example once a week, and that you choose the frequency, the interval, the start time and the time zone. Exact names may differ in your account, and you should build it on a copy of the list first.

One way to build it:

  1. Create a scheduled cloud flow with the Recurrence trigger. Set Frequency to Week and Interval to 1. Choose the day, the hour and the time zone.
  2. Add the action Initialize variable. Name it ReorderText, set the type to String and leave the value empty. Microsoft says a variable can only be declared at the top level of the flow, not inside a loop or a condition, so put this action before any loop.
  3. Add the SharePoint action Get items and choose your list. Microsoft describes it as "Gets items from a SharePoint list." In Advanced options, set Order By to Vendor asc so the email groups by vendor.
  4. Set Top Count to the number of rows in your list, up to 5,000. With the default options, Get items returns only 100 items, so rows after the first 100 would be left out and nothing would tell you.
  5. In the action settings, turn on Pagination if your list may grow past that number.
  6. Add an Apply to each action over the value from Get items.
  7. Inside the loop, add a Condition: Order status is equal to Reorder.
  8. In the yes branch, add Append to string variable for ReorderText. Add one line with the vendor, the item title, the order quantity and the unit.
  9. After the loop, add the Office 365 Outlook action Send an email (V2). Put the office manager in the To field, write a subject like "Supplies to order this week", and put ReorderText in the body.

Microsoft warns that when you use a variable inside an Apply to each loop, the loop should run one item at a time if you need predictable results. One at a time is the default. Leave it that way.

If nothing needs ordering, the email will be empty. You can add a second Condition after the loop that checks whether ReorderText is empty, and skip the email if it is. Or send the email anyway, with a short line such as "Nothing to order this week." Either choice is fine. Pick the one the office manager prefers.

Step 6. Test it before you trust it

  1. Copy the list and point the flow at the copy.
  2. Set one item's On hand below its reorder point and run the flow by hand.
  3. Check that the email lists that item, with the right vendor and quantity.
  4. Change the item back, run again, and check that it no longer appears.
  5. Check a row past the first 100, if your list is that long, to confirm Top Count and Pagination work.
  6. Point the flow back at the real list.

What to avoid

Honest limits

A SharePoint list is a good start for ordering. It is not a purchasing system. It does not place orders with a vendor, scan barcodes, or track lots or serial numbers unless you build that by hand. It does not know about vendor price changes or back orders. The weekly email tells the office manager what the numbers say, not what the vendor can deliver. Which Power Automate features your account includes depends on your plan, so check with your admin.

When to get help

You can build all of this yourself in Microsoft 365. If you would rather have it built and tested for your team, I do that. A tracker like this is a fixed price: $5,000 for one, built in 2 weeks. See "What a SharePoint tracker costs to build". Bring one example of the problem to a free call.

Tell me what's still manual

I am Al Davies. I run 250 High Street LLC and build SharePoint trackers and Power Automate flows for operations teams. I work remotely. The first call is free, and I will not try to sell you anything.

See trackers I built: Tools I built

FAQ

Which columns does a dental inventory list need?

Title, Storage area, Unit, On hand, Reorder point, Par level, Vendor and Last counted, plus the two calculated columns Order status and Order quantity.

How do I track instruments apart from consumables?

Our advice, not a Microsoft rule: add an Item type choice of Consumable or Instrument, and filter your views by it.

When does an office need inventory software instead?

Our advice, not a Microsoft rule: this list handles par, reorder point and one weekly email, and barcode scanning or vendor ordering would need separate software.

How do I count stock across several operatories?

Add each operatory as a Storage area choice and use the "By storage area" view to count room by room.

Why does my weekly email only show some of my items?

The Get items action returns only 100 items by default. Set Top Count to the number of rows in your list, and turn on Pagination if the list may grow past 5,000.

Sources