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
- Customer Resources
- 64.8K Get Help
- 434 Global Discussions
- 138 Industry Talk
- 470 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 65 Community Job Board
- 486 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!