Microsoft Dynamics GP: 1099 Changes to expect -Get ready for Year-end!!

Hello GP Community,

What?!...Isn't it too early to talk about 1099's?   Absolutely not.  It's never too early to get ready for year-end, so let's talk about what to expect for year-end closing so you are well prepared for what changes to expect. 

EXPECT MORE 1099 FORMS TO 'PRINT WITH LINES' 

In October of 2022, we introduced having the 1099-NEC form print with lines/boxes on blank paper, so you didn't need to purchase pre-printed forms, and it was a huge hit!  We heard you loud and clear, so we are fast at work to also have the 1099-MISC form print with lines/boxes on blank paper too, with the 1099-INT and 1099-DIV forms soon to follow.  The advantages of this are that you no longer need to buy preprinted forms and you don't have to deal with alignment issues. How great will that be!! 

1099 ELECTRONIC SUBMISSION IS REQUIRED 

Although we would love to consider adding this as a new feature for Microsoft Dynamics GP, there are already numerous trustworthy and inexpensive options out there that many GP users are already using, so we will not be adding this as a new feature in Microsoft Dynamics GP at this time.  Instead, we wanted to give you plenty of options to help you get started on researching and finding an electronic option for next year that will work for you.  Many options are provided below for you to research on your own: 

• Note that the IRS did provide a FREE 1099 e-filing option, so you can use this link to find out more details about it (size limitations, etc.) and see if it will work for you.  

E-file Forms 1099 With IRIS | Internal Revenue Service (irs.gov)

REMINDER: This magical number of 10 forms from the IRS is including all forms you print.  1099, W2's and 1095 ACA forms.  If you print more than a total of 10 of all of these forms, you need to file electronically.  Dynamics GP will file the W2 for you electronically.

• We also took a survey from several Microsoft Partners to inquire on what solutions they are using to provide electronic 1099 capability for their Dynamics GP customers.  There is quite a list below that is popular among Dynamics GP users, and there may be more...   Feel free to check these out on your own to find one that is a good fit for you.  Hopefully the links below will provide you with more details about these products to get you started along with contact information so you can reach out to them for additional information on functionality, requirements, cost, etc. or even to request a demo. (NOTE: This list is in no particular order, is provided as suggestions only for you to investigate on your own and are not included under Microsoft Dynamics GP Support. You much reach out to each product for Support.  Microsoft Corp does not assume any liability.)  

1099 Pro:  https://www.1099pro.com/prod1099pro.asp

1099 Etc: https://1099-etc.com/

Greenshades:  https://go.greenshades.com/platform/tax-and-compliance/year-end-forms

Tax 1099:  https://www.tax1099.com/integrations/1099-excel-template

eSmart Payroll:  https://www.esmartpayroll.com/excel_form_1099misc.asp

W2 Mate: https://www.realtaxtools.com/1099software/1099software.html

Aatrix - www.aatrix.com

and more.... 

1099 'SQL VIEW' FOR SMARTLIST

You have been asking for a 'SQL View' for 1099 forms so users can view 1099 information in a SmartList....  Ask and you shall receive!    I have provided a script below for a "SQL VIEW" of the 1099 table (PM00204), that will sum 1099 amounts and can be linked to SmartList Designer, so users can verify 1099 amounts from a Smartlist. This information can be used for verification purposes, as well as a base to make an Excel file if you use any electronic filing options above and need to generate the 1099 data to a file.   Note that the IRS makes changes to the 1099 forms each year, so the below script is written for the '2023' forms.   

• KB 4014658 --How to create a SmartList designer report using a SQL View:  
https://learn.microsoft.com/en-US/troubleshoot/dynamics/gp/how-to-create-smartlist-designer-report-using-sql-view

----------------------------------------------------------------------------------------------------------------------------------
/****************************************
CURRENT VERSION OF SCRIPT: For the '2024' tax year
PURPOSE OF SCRIPT: To view 1099 amounts for each vendor and 1099 type/box number from the 1099 table (PM00204) for the year stated above.
NOTE:- $0.00 lines/boxes will not be returned.
- The box numbers in this script are written to match the IRS forms for the year noted (therefore the script results are restricted to the year noted)
- The totals returned by this script should match the 1099 Edit list, and also the totals seen by year in the '1099 Details' window
under Cards/Purchasing/1099 Details.
-You must install the 'Year-End update' each year for Dynamics GP (available in mid-Nov), to get the updated 1099 form changes in Dynamics GP.  

Instructions:
1. Open a SQL Query window and copy in the 'create view' script below and execute against the Company database to create the SQL view.
2. To view the results of this script, execute this SQL statement:
    select * from View_1099_Totals

******************************************/

Create view [dbo].[View_1099_Totals]
as
SELECT 'PER 2024 FORM' AS 'VERSION OF SCRIPT',  --this line will be updated when the script is updated each year
A.VENDORID as 'Vendor_ID',
A.YEAR1 as 'Year',
--define 1099 type
case A.TEN99TYPE
when 1 then 'Not a 1099 Vendor'
when 2 then '1099-DIV'
when 3 then '1099-INT'
when 4 then '1099-MISC'
when 5 then '1099-NEC'
end '1099_Type',
case
when A.TEN99TYPE = 1 then 'Not a 1099 Vendor'
--1099-DIV form 
when A.TEN99TYPE = 2 and A.TEN99BOXNUMBER = 1 then '1a Total ordinary dividends'
when A.TEN99TYPE = 2 and A.TEN99BOXNUMBER = 2 then '1b Qualified dividends'
when A.TEN99TYPE = 2 and A.TEN99BOXNUMBER = 3 then '2a Total capital gain dist.'
when A.TEN99TYPE = 2 and A.TEN99BOXNUMBER = 4 then '2b Unrecap. 1250 gain'
when A.TEN99TYPE = 2 and A.TEN99BOXNUMBER = 5 then '2c Section 1202 gain'
when A.TEN99TYPE = 2 and A.TEN99BOXNUMBER = 6 then '2d Collectibles (28%) gain'
when A.TEN99TYPE = 2 and A.TEN99BOXNUMBER = 7 then '3 Nondividend distributions'
when A.TEN99TYPE = 2 and A.TEN99BOXNUMBER = 8 then '4 Federal income tax withheld'
when A.TEN99TYPE = 2 and A.TEN99BOXNUMBER = 9 then '5 Section 199A dividends'
when A.TEN99TYPE = 2 and A.TEN99BOXNUMBER = 10 then '6 Investment expenses'
when A.TEN99TYPE = 2 and A.TEN99BOXNUMBER = 11 then '7 Foreign tax paid'
when A.TEN99TYPE = 2 and A.TEN99BOXNUMBER = 12 then '9 Cash liquidation distributions'
when A.TEN99TYPE = 2 and A.TEN99BOXNUMBER = 13 then '10 Noncash liquidation distributions'
when A.TEN99TYPE = 2 and A.TEN99BOXNUMBER = 14 then '12 Exempt-interest dividends'
when A.TEN99TYPE = 2 and A.TEN99BOXNUMBER = 15 then '13 Specified private activity bond interest dividends'
when A.TEN99TYPE = 2 and A.TEN99BOXNUMBER = 16 then '16 State tax withheld'
when A.TEN99TYPE = 2 and A.TEN99BOXNUMBER = 17 then '2e Section 897 ordinary dividends'
when A.TEN99TYPE = 2 and A.TEN99BOXNUMBER = 18 then '2f Section 897 capital gain'
--1099-INT form
when A.TEN99TYPE = 3 and A.TEN99BOXNUMBER = 1 then '1 Interest income'
when A.TEN99TYPE = 3 and A.TEN99BOXNUMBER = 2 then '2 Early withdrawal penalty'
when A.TEN99TYPE = 3 and A.TEN99BOXNUMBER = 3 then '3 Interest on U.S. Savings Bonds and Treasury obligations'
when A.TEN99TYPE = 3 and A.TEN99BOXNUMBER = 4 then '4 Federal income tax withheld'
when A.TEN99TYPE = 3 and A.TEN99BOXNUMBER = 5 then '5 Investment expenses'
when A.TEN99TYPE = 3 and A.TEN99BOXNUMBER = 6 then '6 Foreign tax paid'
when A.TEN99TYPE = 3 and A.TEN99BOXNUMBER = 7 then '8 Tax-exemptinterest'
when A.TEN99TYPE = 3 and A.TEN99BOXNUMBER = 8 then '9 Specified private activity bond interest'
when A.TEN99TYPE = 3 and A.TEN99BOXNUMBER = 9 then '10 Market discount'
when A.TEN99TYPE = 3 and A.TEN99BOXNUMBER = 10 then '11 Bond premium'
when A.TEN99TYPE = 3 and A.TEN99BOXNUMBER = 11 then '12 Bond premium on Treasury obligations'
when A.TEN99TYPE = 3 and A.TEN99BOXNUMBER = 12 then '13 Bond premium on tax-exempt bond'
when A.TEN99TYPE = 3 and A.TEN99BOXNUMBER = 13 then '17 State tax withheld'
--1099-MISC form 
when A.TEN99TYPE = 4 and A.TEN99BOXNUMBER = 1 then '1 Rents'
When A.TEN99TYPE = 4 and A.TEN99BOXNUMBER = 2 then '2 Royalties'
when A.TEN99TYPE = 4 and A.TEN99BOXNUMBER = 3 then '3 Other iIncome'
when A.TEN99TYPE = 4 and A.TEN99BOXNUMBER = 4 then '4 Federal income tax withheld'
when A.TEN99TYPE = 4 and A.TEN99BOXNUMBER = 5 then '5 Fishing boat proceeds'
when A.TEN99TYPE = 4 and A.TEN99BOXNUMBER = 6 then '6 Medical and health care payments'
when A.TEN99TYPE = 4 and A.TEN99BOXNUMBER = 7 then '8 Substitute payments in lieu of dividends or interest'
when A.TEN99TYPE = 4 and A.TEN99BOXNUMBER = 8 then '9 Crop insurance proceeds'
when A.TEN99TYPE = 4 and A.TEN99BOXNUMBER = 9 then '10 Gross proceeds paid to an attorney'
when A.TEN99TYPE = 4 and A.TEN99BOXNUMBER = 10 then '12 Section 409A deferrals'
when A.TEN99TYPE = 4 and A.TEN99BOXNUMBER = 11 then '14 Excess golden parachute payments'
when A.TEN99TYPE = 4 and A.TEN99BOXNUMBER = 12 then '15 Nonqualified deferred compensation'
when A.TEN99TYPE = 4 and A.TEN99BOXNUMBER = 13 then '16 State tax withheld'
when A.TEN99TYPE = 4 and A.TEN99BOXNUMBER = 14 then '18 State income'
when A.TEN99TYPE = 4 and A.TEN99BOXNUMBER = 15 then '11 Fish purchased for resale'
--1099-NEC form 
when A.TEN99TYPE = 5 and A.TEN99BOXNUMBER = 1 then '1 Nonemployee compensation'
When A.TEN99TYPE = 5 and A.TEN99BOXNUMBER = 2 then '4 Federal income tax withheld'
when A.TEN99TYPE = 5 and A.TEN99BOXNUMBER = 3 then '5 State tax withheld'
when A.TEN99TYPE = 5 and A.TEN99BOXNUMBER = 4 then '7 State income'
end [1099_Box_Number],
--sum 1099 amounts
sum(A.TEN99AMNT) as '1099_Amount',
B.VNDCHKNM as 'Vendor_Check_Name',
--DBA address ID
B.VENDDBA as 'DBA_NAME',
B.TXIDNMBR as 'Tax_ID#',
C.ADDRESS1 Address_1,
C.ADDRESS2 Address_2,
C.ADDRESS3 Address_3,
C.CITY City,
C.[STATE] [State],
C.ZIPCODE Zip_Code,
C.COUNTRY Country
FROM PM00204 A
INNER JOIN PM00200 B ON A.VENDORID = B.VENDORID
INNER JOIN PM00300 C on B.VADCD1099 = C.ADRSCODE and
B.VENDORID = C.VENDORID
--RESTRICT TO YEAR 2024 and omit lines with no dollar amounts
where A.YEAR1 = '2024'
and A.TEN99AMNT <> '0.00'   --remove this clause if you need zero dollar lines too
GROUP BY A.VENDORID, B.VENDNAME, A.TEN99TYPE, A.YEAR1, C.ADDRESS1, C.ADDRESS2,
C.ADDRESS3, C.CITY, C.[STATE], C.ZIPCODE, C.COUNTRY, B.TXIDNMBR, B.VNDCHKNM, A.TEN99BOXNUMBER,B.VENDDBA
GO
grant select on [View_1099_Totals] TO DYNGRP
------------------------------------------------------------------------------------------------------------------------------------

Thanks
Terry Heley
Microsoft