Every once in a while you get a curveball that takes you by surprise.
This year, that curveball came in the form of the Dynamics GP 2016 and GP 18.3 YE releases in which GP added in a new 1099 Type – Nonemployee Compensation.

New 1099 Field Value – Nonemployee Compensation

Since this release, several eOne customers have seen “issues” in reference to this new field value in various products.  In this article, I will discuss when & where these “issues” can be seen and how to work around them.

The first cases that we’ve seen were with SmartList Builder in various “modified” SmartList Builder reports.  

In the screenshot below, I’m running a modified “Vendors” SmartList  and removed most of the columns for clarity and also renamed the report to Vendors* 

SLB Modified Vendors report

Sorting by the 1099 Type column, we can see that quite a few are blank.  They shouldn’t be “blank” because if they are not 1099 Vendors, the report should say “Not a 1099 Vendor” which is what the GP 1099 Type droplist shows in the GP UI (and the bottom half of the records in the screenshot are set to).  We also see vendors with Dividend, Interest, Miscellaneous 1099 Types, so those also show correctly.  That leave the blank ones to the new Nonemployee Compensation.

There are a couple solutions to this in SmartList Builder.

1. Delete the SLB version of the SmartList

In my case, I just pressed “Modify” on the report in SmartList and then saved the SLB report.  I didn’t actually change it at all in SLB by adding tables or creating any new calculations.  So since my report is essentially “out of the box” in that regard, I could just delete it and go back to the GP version where this field is populated correctly.  This KB talks about how to determine this and then to delete the report.

2. Add this new value

In SmartList Builder, if your column is set to a “DropList” type value, you can set the Integer->Text values so that SLB will display the values instead of the integer values.  In this case, with the addition of the new 1099 Type = 5, we would just let SLB know this.

To do so, open the report in SLB as below and then find the 1099 Type field and select it.
Press the blue expansion button at the top of the column to open the Set Field Options window.
When it is open, add a new entry at the bottom per the screenshot.

Use Set Field Options window to add new 5: Nonemployee Compensation value to the list for the 1099 Type field


Once we Save this and the SmartList, we can see that the modified SLB SmartList (or one created from scratch using any GP Table or SmartList with 1099 Type) will display as expected as shown in the next screenshot.

Nonemployee Compensation now displays correctly


In SmartView, we find that we have the same issue.  However it will also appear with “out of the box” GP SmartLists.

Here I run the out of the box Payables Transaction SmartList and am using a filter to restrict to only Vendors that are “Not a 1099 Vendor”.

SmartView also has the same issue with the new 1099 Type value

Since SmartView doesn’t allow any kind of report modification – like we can with SmartList Builder – we can’t go into the UI to add this new type.
But we can cheat a little and push it into the back end via SQL and SSMS.

TSQL code to add new 1099 Type value to SV

Insert DYNAMICS..SV90200 (List_ID, LNITMSEQ, List_Value) values (25,5,’Nonemployee Compensation’);

 

and we’re done.

We can see the report here with the newly inserted value.

New 1099 Type value added to SV

Lastly, I promised my Microsoft friends I would add this last one to the blog post.

While most of GP is Dexterity based and so the new Nonemployee Compensation value will show correctly, there is a GP function called DYN_FUNC_1099_Type that is used in quite a few GP SQL Views.  And unfortunately the Microsoft View people didn’t add that new Nonemployee Compensation value either resulting in those views returning blank – just like SLB/SV was doing.  I’ll note that none of the eOne products use those views, but could with SLB if you wanted to, so wouldn’t have been affected by the missing item in this function.

Fix GP Function SQL Code

/* PTR: Add new 1099 Type – Nonemployee Compensation – to the GP function that is used by numerous GP Views.
This new value was introduced in 18.3 and 2016 YE releases*/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
ALTER FUNCTION [dbo].[DYN_FUNC_1099_Type] (@iIntEnum INTEGER)
returns VARCHAR(100)
AS
  BEGIN
  DECLARE @oVarcharValuestring VARCHAR(100)
  SET @oVarcharValuestring = CASE
   WHEN @iIntEnum = 1 THEN ‘Not a 1099 Vendor’
   WHEN @iIntEnum = 2 THEN ‘Dividend’
   WHEN @iIntEnum = 3 THEN ‘Interest’
   WHEN @iIntEnum = 4 THEN ‘Miscellaneous’
   WHEN @iIntEnum = 5 THEN ‘Nonemployee Compensation’
   ELSE ”
END
  RETURN( @oVarcharValuestring )
  END
GO

 

I’ll also note that I didn’t write the above – but did add my initials and a comment on WHY the reason for the script.
You should run the above in each GP company to alter the view.  Since this is an ‘alter view’ vs drop & create, you don’t have to run the Grant.SQL script but that NEVER would hurt doing so.

The missing value will be added to the 1099 Type for GP 18.3 and 2016 in upcoming service packs.  I assume Microsoft will fix their view soon as well.

I recorded a quick video of myself running through the above scenario and resolution.

Regards,

Patrick Roth
eOne