Rose Business Solutions Dynamics GP

Archive for the ‘Excel’ Category

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.

 

 

Until my next post!!!

Francisco G. Hillyer

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.

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

Good day to all, here is another post that comes from working with budgets and excel spreadsheets.

Recently I had a case where the customer was trying to import budgets into Dynamics GP. All spreadsheets seemed normal, they uploaded correctly and we were under the impression that everything was ok for all the years we imported.

But to our surprise the customer called in and said “It appears that the sign is reversed, we verified and we entered the budgets with the wrong sign instead of (52,250.00) should be 52,250.00 can you change it? And by the way we deleted the budgets can you import again?.

 

Ok so my coworker started adding columns to the left and multiplying the numbers by –1 to reverse the sign, I saw that this process was a little painful and slow considering there were many spreadsheets to work on.

So here is how I helped them out. The following is a made up sample of the budget sheet

I take an empty cell, and type –1, then I select that cell and copy the value to clipboard or I press Ctrl-C

After I have the value copied I select the range of values to change. Notice that the copied cell is still marked.

Then I right click on the selection and choose Paste Special

The paste special window has a section called operations, this section is able to Add, Subtract, Multiply and Divide, so I select Multiply

And after selecting the operation I click ok and my values are updated with the reversed sign.

 

Just imagine the potential and time saving opportunity here, if you want to subtract a fixed value to all columns or add a fixed value, this makes it relatively easy and fast.

Another example will be when someone asks you to take the numbers from last year and add 25% its so simple to just add a 1.25 to a cell, copy the value, select your range and select multiply. Try it with variations and you will experience this hidden gem from Excel.

I would like to point out that its not just for budgets but for many tasks in real life. Why I added to a Dynamics GP blog? because it originated from working with the budgets.

 

Have a nice day and until my next post.

 

Francisco G. Hillyer

If you don’t have the Sales Forecast module for Dynamics GP, you can still maintain and manage a sales forecast using the Sales Order module.  This information can be used to drive the MRP functionality in Dynamics GP.  There are two steps you need to do to accomplish this:

1.)  Enter your sales forecasts in to Quote documents in the Sales Order module

2.)  Setup MRP to look at Quotes when generating MRP requirements

Here’s a Quote:

Here’s a list of several quotes that make up the total forecast:

Here’s a Pivot Table easily created from a refreshable Excel report that shows the forecasted quantities by item and date:

Here’s the setting to check to make sure that MRP looks at Quotes when the MRP regeneration is executed:

Here is a video that shows this in action: Sales Quotes for MRP

Here’s the same video on YouTube: