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.
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)
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! :)