A Shiny App for Freezer Management
Github Repository | Explore the App
Background
Our engineers and scientists were having difficulty managing all the versions of frozen yogurt (Froyo) made by the production team, and I was brought in to provide a solution.
Froyo can be made from one of several strains of bacteria. Once the fermentation is completed, the yogurt is stored either alone or the product of several lines is combined to create a larger batch. These batches are stored in the freezer until they can be used by one of the downstream groups. The two largest groups are Process, who prepare froyo for sale, and Experimental, who act as R&D for new flavors or production techniques.
However, since we are a growing company, Froyo production and usage are not tightly linked leading to large fluctuations in the amount and type of Froyo available.
The Problem
Our current solution to freezer management is an excel sheet hosted on a cloud service which does not adequately address our needs.
- Manual data entry leads to non-standard entries.
- Tracked values drift from actual contents.
- Some batches are not tracked or persist in database.
- Version conflict on the cloud service is surprisingly common.
To identify or create a solution to this problem, it is important to understand the requirements and restrictions faced by the stakeholders.
Stakeholder Priorities
Stakeholders for this workflow can be divided into two groups: Managers and Operators. Managers as you might expect, set goals, distribute work, and make sure everything runs smoothly. Operators execute the ground level work, designing and executing specific experiments, running the production lines, and harvesting and handling the froyo. As you might expect, these groups have different priorities.
Managers want to track how each froyo batch is being used and by whom. They need to understand the amount and types available to set goals, and they want run charts in order to predict material needs for downstream groups.
Operators have more grounded priorities. They asked for standardization in names and locations. Additionally, they wanted a tool which was easy to use and solved problems stemming from the current workflow (the excel sheet).
| Managers | Operators |
|---|---|
| Tracking of how material is being used and by whom. | Standardization in sample name and locations. |
| Reason for removal. | Ease of batch entry and modification. |
| Clear understanding of weight of available material. | One-click to remove containers of material. |
| No data loss if Shiny app is not maintained. | Understand who else might need material. |
| Run Charts to predict needs of material. | Age of available material. |
Unfortunately, several priorities were outside the scope of this project. Several managers wanted this freezer management system to integrate with other databases within the company. Operators asked for a stronger data capture component to help them with their experiments. While these were considered stretch goals for this project, they were not implemented in this version.
Other Solutions
Before designing our own solution from scratch, it was important to assess existing tools, unfortunately none meet our needs.
Laboratory and Production Software: Pro: Two in-house systems are implemented in other groups which can act as a database. Con: They are either cost prohibitive or inadequate to our needs.
Inventory Management Software: Pro: Tools have been created to manage inventory across many industries, specifically built for this problem. Con: Tech-hesitancy and cost.
Microsoft Access: Pro: Integration with other Microsoft tools such as Excel, PowerPoint, and Power BI. Con: No mobile option and no institutional knowledge.
Shiny App
To satisfy the priorities of the stakeholders the data captured in the app is split into two CSV files stored alongside the shiny app. The Inventory contains information on existing batches, the Ledger, all Inputs and Modifications. These are stored as CSVs instead of another database system due to the need for data to be accessible if the app were to fail and the lack of institutional knowledge of other database software.
Inventory and Ledger Viewer
When the app is accessed, the first page allows easy viewing of the current inventory. Each batch is separated into a clear column and the number and weight of the containers is easily identified. The graph view can be substituted for a table view if a more numerical comparison is needed but for almost all purposes the Quick Look suffices.
In the upper left box different filters can be selected to visualize material of interest more easily. The Strain and Batch IDs are the most common search feature so are listed more prominently, however any feature of the database can act as a filter using the “Group” and “Value” boxes. These provide an easy way to filter and find material of interest and both the Information and Quick Look boxes are responsive, displaying different information based on the level of detail of the search. See it in action.
Batch Input and Modification
The Input and Modification Pages are easily accessible in the sidebar. Each page is laid out in a similar manner, collecting a variety of information about the material being stored and its location. Since many inputs such as Strains, Names, and Groups are repeated, the app supplies a drop down selection or allows manual entry for new values. The Modification page also contains a checkbox for the easy removal of a container.
Finally, the Settings page allows the resetting of each database (after multiple confirmations) in the case of taking inventory or the need for a fresh start. Archives of the Inventory and Ledger are made during the resetting process, so no data is lost.
Conclusion
This shiny app was specifically built to address the problems created by our current system and solves all of them. All priorities from stakeholders were addressed: The app can be used by Operator and Managers without training; it operates over the internet and on mobile while also maintaining static CSV files in case of app failure. Additionally, visualization, data entry and updating are easy and quickly accomplished. The app is ready for internal use immediately.
Future Work
While this version of the app addresses all stakeholder concerns and is ready to use. There are several areas of improvement which I will pursue in the future.
- Data entry might need more strict validation to prevent erroneous data.
- Entering multiple containers and batches could be done with one click instead of having to enter each individually.
- Data collection could be made more flexible so any information could be collected and stored besides the fields needed in the current use case.
- Adding a clear workflow for taking stock of inventory.
- More narrow data analysis features for different workflows
With these implementations and some increases in flexibility, this app might become a useful product which others could benefit from. In the meantime, it is adding a good deal of value to our company and helping use make the best “froyo” we can.
About Author
