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.

Using Nagios to Monitor FileMaker Server – Part 1 of 2

Nagios is a network monitoring system that comes in both open source and commercially licensed flavors. It’s also mighty handy for keeping tabs on multiple FileMaker servers, so if you have multiple clients and would like to know when one of your client’s (or even your own) FileMaker server goes down, or one of the databases on a server goes down, or whether or not a scheduled backup or script ran properly, then Nagios can be enormously useful.  You can read up on it here:

http://www.nagios.org/

If you’d like to check it out, you can follow these instructions to set up Nagios and configure it to track a FileMaker server. It can also monitor many, many other things, but the focus here will be monitoring FileMaker Servers. Since the instructions are a bit lengthy, this will be broken up into Parts 1 & 2. Part 1, this part, will take you through getting Nagios installed. Part 2 will go through configuring Nagios to monitor your FileMaker server(s).

Nagios can be complicated to set up, and since this is just meant to be an introduction rather than a comprehensive overview, we’re going to take the simplest route to getting it installed. We’re going to use something called MacPorts, which is, according to the web site, “an open-source community initiative to design an easy-to-use system for compiling, installing, and upgrading either command-line, X11 or Aqua based open-source software on the OS X operating system.”  It kind of does what the App Store does, and it’s been doing it since before the App Store was around. While the App Store is the place to go for OS X applications, MacPorts is the place to go for UNIX software that runs on your Mac, because, hey, it’s UNIX under the hood.

Here we go:

Step 1: Go to the App Store and install XCode from Apple. It’s free. It’s also a prerequisite for step 3, so we need to do this first.

Step 2: Install XCode Command Line Tools. In Terminal, type:

xcode-select --install

Step 3: Install MacPorts. The easiest way to do this is to go here:

https://www.macports.org/install.php

..and click on the installer for your version of Mac OS X. This post will assume Yosemite (10.10), but these instructions should work for earlier versions.

Step 4: Once MacPorts is installed, you can use it to install Nagios. If you go here:

https://www.macports.org/ports.php

…you can first marvel in wonder at the sheer variety of stuff that’s available – almost 23,000 ports, including Atari and Apple Lisa emulators and games of Pong – and then you can get down to business and search for nagios. Your search should result in this:

FirefoxScreenSnapz143

So now that we know the specific name of the port is nagios (not unexpected, but you never know), we can launch Terminal on our Mac and enter this into the command line:

sudo port install nagios

Once you do that, lots of text will start appearing on your screen as nagios, and whatever dependencies (other applications it needs to work) it has get located, downloaded, and installed. This is WAY easier than installing nagios from source code. It’s also mildly entertaining if you’ve never installed software like this before. When it finishes – assuming it doesn’t run into any errors – it will wrap up with some configuration instructions, which are reproduced at the end of the article, along with some commentary.

Step 5: Verify the web server is running
The web server that comes with Yosemite is Apache. You can verify that it’s running by opening a web browser and putting localhost in for the URL. You should see It works! in the browser.

Google ChromeScreenSnapz018

Step 6: Modify the web server configuration

To get the nagios web interface up and running, we’re going to need to modify Apache’s configuration file. On Yosemite, that file can found at the following location:

/etc/apache2/httpd.conf

If you look around your hard drive, you likely won’t see a folder called etc. That’s because it’s hidden. While in the Finder you can access hidden folders from the Go menu. Choose Go > Go to Folder… and type in:

FinderScreenSnapz273

…to get to the etc folder. You can also type in /etc/apache2 to go straight to the apache2 folder. The httpd.conf file can be edited with any text editor.

It’s a good idea to back up this file before editing, just in case you accidentally render the config file inoperable.  We’re going to need to do several things using the command line in Terminal, so let’s start now and back up the httpd.conf file. After opening Terminal, type (or copy) in the following command and enter:

sudo cp -p /etc/apache2/httpd.conf /etc/apache2/httpd.conf.bak

The -p option preserves the ownership settings. Once the file has been backed up, you can open the file (httpd.conf) with the text editor of your choice.

Find the following lines, and make sure they are un-commented by removing the # symbol at the beginning of the line:

LoadModule cgi_module libexec/apache2/mod_cgi.so
LoadModule alias_module libexec/apache2/mod_alias.so
LoadModule php5_module libexec/apache2/libphp5.so

As you might guess, these modules enable cgi, allias, and php functionality. You need all three of these modules to be running in order for Nagios to work.

As per step 1 of the Nagios doc (at the end of the article), insert the “Nagios stuff” text block somewhere *above* your Apache cgi-bin ScriptAlias statement:

ScriptAlias /cgi-bin/ "/Library/WebServer/CGI-Executables/"

#
# Nagios stuff

ScriptAlias /nagios/cgi-bin/ "/opt/local/sbin/nagios/"
<Directory "/opt/local/sbin/nagios">
AllowOverride None
Options ExecCGI
Order allow,deny
Allow from all
AuthName "Nagios Access"
AuthType Basic
AuthUserFile /opt/local/etc/nagios/htpasswd.users
require valid-user
</Directory>

Alias /nagios "/opt/local/share/nagios"
<Directory "/opt/local/share/nagios">
Options None
AllowOverride AuthConfig
Order allow,deny
Allow from all
</Directory>

# End Nagios stuff
#

The nagios alias immediately above didn’t work for me. I kept getting permissions errors. Once I added or change the lines in italics shown below, it worked:

Alias /nagios "/opt/local/share/nagios"
<Directory "/opt/local/share/nagios">
    Options None
    DirectoryIndex index.php
    AuthName "Nagios Access"
    AuthType Basic
    AuthUserFile /opt/local/etc/nagios/htpasswd.users
    AllowOverride All
    Require all granted
    Order allow,deny
    Allow from all
</Directory>

The second alias block creates the aliased directory /nagios, so if you’re doing this on your local system, localhost/nagios is now a valid URL, even though there isn’t a nagios directory in your web documents folder. The actual location of the web interface files is /opt/local/sbin/nagios/

The first block creates an alias to the nagios CGIs. These CGIs allow you to monitor various hosts and network services. More about those in a bit.

Higher in the document, these settings lock down access to your systems file system:

# Deny access to the entirety of your server's filesystem. You must
# explicitly permit access to web content directories in other
# <Directory> blocks below.
#
<Directory />
AllowOverride none
Require all denied
</Directory>

Note the admonition that if you want access to anything, you need to explicitly turn it on yourself. Failure to do so will result in a 403 access denied error in the web browser.

Given that initial default setting, access needs to be explicitly turned on for aliases, which is why we added the AllowOverride and Require all directives to the nagios alias.

Once you’ve completed the changes in httpd.conf, save the file.

Step 7: Set up the configuration files for Nagios.

The next step is to set up the configuration files for Nagios. According to step 2 in the Nagios instructions, you do this in Terminal:

cd /opt/local/etc/nagios
sudo mkdir sample
sudo cp *.cfg-sample sample/
sudo -s

for i in *cfg-sample; do mv $i `echo $i | sed -e s/cfg-sample/cfg/`; done;
exit

The end result should be that a copy of the files that ended with .cfg-sample should have been copied into a new folder called sample, and the files themselves were renamed so that they now end with .cfg. You should now have:

cgi.cfg
nagios.cfg
resource.cfg

If you have trouble, you can rename them by hand.

In that same folder is a folder called objects. Inside you’ll find more configuration files.

Locate the commands.cfg file and open it in a text editor. (You can do this in Finder by using Go-Go to Folder for /opt/local/etc/nagios).  Here you’ll find several commands that can be used to check on hosts, i.e. computers, on the network. Command directives are in this format:

# 'check-host-alive' command definition
define command{
        command_name    check-host-alive
        command_line    $USER1$/check_ping -H $HOSTADDRESS$ -w 3000.0,80% -c 5000.0,100% -p 5
        }

The first line is a descriptive comment, followed by the command definition. In this example, using the command check-host-alive will cause a ping to be sent to the host being monitored. The host address is passed in as a variable.

For now, we’ll leave the commands file alone, so you can close the file.

Step 8: Start Nagios

The next step is to attempt to start nagios in Terminal with

sudo nagios -v /opt/local/etc/nagios/nagios.cfg

If you get any errors, you can try commenting out the offending line in nagios.cfg and re-attempting the above command until it runs successfully without errors.

Step 9: Set up the Nagios administrator account

Next we need to set up the nagiosadmin user account, along with any other accounts you want, by using Terminal:

sudo htpasswd -c /opt/local/etc/nagios/htpasswd.users nagiosadmin

As the instructions say, you can create other users as desired, omitting ‘-c’ from the command above since it creates the password file.

Next, open the file /opt/local/etc/nagios/cgi.cfg and make sure that all the un-commented directives that begin with ‘authorized_for_ are set to the nagiosadmin account that we just set up, like so:

   authorized_for_system_information= nagiosadmin
   authorized_for_configuration_information= nagiosadmin
   authorized_for_system_commands= nagiosadmin
   authorized_for_all_services= nagiosadmin
   authorized_for_all_hosts= nagiosadmin
   authorized_for_all_service_commands= nagiosadmin
   authorized_for_all_host_commands= nagiosadmin

Step 10: Set up Nagios start on system reboot

If you want Nagios to start when the system reboots, you need to use OS X’s launchctl to do so:
sudo launchctl load -w /Library/LaunchDaemons/org.macports.nagios.plist

To manually start Nagios from Terminal, use this command:
sudo /opt/local/var/nagios/nagios.init start

Step 10: Verify that Nagios started

If Nagios started without errors, you should be able to launch a web browser and go to localhost/nagios:

Google ChromeScreenSnapz019

If you click on the Hosts link on the left, you should see your localhost listed, and it should show with a status of Up.

That’s it for Part 1. In Part 2, we’ll look at how to configure Nagios to monitor one or more FileMaker Servers.

***Nagios doc**

************************************************************
The basic steps to complete the installation are below.  But Nagios is a complex program so you should read the documentation online
at http://www.nagios.org or locally at /opt/local/share/nagios/docs/index.html to fully understand Nagios.
************************************************************

COMMENT: It IS a good idea to read the documentation, but you should be prepared to find that the documentation will assume installation paths that don’t match yours. I’ve included a ‘Rosetta Stone’ at the end of this post that tells you how paths listed in the documentation correspond to paths on your computer.

IMPORTANT: A user and group called 'nagios' has been created and you must run nagios as that user.

1) Setup Apache's httpd.conf file for Nagios (restart Apache when done)

   Insert the "Nagios stuff" text block somewhere *above* your Apache cgi-bin ScriptAlias statement:

    ScriptAlias /cgi-bin/ "/Library/WebServer/CGI-Executables/"

#
# Nagios stuff

ScriptAlias /nagios/cgi-bin/ "/opt/local/sbin/nagios/"
<Directory "/opt/local/sbin/nagios">
 AllowOverride None
    Options ExecCGI
    Order allow,deny
    Allow from all
    AuthName "Nagios Access"
    AuthType Basic
    AuthUserFile /opt/local/etc/nagios/htpasswd.users
    require valid-user
</Directory>

Alias /nagios "/opt/local/share/nagios"
<Directory "/opt/local/share/nagios">
    Options None
    AllowOverride AuthConfig
    Order allow,deny
    Allow from all
</Directory>

# End Nagios stuff
#

COMMENT: If you’re not familiar with your Apache configuration file, it can be found here:

/etc/apache2/httpd.conf

I had trouble with the /nagios alias, and ended up adjusting mine as I’ll show later on. FYI for now.

2) Configure the Nagios sample files in /opt/local/etc/nagios.

a) First, backup the Nagios sample files.

   cd /opt/local/etc/nagios
   sudo mkdir sample
   sudo cp *.cfg-sample sample/

b) Then rename the sample files for use.

   sudo -s
   for i in *cfg-sample; do mv $i `echo $i | sed -e s/cfg-sample/cfg/`; done;
   exit

c) Configure the default commands.cfg file to your requirements. Then test the Nagios configuration with this command until you get
no errors:

   sudo nagios -v /opt/local/etc/nagios/nagios.cfg

3) Set user 'nagiosadmin' as a "superuser" to browse all hosts/services in
   /opt/local/etc/nagios/cgi.cfg

   authorized_for_system_information= nagiosadmin
   authorized_for_configuration_information= nagiosadmin
   authorized_for_system_commands= nagiosadmin
   authorized_for_all_services= nagiosadmin
   authorized_for_all_hosts= nagiosadmin
   authorized_for_all_service_commands= nagiosadmin
   authorized_for_all_host_commands= nagiosadmin

4) Create a 'nagiosadmin' Apache user/password to correspond with the Nagios Apache user created above

    htpasswd -c /opt/local/etc/nagios/htpasswd.users nagiosadmin

   Create other users as desired, omit '-c' since it creates the password file.  Make additional users login names match Nagios
contact names to restrict web viewing to your host's by contact.

5) Set Nagios to start at system boot, and start Nagios

   Run launchctl so Nagios will start at system boot
    sudo launchctl load -w /Library/LaunchDaemons/org.macports.nagios.plist

   You may now start Nagios manually with this command (or a reboot):
    sudo /opt/local/var/nagios/nagios.init start

6) You may now login as 'nagiosadmin' to the Nagios web interface at http://localhost/nagios

NOTE: The SMTP server Postfix is required to enable Nagios email notifications

File Locations:

    Configuration files: /opt/local/etc/nagios
    Nagios plugins: /opt/local/libexec/nagios
    Lock file:  /opt/local/var/nagios/nagios.lock
    Init file:  /opt/local/var/nagios/nagios.init
    HTML URL:  http://localhost/nagios/
    CGI URL:  http://localhost/nagios/cgi-bin/

 


Latest Stories RSS | Comments RSS