(At least I hope it's the end, LOL!). Here are the various parts of my mini-series so far:

  1. The story behind all of this (part 1)
  2. The technical deep dive into the SQL query (part 2)
  3. A bit about namespaces in the FOR XML (part 3)
  4. A bit about validating an XML file against an XSD schema (part 4).

So, what is this post about? This is taking a step back to describe the process itself. Part 1 of the series tells what I'm doing, and ultimately I am all about the process. It's one thing to create some code to create an XML file, but ultimately the best solution for an ongoing, repeatable process is to think about the end-to-end steps from the point of view of the user. This is about the "solution" that I created, which may give others some ideas on either the thought process behind it or ideas on how to mimic the same thing for something similar.

What's the start & end of the process?

The process is handled by our Finance department. They are the ones that track the deposits throughout the year and the interest paid out. Ultimately the tracking system needs to be improved but that's a future phase to this project. For this post, it's not a consideration, yet.

The starting point to the process is what the users have done for years, get an Excel file together with the recipients, names, addresses, SIN or BN, recipient type and interest amount to issue a T5 slip for.

The endpoint to the process is getting an XML file for upload to the Canada Revenue Agency's (CRA) internet filing website + printing T5 forms to mail to the recipients.

What does the process look like?

My vision was this:

  1. Throughout the year, the users would manage the vendor names and addresses in Dynamics GP in Extender. (To start, we uploaded the data to populate the original values for this year). If there are lots of vendors to create the info for, there will be a SmartConnect map to import this Extender info.
  2. Annually, the user checks the setup information in an Extender "T5 Filing Setup" window - put in the proper tax year and verify or update contact information as needed.
  3. When ready to start the process for this year: a user uploads the Excel T5 data to SQL for processing (using SmartConnect to do this).
  4. The user runs either a refreshable Excel report or Smartlist to "get" the data back in the final format, to validate that all recipients have Extender T5 information correctly filled out, validate the # of slips and $ amount etc.
  5. Generate the XML file.
  6. Print the T5 forms using SSRS.

Organization info: Extender Form

Using eOne Solutions' Extender product, I created a standalone Form (and a Menu to access the form) for the users to record the company information for T5 filing.

The thinking behind this is I want to hard-code as little as possible in the XML file (and SSRS report). Addresses change, contact information changes and the threshold to create T5s can change. Anything in the file that could change I put in the window and then the data from there is what is used in the XML file and T5 Print processes. This gives control to the users to manage this instead of needing to update code and reports.

Vendor T5 Info: Extender Window

Next, the interest we pay out is paid via Accounts Payable in Dynamics GP, therefore each recipient has a vendor card. We don't want to rely on the vendor address in case the T5 needs to be sent somewhere specific, other than the mailing address for the A/P cheques. Plus, additional information is required for T5s and the field information needs to be split out into specific fields depending if the vendor is an individual or a business.

That could be done in code but it would be significantly more complicated than I needed (including the payback on the additional effort to get that level of parsing done accurately) so I opted to put some onus on the users to manage that themselves. This also lets me set field formats and maximum lengths so that I don't have to handle that in the code. This makes the code a lot simpler to read and understand for the next person who may need to maintain it down the road.

One issue I ran into is I have more than 15 fields (the max # of fields in an Extender window) and I don't think a scrolling window made sense so I opted to create two separate Extender windows, one for names & recipient types and one for addresses.

The second challenge was one of security. The users responsible for the T5 process don't have access to create or maintain vendors and the A/P team should not be able to view or edit T5 information (because of the potential of stored SIN numbers) so the Extender window is placed on the Inquiry version of the Vendor window instead of Vendor Maintenance. Security-wise, the users have access to this and I can manage security to Extender appropriately using out-of-the-box Dynamics GP security.

Upload to SQL: SmartConnect

Once the users are "ready" to start, with the list of vendors and interest amounts, they save to a specific template and run a SmartConnect map to upload the data to the Dynamics GP SQL server, to a custom database for non-GP things into a staging table. I'm only requiring the bare minimum of data - Vendor ID, Vendor Name for visual comparisons later (ensuring there is no data mix-up anywhere), and Interest Amount. That makes the upload pretty straightforward. I also am truncating the table of previous contents each time the map is run. I opted not to complicate things by maintaining historical data.

Verify Data

My SQL code is based on tables and views, with no stored procedures except to generate the actual XML file. So, as soon as the users have uploaded to SQL via SmartConnect, they can refresh an Excel refreshable report to see the data joined to the Extender data to validate the upload, and the totals and spot-check what they need.

The other option I could implement is creating a Smartlist inside Dynamics GP for the users to do this if I choose. Either way, the thinking behind this is to give the users a way to validate before creating the XML file or printing. This stage shows the data joined to the Extender info so it will highlight records where there is no T5 vendor or address data populated before they go too far into the process.

Generate the XML file

Here's where the process is still a work in progress. This year I generated the XML file manually by executing the stored procedure in SQL Server Management Studio, then saving the file for the users to a folder they can access. I am researching alternatives to enabling xp_cmdshell to be able to allow the users to execute the stored procedure where it creates the XML file in a specific location, on their own. Requiring me or our IT department to execute this stored procedure isn't the worst thing in the world, but it would be better if the user could generate it themselves. For a once a once-a-year process, it's not something I'm going to spend a lot of time figuring out though, to be honest.

From here, the users can upload the XML to the CRA site and be done with the official filing, other than printing the forms.

In previous years, the users had printed the T5s via a mail merge process in Excel & Word. It worked, but frankly, it's a pain in the butt. We can only order 50 forms from the CRA so we're photocopying forms or downloading the blank forms from the CRA website and printing, then printing on that again and trying to hit specific pre-printed boxes for the form. Talk about time-consuming. Printing anything on pre-printed forms is never fun.

Screenshot of a T5 blank form with pre-defined boxes

This year I decided to take the plunge and create a better process, developing a report in SQL Reporting Services (SSRS). This doesn't necessarily make it easier to "hit" a pre-printed box on a copy of a copied form, however, it gives me more flexibility to get an even better process in place for next year. After fighting with the forms a bit this year to get them right, I plan to update the report and add the form itself to the report, boxes and all. T5 forms are one of the forms the CRA allows organizations to customize without requiring approval to use it. So… the next project is to create something resembling the above form so that the form prints completely on blank paper and our printing woes are gone. The time spent on this would be well worth it as the Finance team spends hours every year getting the printing done which would be replaced with a 5-minute activity of printing on blank paper if they didn't have to worry about pre-printed forms.

The bonus of this process was we also printed T5 forms generated by our customer information system (CIS). That system generates the XML for us but it doesn't print properly. I have incorporated a process for that by having the users use SmartConnect to import from the CIS XML file to a printing "staging table" and my SSRS data source can pull from that or the above SQL data relating to the non-CIS records to print the T5 using the same format and same printing process.

Summary

So, to recap, the process is mostly in the hands of the user, except for generating the XML file. There are tweaks to be made for the printing but it works. It's a good example of how I could leverage a tool like Extender to add functionality for a process that isn't entirely related to Dynamics GP in the first place.

I hope this is useful and perhaps gives others ideas on how to leverage a tool like Extender to augment existing processes they have today.