Loading Sales Tax Information

I recently was asked to help create Tax Schedules in Dynamics GP based on a spreadsheet of customers, zip codes, and corresponding city, state, and local tax rates.  I created a series of scripts to load the information from Excel, and I thought I would share these with you all in case anyone else could benefit from them :)  Now, in this case, the tax details were already loaded to Dynamics GP and the spreadsheet referenced the Tax Detail IDs.

Of course, big disclaimer, that you should always test, test, test, and backup, backup, backup when using these scripts.  I created a series of these scripts using the concatenate function in Microsoft Excel to pull in the data needed.  These scripts also assume that the tax schedules in GP have IDs that correspond to the customer zip codes.

--update descriptions for tax details, TX00201, as they were loaded with a generic ID
Update TX00201 Set TXDTLDSC='locality/tax detail description' where TAXDTLID='tax detail ID'

--create TX00102, Tax Schedule relationship to Tax Detail
--assumes static values for TXDTLBSE, TDTAXTAX, Auto_Calculate fields, if in doubt that these are correct for your situation, manually set up a tax detail and check the TX00102 to confirm the proper values
INSERT INTO TX00102 (TAXSCHID, TAXDTLID, TXDTLBSE, TDTAXTAX, Auto_Calculate) values ('zipcode/tax schedule ID', 'taxdetail ID',3,0,0)

--create TX00101, Tax Schedule records
INSERT INTO TX00101 (TAXSCHID, TXSCHDSC) values ('zipcode/tax schedule ID','area description/tax schedule description')

--update customer master tax schedule where the zip code (left 5 characters) match a tax schedule in GP
update RM00101 set rm00101.TAXSCHID=tx00101.TAXSCHID from RM00101 inner join TX00101 on left(RM00101.ZIP,5)=tx00101.TAXSCHID

--update customer master address tax schedule where the zip code (left 5 characters) match a tax schedule in GP
update RM00102 set rm00102.TAXSCHID=tx00101.TAXSCHID from RM00102 inner join TX00101 on LEFT(rm00102.ZIP,5)=tx00101.TAXSCHID

Christina Phillips is a Microsoft Certified Trainer and Dynamics GP Certified Professional. She is a supervising consultant with BKD Technologies, providing training, support, and project management services to new and existing Microsoft Dynamics customers. This blog represents her views only, not those of her employer.

Sales Tax Simplified

It seems like the Dynamics GP forums are alive with sales tax questions lately. So, I thought I might tackle this one in the same manner that I do in the classroom-- by breaking it down in to steps. I treat sales tax as a three step process.

1. Document Default
2. Item or Setup Default (depending on if you are working with Receivables or Sales Order Processing, for example)
3. Tax schedule comparison

So, for this post, we are going to keep it simple and look at taxes in Receivables Management and Payables Management. I promise another post on Sales Order Processing and Purchase Order Processing next week :) But, for now, lets keep it simple.

Let's start with Step 1. There is one key setup that can impact the tax schedule that appears on a document (whether it be Payables or Receivables) by default. In your company setup, Tools>>Setup>>Company>>Company>>Options, there is a setting to Use Shipping Method to Determine Default Tax Schedule.

• If this option is marked, then the tax schedule that appears on the transaction will be dependent on whether the shipping method is a pickup or delivery method (more on this later).


• If this option is unmarked, then the tax schedule that defaults on the transaction will always be the Vendor Address' schedule (on Payables) or the Customer Address' schedule (for Receivables)

Now, let's look at Step 2. Now, Step 2 assumes that in your setup you have chosen Advanced for your tax calculations (Tools>>Setup>>Purchasing/Sales>>Payables/Receivables>>Options) rather than Single Schedule. Single schedule will always charge according to Vendor or Customer Address' schedule without Step 2 or Step 3.

In Step 2, we need to find the setup schedule (in Payables or Receivables). This is the schedule specified for each amount (Purchases, Sales, Freight, Misc, etc) in the setup options window mentioned above. In most examples in training manuals, these schedules are setup to include ALL DETAILS.

So, in Step 3, the schedule from the document (Step 1) is compared to the schedule from setup (Step 2). Details that the two schedules are compared, and only those that exist in both places are calculated.

Easy right? I know, I know..you are probably saying, huh? So, lets look at an example. Let's say that I have the following tax schedules set up:

• MOTAX: Contains the MOSTATE, KCCITY, MOSPEC, and JCKCTY Details which will be assigned to my Missouri based customers


• KSTAX: Contains the KSSTATE and KCKCITY Details which will be assigned to my Kansas based customers


• VALID: Contains the MOSTATE, KCCITY, JCKCTY, KSSTATE, KCKCITY which contains all of the currently valid tax details. I have purposely left off MOSPEC, it was a special tax that was only charged last year in MO and is no longer valid.

I set up my customer ABC AUTO with the tax schedule MOTAX, and I assign KSTAX as the default tax schedule for sales in Company Setup. VALID is specified in my Receivables Management Setup as the tax schedule for Sales.

Let's assume that we have chosen to have shipping method determine default tax schedule. So if I enter a Receivables invoice for ABC AUTO, the tax schedule will default as follows:

• Shipping Method with a delivery type: MOTAX (from Customer Address)


• Shipping Method with a pickup type: KSTAX (from Company Setup)

So, lets assume I use a Shipping Method with a delivery type, so MOTAX defaults. This would then be compared to the VALID tax schedule specified in setup. What is in common?

• MOSTATE, KCCITY, JCKCTY are the only taxes that would be calculated, since MOSPEC did not exist in the tax schedule in Receivables Management setup for comparison

I think of the tax schedule specified in setup as my "control" schedule, containing all valid tax details to be used in comparison against the documents. In many cases, this would be all details. But in some cases, as outlined above, it may be easier to remove a tax detail from the one control/setup schedule than to remove it from all schedules that contain it. This is also a great approach if a tax is only valid (or invalid) for certain portions of the year (think of sales tax holidays).

Please post back your questions or comments or examples to help with the understanding. I promise more next week on distribution. Have a great weekend!

Update from 9/2: Mike Lupro was kind enough to share his naming convention for tax schedules and details. Mike says, "I recently implemented a pretty simple tax scheme for a client and found myself getting wrapped around the axle with schedule names vs. detail names. I finally used the following naming scheme so the client could tell if they were dealing with a schedule or a detail. And to easily have the items sort into a state-by-state order. It also helped them decide if it was a purchasing schedule/detail vs. a sales schedule/detail. Overall it was a pretty simple setup and the naming convention would get more complicated if the taxes were more granular." Thanks Mike!