Topaz Sustainable Timbers Australia, a company operating in both B2B and B2C sectors, faced significant challenges in managing its inventory. As they grew, their reliance on numerous spreadsheets for tracking inventory became unsustainable. Here’s how we tackled their problems with innovative solutions.
What would have normally taken many months to build was built in 55 hours. From data retrieval to backend workflows to frontend computations to UI design on Retool, our team's expertise ensured that every aspect of the project was executed with precision and efficiency.
Problem Statement
1. Initially, Topaz Sustainable Timbers Australia heavily relied on multiple spreadsheets to manage inventory. This approach was not only tedious but also prone to errors.
2. The spreadsheets were interconnected, requiring consistent data integrity, which became increasingly difficult to maintain as the company scaled.
3. Managing vast amounts of data points for each Stock Keeping Unit (SKU), including links for high-resolution images, was particularly challenging. Each excel sheet was large in size and took forever to load.
4. To provide further insight, each Stock Keeping Unit (SKU) in their inventory required a high-resolution image. Previously, the team had to constantly switch between Google Photos, where these images were stored, and their spreadsheets to view high-resolution images of specific SKUs. This process was cumbersome as Excel does not support viewing enlarged, high-resolution images directly within the sheets.
Another significant issue was the use of Google Photos for storing and accessing images. This platform, not designed for the said use cases, resulted in slow and inefficient loading of photos, adding to the overall time consumption and inefficiency of the process.
5. Additionally, selecting the right SKU for their customers required applying multiple filters based on various attributes such as the type of timber, its dimensions, or the price point and so on. This task became increasingly challenging in Excel, especially as the amount and kind of data grew and the spreadsheets became more complex. With multiple data points to consider, filtering in Excel was not only tedious but also prone to errors and inefficiencies.
In contrast, using SQL queries for data management would have allowed for easier and more efficient filtering and retrieval of data. 6. The challenges of inventory management were particularly pronounced for the ground staff, as performing these tasks on mobile devices was even more cumbersome. Simple actions like checking the inventory were fraught with difficulties. Moreover, when new images for SKUs were captured, they had to be correctly named—replacing the default names assigned by computers or mobile devices. These images then needed to be accurately linked with their corresponding SKU codes, and the links were manually pasted into Excel sheets. This process was not only labor-intensive but also inefficient, as it couldn't be effectively managed on-the-go using mobile devices
Our Comprehensive Solution with Retool Desktop and Mobile Apps
Our solution comprised two key components:
1. Database and Storage Solutions:
PostgreSQL Database: To maintain all inventory data efficiently.
AWS S3 for Cloud Storage: For storing and retrieving high-resolution images quickly and efficiently.
2. Retool Desktop App, Progressive Web App for Mobile and Native iOS Mobile App:
Desktop Web App: This app features common filters, column customization, and a primary data table. Complicated Filters for price, length, status, and more can be applied for immediate results, a significant improvement over Excel.
Clicking on a table row retrieves the associated high-resolution image from AWS S3, a functionality previously unavailable. The images are displayed beside the table and enlarged image for detailed viewing can also be found.
The desktop app allows team members to edit data directly from the frontend, with automatic backend updates in PostgreSQL by clicking on 'Save Changes' button.
The image upload functionality is intuitive, enabling users to preview and map images to the right SKUs before uploading to S3 and updating the data on postgreSQL DB as well. The image retrieval process is optimized for speed, even with large image files, through the use of signed URL signatures from S3
Progressive Web App for Mobile: A simplified version of the desktop app, tailored for ground staff to easily view and update data. It all essential functionalities are accessible, even on mobile devices, making the process seamless and less time-consuming than before
Native iOS App for Image Uploading: It specifically addresses the challenge of capturing and uploading images in real time. This app allows users to either take a new photo or attach an existing one, enter the corresponding SKU value, preview the image, and then upload it directly to AWS S3. This process not only saves time but also ensures that the SKU and image are correctly mapped in the PostgreSQL database.
Results
The introduction of these Retool apps and workflows has significantly transformed the inventory management system for Topaz Sustainable Timbers Australia. The company has moved away from the cumbersome and error-prone spreadsheet system to a more robust, efficient, and scalable solution. Key benefits include:
Enhanced Data Integrity: With centralized data management and automated workflows, the risk of data inconsistencies and errors has been greatly reduced.
Improved Efficiency: The ability to apply complex filters and directly edit data on the frontend streamlines the inventory management process.
Time-Saving: Immediate image retrieval and the ability to upload images directly from the ground save considerable time for the staff.
Mobile Accessibility: The mobile app ensures that ground staff can efficiently manage inventory and upload images without needing to access a desktop computer.
Better Image Management: Storing high-resolution images on AWS S3 and linking them correctly with SKUs has simplified image management, a critical aspect for a company dealing with physical products.
In conclusion, the implementation of these Retool apps and workflows has not only resolved the immediate challenges faced by Topaz Sustainable Timbers Australia but also equipped them with a scalable and efficient system for future growth.