Automation for Internal Departments

The Customer Service & Finance teams sometimes have to search through the company’s archives to find specific purchase order documents to confirm past transactions, review transactions in the case of a client inquiry, and more. Well, to find these documents, they submit a support ticket to enlist the help of an IT team member to search for the requested documents.

Most times, multiple documents are requested, so it could take upwards of an hour to fetch and send the document back to whoever requested them. How about automating that process to cut the wait time by 90% with a super easy-to-use solution? 🤔

APIs and Visual "Programming"

This project marked my first experience developing an application with APIs as core functionalities. It was a great learning opportunity where I became familiar with API keys, authorizations, and servers. Additionally, SnapLogic introduced me to creating automations using no-code software. For those who aren’t familiar, SnapLogic is a visual, drag-and-drop integration platform where the designer connects components to create a procedure that takes input, process it based on configuration, and either passes the output to another component or completes the procedure.

Previously, the IT department relied on Liaison EDI Notepad to manually locate documents. To maintain consistency with existing technologies, we utilized ECS Liaison to access and navigate the EDI system – similar to the manual process for document retrieval.

Project Requirements

To function as an offline-capable email service that is both reliable and efficient, it must fulfill the following requirements:

• Connect to the Microsoft Outlook API

• Retrieve new emails from the designated inbox

• Verify the validity of received emails for parsing. If invalid, return an error message

• Parse emails to extract valid Purchase Order numbers

• Connect to the EDI Liaison database

• Filter the database for relevant purchase order documents

• Return the retrieved documents to the original sender

Python Version

The initial version of this project was a Python script designed primarily as a learning exercise for interacting with the database API. During this iteration, I gained valuable experience in making API calls to the EDI system using ECS Liaison. This phase was relatively straightforward, involving database connection through Python requests and parsing numerous documents to locate the requested purchase order.

SnapLogic Version

For this iteration, we prioritized convenience and user-friendliness. We decided to develop it as an email service because employees within the company are already familiar with using email clients for tasks like submitting weekly time sheets.

We utilized SnapLogic to automate the document retrieval process. SnapLogic allows us to connect pre-built, no-code “snaps” to perform specific tasks, including:

• Making API calls

• Parsing data

• Determining success or failure outcomes

• And more!

SnapLogic offers a level of convenience that local scripts cannot match. Employees no longer need to manually update scripts or navigate confusing error logs. All they need to do is provide a single Excel file with correctly formatted document information, with a template provided for their convenience.

Email Service System

The teams at Arden sometime use Microsoft Outlook for existing services, such as submitting weekly time sheets. To minimize the learning curve, we designed the system to be familiar with those existing email services. Other email clients can be used if needed; the only requirement on their end is to send a CSV file containing their requested documents to a designated email address. A new inbox was created specifically for this service to handle those emails.

The only file employees need to provide is a CSV file with two columns: PO date and PO number. These two fields are required for locating the purchase order. To ensure consistency, the documentation I created to explain how to use the services includes a blank CSV file and an example file, preventing employees from having to guess the correct format. Once the employee writes their requested document information into the CSV file (limited to 100 entries per document), they email it to the specified inbox.

The inbox is checked every ten minutes for unprocessed emails. The processing time ranges from 10 seconds to a few minutes, depending on the number of documents requested. Once processed, the original email is moved to a "processed" inbox. The service provides the following outcomes:

• POs Found: The user receives all documents in a compressed attachment

• POs Not Found: The user receives an error message along with a file listing the missing documents

• Mixture of POs Found & Not Found: The user receives a compressed attachment with the available documents and a separate file listing the missing ones

Document Parsing

The PO date and PO number alone are not enough to completely filter out irrelevant documents, as we specifically need those tagged with “850.” “850” is the document tag for a purchase order document. Asking users to include this “850” tag in the CSV file would be both unnecessary and potentially confusing. Instead, we handle the tagging internally to further refine the results to locate a single file that matches the PO number and date combination. The corresponding “850” PO document is then saved as a PDF in the session database.

This process is repeated for any remaining entries in the CSV file. Once all entries have been processed – whether or not they are found – all located PO documents are compressed into a single folder and sent back to the original sender.

Completed as an Email Service

This co-op project was completed as an email service for the Customer Service and Finance teams to take advantage of. Since I haven’t been employed at Arden since late 2023, I’ll never know if they updated it or replaced with it an entirely different service. However, the project as I left it was greatly appreciated by the two aforementioned teams.

I’m very proud that I created an application that truly helps ease the work of other employees to the point of receiving praise directly from the teams! That appreciation really solidified my passion for software development as a whole. Building beneficial software is my way to provide service to the world. 😌

Thank you for reading! 😄