Posts Tagged ‘smartlist’

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

image

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

image

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.

image

 

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

Step 6.- Click OK twice and see your results

image

 

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

image

 

 

Until my next post!!!

Francisco G. Hillyer

Additional Sorts in Smartlist…. not just one

Hello, this time I just want to share something I learned sometime ago but to my surprise being completely honest most people I show them never heard about this.Me

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….

SM1

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”

SM2

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.

SM3

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.

SM4

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

Blog >> SQL View >> Dynamics GP >> Excel

Retrieving information from Dynamics GP for reporting purposes is so easy to do because there are several great tools available and there are many sources of information on how to use the tools and specific code to make and build reports.

Victoria Yudin writes a blog in which she generously shares SQL code that allows you to easily create SQL views to retrieve information from Dynamics GP.  Victoria wrote this article this week, and in it includes code for creating a SQL View that retrieves information about payables invoices and associated purchase order line details.  This is fantastic.

You can easily copy the code from her blog and paste it into a new query using SQL Server Management Studio.  Execute the query and it will create the view.  The SQL view can be used in Excel and SmartList Builder to create SmartLists that all users can have access to.

I created this short video that shows how easy it is to do.

Export to excel find

Patrick over at Developing for Dynamics has a great solution for those of you using Dynamics GP 10 and beyond.

I tested this out on my stinky old PC. I downloaded 20,000 rows in about 50 seconds after making the dex.ini switch. Without the switch I quite after a 1 min and 15 seconds and had only downloaded 2000 rows. Extrapolated that would have taken 12.5 mins to do 20,000.

This has been a limiting aspect of smartlist in the past so was pretty syched to see this solution.

What a great find.

Creating SmartLists using SmartList Builder and SQL Views

There are a number of GP bloggers that often include SQL Query Language as part of their posts.  This is a rich source of specific problem solvers.

SmartList Builder allows you to easily take advantage of these resources to provide data inquiry and reports.

These are the steps to use once you’ve found a SQL Query that would be useful:

  • Create the new SQL View using the SQL Query
  • Grant access to the new view
  • Create the SmartList using the SQL View

I created this video to show you a simple example of how to do it.

Victoria Yudin is one GP Blogger that regularly includes SQL Queries in her posts.  Check it out.

This is the specific post I referenced in the video.

SmartList Builder

There are many ways to use SmartList Builder and many reasons why you should check it out.  SmartList Builder allows you to create your own SmartLists and to enhance existing SmartList views.

You can easily create a new SmartList to compliment the ones that are provided with GP.  And they are very easy to build.

Here’s an example of a simple SmartList I built to look at the MRP results from Manufacturing:

Here’s a simple example of a SmartList with two tables.

Multi-company SmartLists

Here’s a nice little option in SmartList Builder which I’m surprised few people know about.  SmartList Builder allows you to create SmartLists that can report information from multiple companies in one list.

We have several real estate companies and family office clients that have dozens of GP company databases from which they want to report on rather simple things, e.g. all bank accounts, all fixed assets.  SmartLists is perfect for this type of reporting.

To have a SmartList look at multiple companies, go to the “Options” button, check the “Multicompany SmartList” check box, and select the companies you want included.

SmartList1

Here’s an example of a multiple company bank account list:

SmartList2

The problem in this solution is to find a field in the data that identifies the company to which the results belong.  In my example, this is easy because the CM Checkbook Master table includes the Company ID field that can be linked to the Company Master table, to return the name of the company.

I found this query on CodeSnippets to find all the tables in the company database with the column name “CMPANYID”

SELECT table_name=sysobjects.name,
column_name=syscolumns.name,
datatype=systypes.name,
length=syscolumns.length
FROM sysobjects
JOIN syscolumns ON sysobjects.id = syscolumns.id
JOIN systypes ON syscolumns.xtype=systypes.xtype
WHERE syscolumns.name=’CMPANYID’

These are the results:

CT00001
FA49900
CT00002
SOPS113B
RM50103
CT00003
SY00600
POPS0231
JCPS0040
POP10100
PRPF0113
UPR70501
DD40400
QAPS0032
QAPU0032
AAG00906
POPF0113
POPS0132
CO010000
ECPS0000
COSS0401
ECPU0000
ENC40000
ENC40100
RM30701
POP30100
mops0100
MOPS0200
CM00100
EC090000
WCPS0100
MRP0200
BMPF0114
BMPS0114
SVC40500
CAM10210
GTM41000
SY04800
MPPS0230
CAM10200
MPPU0130
WCPF0100
TLPS0100
TLPU0100
RVP0100
RVPS0132
CPO40001
CPO40002
WPPS0114
WPPU0114
PAVW10600
RTPS0130
RTPU0130
PAVW30600

If you have SmartList Builder and several GP company databases, you should try this out.

SmartList Export Solutions in Dynamics GP

If you’re using SmartLists on a regular basis to report on information in Dynamics GP, you’ll want to learn how to use the Export Solutions functionality within SmartLists to add charts, pivot tables, calculated fields, and additional formatting to your data.

Export Solutions allows you to run an Excel spreadsheet macro on the data that SmartLists exports to Excel.

These are the basic steps to set up the macro:

  • Export a SmartList to Excel
  • Start and name a new macro in Excel
  • Perform the formatting you want in the Excel spreadsheet
  • Stop the macro
  • Delete any worksheets you may have added during formatting
  • Delete the data that was initially exported to Excel
  • Save the Excel spreadsheet as a template

These are the steps to attach the spreadsheet with macro to SmartLists:

  • Navigate from SmartList:  SmartList  >>  Export Solutions
  • Document: Select the Excel spreadsheet that contains the macro you wish to execute
  • Works for Favorites: Select the SmartList(s) you want the Export Solution to be available with.

SmartListExportSolution

Once the Export Solution has been attached to the SmartList(s), you can use it when you wish.  Just make sure to select it from the menu drop down, and not “Quick Export”:

DropDown

I’ve created a companion video to this on YouTube.

Subscription Options:
Subscribe via RSS
Articles Categories