Last week, I was asked if I knew of a quick way to update the Basic Personal Exemption amount in Dynamics GP Canadian Payroll. It’s not the first time I’ve been asked as it’s relatively common for an “oops” on the CPY Year End Close routine to either forget to update the personal exemption amounts or that somehow the wrong amounts are in there. So, today’s #TipTuesday is a short one: what’s the quickest (& safest) way to update this if it was wrong on multiple people?Continue reading “Mass Update CPY Basic Personal Exemption”
Today’s #TipTuesday is a security tip to help you identify what exact permissions the SQL fixed database roles have, that start with rpt*. If you’re granting access to some of your windows users or groups, you want to know what they can get into, right?
The purpose behind the rpt* roles is generally for SSRS (SQL Reporting Services) reports that ship with Dynamics GP since users running those reports are outside of GP, not tying into GP permissions. There are plenty of other posts out there about GP & SSRS, so I am not going to get into that here. Instead I am going to show you a SQL query I’ve used to see what permissions are granted to what role, login, user or group in SQL.Continue reading “What’s included in those SQL rpt roles?”
Today’s #TipTuesday is too late for 2018 filings but hopefully those this applies to will file it away for next year. The post is as a result of a question from a consultant who works for one of the Dynamics GP VARs in the Toronto area, whose client asked how to get 1 XML T4 file for multiple employers.
TL;DR version: it does that out of the box although the UI doesn’t make it seem like it would! Since some people may stumble across this post and use other ERP systems, this post is specific to Dynamics GP and the Canadian Payroll module.Continue reading “Cdn Payroll T4 XML & Multiple Employers”
(At least I hope it’s the end, LOL!). Here are the various parts to my mini-series so far, which is the story behind all of this (part 1), the technical deep dive into the SQL query (part 2), a bit about namespaces in the FOR XML (part 3) and finally 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. The part 1 story tells you what I’m doing, and ultimately I am all about the process. It’s one thing to create some code in order 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 you some ideas on either the thought process behind it, or ideas on how to mimic the same thing yourself for a process of your own on something else.Continue reading “Fun with XML, the end”
The last of the “technical” parts of this mini-series is about how to validate an XML file with a parser, against an XSD schema. This would complete the list of things I learned in this journey to create a T5 XML file, that I didn’t know before I started.
If you’re interested in the previous parts, part 1 is the story behind all of this, part 2 is a deep dive into the SQL code and syntax for the query, and part 3 is about getting the namespaces correct on the XML file.Continue reading “Fun with XML, part 4”
The fun continues! Today’s post is about the namespaces. I was unable to figure this out for the longest time and ended up giving up, manually pasting in the values I needed for submission. It was only in prepping this post that I came up with a way to get the namespace attributes in the right spot.
If you are interested in the entire journey, check out part 1 (the story behind this) and part 2 (deep dive into the technical details of creating the XML in SQL).
It seems from searching for this in various forums that other people had the same issue I did: using WITH NAMESPACES seemed to then put the namespaces in every element parent tag, where I only want it on the Submission tag. Here is how I worked around it and I can only assume it’s a “workaround” not a recommended way of doing it. It works so I’m going with it! I hope this helps someone else out there.
Today’s post is a continuation of the mini-series I started on Feb 28th. You can read the first part here if you’d like, which contains the story behind this. In this post I’ll be getting into the technical details on what I learned.
This is about the guts of the XML itself, in SQL, where I ended up writing this procedure. First, before I get into the details, I wanted to reference this Stack Overflow post that was the single most useful post and example for the work I did. The person who posted the example, Ryan, also laid out a very simple temp table structure so I literally copied that code and pasted it into my test SQL server and played with it to understand it.Continue reading “Fun with XML, part 2”
This is going to be a short series of posts (short series, not short posts!) about my journey with creating an XML file for T5 filing with the Canada Revenue Agency (CRA for short). So many things happened in the last couple of weeks on this, that there were plenty of lessons learned to share and stories to tell! The story overall was one of a lot of frustration at times but in the end, a lot of pride in figuring things out with a deadline looming and ultimately, successfully creating an XML file to submit on time for our T5’s.Continue reading “Fun with XML, part 1”
Today’s #TipTuesday is a pretty simple one, it’s about the Additional menus in GP windows that appear when you use Extender (from eOne Solutions) or other ISV solutions or customizations that add functionality to existing Dynamics GP windows. I learned something that shouldn’t have been new to me, so I either didn’t know this or had forgotten about how security worked, as it relates to Extender. I can’t say 100% that this applies generically to all Additional menu items but I believe it does. Someone can comment if I’m incorrect here!Continue reading “Additional menu in GP windows”
This phrase was uttered in the office recently, by the person who is tracking final changes to the year end Financial Statements. Her statements were out of balance, which turned out to be a new G/L Account. The next phrase was “Oh sorry, I forgot to tell you I added that. There must be some way to to alert you because I won’t always remember to tell you!”
Yes, there must be a way! Cue the SmartList-based Home Page Reminders. This is the subject of today’s #TipTuesday post.Continue reading “When did that G/L Account get added?”