In the latest payroll tax updates available from Microsoft, there are 5 new federal filing statuses that were added for the new 2020 federal tax laws. Originally there were 4 statues which were:
- Exempt
- Single
- Married
- Non-Resident Alien
The 5 new filing statuses that were added for the 2020 tax year are:
- Head of Household
- Head of Household Higher WH
- Married Higher WH
- Non-Resident Alien HR
- Single Higher WH
Microsoft updated the user interface to add the 5 new types, however they did not add the new types to their eConnect stored procedures. SmartConnect uses these Microsoft-written stored procedures for importing, so if you try and import any of these new statuses you will get the following error from eConnect:
“Error Number = 4094 Stored Procedure = taCreateEmployeeTax Error Description = Federal Filing Status (FDFLGSTS) is invalid”
In SmartConnect, this error will look like this:
Microsoft has stated that they will not be updating eConnect to add the new fields to either eConnect or Integration Manger: https://community.dynamics.com/gp/b/dynamicsgp/posts/new-2020-payroll-w-4-how-does-it-work-in-microsoft-dynamics-gp
So, in order to import these values, we need to implement a workaround. Our workaround will be to create an “After Document Success” script of the “Run SQL” type to update the record after it is already imported.
The Steps
First step is to determine what the possible values are in the table. In order to import these statuses, you need to know what the possible values look like in the table. Here is what all the statuses are, and how the appear in the table.
- Exempt = (blank)
- Head of Household = HOH
- Head of Household Higher WH = HOHHR
- Married = MAR
- Married Higher WH = MARHR
- Non-Resident Alien = NRA
- Non-Resident Alien HR = NRAHR
- Single Higher WH = SGLHR
- Single = SINGLE
So, when you construct your source file, you should have the values on the right in your column you are using for federal filing status. I created a source file to use for testing, and this is what my source file looks like.
Note: Exempt is blank, meaning there is no value in this column, in my screenshot below, employee ID 004 is Exempt, which means the field is empty.
After we get the correct values in the source file, the next step is the mapping. We cannot use the field inside of SmartConnect because this is using eConnect, which will reject any of the new values. So, when we create our map, we need to leave the field unmapped. So, the field mapping will look like this. In this example mapping, I only have the employee ID field mapped. However, typically you will be mapping most of the other fields as well. The main thing to remember is NOT to map anything to Federal Filing Status.
Now we need to add our task. In the main Map Setup screen, we need to click on the Tasks button.
After the Maintain Tasks window opens, you need to highlight “Tasks that run if the document succeeds” and then click on “Add New Task” and select “Run Sql Command” from the dropdown.
This will open up a SQL task window. At the top of the window, you need to fill in the connection information. You want to setup the connection to your SQL server with Dynamics GP on it, and then point at the database you are importing into. In my case the database is named TWO (Which is the Fabrikam, Inc. company)
The script you are going to put in there is going to look something like this:
update UPR00300 set FDFLGSTS=’_STATUS’ where EMPLOYID=’_EMPID’
However, your source columns may have different names than mine. What you want to do is type in the first part of the script up to the first single quote:
update UPR00300 set FDFLGSTS=’
Then you press the “Insert Variable” button and insert your field that has your Federal Filing Status in it.
Then you type in the rest of the script up to the next single quote, and you insert the field that contains your employee ID in the same manner as above. When you are done, the script will look exactly like this, however the _EMPID and _STATUS fields will be replaced with your column names.
Click on “Save” to save the script, and then click OK on the tasks window.
Now you can run the map. How this works is that the eConnect node will insert the employee ID and all the other fields you have mapped, however since you left the “Federal Filing Status” unmapped all the employees will come in as exempt. Ir immediately after each document, the SQL script will run and change the filing status to what you have in your source file. So, you will no longer get the eConnect error, and the filing status will be set properly.
Here is what my UPR00300 table looks like after I ran the map. Note that all the filing statuses were updated correctly.
If you have any more questions, feel free to email us at sales@eonesolutions.com