PDF Forms: Recreating the Official Soldier APFT and Weight Tracker

Task

The client needed a way to automate the repetitive process of populating official Army forms with data collected during each soldier’s Army Physical Fitness Test (APFT) which is administered (at least) annually and the results of their weigh-in which is administered (at least) semi-annually. Here are the client’s exact words:

The data I use to fill in multiple Department of the Army forms comes from a clipboard.
None of the information that I need to complete these DA forms is currently in a file.
I would like to have an MS Excel file, with a form I could fill out (such as a dialog box) where the answers would populate every DA Form I must complete.
Could all the DA forms I mentioned be converted to Excel format and each be put on an individual tab within a master Excel worksheet?

Challenges

  • Exactly replicate the official US Army PDF forms
  • Create a data entry form to accurately transcribe collected data
  • Automatically populate the data to a new PDF form

This complex project was tackled in several phases. Although the official PDF forms were publicly available online, many versions were out-of-date. So, the first task was to acquire the most recent versions of each of the six PDF forms that I would be replicating. It’s worth noting that a cottage industry had sprung up around these forms. Suppliers of Excel templates included massive lists of body measurements, heights and weights for male and female soldiers. These lists were very helpful, later in the project.

Replication

I made extensive use of Excel’s camera tool, along with a screen-copying utility called Greenshot. However, in order to achieve an exact replica of each form, I had to resort to a torturous, eye-strain inducing protocol of worksheet manipulations:

  • Merge cells (a no-no, under normal circumstances)
  • Provide “landing zones” for form elements by resizing 13 cells (A-M) with width 8 or cells (A-X) with width 4
  • Resize the worksheet to 97% or smaller
  • Map the landing zones to the data imported from the VB.NET data entry form
  • Manually tweak images (for example, the form signature block)
  • Overlay the camera tool’s image (this, ultimately, allowed for the SaveAs PDF to do its magic)
from the User Guide that I wrote for client

Data Entry

Excel doesn’t have the chops for allowing developers to easily design complex data entry forms. I’ve seen impressive Excel-based forms, supported with insane amounts of VBA code. So, it can be done. I just think that VB.NET is a better design tool. Using Visual Studio, I took full advantage of layouts and, yes, insane amounts of support code, to create a form that simulated the look and feel of the paper form. The Visual Basic code took care of many data entry chores:

  • Data validation
  • Auto-copying identical fields, such as a soldier’s name and gender
  • auto-completion and formatting of time data
from the User Guide

Once the layout was completed, I set up some buttons for managing the data entry form, including the all-important Save Forms and Return to Excel. This button created a CSV file that would be imported into Excel via Power Query.

Data entry form created with VB.NET
Example data, with initial calculations in green fields

Automation

Back in Excel, I used Power Query to import the CSV file. In addition to the data collected, each entry had a field ID, which I used to map the data to physical spreadsheet cells on each of the worksheets representing a replica of one of the PDF forms. Numerous calculations, based on the body measurements, heights and weights, were placed on the form. All of this happened with a single click of a button on the main Excel worksheet.

Main Excel worksheet for the PDF forms

Of course, a single click of the button would not have been possible without a lot of preparation. Remember those online Excel templates? Not only were they often out-of-date, the supporting measurement charts–used to determine if a soldier was within the Army standards for his or her height and weight–often were wrong, misaligned or blank. I had to manually rebuild every measurement table and get the client to sign off of the accuracy.

The measurement charts were used to complete the body fat calculation.

Mapping data and calculations to PDF form overlay

The final phase of the project was simply to perform a SaveAs PDF on each worksheet.

Official DA Form 5500

Click on a project link for more details about that specific project.