Things to Consider- Chart of Accounts

During the course of a new implementation of Dynamics GP, we usually have a discussion surrounding the chart of accounts.  Do you want to change it? If so, how?  How well does it work for you today?  And clients sometimes vary in their willingness to explore changing it.  Some are open to discussion, to see how they might tweak it to better support their needs, while others are satisfied with what they use today.  From time to time, we also find ourselves discussing the chart of accounts structure with clients who have been on Dynamics GP for a number of years or even decades.  In those cases, the company may have grown and the reporting needs have also changed.


I thought it might be worthwhile to share some of my own discussion points when exploring the chart of accounts structure with both new and longtime Dynamics GP users.  So where do I start? I always start with the desired end result...Reporting! So let's start there, and then toss in all my other typical considerations...

• What are the current and desired reporting needs?  How are reports divided/segmented (departmental, divisional, etc)?  Are the lowest levels for reporting represented in the chart of accounts today?  How about summary levels?  Do the summary levels change in terms of organization over time (so maybe they shouldn't be in the chart of accounts structure)? Is there reporting and/or other tracking in Excel that should be accommodated by the chart of accounts structure so that the reporting can be automated?
• What about budgeting?  What level does budgeting occur at?  Is that represented? 
• What about other analytics? Are the components available in the chart of accounts?  Are there statistical variables?  Are they in Dynamics GP as unit accounts?
• How does payroll flow to the general ledger, does it align to the chart of accounts (e.g., departments, positions, codes, do they match up)?  Is there an expectation of payroll reporting from the general ledger in terms of benefit costs, employee costs, etc?  Are those levels represented in the chart of accounts?
• Are your segments consistent?  Does a value in department mean the same thing across all accounts?  Or do you need to look at multiple segments to determine the meaning (e.g., department 10 with location 20 means something different than department 10 with location 40)?  Consistency is a goal whenever possible to facilitate reporting.
• How about your main accounts?  Review a distinct list?  Are they logical, in order, and follow the norm (e.g., expenses in the 6000s)?  Is there room to add main accounts?  Are there duplicated/inconsistent main accounts?
• Do you do allocations?  If so, how and by what factors?  Can we use fixed or variable allocations to facilitate in GP?  Do we have the needed components in the chart of accounts to determine what to allocate from and to?  Do you want to offset the allocation in separate accounts to see the in/out of the allocation?

Anything I missed?  Thoughts, comments?  Please share and I will update the list!


Christina Phillips is a Microsoft Certified Trainer and Dynamics GP Certified Professional. She is a director with BKD Technologies, providing training, support, and project management services to new and existing Microsoft Dynamics customers. This blog represents her views only, not those of her employer.

Rare eConnect taPMTransactionInsert error 311 and 312: Tax Schedule ID does not exist

By Steve Endow

Here are two obscure eConnect errors that you should never encounter.  Unlike my customer who did encounter them.


Error Number = 311 Stored Procedure= taPMTransactionInsert Error Description = Misc Tax Schedule ID (MSCSCHID) does not exist in the Sales/Purchse Tax Schedule Master Table – TX00102
MSCSCHID = Note: This parameter was not passed in, no value for the parameter will be returned.


Error Number = 312 Stored Procedure= taPMTransactionInsert Error Description = Freight Tax Schedule ID (FRTSCHID) does not exist in the Sales/Purchases Tax Schedule Master Table – TX00102
FRTSCHID = Note: This parameter was not passed in, no value for the parameter will be returned.


Notice that the error says that the tax schedule ID does not exist, but then says that no value was passed in for the tax schedule ID.

So, if you are sending in a blank tax schedule ID value to eConnect, how can it be invalid, and thus cause this error?

As with many eConnect errors like this, the error is not caused by what you send to eConnect.  It's caused by some value or configuration option buried deep in Dynamics GP, that is impossible to figure out based on the eConnect error alone.

Here is the validation script that triggers the 311 error:

IF ( @I_vMSCSCHID <> '' )
    BEGIN
        IF NOT EXISTS ( SELECT  1
                        FROM    TX00102 (NOLOCK)
                        WHERE   TAXSCHID = @I_vMSCSCHID )
            BEGIN
                SELECT  @O_iErrorState = 311;
                EXEC @iStatus = taUpdateString @O_iErrorState,
                    @oErrString, @oErrString OUTPUT,
                    @O_oErrorState OUTPUT;
            END;
    END;


This would seem to make sense--if a Misc Tax Schedule ID value was passed in, verify that it exists in the TX00102 tax table.

But...what if you aren't passing in a Misc Tax Schedule ID--which our error message above indicates?

Well, we then need to dig a little deeper to find out where a value is being set for @I_vMSCSCHID.  And we find this:

SELECT  @I_vPCHSCHID = CASE WHEN ( @I_vPCHSCHID = '' )
                            THEN PCHSCHID
                            ELSE @I_vPCHSCHID
                        END,
        @I_vMSCSCHID = CASE WHEN ( @I_vMSCSCHID = '' )
                            THEN MSCSCHID
                            ELSE @I_vMSCSCHID
                        END,
        @I_vFRTSCHID = CASE WHEN ( @I_vFRTSCHID = '' )
                            THEN FRTSCHID
                            ELSE @I_vFRTSCHID
                        END
FROM    PM40100 (NOLOCK)
WHERE   UNIQKEY = '1';


So what does this tell us?  If no tax schedules are passed into taPMTransactionInsert, eConnect tries to get default Tax Schedule IDs from the Payables Setup table, PM40100.  Once it gets those Tax Schedule IDs, it validates them.

So...how could that cause the error we're seeing?

Figured it out yet?

The only way the default Tax Schedule IDs in PM40100 could cause the error would be if those default Tax Schedule IDs are INVALID!

Wait a minute.  How could the default tax schedule IDs in the Payables Setup Options window be invalid, you ask?  The Payables Setup Options window validates those at the field level--the window won't let you enter an invalid value or save an invalid value.

So, that leaves either a direct SQL update to set an invalid value in PM40100, or perhaps more likely, someone ran a SQL delete to remove records from TX00102.  My guess is that someone figured they didn't need a bunch of pesky tax schedules, or wanted to change some tax schedule IDs, and they didn't realize that the PM40100 was also storing the tax schedule IDs.

I've asked the consultant to run this query to check the tax schedule IDs setup in PM40100.

SELECT  pm.PCHSCHID, 
(SELECT COUNT(*) FROM TX00102 WHERE TAXSCHID = pm.PCHSCHID) AS PurchIDExists, 
pm.MSCSCHID, 
(SELECT COUNT(*) FROM TX00102 WHERE TAXSCHID = pm.MSCSCHID) AS MiscIDExists, 
pm.FRTSCHID,
(SELECT COUNT(*) FROM TX00102 WHERE TAXSCHID = pm.FRTSCHID) AS FrtIDExists 
FROM PM40100 pm 

If the tax schedules have values, but the "IDExists" fields have a value of 0, then that means there are no matching records in TX00102, and that the values are invalid.

And that is the solution to your mystery eConnect error of the week!

Steve Endow is a Microsoft MVP for Dynamics GP and a Dynamics GP Certified IT Professional in Los Angeles.  He is the owner of Precipio Services, which provides Dynamics GP integrations, customizations, and automation solutions.

You can also find him on Google+ and Twitter

http://www.precipioservices.com

Do you EFT but still print remittances?

Sometimes when it rains, it pours.  It seems like requests come in waives, and in the last two weeks I have had 4 separate clients ask about or implement emailing remittances.  It seems like such an obvious thing, because if you are avoiding printing checks-- why wouldn't you also want to avoid printing remittances as well?

The good news is that it is super simple to set up.  Assuming you want the emails to be routed directly through exchange (and not through a local email client), you first need an account to be used for the sending of the emails.  And second, your exchange server needs to have the auto discover option enabled.  Then it is really as simple as the following 4 steps...


1. Admin-Setup-System-System Preferences, select Exchange for the email option
2. Admin-Setup-Company-Email Message Setup, create a message ID and message for the emails
3. Admin-Setup-Company-Email Settings, set options for emails (including document type) and then click Purchasing Series to enable and specify the email message ID for remittances
4. Cards-Purchasing-Vendor, enter email addresses using the Internet Addresses (blue/green globe) for the remit to address (use the To, CC, and BCC fields as appropriate) and then enable the email remittance under the Email Settings button for the vendor


Once you have these steps completed, it is as simple as choosing to email remittance forms when you are in the Process Remittance window (Transactions-Purchasing-Process Remittance).  Keep in mind, I definitely recommend doing this first with a single vendor using your own email address.  As you may want to tweak the format and/or the email message.


Happy emailing!

Christina Phillips is a Microsoft Certified Trainer and Dynamics GP Certified Professional. She is a director with BKD Technologies, providing training, support, and project management services to new and existing Microsoft Dynamics customers. This blog represents her views only, not those of her employer.

Multiple Fixed Asset Calendars

So many of you may already be aware that GP now has the capability to handle different calendars for different fixed asset books.  For example, your corporate book could be based on your fiscal year while your tax books could be based on a calendar year.  The calendars are managed in Fixed Assets under Setup, then Calendar.  The system comes with a Default calendar that is assigned to books by default.  However, until you run depreciation, you can change the calendar associated with a book (set up new ones). Once you run depreciation, however, you will have to set up a new book if you want to change the assigned calendar.


With the multi-calendar functionality, dealing with short or long years due to a fiscal year change has become much simpler.  In the calendar setup window, you now have options for these situations:

If the selected year needs to be either short or long, simply mark the option for that year (make sure you have the correct year selected).  Then you need to specify how much depreciation you want to take in the elongated year (100% would be the norm for a 12 period year).  So, for example, if you extended the year by 6 months then you might enter 150%.  Or if you have a short year of 6 months, you would enter 50% of the full year depreciation.  Easy Peasy Lemon Squeezy, right?


I also highlighted the options to build your future years based on the fiscal period setup.  You will want to do this so that they are synced to your new fiscal calendar including the prior year setup, the short/long year, and the future year setup (just make sure you have a future year setup with the normal fiscal year).


Assuming when you make these changes that you are not actually changing the depreciation to be taken in a period that has already been processed in Fixed Assets, there is no need to run a reset on the assets. 


Christina Phillips is a Microsoft Certified Trainer and Dynamics GP Certified Professional. She is a director with BKD Technologies, providing training, support, and project management services to new and existing Microsoft Dynamics customers. This blog represents her views only, not those of her employer.

The value of proactive integration logging and error notifications

By Steve Endow

Logging is often an afterthought with Dynamics GP integrations.  Custom integrations often do not have any logging, and while integration tools may log by default, they often log cryptic information, or log lots of events that are not meaningful to the user.

A few years ago I developed a custom RESTful JSON web service API for Dynamics GP that would allow a customer to submit data from their PHP based operational system to Dynamics GP.  They needed to save new customer information and credit card or ACH payment information to Dynamics GP, and they wanted to submit the data in real time.

I originally developed the integration with my standard logging to daily text log files.  While application logging purists (yes, they do exist) would probably criticize this method, my 12+ years of experience doing this has made it very clear that the simple text log file is by far the most appropriate solution for the Dynamics GP customers that I work with.  Let's just say that Splunk is not an option.

The GP integration worked great, and the logging was dutifully working in the background, unnoticed.  After a few months, the customer observed some performance issues with the GP integration, so I enhanced the logging to include more detailed information that would allow us to quickly identify performance issues and troubleshoot them.  In addition to enhancing the detail that was logged, I added some proactive measures to the logging.  I started tracking any delays in the GP integration, which were logged, and I added email notification in case any errors or delays were encountered.

The logging has worked very well, and has allowed us to identify several very complex issues that would have been impossible to diagnose without detailed, millisecond level logging.

Today there was a great demonstration of the value of the integration logging, and more importantly, the proactive nature of the error notification process.

This is an email that was sent to the finance department at 9:18am Central time.  It notifies the users that an error has occurred, the nature of the error, and recent lines from the log to help me quickly troubleshoot the issue.  The user won't be able to understand all of the details, but they will know within seconds that there was a problem, and they will see the customer record that had the problem.


Subject: GP Web Service - Registration Error - PROD

The Dynamics GP Web Service encountered the following errors on 11/21/2016 9:18:22 AM: 

SubmitRegistration for customer Acme Supply Co exceeded the timeout threshold: 15.61 seconds

Here are the most recent lines from the log file:

11/21/2016 09:18:06.505: 10.0.0.66 SubmitRegistration called for customer Acme Supply Co (client, Credit Card)
11/21/2016 09:18:06.505: (0.00) SubmitRegistration - ValidRegistrationHMAC returned True
11/21/2016 09:18:06.505: (0.00) RegistrationRequest started for customer Acme Supply Co
11/21/2016 09:18:06.739: (0.22) ImportCustomer returned True
11/21/2016 09:18:06.786: (0.28) InsertCustomerEmailOptions returned True
11/21/2016 09:18:22.43:        (15.53) Non-Agency ImportAuthNet returned True
11/21/2016 09:18:22.121: (15.60) Non-Agency ImportAzox returned True
11/21/2016 09:18:22.121: (15.60) RegistrationRequest completed
11/21/2016 09:18:22.121: (15.61) SubmitRegistration - RegistrationRequest returned True
11/21/2016 09:18:22.121: WARNING: SubmitRegistration elapsed time: 15.61


Just by glancing at the email, I was able to tell the customer that the delay was due to Authorize.net.  The log shows that a single call to Authorize.net took over 15 seconds to complete.  This pushed the total processing time over the 10 second threshold, which triggers a timeout error notification.

Subsequent timeout errors that occurred throughout the morning also showed delays with Authorize.net.  We checked the Authorize.net status web page, but there were no issues listed.  We informed the client of the cause of the issue, and let them know that we had the choice of waiting to see if the problems went away, or submitting a support case with Authorize.net.

The client chose to wait, and sure enough, at 10:35am Central time, Authorize.net posted a status update on Twitter about the issue.

That was followed by further status updates on the Authorize.net web site, with a resolution implemented by 11:18am Central time.

Because of the proactive logging and notification, the customer knew about an issue with one of the largest payment gateways within seconds, which was over an hour before Authorize.net notified customers.

We didn't have to panic, speculate, or waste time trying fixes that wouldn't resolve the issue (a sysadmin reflexively recommended rebooting servers).  The users knew of the issue immediately, and within minutes of receiving the diagnosis, they were able to adjust their workflow accordingly.

While in this case, we weren't able to directly resolve the issue with the external provider, the logging saved the entire team many hours of potentially wasted time.

So if you use integrations, particularly automated processes, meaningful logging and proactive notifications are essential to reducing the effort and costs associated with supporting those integrations.

Steve Endow is a Microsoft MVP for Dynamics GP and a Dynamics GP Certified IT Professional in Los Angeles.  He is the owner of Precipio Services, which provides Dynamics GP integrations, customizations, and automation solutions.

You can also find him on Google+ and Twitter

http://www.precipioservices.com

Why Use The System Password in Dynamics GP?

Earlier today I had a client mention the use of the System Password.  I will admit that I tend to toss the concept of the System Password in Dynamics GP in the same pile with User Classes-- used extensively in the past but not so much in new implementations.  For those of you that aren't familiar with it, the System Password in Dynamics GP (Admin Page..Setup..System Password) protects all system menus (Setup, Cards, Reports, etc) with a password.  So to access the system windows, a user is prompted to enter the password (even if they technically have access to the window).

In older versions of Dynamics GP, this was particularly useful since security was optimistic with all users starting out with full access to all windows.  So enacting the System Password was a quick way to protect security level settings.  However, over time the usefulness has faded for a number of reasons...


1. Although there are several critical windows under System, many critical (and damaging) windows are available in the module level setups, routines, and utilities-- so a comprehensive security setup must be in place if you truly want to protect your system
2. The system password is all or nothing, so if you have a user who only needs access to handful of useful windows (like exchange tables, or Smartlist options) then the password will not allow them access
3.  The password is actually easily recoverable (meaning someone with enough knowledge could easily find out what the password is even if they don't have access to the setup window)


I believe that the System Password can give administrators a false sense of security, implying that they have "locked down" the most important aspects of the system when they actually have not.  When Dynamics GP introduced pessimistic (by default, users only have access to log in to the system and nothing else) role and task based security, many existing users kept the System Password in place.  For some,  it provides a simple double-check by prompting the password.  I don't think this is a problem, as long as security is still well-defined and thought through (including the windows accessed through the security menus).  But if you have not considered the points above in your security strategy, I would encourage you to avoid using the System Password as your primary line of defense in your system.


Christina Phillips is a Microsoft Certified Trainer and Dynamics GP Certified Professional. She is a director with BKD Technologies, providing training, support, and project management services to new and existing Microsoft Dynamics customers. This blog represents her views only, not those of her employer.

Dynamics GP FP: Can't close Table! and FP: Couldn't close table! error messages

By Steve Endow

A Dynamics GP customer contacted me and asked why this error message occurred:

There are a few forum threads on this discussing possible causes, but I thought I would note my experience with this error message.

I see this quite a bit on my numerous Dynamics GP development VMs because of the testing and changes I perform on the machines.  I can easily produce the error with these steps:

1. Launch Dynamics GP and login to a company
2. Open a Dynamics GP window, such as Customer Maintenance
3. Select a record on the GP window, such as a customer, so that you are viewing the customer data
4. Restart the SQL Server service
5. After the SQL Server service is running again, close the Dynamics GP (Customer Maintenance) window (not the whole app)
6. The "FP: Can't close Table!" message should appear
7. Close Dynamics GP completely.  You may get one or more other error messages, and then another "FP: Couldn't close table!" error.  (slightly different wording and Table is not capitalized)

Given this, my initial explanation for this error message is that the Dynamics GP client application lost its connection with the SQL Server.  There may be other causes, but that is always the reason why I happen to see it on my servers.

If you don't think that the SQL Server was rebooted or the SQL service was restarted, then my next guess would be that there was a network interruption between the machine running GP and the SQL Server machine.

If this happens to a GP client running on the SQL Server (as it did with my customer), then that would typically rule out a physical network issue, and I would look into whether SQL was restarted.

There is the possibility of a deeper network configuration issue or an antivirus issue, as discussed in this post:

https://community.dynamics.com/gp/b/dynamicsuniversitygp/archive/2013/11/04/fp-couldn-39-t-close-table-one-setting-that-may-end-random-disconnects-to-your-gp-databases


I personally can't remember the last time I saw the FP error at a customer site, so I can't speak to those as likely causes, but it wouldn't surprise me.

Steve Endow is a Microsoft MVP for Dynamics GP and a Dynamics GP Certified IT Professional in Los Angeles.  He is the owner of Precipio Services, which provides Dynamics GP integrations, customizations, and automation solutions.

You can also find him on Google+ and Twitter

http://www.precipioservices.com