Dynamics GP 2010 R2 Database SDK – Siva

I was in fact awaiting Sivakumar’s post about the new section that he has been working on. I got the privilege to go thru’ this first before his post went public.

Siva’s new section is all about GP 2010 R2 SQL Objects (Tables, DBs, Stored Procedures, Views, etc.). Like the one we have in MS Word documents that can be accessed from GP SDK, this is also an interesting concept and also very informative for all developers out there, who may not have access to SQL Server on a real time.

Read his post here, Dynamics GP 2010 R2 Database SDK, and share your thoughts with him. 

I am sure this is going to be of immense help for all of us.



GP Homepage Layout Mass Update – Thru’ SQL

I love SQL. Simply because, it eases lot of pain in doing some redundant work.

When you create a user, by default, GP creates the following segments on his/her homepage:

1. To Do (Reminders/Tasks)
2. My Reports
3. Microsoft Outlook
4. Quick Links
5. Connect (links to Online Resources such as CustomerSource, Forums, Blogs, etc.)
6. Metrics (quick dashboards for users)

All of the above work perfectly, but at some stages (especially when you are working on a GP Terminal Server) some of these components would halt GP for a while, not allowing users to do anything.

One classic example is Microsoft Outlook. We recently migrated our Email Services to Microsoft Office 365. For those users who use Thin Client to on their day-to-day job, Terminal Server is THE destination. Quite obviously, Terminal Server has got Outlook installed.

Before Office 365, Outlook profile used to connect to our on-premise Exchange Server. After migration, we had to change the profile from Exchange Server to Office 365 hosting server.

Whenever a user logs on to Terminal Server and opens GP, GP will halt for at least 3-5 mins to get this Outlook configured. I am getting calls from users (yes, even now) that their GP session does not work and they are not able to do anything. Following icon shows up on their taskbar when this happens:

It didn’t take me much time to realize that all these users had added Microsoft Outlook on their GP homepage.

“Do we really need Outlook on GP homepage?” is a question to be asked to oneself. Yes, Microsoft has integrated your ERP with one of the most used product by maximum business users. Agreed. But is that alone a reason for you to use it? Let’s not get into that argument in this post, anyways.

I had to take a stand and remove Microsoft Outlook from those users’ homepage. But then, I don’t know, right now, about how many users out of 100+ total users have added Outlook on their homepage.

Certainly not without the help of a SQL query.

The table where this information has got stored is SY08100 (Technical Name: syHomePageLayout). In this table, the column SectionID denotes the sections which are available for a user to add to his/her homepage. Following is the legend:

1 – To Do (Reminders/Tasks/Cues)
2 – Microsoft Outlook
3 – Metrics (quick dashboards for users)
4 – My Reports (links to users’ favorite reports and smartlists)
5 – Quick Links (links to users’ frequently used windows)
6 – Connect (links to Online Resources for GP such as Forums, Blogs, etc.)

Typically, for each user, this table will contain 6 rows, each row denoting each of above section. SY08100 also contains a column named Visible. This denotes whether or not to show a particular section on GP homepage.

For instance, if user SA has setup his GP homepage to show To Do, My Reports & Quick Links to show up, the following will be SY08100 records:

So, if I want to hide Microsoft Outlook from all users’ homepage, I just have to execute the following SQL query on DYNAMICS database:

UPDATE SY08100 SET Visible = WHERE SectionID = 2

We must also understand one thing. Users can always add this again thru’ Customize this page… option. So this is NOT A PERMANENT SOLUTION.

Whenever there is a necessity, as in my case, you can certainly rely on this SQL method to do a homepage layout mass update.


Customer Credit Summary: Average Days To Pay

I received an email today with a query on GP’s “Average Days To Pay” on a Customer’s Credit Summary.

Query is: How GP calculates Average Days To Pay for a customer?

According to GP’s Receivables Management user manual:

After a customer has paid his or her first invoice, the average days to pay (ADTP) is calculated based on the number of invoices a customer has, the time taken to pay the first invoice, and the time taken to pay the most recent invoice.

The formula for calculating the average days to pay is: 
ADTP = (Current ADTP) x (Number of Invoices) + (Number of Days Taken to Pay Most Recent Invoice) / (Number of Invoices + 1)

The time it took to pay the first invoice would provide the initial value for the Current ADTP. Any later invoices paid by this customer will provide the values for the number of invoices and the number of days taken to pay the most recent invoice. The ADTP calculated on the customer’s initial invoices then becomes the “Current ADTP.” You can use this value when you recalculate the ADTP for later invoices.

There are two ADTPs; LTD (Life To Date) and YTD (Year To Date).

The important point that you may have to remember is that Average Days To Pay YTD will be calculated only based on Amounts Since Last Close.


ERROR: A valid exchange rate could not be found. Choose Continue to open the Exchange Rate Entry window, where you can enter or select a valid date.

The following was the message one of the users received when she tried entering a Receiving Entry.

The currency was GBP and there was indeed a valid exchange rate that’s been uploaded on a daily basis. The rate validity duration is set to “Week”. That means, I have a rate which is valid till 23-Nov-2011.

The user mentioned to me that when she uses a particular batch, this message is thrown up. If any other batch is used, it picks up a rate without any issues.

When I checked the batch, I found the reason behind the issue.

Posting date of that batch was in future. This user had created a batch with the last day of this month as posting date, for which I don’t have a valid rate in the system. When I select this batch and then enter the currency, GP does not find a valid rate and throws above error message.

By default, the exchange rate for a transaction is taken from either the transaction date OR the batch posting date, if a batch is assigned before the currency is entered.

The user do not post the batch itself and always post individual transaction. Keeping this on mind, I had to recommend her in changing the posting date of that batch as first date of the month, so we always have a valid exchange rate for all currencies.

It’s quite a simple concept, but is very VERY critical.


Dynamics GP Developer Insights – Series of Posts on Developing For Dynamics GP

Brian Roney and his team at Microsoft (Dynamics GP Technical Division) is going to post series of articles which will enlighten us on what’s been going on with Dynamics GP development.

Series titled as Dynamics GP Developer Insights.

GP developers across world, stay tuned for a brilliant series.


Fixed Assets Suffix

Fixed Assets Suffix is quite confusing, at least that’s what I was thinking about.

According to GP:

When you enter an asset ID, you also must enter a suffix for it. The asset ID is used throughout Fixed Asset Management to identify assets; the suffix is used to identify components of assets. The default suffix is 1. You can accept the default suffix or enter or select a different one.

For someone who do not understand what exactly it is, I would like to simplify.

Consider the following scenario:

I am purchasing 10 laptops of identical configuration for my staff. Following will be my asset record:

Suffix: 1
Acquisition Cost: $3000 ($300 per laptop)
Quantity: 10
Location: My Office
Physical Location: IT Stores
Custodian: IT Manager or IT Department (whichever way you would like to perceive)

Now, my IT department allocates one laptop to an employee belonging to Finance department. I must now pass an Asset Transfer for one laptop from this asset ID. Once I transfer one laptop, following will be my new & updated asset records:

— For all other 9 laptops that are not allocated yet
Suffix: 1

Acquisition Cost: $2700 ($300 per laptop)
Quantity: 9
Location: My Office
Physical Location: IT Stores
Custodian: IT Manager or IT Department

— For 1 laptop that got allocated to an finance employee
Suffix: 2
Acquisition Cost: $300
Quantity: 1
Location: My Office
Physical Location: Finance Department
Custodian: Employee to whom this laptop is allocated to

Asset Suffix will be incremented as and when you do a partial transfer. It is not advisable to use Asset Suffix for non-identical assets. For instance, if you purchase 10 laptops but non-identical configurations, it’s not standard practice to create one asset ID with quantity 10.

Hope this helps.


Smartlist Favorite Visibility

Most of the queries that I am receiving from my users regarding Smartlist is like below:

1. Why my Smartlist Favorite is being shown to all?
2. I created a Smartlist Favorite in one company. It is not showing up in other company. Why?

Answer is quite simple and straightforward. They had not selected an appropriate *Visible To* option.

Smartlist favorites can be created with either one of available four different visibility options.

1. System
2. Company
3. User Class
4. User ID

Shown below is how it looks like:

1. System: Visible to all users across all companies.
2. Company: Visible to all users, but visible only on that particular company.
3. User Class: Visible to all users under the user class in which the user who creates this favorite is, but visible across all companies.
4. User ID: Visible only to this user, but across all companies.

Related PostChanging a smartlist favorite’s visibility thru’ SQL


Sales Quote Line Item Cancellation

I recently took a training for users on GP SOP. Training was a success. There were couple of users who are already into GP for quite sometime now (4 years precisely).

Interestingly, when I was explaining about Quantity Quoted Vs. Quantity To Order Vs. Quantity Cancelled, one user said they had never used Quantity Cancelled at all to record any line item cancellation. That was quite a surprise to me.

Consider this scenario: A customer enquires about couple of products and we prepare a quote and send it to them. Out of two products, they confirm only one and we are to transfer this quote with only product. There are three ways we can adapt:

1. Delete that unwanted product line on the quote and transfer this quote to an order (Bad Approach).
2. Transfer the quote in totality and from Sales Order delete that unwanted product line. (Bad Approach).

Above two approaches do not give us the fair picture on quote conversion. Considering the fact that GP SOP do not maintain a revision history of each document, it’s not advisable to follow above steps.

3. Cancel the quantity, by removing “Quantity To Order” and entering “Quantity Cancelled”. Transferring this quote will not transfer unwanted product to Sales Order.

By doing this you are retaining originally quoted products list and transferring only those which your customer wanted, also for your self-assessment on how you have performed in winning the quotations. Most recommended approach.

Of course, we would be forced to add/remove product lines from Sales Order for some reasons. But at least, quote conversion would give us facts at the time when we won the quote for that particular customer.

UPDATE: Siva has written a post which expands my post into different modules and how we can maintain records for better and painless auditing.


Check Links for Analytical Accounting in Microsoft Dynamics GP 10 & 2010

Inside Microsoft Dynamics GP blog has got a post that cautions us about two important things:

1. Don’t run Check Links process for AA at this time on versions GP10 and GP2010.

2 Apply latest Service Packs or Hotfixes for Microsoft Dynamics GP (versions 10 and 2010) to get AA related latest fixes.

More on this here: Information about Check Links for Analytical Accounting in Microsoft Dynamics GP.


Sales Document Copy Feature

Consider this scenario: You have a sales quotation with nearly 100 line items prepared for a customer/prospect. Another customer/prospect inquires about same set of items. Are you going to enter that 100 line items again on the new quotation? If your answer is YES, then you are seriously wasting time.

Sales Document Copy feature is a boon for those who deal with sales documents with more than 25 line items (or even 10 that matters) on a daily basis. Below is the Sales Document Copy window:

This is not a new feature and is existing for long time now. So I am not going to explain anything new obviously.
I just thought, for those who have not used this feature or not knowing about it till now, it’s a great one to be availed.
Just make sure that you read all options out there and select only relevant options for you. For instance, you may not want to *Copy Markdowns* from source document to target document. You may have given a discount to previous customer which you may not want to expose it to new customer.
Trust me, this feature saves immense data entry time and eases out sales document preparation.