Microsoft Dynamics GP tips and tricks – How to use Combining and/or Modifying Debtor IDs and Creditor IDs and Account Roll up Enquiry to their full potential
Would you like to know a series of tips and tricks to help you use your Microsoft Dynamics GP solution to its full potential? We have produced a series of blogs where we will be talking about best practices within this great solution. We started the sequence off with GP Macros followed by audit trail and user messages. In this third edition will be focusing on ‘Combining and/or Modifying Debtor IDs and Creditor IDs and Account Roll up Enquiry’
Combining and/or Modifying Debtor IDs and Creditor IDs
Companies, particularly those who have been using the system for a number of years, may find that some of their creditors or debtors have changed name over the years whether through re-branding or acquisition.
If you were using an alphanumeric system to create codes for your accounts, then a name change would mean that your e.g. creditor ID is no longer meaningful.
An example would be something like a creditor called “Keegan & Daughter” becomes “Office Systems”. The creditor account may be something like KEE001. You could always change the creditor name in GP but not the ID. Using the Modifier, you can select the old Creditor ID and enter a new one. The utility will go through every table in the system and update the creditor ID to the new one.
Before having the ability to make this change, the above could lead to situations where a (relatively) new member of staff receives an invoice from “Office Systems”, expects a creditor ID of e.g. OFF001, doesn’t find it, so creates it. Now you have two creditors in the system for the same supplier.
Historically, once this was discovered (and hopefully before there were too many transactions!), you performed manual adjustments to move the transactions from one creditor to the other.
With the Combiner/Modifier you can now combine these two creditors. You just need to decide which one you want to use going forward.
The Combiner/Modifier for customers can be found on the Sales Desktop under Utilities >> Customer Combiner and Modifier and for suppliers on the Purchasing Desktop under Utilities >> Vendor Combiner and Modifier.
When running the Combiner/Modifier you must be logged on as the ‘sa’ user (or any other user with SQL admin access i.e. one that can create new users in GP).
Account Rollup Enquiry
Most GP users will use a reporting tool such as Management Reporter, Power BI and so on. These tools allow you to do all sorts of groupings and analysis.
There are times however when you may be asked for something like the total utilities spend across all departments and to get a quick figure for something like this you can use the Account Rollup Enquiry rather than having to run a full financial report.
In the following example, there are six departmental accounts for utilities:
To create an account rollup for these accounts, go to the Financial desktop >> Enquiries >> Account Rollup. You will see this window looks like the GL Summary Enquiry window.
Type a name for your Enquiry e.g. Utilities and tab off the name field. You will be prompted to add a new enquiry (you can select an existing enquiry from a list once you have created some).
You can have a maximum of four columns in the enquiry of the following types; Actuals, Budget, Calculated, Previous Year, Other Currencies. Different options will become available depending on which of these you select.
The restrictions are held in the bottom part of the screen and with any filter in a GP report option, don’t forget to hit ‘Insert’!
Here is an example of an account rollup for the accounts listed above:
Saving and refreshing the Enquiry window gives:
You can drill back on the figures in the same way as on your GL Summary Enquiry window, by highlighting the relevant field and clicking on the column title:
The information in this blog is based on simple versions of events, if you would like any further assistance, then don’t hesitate to get in touch with your Account Manager or contact us via our website. Look out for the next edition in our series of Microsoft Dynamics GP tips and tricks blogs which will focus on the GP Intercompany feature.