I was a little surprised to see Microsoft announce Display Duplicate G/L Accounts on Budget Exception Report as a separate feature of the day as it looks to be part of the Budget Import Exception Report which is a new feature in Dynamics GP 2016 R1 itself:
The original feature of showing accounts the budget which don’t exist in Dynamics GP is going to be fabulously well received; this one is a very good addition which I think may not be fully understood by some.
I have had to explain to a few clients that having the same account on the file more than once will result in only the final value ending up in Dynamics GP as it will overwrite the previous entries. If you want to load values in multiple times and have the cumulative values, you need to load multiple budgets and then use the Consolidate Budgets feature.
There are some feature of the days which I look at and think “so what?”. And then there are others I look at and know they are going to be very popular.
This feature is very much one of the latter (I have one client who would be willing to upgrade to just top get this one feature). It is the ability to have Cash Receipts automatically posted in Bank Reconciliation:.
The main features of are:
- When Automatically Post Cash Deposits is marked and you post a batch with cash receipts in it, one deposit will be created for all cash receipts in the batch.
- If you transaction level post, each transaction will create a separate deposit.
There is one downside to this feature; if you want your deposits to show as individual deposits in bank you have to post them separately in Receivables Management. Doing one transaction per batch (transaction level posting would result in manual posting being required of the resulting GL batches) will not be as time consuming as doing all of the deposits manually, but is moving an element of the work from one part of Dynamics GP to another (and possibly from one team to another).
The client I references at the start of this post runs a number of visitor centres so will have a lot of cash receipt transactions being imported into the system, but the majority of them would hit the bank as a single deposit. I’m onsite with them soon to help integrate their new EPOS system into GP so can work with them to make sure transactions are brought into Dynamics GP in the way necessary for them to benefit most from this feature.
Microsoft Dynamics GP 2013 R2 saw the introduction of the Workflow 2.0 (which was the subject of myfourth book. Workflow 2.0 was enhanced further in both Microsoft Dynamics 2015 R1 and R2, and is again being improved in Dynamics GP 2016 R1.
This time, the enhancement is a twofold one. Firstly, the Batch Edit lists have a default Word Template and, secondly, this can be emailed to the workflow approver:
The main features of are:
- Workflow Batch Approvals now have a default Word Template for the following edit lists:
- Gneeral Ledger Batches
- Payables Batches
- Receivables Batches
- Can be emailed to approver for complete information before approving
I think this feature will prove to be popular with those clietn using Workflow approvals as an approver will always want s much information before approving as possible. This will likely be of great interest when Workflow approvals are being used.
MDGP 2016 R1 Feature of the Day: Budget Import Exception Report
This feature of the day is the production of an exception report during the Budget import. This is going to be a very popular one:
The main features of are:
- When importing a budget from Excel, an exception report will print displaying accounts that aren’t setup in Dynamics GP
The Budget Wizard is a good tool, but it has always been problematic to identify accounts on the budget upload which didn’t exist. This new feature will make importing budgets far easier now any missing accounts are highlighted.
I have a couple of customers who might be tempted to upgrade just to get this one feature (including one who just upgrade to Dynamics GP 2015 R2.
Microsoft Dynamics GP
Error Registering Table GL_Account_MSTR
I have done a fair bit of work recently for clients with the Web Services for Microsoft Dynamics GP. One of the checks the configuration tool does is to check if all of the companies have a functional currency defined.
However, if it returns a fail flag for this check, it doesn’t actually tell you which company (or companies) has failed the check. And when your client has well over a hundred companies you need an alternative method to going through each one manually.
That alternative method is the script below; it uses a cursor to look through the company databases and returns a list of all companies and their functional currency. To only see those companies without a functional currency, there is a where clause at the bottom which is currently commented out.
CREATE TABLE #FUNLCURR ( INTERID VARCHAR(5) ,FUNLCURR VARCHAR(15) ) DECLARE @SQL_Statement VARCHAR(1000) DECLARE cursor_InterID CURSOR FOR SELECT INTERID FROM SY01500 OPEN cursor_InterID DECLARE @INTERID VARCHAR(100) FETCH NEXT FROM cursor_InterID INTO @INTERID WHILE (@@FETCH_STATUS <> -1) BEGIN IF (@@FETCH_STATUS <> -2) SET @SQL_Statement = 'INSERT INTO #FUNLCURR (INTERID,FUNLCURR) VALUES (''' + @INTERID + ''','''')' exec (@SQL_Statement) SET @SQL_Statement = 'UPDATE #FUNLCURR SET FUNLCURR = (SELECT FUNLCURR FROM ' + RTRIM(@INTERID) + '.dbo.MC40000 MC) WHERE INTERID = ''' + @INTERID + '''' exec (@SQL_Statement) FETCH NEXT FROM cursor_InterID INTO @INTERID END CLOSE cursor_InterID DEALLOCATE cursor_InterID SELECT #FUNLCURR.INTERID ,SY01500.CMPNYNAM ,#FUNLCURR.FUNLCURR FROM #FUNLCURR INNER JOIN SY01500 ON SY01500.INTERID = #FUNLCURR.INTERID /*WHERE LEN(FUNLCURR) = ''*/ ORDER BY #FUNLCURR.INTERID DROP TABLE #FUNLCURR
We are currently in the process of upgrading a large 20 site client from Microsoft Dynamics GP 9 to a later version (unfortunately not 2015 due to third party add-ons which are not supported on this version). The client previously had standalone implementations at each site, but we are aggregating them into one implementation for ease of maintenance and also to reduce licensing costs.
The first few we centralised went fine, but we then ran into problems when we did the head office companies. When they ran the GL Summary TB on the old and new systems there was a mismatch between them.
After investigating, the support team established that the problem was down to some corruption in the Account Index Master table (GL00105) where some of the accounts had segments separated with dots instead of dashes as defined in Account Format Setup ().
The solution they followed was fairly simple, and in three steps:
- Run a script to clear the Account Index Master table:
DELETE FROM GL00105
- Run Check Links ( ) on Account Master.
- Run the General Ledger Reconcile utility ( ) on all years starting with the oldest and working forward.
After this was done, the report produced from the upgraded system matched the old one.
We recently upgraded a client with well over a hundred companies from Microsoft Dynamics GP 10 to GP 2015; one of the new pieces of functionality they wanted to start using was the Budget Transactions.
One problem is that the keep history checkbox for Budget Transactions in General Ledger Setup () is unmarked.
Enabling it for this many companies was going to take a substantial amount of time (even if we generated a macro to do this in automatically) as the client has over 120 companies in Dynamics GP. It is a simple setting in one table which needs to be updated, so instead of using a macro I created a SQL script containing a cursor to loop through all of the databases attached to the system database and enable this setting.
As with all scripts which change data, please make sure you have a good backup of your database (or in this case all of your company databases) before running the script.
DECLARE @IntercompanyID VARCHAR(5) DECLARE @SQLStatement AS VARCHAR(2000) DECLARE cursor_InterID CURSOR FOR SELECT INTERID FROM DYNAMICS..SY01500 INNER JOIN master..sysdatabases ON name = INTERID OPEN cursor_InterID FETCH NEXT FROM cursor_InterID INTO @IntercompanyID WHILE (@@FETCH_STATUS <> -1) BEGIN IF (@@FETCH_STATUS <> -2) SET @SQLStatement = 'UPDATE ' + @IntercompanyID + '..GL40000 SET KPBUDTRXHIST = 1 WHERE KPBUDTRXHIST = 0' EXEC (@SQLStatement) FETCH NEXT FROM cursor_InterID INTO @IntercompanyID END CLOSE cursor_InterID DEALLOCATE cursor_InterID
You will need to amend the script if your System Database is not called DYNAMICS.
Microsoft Dynamics GP 2015 R2 has been released and, as with previous releases, Microsoft have started a Feature of the Day series on the Inside Microsoft Dynamics GP Team blog. As I have done with the last three series, I’ll be reposting these blog posts here with a little commentary.
The twenty third Feature of the Day is Automatically Deposit Cash Receipts. This feature provides the option to have cash receipts directly update the checkbook balance without the need to manually post the cash receipt deposit in Bank Reconciliation.
This option was previously available in Analytical Accounting. In Microsoft Dynamic GP2015 R2, the option is made available in the core dictionary, expanding the option to companies not using Analytical Accounting.
To select the option, mark the “Automatically post cash receipt deposits” checkbox in the Company Setup Options window.
When this option is marked, cash receipts entered in Cash Receipt Entry, Receivables Transaction Entry, Sales Transaction Entry, and Invoice Entry will be deposited automatically in the checkbook, updating the checkbook balance.
I have a few clients who will benefit from this one, but I have one client especially in mind for it who has asked before about if this was possible.
Dynamics GP includes a variety of tools and modules to assist in controlling processes and data; one of the major modules for this was the Dynamics Workflow module. However, this module had major flaws which very much limited its usefulness; it was slow, clunky and difficult to install, configure and maintain.