Posts Tagged ‘GP’

Sorting in Smartlist the Smart way

312339_117542088417759_1094173370_nLast year I worked closely with customers that performed data dumps into excel from Dynamics GP.

There are the easy exports where they just publish the report in text mode and then open the same in excel and I’ve seen customers that love formatting data in excel, spending countless hours formatting and at the end they have to do it again because data was refreshed.


There are others that are more complicated but yield better results, like Excel Reports, SSRS or plain T-SQL extracts and just very few who take advantage of the Export Solutions but that will be a subject of a future post so stay tuned.


Smartlist is a very useful tool within GP but some customers find it confusing because of the filters and how data is stored in GP.

One of the questions that made me write this post for you is, How can I sort data in Smartlist using more than one field, so I don’t have to do this in Excel?


The solution is simple but believe it or not, I learned that not many people know about this, I queried my own colleagues and I was shocked.

Here is my revelation for you, hope I can save you some time …

Use the Order By feature in Smartlist. Let’s go thru a sample:

Step 1.- Open Smartlist

Step 2.- Open Financial > Account Transactions

Step 3.- Double click on the * named smartlist


Step 4.- In the Search Account Transactions window click on the Order By button


Step 5.- Add the sort rules/levels from the list on the left, by using the Insert button

in my sample I am sorting by Account, then by the amounts, but I want all debits  from highest amount to the lowest, then I want all Credit postings same rule and lastly I want to sort by the Series.



And here is the result, so no more single column sorting!!!

Step 6.- Click OK twice and see your results



Here is another sample where I am sorting by Journal entry then Highest Debit Amount to lowest and lowest Credit Amount.




Until my next post!!!

Francisco G. Hillyer

What happens if I update Payroll tax tables and…

Hello all, here is something I was asked recently:

“I have processed my 2012 payrolls for the year, my IT is always on the latest and greatest regarding service packs, hotfixes, updates etc, and they installed the 2013 payroll tax tables update, and I have not executed the Payroll Year End, can I close it anyway?”

Short answer: Yes you can close it, but it will be incorrect.

Please if this happens to you, make sure to restore the Dynamics DB prior to the install of the payroll tax table update because I am sure you always do a backup of your databases prior to any updates installed right?

Leslie Vail has a post from 2011 where she explains how FICA/S wages and withholding is calculated Leslie’s Post

NOTE FOR LESLIE (Step 5b says “he year-end” instead of “the year-end”Smile)

But I wanted to provide a graphical representation of it

01-09-2013 12-21-51 PM


Until my next post…


Francisco G. Hillyer

DYNSA and SQL Maintenance for Dynamics GP

Hello all, it has been quite some time since my last post, kind of missing all of you, specially with the holidays approaching etc.598846_4784214609951_774409899_n

This time I would like to share with you something that I recently learned “the hard way” obviously on a support case.

First of all, I want to express the importance of validating your information and that the engineers/partners that are/were involved in your company setup of Dynamics GP were bound to the best practices established by Microsoft and supported by many of my colleagues.

In my case, a customer did a side by side upgrade of SQL, with this came the issue of not having the DYNSA login in the new SQL instance, we followed certain processes to make sure DYNSA was the owner of the Databases Dynamics GP is using.

But you may ask who or what is DYNSA? my friend Mariano Gomez has a post very complete about this subject and you can find it here: Mariano’s DYNSA Info since I am not reinventing the wheel take a look at Mariano’s blog its packed with information for all audiences (GP related !!).

So when I was working on this customer DYNSA setup, I suddenly remembered another case where I was having issues with a third party, I jumped into their environment (literally) and started investigating this DB configuration, to my surprise the owner of the databases was an AD account not DYNSA. I proceeded to replace the owner and then certain SQL reports started working and producing results. I am still intrigued on why, but I will do a full research on spare time.

So back on the game for this customer issue I was having while trying to archive data, just imagine a SOP30300 table with 11 million records and a huge base of customers.

The queries running were taking countless hours to execute not even mentioning the impact on the processor. memory and of course user experience.

I learned that the customer had a “Maintenance Plan” where they executed the Shrink process on SQL, as you may know I am a SQL enthusiast and I recalled an important blog post from another noted resource Mr. Pinal Dave aka “The SQL Authority” here is his post about why is BAD to shrink a DB Shrink is Bad for you… there is one section in the article that explains that Shrinking a DB to obtain disk space will actually fragment your tables, obviously to reduce fragmentation you rebuild indexes. So this maintenance plan was being executed to reduce disk space, then to improve performance, and the disk space was gone again. Wise words from a mentor that prefers to be in the shadows once said “with current prices on storage why waste time shrinking when you can focus on performance”.

I ended up tweaking some SQL scripts to automate a SQL job on finding fragmented tables in the DB and executing that as part of DB maintenance, as I said “go buy another disk drive and add it to your server, move the logs to this new disk and keep data apart from the logs and you will be better than now”.

If you need help, let us know, our team at RoseASP and RBS we have experience in solving this type of issues.

I hope my experience helps you for a better community.


Until my next post

Francisco G. Hillyer

Dynamics GP Insights to Distribution – Sales Orders

I put together some basic Dynamics GP user documentation and some of our specific blog articles related to sales order processing in Dynamics GP. You can request the document here, and it will be e-mailed to you for download:

Click here to fill out the form

Some of the subjects covered are:

  • Fulfillment Workflow
  • Drop Shipments
  • Sales Kits
  • Advanced Distribution – Pick, Pack, Ship

Let me know if you find this helpful.

Microsoft Dynamics GP 2010 R2–Available

Yes, you are reading correct, the long awaited R2 for Dynamics GP 2010 is now available for us to download.avatar

Click this link to review the brand new “What’s New PDF” CLICK HERE

New customers will not be able to obtain registration keys until GA May 1st.


The DVD for Microsoft Dynamics GP 2010 R2 is now on the Product Release pages below.  The Word Template Generator is a NEW PRODUCT for Release R2 and has its own PARTNER ONLY download page below.

Service Pack 2 for each product is included on the DVD and has also been posted to each individual page.  The links to each page are below.


The Microsoft Dynamics GP Service Pack 2 version is 11.00.1752.

Product Release Downloads for Microsoft Dynamics GP 2010

Direct Link to the DVD – – 2GB

Word Template Generator – PARTNERSOURCE ONLY

Service Pack, Hotfix, and Compliance Update Patch Release for Microsoft Dynamics GP 2010

Direct Link to the patch – MicrosoftDynamicsGP11-KB2435566-ENU.msp – 257 MB

Direct Link to SP2 Fix List and Install Guide – 11.0SP2_install_guide_ENUS.pdf

Software Development Kit for Web Services for Microsoft Dynamics GP 2010

Software Development Kit for Microsoft Dynamics GP 2010

Software Development Kit for Visual Studio Tools for Microsoft Dynamics GP 2010

Software Development Kit for Workflow for Microsoft Dynamics GP 2010

Service Packs and Hotfixes for Personal Data Keeper for Microsoft Dynamics GP 2010

Service Packs and Hotfixes for Web Services for Microsoft Dynamics GP 2010

Service Packs and Hotfixes for Workflow for Microsoft Dynamics GP 2010

Service Packs and Hotfixes for Integration Manager for Microsoft Dynamics GP 2010

Service Packs and Hotfixes for eConnect for Microsoft Dynamics GP 2010

Dexterity Development Releases for Microsoft Dynamics GP 2010 (Available Soon!)

Currency Translation Default Exchange Table IDs–Demystified!!!

A few days ago I was browsing GP when I noticed a section in GP that I was not familiar with. avatar

I used to provide multicurrency support in LATAM as well for a company I sued to work but this time I was left with a question mark.

If you go to Tools > Setup > Financial > Multicurrency

you will see Multicurrency Setup window that now includes a section named “Currency Translation Default Exchange Table IDs”



















I searched all over the internet and GP help files but with no luck, then I asked on Microsoft Communities when a friend from Fargo provided such information.

After installing GP 2010 SP1 you can translate GL amounts in a specified currency. Management Reportes can use translated amounts directly from Dynamics GP.

A currency translation allows you to report GL in different currencies that are not either the functional currency or the originating currency.

That’s why they (MS) added this table to setup exchange rate to be used for translation.


Translation Types:

Average uses a single exchange rate for each GL period, the values are converted to the reporting currency using an average exchange rate for the individual period as entered in the exchange table.

Current uses a single exchange rate based on the report date, the values are converted to the reporting currency using the exchange rate on the “as of date” for the report.

Historical uses a single exchange rate based on the transaction date, when an account is converted to the reporting currency individual transactions for the account are converted to the reporting currency using the exchange rate that was valid on the transaction date, then all summary information will be recreated for the account, based on the translated amounts.


In all three types if an exchange rate couldn’t be found then functional currency amounts are used.


There are more features that came along with this new characteristic, and obviously you need FP1 for Management Reporter.


Have a great day!!

Francisco Hillyer

U.S. Payroll – March Round 4 Tax Update for GP 2010 including 941 2011 format….released!

Its finally here, the long waited update from Dynamics GP team for payroll has been released.


Here is the link to the direct download:

U.S. Payroll Tax Update for Microsoft Dynamics GP 2010 (Round 4)
This page contains the latest U.S. Payroll Tax Update for Microsoft Dynamics GP 2010.


Here are the updated version numbers

Microsoft Dynamics GP


Project Accounting




Human Resources


Field Service


Grant Management


Dynamics Online Services


The automatic tax update site is available.


If you have employees paid in the state of Oregon, you need to install the tax update and the code update…major changes in their tax structure…there is also a new filing status for Oregon, so read the instructions if you are in Oregon carefully, please.

Thanks THeley…

Integration Manager Advanced Techniques – Scripting in VBScript

In case you were not aware, Integration Manager has a slew of sample VBScripts that you can copy and paste into your integration to accomplish such useful tasks as:

  • Autocreate Alphanumeric Vendor or Customer ID codes (e.g. for vendor “Acme Company” becomes ACME0001)
  • Prompting the user for input (e.g. automate requesting a batch id or document date from user)
  • Removing non numeric characters from phone numbers
  • Concatenating multiple fields from source file into one GP destination field
  • Deleting a source file after successful integration
  • Prompting User to define source file location
  • Sending an outlook email that notifies receipent of errors or warning messages
  • etc etc

Adding a sample script is simple:

  1. Open an integration.
  2. From the Integration window, choose Properties, and choose the Scripts tab.
  3. An icon next to a script indicates that the script is attached to this integration.
  4. Select the script type for the integration.
  5. Select Add based on when the scripting task should occur (before integration, before each document, etc).

Additional sample scripts are listed below:

GP Specific Sample Scripts
· Assigning an Alphanumeric Vendor ID
· Assigning a Sequential Vendor ID
· Setting the Batch Date
· Prompting the User for a Batch ID
· Concatenating Two or More Source Fields
· Prompting the User for a Document Date
· Setting the Document Type in Payables or Receivables based on the Document Amount
· Setting a GL Account in SOP based on Item and Customer
· Adding Leading Zeros to a Check Number in Bank Reconcile Integrations
· Removing Leading Zeros from a Check Number in Bank Reconcile Integrations
· Automatically Set the Reversing Date for a GL Transaction to the First Day of the Next Month
· Integrating Multi-column Budget Information
· Integrating Multi-column GL History
· Removing Specific Characters from a Phone Number
· Removing All Non-numeric Characters from a Phone Number
· Setting the Posting Type for a GL Account
· Updating the Default Purchasing U of M for an Item
· Setting a source field that is blank to the default value
· Setting the Typical Balance for a GL Account based on number scheme

General Purpose Sample Scripts
· Looking Up a Value in a Database Table
· Deleting a Source File After a Successful Integration
· Executing a Stored Procedure in SQL
· Filtering ODBC/Text Source Data based on User Input at Runtime
· Validating Foreign Keys
· Prompting the User for the Path to the Source Files
· Marking Source Records as Integrated
· Displaying a message box with the error and warning messages
· Sending an Outlook E-mail that Notifies the Recipient of Document Errors
· Protecting an Integration with a Password
· Renaming a Source File that Uses Today’s Date in its Name
· Validating Data based on a List of Values

Subscription Options:
Subscribe via RSS
Articles Categories