Overview:

This article will illustrate how Merit Technologies built a custom application to handle Mail Merging account data into Word Documents, inserting barcodes on those documents, and then receiving back the signed copy, reading the barcode, and attaching the file to the account record in the system.

The Stack:

For those who have not heard the term “stack” in the context of software development, it simply refers to the combination of technologies and platforms used to run the application. This ranges from the server’s operating system to the programming language used to write the code.

For this project, Merit is running what’s call a LEMP stack, Linux operating system, Nginx web server (pronounced “engine-x”, hence the “E” in LEMP), MySQL database, and PHP programming language.

The Challenge:

For this project, the client needed to process large amounts of creditor data, generate word documents filled out for the specific account to be mailed off, and then receive and process the returned signed copies of those documents for archiving purposes.

Step 1. Dynamic Mail Merge Templates

The first step involved building a mail merge template system that allowed the client to upload their own merge templates and build rules around how a template is matched to an account for merging. Those rules include things like which state the account is from, are additional documents need, is there a fee for court processing, etc.

The templates used a merge tag in the format: ${field_to_merge}. This allows account specific data to be inserting by the program into the word document template.

The Templates can be created and edited by any user with the appropriate permissions. Templates also have history revision tracking to make sure each version of the template is accessible, the most recent version being the one the system will use to perform the mail merges.

Once the template has been uploaded, template administrators can create Merge Rules that govern how account data determines which template to use. Rules belong to a specific process and optionally have conditions to better customize when specific templates are using.

For example, most templates always have as State condition where IF state = [State to Match], then use the template selected. See screenshot:

Step 2. Verify Account Template and Run a Smoke Test

Now that the Merge Template has been setup, it is time to verify our test account is properly matching to the template, based on the rules we built.

The application offers a comprehensive view for each account where are related data is grouped and optimized for the consumption of the end user. Looking at the test account shows it is properly matching to the Merge Template.

To perform a merge, a Smoke Test must pass validation errors. The Smoke Test action reads the template for all merge tags using the format ${field_tag}, and then compares those tags to the account data available. If any values are empty or missing, a smoke test errors is logged and displayed on the account view page.

Correcting the missing data and re-running the Smoke Test will clear the errors and allow the merge to be performed.

Step 3. Running the Mail Merge

Mail merge can be run from the account view page, or from a few other places in the application that allow bulk merges. For this article, the single test account will be the only merge performed.

Clicking the “Mail Merge” button triggers the merge, builds the word document from the template, and saves the new copy to the account view for history tracking purposes.

After downloading and opening the file, we can see all the account’s data has replace the ${field} merge tags. Observe the highlighted fields in the screenshot below.

Additionally, the ${data_id} tag is a special tag used by the system to barcode the document. This barcode will later be used by the scanner to create the return document for uploading into the system. These documents are download, printed, and mailed out to the appropriate parties for signing.

Step 4. Receiving the Document Back for the Signatory

As the physical documents are received back by mail, the scanner reads the barcodes and names the scanned PDF after the data_id encoded in the barcode.

If a document has multiple pages, the scanner will separate individual documents by using the barcode to denote the end of a previous document and the start of a new one. This allows hundreds of physical copies to be loaded into the scanner and rapidly processed.

The result is a ZIP file containing all the signed PDF scans. See screenshot:

Step 5. Import the ZIP of Scans (Final Step)

The scans are either delivered to the user via email or saved on a USB device inserted into the scanner. For the final step, the user must upload the ZIP file back into the application so it can process all the documents.

In this example, we only have 1 document being processed, but it is common to have hundreds of documents in these zip files. This is where real time savings occur by using an application like this.

The user will receive an alert when the ZIP import is complete. The account will now display the uploaded PDF in the account files section, which can be downloaded for review at any time.

Summary

In this article, we have covered how raw data can be sanitized, validation, barcoded, pushed into a Word Document, and finally scanned back into the application.

While the demonstration only showed 1 account being processed, the application is capable of processing thousands of accounts per day, thereby freeing up man hours and reducing human error by automating the process.

Leave a Comment

You must be logged in to post a comment.