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, so I can post my thoughts here. 

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, use this section to your betterment and very importantly share your thoughts with (and thank) him for his work.

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

VAIDY

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 = 0 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.

VAIDY

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.

VAIDY

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.

VAIDY

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.

VAIDY

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:

Asset ID: LAPTOPS
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
Asset ID: LAPTOPS
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
Asset ID: LAPTOPS
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.


VAIDY

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 Post: Changing a smartlist favorite's visibility thru' SQL

VAIDY

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.

VAIDY

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.

VAIDY

Auto Assign Serial / Lot Numbers on SOP Document

When do we select this option in SOP Setup? Is there any valid requirement that would arise so we setup SOP that on a Sales Document, Serial/Lot numbers automatically get assigned?

I am talking about *Auto-Assign Serial Numbers* and *Auto-Assign Lot Numbers* options in Sales Order Processing Options Setup window.

As a normal practice, I would always keep it unselected. I would like to make sure that I handpick items with specific serial numbers so I won't let the system pick recent ones first and retain old ones.

Simply speaking, I would always want to sell old items first to make sure that I don't leave it in my stock as unsold. Like a FIFO.

There are prominently two queries that arise on my mind:

1. How GP automatically takes Serial/Lot numbers for a particular item, if we select the above options? Does it follow FIFO approach or does it randomly pick some serial/lot number?

2. Is there a practical scenario where we require these options selected?

I would be grateful to receive your inputs as comments.

Thanks in advance.

VAIDY

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.

VAIDY

Microsoft Dynamics GP Web Client - Teaser Articles

Matt Landis shows us a great glimpse of Microsoft Dynamics GP Web Client. Awesome to see GP's future.

Following image is from Mariano's blog article, Microsoft Dynamics Convergence Atlanta 2011: Day 2 Morning:

The web client seems to be getting developed using Microsoft Silverlight. There are much more news and announcements to come from Convergence 2011.

Trust me, it's gonna be fun in an year from now with Microsoft Dynamics GP.

VAIDY

Failed To Create Object - OLE Error due to Adobe Reader X

Something seems to be amiss with Adobe Reader X and Dynamics GP. Yesterday we upgraded Adobe Reader to version X on our Terminal Server, where our Dynamics GP application resides.

Till then, users were able to attach PDF files to GP records using OLE Object Container. But post upgrade, all users are facing the following error message whenever they try to attach a PDF file or view an existing PDF attachment:

Looks like I am not the only one to face this issue. There is a community forum post which raises the same question. Link to that post: Errors when opening or creating PDF OLE Attachments in Dynamics GP notes.

Not sure how to resolve this at the moment, other than retaining older version of Adobe Reader.

VAIDY

Microsoft Dynamics GP "12" To Have For Support Mac Computers

** Excellent news from David. Microsoft Dynamics GP "12" to have support for Mac Computers.

Exciting to say the least. And I am dying to see GP "12" feature additions and enhancements.

VAIDY

* This was an APRIL FOOL DAY's post from our beloved David Musgrave. Alright I did not know that in first place. I was damn so excited that it didn't strike my mind.

David, you caught me there.

The Future of Microsoft Dynamics GP Technologies - Mariano

Mariano highlights 5 coolest technologies for Microsoft Dynamics GP in coming years to watch out for. I can't agree for more, since I am already seeing 3 of the highlighted 5 currently.

Just to list you those technologies:

1. Microsoft Lync
2. Microsoft Silverlight
3. Microsoft Visual Studio Lightswitch
4. Cloud Computing
5. Microsoft Dexterity (yeah you did hear it right)

And to know why the above are cool, check out his post: The Future of Microsoft Dynamics GP: 5 Cool Technologies You Should Watch For (and Learn) in the Coming Years

VAIDY

Last Sale By a Customer & Item - Victoria's SQL View

Victoria's latest SQL view retrieves the last sale by customer and an item. With little tweaks here and there, this SQL view will serve us immensely to show various information pertaining to Customer and/or Item sales.

Thanks Victoria for this view. This has come especially when I am working on some dashboards and reporting. It's surely going to help me deriving some important reports.

VAIDY

Microsoft Dynamics GP 2010 R2 - Business Portal External Lists

Looks like an excellent feature addition. So far, we had to rely on Extranet Services for any edit from Business Portal. This Business Portal External Lists feature now fills that gap. We have to wait and see how much we can do using this External Lists.

Errol introduces this feature to us on recent Inside Microsoft Dynamics GP post.
VAIDY

GP 2010 R2 - Substitute Fields for Email

This is going to be another interesting feature. Inside Microsoft Dynamics GP announces another feature added to GP 2010 R2, which is Substitute Fields for Email.

Of course, this is going to be a boon to End Users, but let me also add that this would not be possible till someone knows about how to browse thru' and learn GP Fields and the way it is designed. Which puts them in front of a whole new training session.

The very concept of Resource Descriptions in GP is to educate Users to learn about GP Tables, Forms, Windows & Fields, so that they can do some on-spot Reporting or Windows modification. Having said that, not all End-Users are going to be interested in this learning experience, especially when they are suppose to concentrate on their day-to-day tasks.

Nonetheless, this is going to be one heck of a feature to look at.

VAIDY

Microsoft Dynamics GP 2010 R2 - 50+ New SSRS Reports

Yeah, we heard it right. We are going to have 50+ new SSRS Reports.

Inside Microsoft Dynamics GP excites us with this post: Feature of the Day: New SQL Reporting Services Reports.

I am more interested in knowing Field Service Reports, since that's where GP lacks a bit in terms of realtime reporting. I sincerely hope R2 fills that gap to most extent. Of course, requirements from different customers would always be different.

VAIDY

Microsoft Dynamics GP 2010 R2 - Report Builder Integration

The latest Feature of the Day from Inside Microsoft Dynamics GP gives us a glimpse of what is going to be a terrific feature for all customers.

All Homepage Metrics dashboards are SSRS reports on the background. And I used to wonder how I could customize that to cater for Customer's requirements. The new feature, Report Builder Integration, is going to be a boon to all such consultants (and of course for all customers) to view Metrics Dashboards accordingly to their own likes and dislikes.

For more information, read the post; Feature of the Day: Report Builder Integration.

VAIDY