Posts Tagged ‘Sort’
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
I know people that use Smartlist extensively, they really use it but the pain they have is sorting, I have witnessed many exports to Excel and even some people were some kind of creative that they made their own “Export Solution” to do what? you may ask, and yes you have the answer, to SORT by other columns.
So when I show them this trick they just think that I’ve been hanging around with MGomez, FHamelly, DPitcher, the famous SChapman, MPolino and many more just I wont finish the list.
Here it is… how about if I tell you I can show you how to sort Smartlist by using multiple fields, different sorts and even with fields not being displayed on the smartlist itself?
Lets open Smartlist….
Double click on one of your smartlist objects, I am choosing the “All” in Account Transactions.
Once the Search dialog appears click the button on the bottom called “Order By”
Then the Select Order By window appears, note on my example that I sorted my list by Account, then by Series (Descending), by Transaction Date and by the User Who Posted.
The User Who Posted is a field that is not being displayed in the screen, even in this example the user who posted is not doing us any good report wise as we cannot see the value, it’s a clear example that you can use a non displayed field to sort.
Once you have chosen the available columns and moved them to the Order by section you click OK to go back to smartlist search dialog, then OK again to have the search executed.
As you can see the result is ordered by the Account number, then by the series int value, then by date.
Don’t forget that once you define your sort, save your smartlist definition so you don’t have to re-work.
I hope I can save you sometime with Excel macros and ease your job directly from Dynamics GP Smartlist.
Until my next post!!
Francisco G. Hillyer
Even customers are truly surprised when I show them how Integration Manager can pull information from one company or even better from another SQL server into Dynamics GP as a source.
Yes I know you are surprised too that I am telling you that your source can be a SQL Query Script…….
We have many ways of sorting data in integration manager, we even have filters and sort fields on the source definition, but nothing beats the customer frustration when they have a sorted file and Integration Manager scrambles the file, then open up the distributions for example on a JE import and see that the order is not even close to what the source file have.
One thing important is that there is a setting in the IntegrationManager.ini file. This setting is called UseOptimizedFilter normally I see this option with the value True.
When this option is enabled, Integration Manager uses a series of ninja techniques to optimize the data upload but that has an impact on the sort order.
If you want to have the distributions ordered or your transactions in the order you have them in your source file, close your Integration Manager, go to this file located on the Integration Manager code folder and set to false this setting.
Try your integration once again and voila!!!! Its just like magic, everything is the way you had it on your source file.
I am hoping to save techs from headaches here, and if this tip helps you, let us know, we want to know if our work can actually influence a better Dynamics community.