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.