# A food bank inventory tracker in SharePoint > For the full documentation index, see https://250highstreet.com/llms.txt > Counts on paper and best-by dates missed? Build an Items list, a Movements list and an expiring-soon view in SharePoint, with the honest limit on totals. Use two SharePoint lists: Items (what you have, reorder level, best-by date) and Movements (every box in or out). Add a view that shows items with a best-by date in the next 30 days. A list will not add up Movements into a running total by itself; Step 5 shows three ways to handle that: type the count, run a Power Automate flow, or total it in Excel or Power BI. ## Why paper counts and missed dates happen Small food programs may start with a clipboard, a whiteboard or a spreadsheet on one person's laptop. Counts get written in the shelf room and typed in later, if at all. Different shifts use different pages. Best-by dates live on the boxes, so nobody sees them until someone moves a box. Causes: - The count is on paper, so only the people in the room know it. - Nobody writes down what went out, so the count drifts. - Best-by dates are not in any list, so there is nothing to sort or filter. - Volunteers change each week, so the habit is not shared. If you only need a simple stock list for a small team, see "[Inventory tracking for a small team without new software](/guides/inventory-tracking-small-team/)". This guide adds the movement log and the best-by view for a place where different people come and go. ## Safety and policy This guide is about counting and dates. It does not tell you what is safe to keep, give out or throw away. Follow your own safety policy and any guidance from the group you work with. A tracker only shows you the dates you type in. ## What you will build Two lists in one SharePoint site: - Items: one row for each kind of product. - Movements: one row each time a quantity comes in or goes out. You will also make one view and, if you want, a daily email. Microsoft's help pages describe how to create a list and columns. Exact names may differ on your screen. ## Step 1. Make the Items list 1. Create a new blank list named Items. Microsoft explains how in "Create a list from the Lists app". 2. Use the Title column as the item name, such as "Canned beans". You can rename it. 3. Add a Choice column named Category. Our advice, not a Microsoft rule: put a number in front of each choice. 1 Canned 2 Dry goods 3 Fresh 4 Frozen 5 Other 4. Add a Single line of text column named Unit, such as "case", "bag" or "box". 5. Add a Number column named On hand. Set the minimum to 0 if the form offers it. 6. Add a Number column named Reorder level. This is the count at which you want to restock. 7. Add a Date column named Best-by date. Choose date only, not date and time. Microsoft's page "Create a column in a list or library" covers each step. Microsoft's page on column types explains Choice, Number and Date. A sample Items list to start from, with Category and Unit filled in: - Canned beans, 1 Canned, case - Rice, 2 Dry goods, bag - Apples, 3 Fresh, box - Frozen chicken, 4 Frozen, case - Paper bags, 5 Other, bundle Use your own items. Add On hand, Reorder level and Best-by date as you count. ## Step 2. Make the Movements list 1. Create a second list named Movements. 2. Use the Title column for a short label, such as "Delivery from partner" or "Packing day". 3. Add a Lookup column named Item. Pick the Items list as the source and the Title column as the one to show. Microsoft says the source list must be on the same site. For more on lookups see "[An equipment maintenance log in SharePoint for a clinic or lab](/guides/equipment-maintenance-log-clinic-lab/)". 4. Add a Choice column named In/Out with these choices: 1 In 2 Out 5. Add a Number column named Quantity. Count in the same unit as the Items list. 6. Add a Date column named Date. Set the default to today's date. Microsoft says the TODAY function can be used only as a default value. Now a volunteer adds one row for each delivery or each pickup. This is a log. It is not the total. ## Step 3. See the expiring-soon view A calculated column cannot show "days left". SharePoint blocks \[Today\] in calculated column formulas. Microsoft's page on the TODAY function says you cannot use it in a calculated column. A view filter can use it. 1. Open the Items list. In the view menu, create a new view named Expiring soon. 2. Use the classic view editor. Microsoft says the \[Today\] and \[Me\] keywords cannot be used when you filter from a column header, and that you must use the classic view editor for them. In the view menu choose Edit current view, then scroll to Filters. 3. Select Show items only when the following is true. Pick Best-by date. 4. Set it to is less than or equal to \[Today\]+30. 5. Sort by Best-by date, earliest first. Items already past their date also show here, because a date that has passed is still less than \[Today\]+30. That is useful. You can change 30 to 14 or 60. Microsoft's filtering page shows a filter with \[Today\] - 30. Plus looks ahead the same way. Test it with one sample row. If a row has no best-by date, the filter will not show it. That is fine for items such as paper bags. ## Step 4. A low-stock view 1. In the Items list, make a view named Low stock. 2. Sort by On hand, lowest first. This does not need the classic editor. 3. Open it before you plan an order. A filter that compares On hand with Reorder level on the same row is not something the standard view filter does. Sorting by On hand and reading the Reorder level beside it is one way to start. Another is a column that shows a yes or no flag. You could add a calculated column named Low with this formula: =IF(\[On hand\]\<=\[Reorder level\],"Yes","No") That formula does not use today's date, so it is allowed. Then filter the view on that flag. ## Step 5. The honest limit: running totals A SharePoint list does not add up rows from another list. When a volunteer adds "Out, 12" to Movements, the On hand number in Items does not change by itself. You have three ways to deal with this. Option 1: Type the count yourself. Each shift lead edits On hand in Items at the end of the shift, using the Movements rows as a check. It is the simplest and the easiest to get wrong. Option 2: Use a Power Automate flow. This is one way to start, and you should test it with sample rows first. The SharePoint connector in Power Automate lists an Update item action. The idea: 1. Trigger: When an item is created, on the Movements list. 2. Add the SharePoint action Get item on the Items list. Use the ID from the Item lookup. 3. Add a Condition. If In/Out equals 1 In, add Quantity to On hand. If it equals 2 Out, subtract Quantity. The choice text must match the Choice column exactly, including the number. 4. Add the SharePoint action Update item on the Items list. Set On hand to the new number. Set Title to the Title from Get item if the action asks for it. Pick the values from the dynamic content list in the flow editor, because exact names may differ. Two people adding rows at the same moment can make the flow read an old number. Test that case, and review the Movements log now and then. If you add a second flow that reads more than 100 rows with the Get items action, set Top Count and turn Pagination on. Microsoft says the default item limit is 100 and that you can raise it up to 5,000 with Top Count. Without Top Count, the flow returns 100 items. Option 3: Do the totals outside the list. Export the Movements list to Excel and sum it there, or connect the list to Power BI and make a total for each item. Microsoft documents both. This keeps the list simple and puts the math where it belongs. The cost is that the totals are only as fresh as your last export or refresh. See "[SharePoint list export to Excel fails or misses rows](/guides/sharepoint-list-export-to-excel-not-working/)" and "[Turn a SharePoint list into a simple dashboard](/guides/sharepoint-list-to-power-bi-dashboard/)". Pick one option and tell every volunteer which one it is. ## Step 6. Send a daily email about dates A view only helps when someone opens it. A scheduled flow can open it for you. Build it the same way as the flow in "[Send a reminder before a due date in a SharePoint list](/guides/send-reminder-before-due-date/)": a Recurrence trigger, Get items with Top Count and Pagination set, a filter on Best-by date, and one summary email with a link to the Expiring soon view. Send one email, not one for each item. ## Who should be able to edit Views are not security. Hiding a column in a view does not stop someone from seeing it. If only some people should edit Items, change the list permissions. Microsoft's page "Customize permissions for a SharePoint list or library" explains how. One way to start: let all volunteers add Movements rows and let two or three leads edit Items. Our advice, not a Microsoft rule. ## What to avoid - Do not use a calculated column for "days left". SharePoint blocks \[Today\] there. Use the Expiring soon view. - Do not use a column-header filter for \[Today\]. Use the classic view editor. - Do not type choice values in a flow that differ from the column. "1 In" and "In" are not the same text. - Do not skip Top Count and Pagination on Get items. The default returns only 100 rows. - Do not keep a second copy of the count in a spreadsheet. Use a view or an export from the list, so there is one source. - Do not rely on the tracker for rules about what is safe to give out. Follow your own safety policy. - Do not expect barcode scanning from this build. It has none. - Do not start with all products at once. Begin with one shelf or one category, fix what does not work, then add more. ## Honest limits A list does not count boxes for you. It stores what people type. If volunteers skip the Movements row, the totals drift. A physical count now and then, typed over On hand, brings the list back to the truth. Also, the best-by date on an Items row can hold only one date. If one product has several batches with different dates, make one Items row for each batch, for example "Canned beans, batch A". This is our advice, not a Microsoft rule. ## 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](/guides/what-a-sharepoint-tracker-costs/)". Bring one example of the problem to a free call. 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](/portfolio/) ## FAQ ### Is a SharePoint list enough for food pantry inventory management, or do I need software? A list covers counts, reorder levels, best-by dates and a movement log. It does not scan barcodes and does not total movements by itself. If you need either, you need more than this build. ### What columns does a sample pantry inventory list need? Items: Title (item name), Category, Unit, On hand, Reorder level and Best-by date. Movements: Item, In/Out, Quantity and Date. A sample Items row: Canned beans, 1 Canned, case, On hand 40, Reorder level 10, Best-by date 2027-03-01. ### How do I track expiration dates at a food pantry? Add a Best-by date column to Items and a view named Expiring soon with the filter Best-by date is less than or equal to \[Today\]+30, set in the classic view editor. Use one Items row for each batch. ### Can the list add up Movements into On hand by itself? No. A list does not sum rows from another list. Type the count yourself, use a Power Automate flow with the Update item action, or total the Movements in Excel or Power BI. ### Does the tracker tell me what is safe to give out? No. It stores the dates and counts you type. Follow your own safety policy and any guidance from the group you work with. ## Sources - [Microsoft Support, Create a list from the Lists app](https://support.microsoft.com/en-us/office/create-a-list-from-the-lists-app-b5e0b7f8-136f-425f-a108-699586f8e8bd) - [Microsoft Support, Create a column in a list or library](https://support.microsoft.com/en-us/office/create-a-column-in-a-list-or-library-2b0361ae-1bd3-41a3-8329-269e5f81cfa2) - [Microsoft Support, List and library column types and options](https://support.microsoft.com/en-us/sharepoint/lists/data-and-lists/list-and-library-column-types-and-options) - [Microsoft Support, Create list relationships by using lookup columns](https://support.microsoft.com/en-us/sharepoint/lists/data-and-lists/create-list-relationships-by-using-lookup-columns) - [Microsoft Support, Use filtering to modify a SharePoint view](https://support.microsoft.com/en-us/office/use-filtering-to-modify-a-sharepoint-view-3d8efc52-0808-4731-8f9b-3dfaeacea3d4) - [Microsoft Support, TODAY function](https://support.microsoft.com/en-us/sharepoint/functions-data/today-function) - [Microsoft Support, Customize permissions for a SharePoint list or library](https://support.microsoft.com/en-us/sharepoint/lists/sharepoint-sharing-and-permissions/customize-permissions-for-a-sharepoint-list-or-library) - [Microsoft Support, Export to Excel from SharePoint or Lists](https://support.microsoft.com/en-us/sharepoint/lists/data-and-lists/export-to-excel-from-sharepoint-or-lists) - [Microsoft Learn, SharePoint connector (Update item, Get items)](https://learn.microsoft.com/en-us/connectors/sharepointonline/) - [Microsoft Learn, In-depth analysis into Get items and Get files SharePoint actions](https://learn.microsoft.com/en-us/sharepoint/dev/business-apps/power-automate/guidance/working-with-get-items-and-get-files) - [Microsoft Learn, Create a report on a SharePoint List in Power BI Desktop](https://learn.microsoft.com/en-us/power-bi/connect-data/desktop-sharepoint-online-list) ## Related guides - [Food truck inventory tracker in SharePoint](/guides/food-truck-inventory-tracker/) - [Uniform inventory tracker in SharePoint](/guides/uniform-inventory-tracker-sharepoint/) - [Restaurant prep list and food temperature log on a phone](/guides/restaurant-food-temperature-log-prep-checklist/) Source: https://250highstreet.com/guides/food-bank-inventory-tracker/