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.

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 = 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 PostChanging a smartlist favorite’s visibility thru’ SQL

VAIDY