Automate your middleman tasks!

Some days you are just a middleman. I was in such a situation a few months back.

The middleman task

I was working on some software that tracks shipments that have been pushed between two different systems. One system would generate the shipment and then it would send shipment information to the second system for reporting purposes. Normally, this software works out very smoothly and there aren't any communication issues between the two systems.

Once in a while though, a shipment will not get reported to the second system. At this point, the team in charge of the second system will contact me and ask for me to supply them with a file that contains all the shipment information based on tracking numbers in another file they provide.

This used to be a real pain point for me. Not only did it interrupt me when I was in the middle of other projects but, it could be really time consuming. Sometimes the file they would send over to me would contain hundreds of shipments that didn't get reported. It was not uncommon to spend hours looking up all the shipments and generate a file in the correct format.

Use automation for that middleman task

It was a really weird position to be in. I was the middleman for two different systems and neither side was willing to change the way they processed the shipments. Sometimes, if the shipment volume was really high, I would get two of these e-mails in a week.

I wanted to change the situation that I was in. I started to document the steps that I was taking to get this missed shipments file created. I don't think that people understand how important this step is when you are trying to automate a process.

The process was fairly straight forward for me.

  • Read the file that had been provided so that I could parse some of the shipment information like the tracking number.
  • Loop over the tracking numbers and grab the shipment information that matched that tracking number.
  • Take that shipment information and make sure the various fields are in the correct format the file needs.
  • Generate a file in the correct format with the appropriate columns.
  • Generate another file that lists all the tracking numbers that were not found in our system.

The above steps are very generic and they do not include some of the business requirements that I needed to worry about as well.

The results of the automation

I finished writing this script in about two weeks during my spare time (it took longer than expected, mostly because the business rules were very tricky and took awhile to test). In all, I probably spent about 16 hours writing it and tuning it. The script paid for itself in about 4 or 5 requests. It took almost a month to get that many requests.

Now, I have handed that script over to our newest team member and he doesn't have learn the process that I had to learn when I wrote the script. The process is basically a non-event for him! He gets a file full of tracking numbers and runs it through the script. It generates the two files he needs and then he replies in an e-mail with the generated files. We are at the point where the script is so well tested that we might just automate the sending of the e-mail as well.

Now if we can just get the two systems to agree on a few things so we don't have to keep running this script all the time.

Happy Coding!