Reporting most recent inventory number
I'm creating a dashboard for my clinical leadership so that they can see how much PPE staff is reporting is available onsite. I've created a form that staff will use to report current stock numbers but I am stuck on how to have the most recent reported number be reflected on a widget in a dashboard?
Staff will be reporting new numbers Bi-Weekly and leadership only wants to see the most recent number. Anybody have some suggestions on how to display this?
Answers
-
Sounds like you need a Max(Collect....) formula.
I use a helper checkbox column with the formula below
=IF([Date]@row = MAX(COLLECT([Date]:[Date], [Item Number]:[Item Number], [Item Number]@row)), 1)
This flags the newest entry for that item number.
-
Create a column named "Last Inventory Date" and plug this formula in.
=MAX(COLLECT([Inventory Date]:[Inventory Date], [Staff Name]:[Staff Name], [Staff Name]@row))
You can use locations instead of staff names if they are reporting inventories from different locations.
Create a new column named something like "Last Reported Inventory", this will be where the newest number is populated. Then plug this formula in that column.
=INDEX(COLLECT(Total:Total, [Inventory Date]:[Inventory Date], Last Inventory Date@row), 1)
Total Column = the inventory number column on the form that the staff is reporting.
You can add a "metric" widget to the dashboard and only include the cells that are updated.
Example: First two rows
Staff - A / Last Reported Inventory: 87 / Last Inventory Date: 01/30/22
Staff - B / Reported Inventory: 75 / Last Inventory Date: 01/29/22
The numbers and dates will change as forms are completed and rows are added below.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.9K Get Help
- 379 Global Discussions
- 209 Industry Talk
- 441 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 297 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!