Generating fixed width documents in Ruby: Introducing Fixy

A few weeks ago, we posted an article explaining how ACH works from a developer perspective. A question that came up a lot through the comments is how we generate the ACH debit files that are transmitted to the bank.

This is a particularly interesting question, because it really is asking how we interface with systems that do not provide a modern restful API. Most of these older systems that have been around for 10-20 years, use fixed-width documents for communicating between platforms. To name a few examples:

  • ACH debit file: Upload a fixed-width ACH document into the bank's FTP server to initiate payments. Download an FTP server to retrieve transaction errors. (specifications)

  • W2 E-filing: Upload a fixed-width document detailing employee's tax details through the goverment's web portal. A few days later, a confirmation will be released to let us know whether the file is valid or not. (specifications)

  • 1099 E-filing Upload a fixed-width document detailing contractor's tax details through a separate goverment portal. A few days later, a confirmation will be released to let us know whether the file is valid or not. (specifications)

At Gusto, we strive to achieve simple, paperless payroll for our customers. To reach that goal, automation is key using whatever means as available (at times, we have to get pretty creative). That's why we created Fixy, a gem to make it easy to work with fixed-width documents.

Fixed-width documents overview

Fixed-width documents are composed of multiple fixed length records. Every line in the document is a record. Below is the document structure for the a 1099 e-filing document.

document structure for 1099 e-filing

In turn, every record follows a very defined structure, which describes how every character within that line should be generated. Below is a quick sample of what most documentation looks like.

A first approach to generating documents

When working with a document of this kind for the first time, it's tempting for the sake of simplicity to generate a document like this:

def record_t(year, transmitter)  
    record  = ''
    record << '1'
    record << year.to_s
    record << ' '
    record << sanitize_and_pad(transmitter.tin, 9)
    record << sanitize_and_pad(transmitter.control_code, 5)
    # ... and so on ...
    record
end  

While this approach works, it's not as scalable as it is very error prone due to off-by-1 errors in offset. It's also hard to maintain because record declaration, definition, formatting, are tightly coupled.

Introducing Fixy

We created Fixy because we wanted to express a document in a re-usable, more manageable way, that is also debug-friendly. Let's walk through defining the record T above.

The first thing to do is to define the length of the record, and required formatters. Most of the time, all records within a document have the same length and formatters. For that reason, let's start with creating a base class for all our records:

class BaseRecord < Fixy::Record  
  include Fixy::Formatter::Amount
  include Fixy::Formatter::Numeric
  include Fixy::Formatter::Alphanumeric

  set_record_length 750
end  

Next is field declaration. This is very much a dump of the documentation table. Occasionally, this section can be auto-generated directly from the documentation.

class RecordT < BaseRecord

  # ----------------------------------------------------------------------------
  #         Field Name                Size      Range         Type
  # ----------------------------------------------------------------------------

  field :record_type               , 1 ,  '1'     ,  :alphanumeric
  field :payment_year              , 4 ,  '2-5'   ,  :alphanumeric
  field :prior_year_data_indicator , 1 ,  '6'     ,  :alphanumeric
  field :transmitters_tin          , 9 ,  '7-15'  ,  :alphanumeric
  field :transmitter_control_code  , 5 ,  '16-20' ,  :alphanumeric
  # ... more fields ...

Because every record depends on specific information, we'll want to update the construction as we see fit. In our case, all we need to build this record is the current filing year:

  attr_accessor :year
  def initialize(year)
      @year = year
  end

Finally, we need to define which data every field should contain. We don't need to worry about how it gets formatted, because we already define the formatter, and allocated space in the sections above. For convenience, we can use a lambda, or a method for calculating the field value:

  field_value :record_type                                 , 'T'
  field_value :payment_year                                , -> { year }
  field_value :prior_year_data_indicator                   , ''
  field_value :transmitters_tin                            , AGENT_EIN
  def transmitter_control_code
    AGENT_1099_TRANSMITTER_CODE
  end
end # closing bracket for class  

Our record is now fully defined. A document, which is composed by a set of records, is described through a Fixy::Document object. Continuing with our example, our 1099 E-filer document would look as follows:

class Efiler1099 < Fixy::Document

  attr_accessor :year

  def initialize(year)
      @year = year
  end

  private

  #
  # Document format:
  #
  #   T Record: Identifies the Transmitter of electronic file.
  #   A Record: Identifies the Payer, and the type of payments
  #   B Record: Idenfifies the Payee, and the payment amounts
  #   C Record: Summary of B Records
  #   K Record: Summary of State Totals
  #   F Record: End of Transmission
  #
  # Example of document:
  #
  #   T - A - B - B - B - C - K - A - B - B - B - C - K - K - F
  #

  def build
    append_record RecordT.new(year)
    Company.with_contractors.each do |company|
      append_record RecordA.new(year, company)
      company.contractors.each do |contractor|
          append_record RecordB.new(year, contractor)
      end
      append_record RecordC.new(year, company)
      append_record RecordK.new(year, company)        
    end
    append_record RecordF.new
  end
end  

That's it! with our document (Fixy::Document) and records (Fixy::Record) fully defined, we can now seamlessly generate the fixed-width document using the following command:

Efiler1099.new(2013).generate_to_file('1099.txt')  

Alternatively, an interactive HTML debug version of the document can also be created:

Efiler1099.new(2013).generate_to_file('1099.txt', true)  

Debug View

Using Fixy in your own projects

We packaged Fixy into a gem, and released the source code on GitHub. The ability to create, maintain, and debug fixed width documents has been incredibly useful to Gusto, and we hope it will be useful for you too. Enjoy! :)