The Moyer Group FileMaker Blog

Fear and Loathing and IRS 1099 Forms

The early part of the year is the season for taxes, and in the case of many organizations, 1099 forms. Printing labels has long been a strength of FileMaker’s, and printing data out onto an IRS Form 1099 is just another variation on label printing.

This is one of those things I seem to revisit every year because the IRS has been changing the format of the 1099 form on a regular basis. There used to be three on a page, and then they made them larger, only allowing for two on a page. They seem to be constantly tweaking the form, and so for clients who need to print 1099s, I am required to tweak the printing process.

It should be a simple thing to cook up a layout for printing 1099s, but it can be surprisingly challenging. The problem is that the form is not symmetrical, top to bottom, and so fields that are in perfect alignment with the form on top are too high on the bottom.

When you encounter such a problem and need to move subsequent iterations (on the same page) down, the first thing to try is making the body part longer, which makes the subsequent iterations start lower on the page. The problem in this case is that the 1099 already fills half of a page pretty tightly, so as soon as you lengthen the body, the body becomes too tall to fit two records on a page. You can also play with the height of the header and footer, but maximizing those two options just doesn’t move the fields down enough.

What to do? FileMaker’s hide object feature can certainly help. You can duplicate your fields and have one set show on the top and have the other set only show on the bottom, and that way you get to have different placement for each set. So what calculations do you use to hide each set?

Since there are only two forms per page, you can assume that odd-numbered records, i.e. 1, 3, 5, etc. should show for the top half of the page, and you want the opposite to happen for the bottom. Record number, unlike record ID, is specific to the found set, and so is ideal in this case. The hide object calculation for the top fields would be:

Mod ( Get ( RecordNumber ) ; 2 ) = 0

Here we’re using the Mod function to determine if a record is even or odd. In other words, if we’re on record 1, for example, the modulus of 1 ÷ 2 is 1. In that case, we DON’T want the fields to be hidden, and since Mod ( 1 ; 2 ) = 1, they wouldn’t be.

For the bottom fields, we could make the calculation even shorter:

Mod ( Get ( RecordNumber ) ; 2 )

For record 2 (which should display on the bottom), the calculation would evaluate to:

Mod ( 2 / 2 ) = 0

A zero value is treated as a Boolean False by FileMaker, and since the result is false, the fields on the bottom of the page would not be hidden. For the first record though, the result would be one, which is a Boolean True, and so the bottom fields would be hidden.

When we implement this, we find it works perfectly. Except in this one case…

I have a client who has different business units, actually separate corporations, each issuing their own 1099s. All of the 1099s are printed together in a single print job. They’re broken into groups by sorting on the corporation ID, and above the body part is a paper-thin subsummary part that’s set to add a page break before each new group. Why is that a problem? If a business unit has an odd number of 1099s, let’s say 5, then the fifth one will fall on the top of the third page. Record 6 begins a new group though, so instead of falling on the bottom of the third page, it lands on the top of the fourth page. Our even-odd logic assumed that odd records would always be at the top, and even records would always be at the bottom, and that’s not the case here.

Now we need to know if a record is even or odd within its group. To that, we can add the below script snippet into our 1099 printing script. This would get inserted after we found our set of records and sorted them by the desired sort order, which in this case is:

  • Corporation Name
  • Corporation ID
  • Recipient Name Last
  • Recipient Name First

The goal is to have the printed output come out of the printer sorted alphabetically by corporation name, then recipient last name, then first name. Once sorted in this way, we need to identify which records will appear at the top of a page, and which at the bottom.

Go to Record/Request/Page [First]
Set Variable [$Counter; Value: 1]
Set Variable [$CorpID; Value: Ten99::_CorporationID]
Loop
   Set Field [Ten99::Page Number; Ceiling ( $Counter / 2)]
   Set Field [Ten99::TopBottom; Case ( Mod ( $Counter ; 2 ) ; "Top" ; "Bottom")]
   Go to Record/Request/Page [Next; Exit after last]
   If [Ten99::_CorporationID ≠ $CorpID]
      Set Variable [$Counter; Value: 1]
      Set Variable [$CorpID; Value: Ten99::_CorporationID]
   Else
      Set Variable [$Counter; Value: $Counter + 1]
   End If
End Loop

What this script does is loop through each group of records within a corporation and set the top or bottom placement based on whether it’s an odd or even record within the group, regardless of whether the record is odd or even in the context of the entire found set. Once this is implemented, the hide object calculations are changed to:

Ten99::TopBottom = "Bottom"

…for records that should appear on the top of the page and

Ten99::TopBottom = "Top"

…for records that should appear on the bottom of the page. It’s a little counter-intuitive, but if you think it through, it makes sense.

What do you think?

Leave a Reply


Latest Stories RSS | Comments RSS