Shortage Report
https://shortagereport.flickerbook.co.uk was a project I did to make my job faster as a logistics administrator. I noticed that there is a need for it after couple months of working as an admin, and decided to create it as I had an idea of how to do it and wanted to see if I can bring this idea to fruition.
How it works
Two steps are necessary for this web app to work:
- future deliveries details have to be uploaded to database.
- text from shortage sheet has to be passed into input field.
The web app uses the data from the shortage text to query database with deliveries details and find the nearest deliveries details to generate a shortage report table.
Problems Shortage Report solved for me
One of my tasks as a logistics administrator is to process orders I receive from factories, then depending on were the requested stock is located I have to print pick sheets so warehouse operatives know were to pick from. However often times factories request stock that we do not have. This might be for several reasons, like late delivery, rejections, stock count errors or making order with incorrect or outdated product code.
Normally a shortage sheet gets printed together with the order, but the factory which placed the order is not aware of the shortage unless it receives the sheet, which often tends to get lost.
I could just send a print screen or a pdf file of the shortage sheet back to factory, but I figured I can do something a lot better then this, and it can be done with a click of a button. So instead of just sending the list of stock that is short for order, I can also provide details of next delivery like ETA and quantity to be delivered (usually if I just send the shortage list, they request these details anyway, so I can shoot two birds with one stone).
As my current employer uses Protean to manage stocks, there is an option to export delivery data as one large text string. So the basic idea behind this project was this: from the delivery text string fork out delivery times, product codes, product quantities and store them in a database. Then copy text from shortage report and fork out product codes and shortage amounts and use this data to query the database for the nearest delivery. Then when I have results I can create an HTML table and just copy and send it back to factories.
This approach is also a time saver for me, as otherwise for each item in shortage sheet I would need to get delivery details one by one, and now I can just copy all the shorts and just loop trough the database to populate an html table automatically. Below is an example of an HTML table for shortage report.
| Product code | Product name | Shortage | Next delivery | Expected quantity | Enough to cover |
|---|---|---|---|---|---|
| M001 | Carrot | 4 | Today | 10 | Yes |
| M002 | Tomato | 5504 | Today | 5400 | No |
| M003 | Courgettes | 10 | Today | 26 | Yes |
| M004 | Cucumber | 181.29 | Today | 2730 | Yes |
| M005 | Chives | 1.25 | Tomorrow | 6 | Yes |
| M010 | Mint | 22.16 | Tomorrow | 80 | Yes |
| M011 | Tomato Pomodorino | 185.69 | Today | 210 | Yes |
| This report does not take into account stock that is ONHOLD or on GIPOSR. | |||||
What I learned while developing Shortage Report
The main advantage for me was that I had to work with strings, and find ways to extract the data I needed from them. Also wanted to code this web app using CSS grid layout, as I have never done this before. And the best part is that this work got appreciated!
All the code for this project can be found on my GitHub.