SCM Metrics – I: Back Orders

Some organizations would not just want to use Dynamics GP for their Financial Reporting purposes. They would also want the Dynamics GP to meet their Operational Reporting needs. Among those Operational reporting needs is the most important chunk, Supply Chain Management (SCM). Dynamics GP offers several reporting features to accomplish these needs. One such feature is Smartlist. I’d discuss several SCM metrics in my upcoming posts and I’ll discuss about Back Orders in this post. 

Back orders: Back order can be defined as an unfulfilled customer order. Back orders are created when a SKU(s) whose current inventory level is insufficient to meet the demand. The more the back orders pile up over a period of time, be it in a manufacturing company or a Trading company, implicates that the company is not able to meet the demand on time with its current available inventory. Tracking backorders efficiently ensures an adequate supply of inventory in time equivalent to the current and future demand of the SKU. So, How should we be tracking it in Dynamics GP is the question.

1.Back orders can be tracked by each customer, division and Total company as a whole.
2. Aged Back orders that went past due the Requested Ship Date

This can be easily achieved in Dynamics GP using the Smart list Builder using Sales Transaction Work Table, adding a restriction to return only back orders as well a Calculated field to return days past due from the current date and Requested Ship Date.

If we need to track the back orders by each SKU, their QTY or value, the same smartlist can be built using Sales Transaction Amounts Work Table. I built two sample smartlists for the same that can be downloaded here.

Remember that the the way backorders are tracked vary from company to company. Some companies might just count items that are not confirmed and past the requested delivery date while some companies may also count those items with inventory confirmed, but past due.

How Dynamics GP BI can help drive Sales in New economy

Pam presents us a whole lot of information that includes a partner focused Web seminar video on driving existing customer sales around Business Intelligence. This post has also got all references to information referenced in the web seminar video.

Here is the link


Optimize security using Field Level Security

During an Implementation of Microsoft Dynamics GP, My client asked me how can I hide the Unit Cost field in SOP Transaction Entry window for certain users (not for all users). If the client said he needed it for all users, my job is quite simple, as to just go to global setting of SOP wherein I unmark the Display Item Unit cost option. If I want to achieve the same in User Security, I can’t because in User Security, I can’t limit the access by fields.Then, I was thinking of inserting a small VBA code in each client machine for hiding this field. Is this the only option? Certainly Not. This is where the Field Level Security(FLS) comes into picture. Field Level Security (Termed as Advanced Security in Dynamics GP V9.0) is an additional security layer(module) in Microsoft Dynamics GP that provides the ability to restrict access to any field, window or form. It does offer several options in terms of access restriction through Security Modes. Let us see what security modes does the FLS possess

Password Before

Access is restricted for a field by password before field is accessed

Password After

Password must be entered for any changes to field to be saved

Warning Before

Access will be denied for a field combined with a warning

Lock Field

User can’t use or modify the field

Disable Field

Field will be displayed but will not be available

Hide Field

Field wont be displayed

Password Window

Window level security – Must enter password while opening the window

Disable Window

Window level security – Only “sa’ password is permitted for displaying the window

Password Form

Form Level Security – Must enter password for accessing the form

Disable Form

Form Level Security - Only “sa’ password is permitted for displaying the form











Based on the selection of the Security Modes here in this FLS Security Maintenance, the respective access restriction would apply to the specific user(s) and specific company or companies. Out of the 10 security modes available, 4 modes are actually applicable for windows and Form Level security. Password window makes sure when a user tries to access a particular GP window, it asks for a password unless otherwise the access would be denied. Disable window makes sure the user doesn't have complete access to a specific GP window. Similar case is for password Form and Disable Form.

In another example where I configured a Sales process in SOP Transaction entry for Service items. In this case, The user don’t need to see the fields related to Req Shipping date, Actual Shipping Date and so on. I used FLS to completely hide those fields using “Hide Field” security mode for all users. Now, Assume we have a scenario where the client says I need select Users to view the Unit cost while entering the Purchase order but they should not have access to modify the same. In this case, we can set security using “Disable Field” mode. Assume, the client says select users should not access the General Entry screen itself, it can be either set up at the User Security level itself or at FLS using “Disable Window” mode.

There are several other possibilities that we can make use of, in Field Level Security Feature so as to ensure an optimum security layer for any organization.



Web Services Install: Fatal Error during Installation

During Web Services for Dynamics GP installation in windows 2003 Server, we receive a Fatal Error during installation, if it doesn’t meet certain pre-requisites. I’ll cover the possible troubleshooting steps in this article.We also see Rolling back IIS Metabase transaction, then the installation fails giving fatal error. We also might receive an error “The user account specified for the Dynamics Security Service worker process is invalid”.


 Possible Resolutions:

1. Ensure Full permissions are assigned to the ASP.Net Temp folder to IIS_WPG group before installing Web services. The default location would be C://Windows/.Net Framework/V2.0.5727/Temp folder.
2. In the web server running IIS6.0, Ensure Directory security is set to Integrated Authentication and Unmark the Anonymous Access.
3. If installing to Default Website, Ensure the port-80 is free and unused by any other site
4. If SQL server and the Web server where Web services are being installed on different server boxes, Ensure that Network DTC is enabled in the web server. To check  or To enable Network DTC, Go to Add/Remove Programs, Add/Remove Windows components, Application Server | Details | Network DTC.
5. Make sure Web services is being installed to a standard IIS web site that does not have anything on it like SRS, CRM etc. In addition, this site cannot be extended with SharePoint to turn it into a web application
6. Ensure you’re installing on a computer with Domain Administrative rights if multiple applications exist, or else, if all applications like GP, Business Portal will be installed on a single server, then local admin rights are enough
7. Web services rely on ISO currency codes so, prior to installing web services, all companies in GP must be setup with currencies mapped to their ISO codes as well a Functional currency setup is a **MUST**.
8. Ensure you are installing the web services with the latest service pack.

Besides all these troubleshooting steps, I’ve seen an issue in Dynamics GP Partner forums where a forum member couldn’t resolve the same with the above troubleshooting steps. Here is the resolution that was provided to the member by the Microsoft Support. Possibly, it’ll help us all sometimes to ensure these are taken care which is why i thought I'll include the same for everyone’s knowledge.

1. It was found that some databases were missing Field Services modules (SVC*) tables. Field Service is a core module that is automatically installed and initialized, so these tables would exist in every GP install unless they had been manually removed or the Dynutils.set modified to remove references to it before the GP company was created.The Web Services install try creating SQL views and\or stored procedures that make calls to the SVC* tables.  When those will not be found the loaders fail and return the fatal error. To resolve this, we might need to go through Microsoft Support route to recreate the tables in all databases.
2. If enter the domain name in the credentials window with the .local or .com in the domain name, the install fails immediately. If you don't enter the FQ domain name in the credentials window, the install proceeds further but eventually fails again. This is a known issue with web services that we can’t install it to .com or .local domain which I suppose that it is slated to be resolved in future versions.  

Mobile Sales Integration with Dynamics GP

One of the finest strengths of Dynamics GP is its ability to integrate with a variety of applications.Among those integrations is its ability to integrate with Mobile Sales applications. In today’s world of automation, Sales process play a key role in ensuring the financial stability of any organization that it is its backbone. Which is why, Sales processes in Enterprise applications have always been demanding to technology upgrades constantly so as to achieve an economy of operation in terms of reducing the cycle time for the process. In this article, I’d focus on a Microsoft Dynamics Certified Mobile Sales Integration solution available from jLanmobile.  jLanMobile Sales for Microsoft Dynamics GP is a complete PDA based mobile sales software solution. The solution integrates with Receivables, Sales Order processing and Inventory data in Dynamics GP.

Below is a PDA screen where the Dynamics GP Items can either be entered manually, through Lookup or a Barcode Reader for Sales Order. jlanmobile_1 
This is the order confirmation screen in the PDA with an electronic signature of the customer specifying his acceptance


Here is a sample PDA screen how it shows a Salesman’s Route. Green Color indicates that the Salesman has completed the visit, Yellow means skipped the visit, Red means the visit is pending.


The order made through PDA is now synchronized with Dynamics GP. Synchronized Sales document looks like this in PDA as well as GP



jLan has a mobile server that manages the entire process. Here is a screen captured from jLan Mobile server depicting the jLan Route Manager console showing the daily activities of all PDAs.

jlanmobile_6  (Click the image to enlarge)

This solution also supports the Dynamics GP Serial Numbers allowing the Salesman to sell by Serial Numbers.
(Disclaimer: This article objective is just to bring to the attention of readers interested in Dynamics GP Application on how a Mobile Sales solution integrates with Dynamics GP. The author has no business relationship with Solution vendor either directly or indirectly. )

Dynamics GP New Scalability Test Report is out

A New scalability test report was out on Microsoft Dynamics GP. V10 SP2 along with Windows Server 2008 and SQL Server 2008. The results of the test that a whopping 3.28 Million business transactions and 10.66 million transaction line items are processed over a period of an 8 hour day.

The scalability Test report is available for partners by clicking here.

For a Complete news, Visit the link below


Project Accounting – Retainer Fee Questions

Here in this post is a collection of Retainer Fee (Project Accounting Module) FAQ that would be of interest to everyone.

. In billing entry, when attempting to add a tax schedule ID to a retainer type fee, we get the message:  “You can not enter a Tax Schedule ID for retainer fees”

A. Tax Schedule ID for retainer fees cannot be entered in the Billing entry window. To add tax to a retainer fee billing invoice we need to go to Tools | Setup | Project | Billing, enable the option Retainer Fee taxable and then save the change. Then go to the fee setup window, select the retainer fee that is being used, and make sure that the tax option is either set to taxable and a tax schedule is inserted or the tax option is set to based on customer. The retainer fee billing invoice will now include tax when it is raised after these changes.

Q. Is there a way to apply the Retainer after the fact?

A. There isn’t a way to apply the retainer to the billing after the fact. The only workaround to correct this would be to enter a T&M Return for the billing transaction, then reverse it out, and then re enter the billing transaction again. While entering this billing transaction we need to make sure that we click on the More Info button and apply the retainer to that billing transaction.

Q. When a client pays a retainer invoice using a credit card, the 'On Account' amount doesn’t update?. 

A. When a Customer pays a Retainer Fee using the credit card, it updates the On Account field. The credit card amount for Retainer need to be entered in Billing Entry | More Info | Credit Card amount. Once the amount in credit card field is entered, it opens the credit card Payment entry field where we need to input the Credit Card ID and once the Billing Entry is posted, the On Account Amounts are updated. There is also a possibility of amounts not getting updated If security to the Cash Receipts window in Receivables module is not granted to the user. For more info on Security in Cash receipts window, click here for my earlier post.

Q. How to transfer a retainer fee from one project to another?

A. To transfer a retainer fee from one project to another follow these steps, follow KB article 857048

Q. If a Retainer is partially applied, How can it be unapplied?

A. If the retainer has been paid and partially applied to a Project Accounting invoice then we can follow with the below steps.

1. Enter a payment in Receivables Management for the amount that is to be returned.
2.Apply the same to the unapplied retainer billing which will increase the On Account amount.
3.Then enter the negative retainer billing which would decrease the On Account amount. 
4.Proceed to enter an invoice directly in Receivables Management for the returned amount
5.Apply the credit created in step 3 to the invoice from step 4.


Reconcile Bank Statement directly with GL

Can it be just possible? The standard GP functionality reconciles the bank statement against the Checkbook Register. So, How can it be possible to reconcile a bank statement directly with General ledger open transactions? Same question came to my mind when i was writing the scope for a large Client whose business process demanded a Reconciliation module that actually reconciles the bank statement against the GL transactions in Dynamics GP. The client said they also need the reconciliation to provide complete reconciliation history. Then came the Advanced Bank Reconciliation module from Nolan Computers as a big rescue.I’ll cover the functionality of this module in brief in this post just for those clients who are looking for an advanced version of bank reconciliation.

1. Transactions are read directly from the General ledger
2. Can track multiple General Ledger accounts for the same Bank
3. Enhanced Multicurrency support, transaction currency needs to be same as the bank account
4. Flexible import definitions for electronic as well as almost all forms of bank statements.
5. Complete Transaction history available
6. Aging of unreconciled Transactions

How it works?
I.Just do the necessary setups in the ABR Setup window, Bank Setup and Reconcile Rules windows.
II. Setup Import format for the Bank and do an import definition
III. Extract GL Transactions from Dynamics GP GL to ABR module.

IV. Import the Bank Transactions into ABR using Statement import
V. Reconcile the transactions with one click of a button

If any business requirement is such that it requires a reconciliation module to reconcile directly the GL with bank statement transactions, this is one good solution to be looked at. Moreover, its multicurrency capabilities as well as attaching multiple GL accounts to one bank account make it more productive for businesses who look for advanced reconciliation in Dynamics GP.


Advantage Microsoft Dynamics for Sage Customers

According to an article posted in MSDynamicsWorld, An independent authority for news and views on MS Dynamics, Microsoft is inviting Sage customers and partners to consider available alternatives from Microsoft, including Microsoft Dynamics Enterprise Resource Planning (ERP) solutions. Sage’s largest channel partner for the past two years, MIS group suddenly shut down citing economic crisis.

This is a major opportunity for Microsoft Dynamics Resellers in particular.

For full article posted on MSDynamicsWorld, click here.
Note: You might require to login to the site for full article access

Workflows Installation Error

While trying to install Workflows for Dynamics GP, we might encounter the following errors.

Microsoft.Dynamics.Workflow.InstallSystemSecurityMetadata has encountered a problem and needs to close.

Microsoft.Dynamics.Workflow.InstallSystemSecurityMetadata returned –532459699 




This error comes primarily if we install MOSS 2007 Enterprise Edition as a basic installation instead of selecting Advanced installation. Only when we select the Advanced installation, the SharePoint 2007 would be installed as a “complete installation”. To verify if SharePoint was a complete installation, go to SharePoint 3.0 Central Administration site, Click Operations tab, then go to Topology and Services area, Click Services on server. Here we should be seeing web server for medium server farms should be marked. otherwise, it need to be treated as basic installation. If it is basic installation, we need to uninstall the MOSS and then install it again in Advanced mode to install it as “complete installation”.

There are other possibilities as well for getting the same error that are clearly detailed with appropriate resolutions in KB942001

Visual Studio Tools for Dynamics GP lessons from Mariano

Mariano Gomez started an interesting series of articles on VST for Dynamics GP. Check it out for yourself in this link


Another link related to this


Item Purchasing Controls

Most of the mid-size and large-size companies would be interested to have an efficient control on their purchasing processes. Among such common controls are the ability to restrict the Order quantity by Minimum order quantity and maximum order quantity. Minimum order is the minimum quantity of an item that can be ordered. Maximum order is the maximum quantity of an item that can be ordered. Some vendors require that items must be ordered at least the minimum quantity specified by them. In these cases, having a restriction in terms of Minimum order and Maximum order helps efficient purchasing process in any organization. Dynamics GP has many such controls available as part of its Purchasing and Inventory series. Some of the salient features of Item Resource Planning in Dynamics GP include

1. Ability to restrict purchase order by Minimum and Maximum orders
2. Calculate the Average Lead time automatically
3. Suggested Purchase order generator
4. Selection of Vendors based on lowest cost or shortest lead time
5. Replenishment of Items based on Order point quantity, Order up-to level, Vendor EOQ (Economic Order quantity)

In this post, I’ll show how we can setup the minimum and maximum order quantities and how it controls the purchase order entry.

In Dynamics GP V10, minimum order and maximum order quantity is tracked per Item-vendor combination (Purchasing => Cards => Vendors). In case we would like to order the same item from multiple vendors, the minimum and maximum order quantity has to be specified for each vendor as shown in the screen below


In the screen above, the minimum order is set at 25 units while maximum order is set at 100 units. So, whenever the purchasing department raises a purchase order for this item and the vendor, the system will not allow them to process the PO for a quantity less than the minimum order or greater than the maximum order quantity. It gives the following message whenever we try to order an item less than the minimum order in the PO line entry.


Similar message would be displayed whenever trying to order more than the maximum order quantity. Maintaining minimum and maximum orders efficiently in Dynamics GP results in effective Supply Chain Management.


Login failed for sa while launching GP

A user reported that when Dynamics GP V10 is installed on a server and trying to login as ‘sa’ user for the first time in GP, it is returning the following error message.

You’re attempting to log in from a data source using a trusted connection. Update the SQL server settings for this data source to disable trusted connections and try logging in again.”

Logging into GP Utilities has no issue and everything runs fine while running Utilities. However, the error only surfaces when launching GP, the user reports.


Check the ODBC settings. This will be caused mainly when a SQL Server is selected as the Data source Driver instead of SQL Native client. Delete the old ODBC and setup a new ODBC with SQL Native client as the Driver for the data source.


Analytical Accounting Uninstall – Error in Smart list Favorites

In Dynamics GP10, When we uninstall Analytical Accounting feature in Add/Remove programs, we might get the following error while trying to open default Smart lists like Accounts, Customers, Vendors,Items. The same error will be displayed in the smart list lookups in the Cards windows for Accounts, Customers and Vendors.

This Favorite Contains a column from a product(3180) not installed on this workstation. The following columns will not be displayed:Node



To avoid this message, Go to Administration => Setup => System Setup => Smart list Options, Select Accounts in Category drop down, Unmark the Node column.


Continue the same for Customers,Vendors, Items categories if the same error is popping up in smart list lookups or smart list views.

Note: This error was replicated on Dynamics GP V10 SP3 (10.00.1193)

Multicurrency Revaluation: Post Results to Account Vs Post to Offset Account

Whenever we set up an Account in Financial => Cards => Account, We’d go to Account Currencies window and assign the Multi currencies in case the account will have transactions originating in currencies other than Functional Currency. Besides assigning the Multi currencies here, it also involves to check whether we need those currencies to be revalued or not. If Yes, we need to mark the Revalue Account option and then choose to Post Results to Account or Financial Offset Account. Here, Results mean the the Realized gain / Loss or the Unrealized Gain / loss as the case may be, resulting out of Revaluation of Multi currencies in the Financial Routines ( Financial => Routines => Revaluation)


The basic difference between choosing Post results to an offset A/c and post results to Account is If you choose the Offset a/c, the revaluation results would be posted to a separate account that is defined as Financial Offset A/c in Posting Accounts setup (Administration => Setup => Posting =>Posting Accounts). On the contrary, If you opt to post the revaluation results to Account instead, the revaluation results would post directly to the same Account. For example, You have an account "000-1100-00" assigned a MC US$ and you marked Revalue, Post Results to Account. So, When a Revaluation occurs, the gain/loss would post to 000-1100-00 itself. 
Let us consider three accounts for our example.
1. Posting A/c - Cash (CAD) A/c - 000-1100-00
2. Posting A/c - Financial Offset A/c - 000-1199-00
3. Posting A/c Realized Gain/Loss A/c - 000-9999-00(let us use the same as Unrealized Gain/Loss as well). 
4.Consider USD as your functional Currency. 
5.Consider we posted a GL Entry on 01/01/2009 for CAD1000(Exchange Rate - 0.50) into Cash(CAD) A/c. Now, we want to revalue  Cash(CAD) A/c as of Today 03/07/2009 (Exchange Rate - 1.00). So, Realized Gain in this case would be (CAD1000*1.00 - CAD1000*0.50) = USD500 Gain.

Option1: Assume we set up the Cash(CAD) A/c to Revalue A/c to Account - USD500 gain would be posted as follows

000-1100-00 A/c     Dr   US$500 (Cash (CAD) A/c)
000-9999-00           Cr   US$500 (Realized Gain/Loss A/c)

Option 2: Assume we set up the Cash(CAD) A/c to Revalue to Financial Offset A/c - USD500 gain would be posted as follows

000-1199-00          Dr   US$500 (Financial Offset A/c )
000-9999-00         Cr    US$500 (Realized Gain/Loss A/c)

IIS Website is not displaying in the drop down while installing Business Portal

While installing Business Portal for Dynamics GP, We might not be able to see the IIS website in the drop down where we’d want the Business Portal be installed to.


This could happen commonly if you’re installing the Business portal on a computer logged in is not the Site collection administrator in Windows SharePoint services (WSS) or Microsoft Office SharePoint Services (MOSS).

There are various other reasons as well  that are outlined in KB947927.


Customer/Vendor Transactions

In most businesses,we come across the situation where a vendor is become a customer to us and vice versa. In these types of cases, the business needs demands us to consolidate the customer and vendor activity so that the statements reflect true and fair view of the transaction activity. Dynamics GP provides standard functionality to take care of Customer/Vendor Consolidation.

Features of Customer/Vendor Consolidations include:

1. Auto Create Vendors and Customers
2. Consolidate balances in Payables or Receivables for a single company
3. Assign Relationships between existing customers and vendors
4. Easily Track the Consolidations using Cross-Module Link
5. Vendor Balances and Customer Balances can be consolidated including National Account Customers
6. All Open Payables/Receivables debit &credit documents can be applied against each other to consolidate the current balances 

Setting up Customer/Vendor Consolidations is quite easy to start with.
1. Assign a Suspense Account that will act as an offset account between the Receivables and Payables
2. Assign Default Vendor and Customer Classes to be mapped when a vendor or customer is auto-created using Customer/Vendor Maintenance
3. Map the Vendor and Customer Relationship

I.Setup the Customer/vendor Consolidation in Administration => Setup => Company => Customer/Vendor Setup


In the setup, Provide the Default Suspense Account that can be a posting account or an allocation account. This account is used whenever the customer/vendor transactions are posted as offset account.

In the ID option, we have two options. Vendor  ID = Customer ID or Vendor ID = Next Default Vendor ID and vice –versa. Remember these options are available only if Auto-create Customers/vendors is marked.

II.Now, We can go to Purchasing => Cards => Customer/Vendor or Sales => Cards => Customer/Vendor and setup the Relationship between Customers and Vendors. For our example, Assume we have Customer – 00ABC who is also vendor to us. Once we enter 00ABC in Customer ID, Click Create Vendor, Vendor ID – 00ABC will be auto-created.

customer_VendorRelationship Once the Relationship is established, we can post the customer/Vendor Transactions.

III.Now, Assume, we have two transactions, one in payables for $1000 for the Vendor 00ABC, another in Receivables for $1000 for the Customer 00ABC. Now, we can consolidate the same using Customer/Vendor Transactions.  (Sales => Transactions => Customer/Vendor Transactions)


Once the Customer ID 00ABC is entered and select the Consolidate Documents, it’ll list all transactions for 00ABC that includes both Payables and Receivables. If Transfer to Payables is selected, Only the Receivables transactions would be displayed and If Transfer to Receivables is selected, only Payables transactions would be displayed. Now, once the consolidate option is selected and required documents are marked and posted, it’d update both Receivables and Payables documents with the prefix mentioned in Setup.

Default Distributions for invoices consolidation would be as follows

In RM Module:

Default Suspense A/c                     Dr  $1000

   To Accounts Receivable A/c               $1000

In PM Module:

Accounts Payable A/c                         Dr $1000
       To Default Suspense A/c                   $1000

Now, We can go to Purchasing => Inquiry => Transactions by Vendor, Enter the Vendor ID 00ABC, Select the Payables Transaction Document and click the Document Number link that will open the Transaction Inquiry window.

Customer_Vendor Inquiry

Clicking the Description Expansion button would open the related Receivables Document Inquiry window as below

Customer_Vendor Inquiry1


Extender Inquiries

I’d shed some light on how we can link Extender Inquiries to Extender windows. For this purpose, I’d take the Payables Transaction Entry window.

First, let us create an Extender window for Payables Transaction Entry. For this, Go to Microsoft Dynamics GP Menu => Tools => Extender, Click New window that will open the below window. I’ll fill the fields with the necessary details. Click Save to close the EXT window.


Now, Click New Inquiry window in Microsoft Dynamics GP Menu => Tools => Extender. Fill the Inquiry window with the necessary fields.


I used the Voucher Number – work as the key field for the Extender window. If we create the Inquiry just with the above fields, it will return error due to voucher number – work field. To avoid this error, we can make use of the Use Alternate Key field in the Inquiry window.


We’ll set the alternate field by clicking in the Alternate Keys menu that will open the window below.Fill the fields as shown below and click Save.


Now, Go to Payables Transaction Entry and enter the data in the Extender window PM_TRX_EXT in Purchasing => Payables Transaction Entry => Additional Menu.


Once we post the transaction, we can now go to Purchasing => Inquiry => Transaction by Document, Insert the document number to open the Payables Transaction Inquiry Zoom Form that also stores our extender Inquiry Form in Additional menu



In this fashion, we can build any other Extender windows and corresponding Inquiry windows as well.

Which Security Tasks provide access to a particular GP window

In GP10, Security is managed by Security Tasks, Tasks mapped to respective Security Roles, Roles then mapped to User Security apart from Field Level Security.


So, Each User will have one Role or Many Roles, Roles again have one or many security Tasks. Mostly, we land up having each user mapped to several security Tasks. Sometimes, It would be difficult for us to ascertain which security tasks are allowing access to a particular GP window. Or sometimes, we think we did everything to grant access to an user and the User in turn comes back saying he is getting a security access denied error. In these types of scenarios, I now rely upon the Security Debugging Tool. Earlier, it used to be a nightmare for me trying to find which task is allowing access to a particular window or which task is needed to be marked to allow access to a particular GP window until I started using this Tool, many Thanks to David Musgrave.

Now, How does the Security Debugging Tool help in finding which security task is allowing access to which window. I’ll illustrate the same below

Resource Information

This window is accessible in Microsoft Dynamics GP Menu => Support Debugging Tool => Debugger => Resource Information.

Here in this window, when I want to view the security information for General Entry window, I’d first unmark the case sensitive option, change the search mode to Contains, then enter Transaction Entry in Display Name and hit Tab that automatically fills the fields in the window. Now, I click the Security Tab that will open the window below


In the above window, I can very much identify which security Task is allowing access to the General Entry window including the security Roles information. This eases my job in terms of assessing the security for each window whenever I need.

Support Debugging Tool is available for download in partner source. There are other blog posts related to the same subject.




Where our Product feedback goes?

Most of the times we wonder if our Dynamics product suggestions or feeback are even read by someone in Microsoft.

A blog post has been recently posted to address these questions that might be of interest to everyone.


Another blog post written by Scott Stephenson in Developing for Dynamics GP on providing Product Feedback using Microsoft Connect is also helpful in this case.



Correct option - General Entry takes longer time than expected

In the General Entry, We have the option to back out a Journal Entry or Back out and post correcting Entry option.

Clicking on the Correct option will open the Correct Journal Entry option

In this window, when we open the Original Journal Entry lookup, it will take longer time than expected. To avoid this delay in bringing up the results, adding the following index script to the company database will help. The journal entry records will then be displayed much faster. This script was provided by Microsoft support sometime before. (I dont really remember when though).

CREATE NONCLUSTERED INDEX [SEE_GL30000PerformanceCustom] ON [dbo].
( [Back_Out_JE] ASC,

CREATE NONCLUSTERED INDEX [SEE_GL20000PerformanceCustom] ON [dbo].
( [Back_Out_JE] ASC,

To add the above script, Open SQL Server Management Studio, Click to open the Query Analyzer, Select the Company Database against which you'd want to create the index, Copy the script, Click F5 or Execute.

This will then help to ensure faster display of journal entry records in the lookup

Smartlist Builder: Building GoTo

Smartlist Builder is one of various tools available for extensive reporting inside Dynamics GP. One of the features in Smartlist Builder is the functionality to build custom Go To's. In the Go To's we can make use of Advanced Go To's that just need to be added to the custom smartlist. They doesn't need any tasks to be performed for displaying the Go To's. However, Advanced Go To's are limited. Available default Advanced Go To's are

1. Payables Transaction Inquiry
2. Purchase Order Inquiry
3. Receivables Transaction Inquiry
4. Sales Inquiry

So, If we need to build any other Go To on any custom Smartlist, We need to build it ourselves making use of the Go To Functionality. I'll try to explain how we can build a Go To Check Book Register Inquiry using this functionality in this example.

For my example, I've built a custom smartlist making using of CM Transaction Table. Once the CM Transaction Table is added, I'd add checkbook Register Inquiry Go To. To add this Go To, I'll take through the following steps.

1. Click Go To button on the Smartlist Builder that will open the following window

2. Click the '+' sign that will open the following window

3. Fill the Description with your own name, Product => Microsoft Dynamics GP, Series => Financial, Form => Check book Register Inquiry. Once added, Now Click the + sign for adding the Tasks. Adding Tasks is necessary to display the Go To with the required data. Unless otherwise, it would display a blank Checkbook Register Inquiry Form with no data.

4. On clicking the + sign for tasks, it will open the task window. Set up the fields for each task as shown the below diagram in the sequential order.

Task 1: This task will ensure the checkbook ID selected in the smartlist is filled in the checkbook ID field of Checkbook Register Inquiry Form and also run the field script simultaneously so that the data for that checkbook ID is also displayed.

Task2: This Task will ensure the Bank Transaction Number selected in the Smartlist is filled in the From Document Number field

Task3: This Task will ensure the Bank Transaction Number selected in the Smartlist is filled in the To Document Number field

Task4: This task will ensure the focus to be moved to Redisplay button.

Task5: This task will ensure the field script is run on the Redisplay Button.

Once these tasks are created, Click Save on the Add Go To - Open Form and then also Save the Smartlist. Now, Come to Smartlist that will first add these changes to the smartlist. Now, Select the Bank Transaction Custom Smartlist and double click on any transaction that will open our custom built Go To Checkbook Register Inquiry. The window will look like this when a Checkbook ID "UPTOWN TRUST" and Bank Transaction Number "1000.1"

Similar to this example, we can build any Go To for the custom smartlists.

RMS-GP Integration

Microsoft Dynamics provides a free integration between its Retail Management System (MRMS) and Dynamics GP. The integration is available for Dynamics GP Versions 9.0 & 10.0. I'd put the steps in setting up the integration in GP for version 10.0.

StepI: Install the RMS integration feature from Dynamics GP V10 media

Do not forget to complete the GP Utilities process unless otherwise the SQL objects required for the integration would not be installed in SQL Server. After installing the RMS Integration feature, the integration would not be immediately available in Dynamics GP. Because we have to set the connection properties for Dynamics GP to fetch data from RMS database. To establish this connection, we have to make use of Linked Server in MS SQL Server.

StepII: Configure the Linked Server in the MS SQL Server. For this, Go to the SQL Server Management Studio => Server Objects => Linked Servers. Right Click on the Linked Servers and select New Linked Server.

StepIII: Configure the Linked Server. In the General Tab, Provide a Name for Linked Server under Name, Select SQL Native Client as Provider, Provide the RMS database name under Product Name, SQL Server Name under Data Sources.

Now, Go to Security Option, Provide the Local Login and Remote User Login. Select Be Made using the Login's current security context for the connection.

After the setup, Right click the Linked Server and Click Test Connection. We should get Test connection succeeded message to ensure the Linked server is working properly.

StepIV: Once the Linked server is setup and configured successfully, now, we have to setup the RMS integration in Dynamics GP. To set it up, Go to MS Dynamics GP Menu => Tools => Setup => Retail Management => RMS integration setup. In the setup window, we should now be able to see the linked server that we setup as above. Select the same in Linked SQL Server from the drop down. Also Select RMS HQ Database from the drop down.

Now Proceed to map the RMS and GP stores,Accounts, Inventory, Tx, Currency and Defaults as shown in the tabs above. Fill the required accounts in the Accounts Tab for each store. Remember to use Site Segment substitution available in Dynamics GP to provide multiple accounts.

With this we are able to complete the RMS Integration setup in Dynamics GP. From Now on we can proceed to import Items, Sales documents, customers from RMS. This is available in Transactions => Retail Management.

We can import the data for any dates and no need to worry about duplication because the integration will validate for the existence of records before it imports the RMS data into GP and hence any existing records are ignored.

To view the SQL Tables associated with the RMS Integration, Go to the Company Database, and look for tables starting with RMS.

Project Budgeting Questions

Most of the clients ask us these common questions on Project Accounting whether it is during a Pre-sales activity or during an implementation cycle. During a recent discussion in Dynamics GP partner forums, Andy Sather from Microsoft has given great answers to these questions. I thought I'll put the same here.
Q1: In Dynamics GP Project Accounting Module, Can we have zero budgets defined?
Q2: In Dynamics GP Project Accounting Module, Can we modify the Baseline Project Budget Amounts?
A1: The answer to Q1 is partly yes and partly no. We can have zero budgets if the the project type is "Time & Materials". We cannot have zero budgets for the other two project types, Cost Plus and Fixed Price.

Time & materials projects are based on cost transaction amounts. So, We dont need to budget T&M projects at all.

Fixed Price projects use the Baseline Billings amounts to bill off of. Whenever you make a change to the baseline, it will update the forecast and you are not given the option to make changes to the forecast.

Cost Plus projects also use the Baseline Billings amounts to bill off of but you can make changes to the profit on both the Baseline and Forecast values.

A2: We can very much modify the Baseline amounts despite the project status is set to open. For changing the Baseline budget we need to go through the following steps.

I. Go to Project => Cards => Project => Forecast. Change the status of cost category to "Estimate" from "Open" status. (In the open status, it wouldn't allow us to edit the baseline cost)

II. Change the QTY / Total cost on the Baseline. Once the amount is saved, it opens the following window. Click Yes to it.

III. Now, your baseline amounts would stand changed.

IV. Now, change the cost category status back to "open".