1099 Reporting, Or Not?

Recent versions of Dynamics GP have included terrific enhancements to 1099 tracking.  The ability to track 1099 amounts per type and box at a transaction level allows users to track in greater detail without having to assume that all of a vendor's payments go to the same 1099 type and box number.

Along with these enhancements, though, there are not a lot of great reporting capabilities to review this information by type and box.  Of course, you can inquire on the information easily (Inquiry--Purchasing--Vendor 1099 Details, Cards--Purchasing--1099 Details).

Cards--Purchasing--1099 Details

1099 Details window

The issue with these windows is that you have to review the 1099 information vendor by vendor.  This is normally fine if you have a small amount of 1099 vendors.  But for those users who have 100, 200, or even 500 1099 vendors, a report or export is much easier to use.

So, we look to SmartList, where there are 1099 fields available...

Microsoft Dynamics GP--SmartList--Purchasing--Vendors

With 1099 fields added

However, if we look at the 1099 fields available directly in SmartList, we will see that they are the "Amounts Since Last Close" summary fields. Which means that they are not date sensitive (they would be based on the last time you closed the calendar year for Purchasing), and they also do not include amounts by 1099 type and 1099 box although you can display the vendor's default 1099 type.  So, this may work okay if a vendor uses only one type and one box, and you are confident that you closed the Purchasing module on time for the prior calendar year and did not post back to the prior year or forward to the new year.

So, what about reporting?  Yes, you can print a report from Cards--Purchasing--1099 Details, but is only available for one vendor at a time.  In the past, I would tell users to print the 1099 edit list from Routines--Purchasing--Print 1099.

Routines--Purchasing--Print 1099

1099 Edit List

Although this report is based on the actual transaction dates (therefore it is date sensitive), it does not break out the 1099 amounts by 1099 type and box.  So, although it may work better than the SmartList, it still would work best for those vendors that use the same 1099 type and box.

So what to do if you have a variety of vendors with a variety of 1099 types and boxes?  Aside from actually printing the 1099 themselves to review?  Well, if you have SmartList Builder, it is not all that hard to create a summary SmartList to display 1099 information.  There are two primary tables involved:



The Purchasing 1099 Period Detail table includes the key fields necessary for reporting the 1099 amounts by box and type:

It is important to note that the 1099 Box Number noted in this table is NOT the actual 1099 box number.  Rather it is an incremental number assigned to the boxes in order.  So, for example the second box on a 1099-DIV is actually labeled as box 1b on the 1099 form but it would be noted as 1099 Box Number 2 in this table.   I also added the PM Vendor Master file to the SmartList, in order to pull the vendor's Tax ID Number and address.

Now, at this point, you could use this SmartList as is but you would get multiple lines for each vendor since it would list out the amounts per box per period.  If you would prefer to condense the SmartList to one line per vendor, per type, and per box you can make it a summary SmartList.  To do this, you can click the Options button in the upper right corner of the SmartList Builder window.

SmartList Builder Options button

Options Window

Select table to display summary options

Mark the Summary SmartList checkbox in the Options window to convert the SmartList to a summarized a version.  Then for each field available in the SmartList, you must select the Summary Type.  To change the Summary Type, double-click on the field.  In the first screenshot above, we are selecting the options for the fields in the Purchasing 1099 Period Details SmartList.  Note that we select the option of Group By on those fields we want to summarize by... in this case, by 1099 Box Number, 1099 Type, Vendor ID, and Year.  The 1099 Amount field is then set to be a Sum. 

For the PM Vendor Master File, we have set many of the fields to Maximum.  Keep in mind that since we are grouping by Vendor ID, the fields should be the same for each vendor.  So when we say to pull the Maximum, we are simply saying to pull that common value for the vendor.  In this case, we also set to group by the Vendor Check Name.  This is redundant, though, since we have already grouped by vendor ID.

You may also want to add a restriction to display only those summaries with a 1099 amount greater than zero. 

SmartList Builder Restrictions button

Restrictions window

Click the Restrictions button at the top of the SmartList Builder window to add a restriction. 

The final SmartList will appear something like this:

Summary SmartList Grouped by 1099 Box Number, 1099 Type, Year, and Vendor ID

The final SmartList will display the summarized information, including the 1099 Amount by type and box number (keeping in mind that is the sequential box number).  For extra credit, you can even create a series of calculated fields to break the amount out in the correct box labels.  If you do this using a CASE statement to test for the sequential box number, a couple things to keep in mind...

1.  Because box labels change for 1099 type, I would suggest creating a separate SmartList for each 1099 type and adding a restriction to only display one 1099 type per SmartList.

2.  You will want to remove the group by on the 1099 Box Number and not display it in the SmartList.  As you will be summarizing only by the 1099 Type, Year, and Vendor ID.  The calculated fields will then display (with a separate column for each box) the amounts broken out by box.

Here is a sample of a CASE statement used to pull an amount for a specific box.  You will need a separate calculated field for each box on the 1099.

CASE WHEN {Purchasing 1099 Period Detail:1099 Box Number}= '1' THEN {Purchasing 1099 Period Detail:1099 Amount} ELSE 0.00 END

Good luck and happy new year!!

Christina Phillips is a Microsoft Certified Trainer and Dynamics GP Certified Professional. She is a supervising consultant with BKD Technologies, providing training, support, and project management services to new and existing Microsoft Dynamics customers. This blog represents her views only, not those of her employer.