Archive for the ‘Customization’ Category

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

Why my GP is too slow?

Hello all, I would like to provide you with some information on what to look when 312339_117542088417759_1094173370_nDynamics GP seems to be working slow. As you may already know Dynamics GP is a process driven application and you may experience slow performance when specific processes are performed in GP please take a look:


· While posting might be due to the PJOURNAL table as you know checks post too or remittance is being printed separately

· Client workstations should have a default printer setup and online, remove invalid printers

· While opening windows, the autocomplete feature may cause performance issues and if not used can be turned off

· While login into Dynamics GP or utilizing 3rd party dictionaries as well if Menu Master table (SY07110) became too large.

· The location of the modified dictionaries other than local workstation

· Certain Smartlist reminders might interfere with login into Dynamics GP

· You may have shortcuts to network locations that are no longer mapped or available

· Printing to file directly into the client/remote computer instead of the hosted server user folders

· OLE Notes path in Dex.ini

· SQL AutoClose and AutoShrink options not set to false

· Virus scanner setup not excluding the following extensions (CNK, DIC, CHM, SET, INI, DAT, IDX, VBA, LOG, LDF, MDF)

· The Dynamics GP homepage smartlist favorites

· The Dynamics GP homepage outlook integration

· Enabling tracing options in DEX.ini

· Bad user defined triggers in SQL

· Bad configuration of SQL server memory allocation

· SQL server or Dynamics GP server available disk space

· SQL server log file is full and is not set to Autogrow

· TNT*.* files, your %TEMP% folder has not been cleaned

· Non-compliant SQL server/GP Server/Client hardware

· Different DB owner than DYNSA

· Little or no SQL server maintenance (Table Fragmentation)

· You might be missing table indexes or statistics

· When exporting a budget, thru the budget wizard it seems locked (if you are using the excel wizard to export, make sure the “save as” dialog is not on the background, Alt-Tab to it as it must have been opened and its behind your main GP windows.

· Too much history (You can archive historical years, specially if you have large tables like Item Master, Customers, Vendors) Believe me I ran reconcile one time and it took 6.5 days on a company with more than half a million SKUs and 5 years of sales.


I have witnessed how few administrators that in order to preserve enough disk space they have a tendency of running SHRINK on the SQL server, this obviously will fragment tables affecting performance. I have a post that covers that here.


If you want us to take a look at your environment don’t forget to contact us, and as always when troubleshooting record answers for the following questions:

1.- Can you replicate the issue? write down steps that let you reproduce the issue.

2.- If its related to posting, please note the module( s ), how many transactions are in the batch, how long does the process last? how long did the process last before?

3.- On a Server/Client install, can you replicate on the server?

4.- Can you reproduce on all or other clients?

5.- Are there any 3rd party products running on the same SQL/GP server or together with Dynamics GP?

6.- Are there any customizations in GP?


Until my next post and let us know if we can help!!

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


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

Get the job done!!!

Hello, have you ever worked on a project, giving away your weekends trying your best to keep up with a tight deadline and stretching beyond your means?.Frank New RBS

Does this sound familiar? being so close to completion and all of the sudden you get a dozen “Just one more thing…”?

I feel lucky to ever go that route once and for all.

As you read this you may feel like I am complaining but believe me I am not, I feel lucky as this makes me a better consultant as I have kind of placed myself in the customer shoes.

Lessons learned:

  • Never assume project settings
  • Do communicate, don’t be afraid
  • Better now than later (changes)
  • Choose the right tool
  • The wheel was invented years ago, don’t spend time reinventing it

Never assume project settings

Ask, please ask about settings on current environment I started my project using .NET 4.0 and SQL server 2008 R2, when I tried to deliver….. yes you are right the company environment was not totally up to date and .NET 4.0 could break current integrations so it was a no Go on it, I moved my application to .NET 3.5 SP1 and about SQL they had SQL Server 2005 SP2, so I could not just backup and restore my database settings I had to re-deploy all SQL objects.

Do communicate, don’t be afraid

Even if you know it all and have all the specifications do ask questions, get back to the customer even if you know the answer, sometimes the customer might add a quiz or a puzzle in the documentation to see if you are looking or really paying attention. Do not wait until the last minute.

Better now than later (changes)

As Franny Fried explains on her blog post “The email I just sent asking you to estimate what it would take to tweak your project according to my specifications is added work”.

I understand that it gets annoying to be so close to complete your task and be “stuck” applying all this late changes. But it’s better now than later, imagine you compile/publish/package/deploy/install/test your application and the customer send this request to you all that time for nothing, better do it while still on the process and do it once.

Choose the right tool

You can say that you are expert in C#, or an expert in T-SQL for Dynamics GP there are different tools, eConnect, Dexterity, Modifier, VBA, .Net, Continuum etc.

Each one has its limitations and advantages choose wisely the best development tool that adapts to the project needs and stick with it.

The wheel was invented years ago, don’t spend time reinventing it

When the tool I was assigned to build, one day a coworker came to me and asked: “Francisco so are you using integration manager to push the data?” Ok let me explain Integration Manager is great but its not as fast as you can expect to import/process 65,000 records in a short period of time, or the use of direct table integration.

For that particular reason there is eConnect and you can use it to your advantage in a .NET development, or if you are a true master for GP and know its ins and outs, you can use plain T-SQL to accomplish this task easily and executing the needed processes to validate your data. Don’t spend time decrypting scary XML maps or other stuff don’t complicate yourself read and reach out, asking for help is not considered a weakness its intelligent (unless you are asking me why you see asteroids on the screen when entering your password).


To read Franny blog post click here: My foot on your backside…is that in scope?


Thank you for reading and let me know anything you have learned from a painful experience and how that made of you a better consultant/employee as well how this changed from undeliverable to GET THE JOB DONE!!!.


Francisco G. Hillyer

Common Support Debugging Tool Myths – Fact or Fiction?

David Musgrave has compiled a list of statements that have fallen into Fact or Fiction area he has now enlightened us unveiling the truth behind Support Debugging Tool.

The myths around this tool are the following:

The SDT is hard to install?,  hard to setup?

can only capture logs?, SDT creates tables at the SQL Server level?

The SDT causes performance problems

The SDT has features that are dangerous

The SDT is Microsoft’s most guarded secret

The SDT will make you more attractive to the opposite sex

The SDT will regrow hair

The SDT was created by advanced aliens, David just stole their technology


And a lot more if you want to be on the ring of power

Click on the following link to know the truth

How to Create a Simple Extender Window in Dynamics GP

This short video shows you how to easily add an Extender window in GP.

Subscription Options:
Subscribe via RSS
Articles Categories