Monday, January 14, 2013
Sorting in Smartlist the Smart way
Posted by Armanino Dynamics Team
Last 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.
We enable an organization by supporting each stage of your company’s growth by implementing scalable Microsoft Dynamics solutions. As Microsoft Certified solutions experts and Inner Circle Partners, we work to eliminate redundant systems and leverage existing software investments, while keeping your goals for the future in mind.