If you read the blogs at the moment they are all full of the wonders of 2010, however here in the UK a lot of us will be experiencing our year ends in April before 2010 is released.
Therefore I thought I would devote a little time towards some practical advice for year end.
Once you have got year end out of the way you may then want to turn your sights to Dynamics GP 2010, and if that’s the case may I suggest that you might like to come along to our Dynamics GP 2010 launch event on May the 19th, follow the below link to sign up:
www.touchstone.co.uk/gp2010launch
Back to Year End
The below is a simple checklist you can follow to complete year end, you may notice we are rather fond of doing backups.
1. Do a back up! (told you)
2. Make sure you have enough free disk space on the server. It will need to be a least the size of the GL20000 table but double that would be preferable (It’s going to move all the closed years transactions from the Open table to the History Table so it will need some room).
3. Make sure you run the year end close for all other modules first before the GL (Once you have done the GL you cannot go back and do the others).
Complete year end for each module in the following order:
Inventory
Receivables Management
Payables Management
Fixed Assets
General Ledger
4. Post your final adjusting entries for GL.
5. Print account list to verify posting type (Reports-Financial-Accounts-All Accounts). Incorrectly marked account types is one of the major causes of year end problems. You can also check the posting types using smartlists and the accounts option under Financial.
http://intouchdynamicsgp.blogspot.com/2009/09/no-1-cause-of-year-end-issues.html
If the account's balance is brought forward at the end of year, it should be set up as a Balance Sheet Posting Type.
If the account's balance will be closed to a retained earnings account at the end of the year, it should be set up as a Profit and Loss Posting Type.
6. Close the last period of the financial year.
7. Run the file maintenance routines on financial series i.e. Check links.
8. Make sure Maintain History for Accounts and Transactions is marked in Setup>>Financial>>General Ledger . (If you want to keep history records.)
9. Do a second backup !
10. Print a final detailed Trial Balance, Reports>>Financial>>Trial Balance (I would send this one to file and keep it somewhere safe).
11. Print final Financial Statements.
12. Setup a new fiscal year (Setup>>Company>>Financial Periods)
13. Make sure the Retained earnings account is populated(Routines>>Financial>>Year end closing).
14. Using the Year-End Closing window, enter the starting journal entry number that will be used as the first journal entry number in the new year or accept the default.
15. Close the year, use the Close Year button in the Year-End Closing window .
If the progress window seems to hang at 50%, don't reboot. As long as your hard drive is processing, let it continue. Also make sure you do not click it twice, if you do it we close the next year as well.
16. Close any periods for the new year you do not want people posting to.
17. Adjust budget figures for the new year (remembering to budget for your upgrade to 2010) and print financial statements.
18. Do a third and final backup!
19. Put your feet up and have a well deserved cup of tea
And that is that
Friday, 12 March 2010
Tuesday, 9 March 2010
Dynamics GP 2010 UK launch Event
On May the 19th we are having our Dynamics GP 2010 UK launch event.
This will be held at the London Chamber of Commerce.
As well as an introduction to the benefits, new features and functionality, we will be hearing from one of our clients that took part in the early adopters program from Microsoft on their experiences with Dynamics GP 2010.
Microsoft will also be presenting at the event and our consultants that assisted Microsoft in the early adopters program will be on hand to quiz about their findings.
The helpdesk will also be there and we will be happy to talk about the results of our in-house testing with Dynamics GP 2010.
To sign up or for more info follow the below link:
Register today to ensure your place: www.touchstone.co.uk/gp2010launch
This will be held at the London Chamber of Commerce.
As well as an introduction to the benefits, new features and functionality, we will be hearing from one of our clients that took part in the early adopters program from Microsoft on their experiences with Dynamics GP 2010.
Microsoft will also be presenting at the event and our consultants that assisted Microsoft in the early adopters program will be on hand to quiz about their findings.
The helpdesk will also be there and we will be happy to talk about the results of our in-house testing with Dynamics GP 2010.
To sign up or for more info follow the below link:
Register today to ensure your place: www.touchstone.co.uk/gp2010launch
Friday, 5 February 2010
How to edit VBA when the client only has customization site licence
Well you learn something new every day and today our tame Dex developer is sat next to me in the office (some say he writes code in his sleep, others say he has dexterity tattooed on his heart, all we know is, he is called Jon) so picking up all sorts of things.
This one however is really simple and very useful. Often we will develop modifications for clients on our systems then import these using customization maintenance onto the client systems. This means that the client does not need to have modifier etc, just the customization site licence to run the mods.
However if the VBA on the mods need editing you need to export it back out onto your system to do this then re import it.
Well here is a simple tip. Export the modification out into a package file. Then open the package file up with notepad.
In the VBA code add in the word STOP. This command will tell the VBA to debug.
Re import the package file then open the screen or run the report. The STOP in the VBA code will then open the VBA window and allow you to edit the VBA.
Next handy tip DONT CLOSE THE VBA WINDOW if you do you will have to go through all this again to open the window so leave it open till you have finished and remember to remove the STOP from the code.
This one however is really simple and very useful. Often we will develop modifications for clients on our systems then import these using customization maintenance onto the client systems. This means that the client does not need to have modifier etc, just the customization site licence to run the mods.
However if the VBA on the mods need editing you need to export it back out onto your system to do this then re import it.
Well here is a simple tip. Export the modification out into a package file. Then open the package file up with notepad.
In the VBA code add in the word STOP. This command will tell the VBA to debug.
Re import the package file then open the screen or run the report. The STOP in the VBA code will then open the VBA window and allow you to edit the VBA.
Next handy tip DONT CLOSE THE VBA WINDOW if you do you will have to go through all this again to open the window so leave it open till you have finished and remember to remove the STOP from the code.
Tuesday, 2 February 2010
Comparing tables In Dynamics (GP)
While we are on the subject of SQL tables, we often need to find records that are in one table but not another. This usually happens after there has been some kind of interruption during a posting process.
This can be done fairly easily with a nested SQL query.
The first thing you need to do is determine which tables you need to look in (Take a look at our previous article on Integration Basics). It is also worth remembering the process flow through Dynamics( GP) (Work>Open>History).
Work tables will start with a 1 after the two alphabetic characters that represent the module/series.
Open tables start with a 2
And you guessed it, history tables start with a 3.
Keys tables etc. start with a 0.
Once you have determined which tables you need to determine which field value you are going to compare. As a rule of thumb, its best to use primary key constraints. You can determine the primary key by using sp_helpindex TABLENAME (Replace TABLENAME with the table i.e. GL10000).
Also it worth remembering that vouchernumber is a unique reference in payables and document number is a unique reference in receivables.
Journal number is unique in GL but only for that year as it is possible to start again with journal numbers after year end in Dynamics (GP) if you so require.
So we now have our tables and which field we are going to compare.
Now we create a nested SQL query
Select VCHRNMBR from PM20000
where VCHRNMBR not in
( Select VCHRNMBR from PM30200)
go
The above statement will return all the records from the PM20000 where they are not in the PM30200. If you wanted to return records in both just drop the not from the statement.
It gets slightly more complex if you are comparing to the master keys table as the Vouchernumber is the control number in this table so I would select into a temp table first and change the field name during the process to match that of the other table. The temptable can be called anything you like as long as it is unique and starts with a # to designate it as a temp table. Mine is #TCLCONTROLNUMB. The temp table will remain in existence as long as the query window is open. Once you close the query the temp table is removed.
Select VCHRNMBR as CNTRLNUM into #TCLCONTROLNUMB from PM20000
go
Select CNTRLNUM from #TCLCONTROLNUMB
where CNTRLNUM not in
(Select CNTRLNUM from PM00400)
go
This can be done fairly easily with a nested SQL query.
The first thing you need to do is determine which tables you need to look in (Take a look at our previous article on Integration Basics). It is also worth remembering the process flow through Dynamics( GP) (Work>Open>History).
Work tables will start with a 1 after the two alphabetic characters that represent the module/series.
Open tables start with a 2
And you guessed it, history tables start with a 3.
Keys tables etc. start with a 0.
Once you have determined which tables you need to determine which field value you are going to compare. As a rule of thumb, its best to use primary key constraints. You can determine the primary key by using sp_helpindex TABLENAME (Replace TABLENAME with the table i.e. GL10000).
Also it worth remembering that vouchernumber is a unique reference in payables and document number is a unique reference in receivables.
Journal number is unique in GL but only for that year as it is possible to start again with journal numbers after year end in Dynamics (GP) if you so require.
So we now have our tables and which field we are going to compare.
Now we create a nested SQL query
Select VCHRNMBR from PM20000
where VCHRNMBR not in
( Select VCHRNMBR from PM30200)
go
The above statement will return all the records from the PM20000 where they are not in the PM30200. If you wanted to return records in both just drop the not from the statement.
It gets slightly more complex if you are comparing to the master keys table as the Vouchernumber is the control number in this table so I would select into a temp table first and change the field name during the process to match that of the other table. The temptable can be called anything you like as long as it is unique and starts with a # to designate it as a temp table. Mine is #TCLCONTROLNUMB. The temp table will remain in existence as long as the query window is open. Once you close the query the temp table is removed.
Select VCHRNMBR as CNTRLNUM into #TCLCONTROLNUMB from PM20000
go
Select CNTRLNUM from #TCLCONTROLNUMB
where CNTRLNUM not in
(Select CNTRLNUM from PM00400)
go
Monday, 1 February 2010
Integrating to Dynamics (GP) The Basics
Its only Monday and I have already been asked by two clients about integer values in tables and the best way to integrate to them without using Integration Manager. Add to this the couple of calls I had last week on the same subject I thought it would be worthy of a blog article.
The first question that gets asked is “what is the best way of integrating?”.
Well that depends on what you’re integrating. If you’re integrating static data (addresses, debtors, accounts) then SSIS and other such tools are fine. However if you are integrating transactional data I would suggest it is best to use a tool that will use the business logic of Dynamics (GP) and thus prevent you pumping rubbish into the system. These tools will usually use eConnect to access that business logic, tools like Scribe, Integration Manager and Smartconnect can all use eConnect.
The next question tends to be “what tables do I need to integrate to?”
Well there are several ways of finding this out and a combination of them will provide you with the best results.
• Install the SDK from additional products on your Dynamics (GP) CD. This gives excellent help on integrations and will advise you on tables and the values required for integer fields etc.
• Use the Direct to table import. Open the window in which you would normally enter the information manually, then in that window select Tools > Integrate > Table Import. This will then display a list of tables that this window accesses and therefore would be updated if you entered information.
• Resource descriptions. These can be found under Tools > Resource descriptions > table definitions. Here you can find all the tables within Dynamics (GP) and their structure. It will also show you their physical name (The one used in SQL) and their display name as well as their technical name (Usually the one used in error messages).
Using a combination of these you should be able to get all the info you need. This is a very basic look at integrations and does not cover all the options, it is more of a pointer in the right direction.
And always integrate into a test database first when you’re developing your integration.
The first question that gets asked is “what is the best way of integrating?”.
Well that depends on what you’re integrating. If you’re integrating static data (addresses, debtors, accounts) then SSIS and other such tools are fine. However if you are integrating transactional data I would suggest it is best to use a tool that will use the business logic of Dynamics (GP) and thus prevent you pumping rubbish into the system. These tools will usually use eConnect to access that business logic, tools like Scribe, Integration Manager and Smartconnect can all use eConnect.
The next question tends to be “what tables do I need to integrate to?”
Well there are several ways of finding this out and a combination of them will provide you with the best results.
• Install the SDK from additional products on your Dynamics (GP) CD. This gives excellent help on integrations and will advise you on tables and the values required for integer fields etc.
• Use the Direct to table import. Open the window in which you would normally enter the information manually, then in that window select Tools > Integrate > Table Import. This will then display a list of tables that this window accesses and therefore would be updated if you entered information.
• Resource descriptions. These can be found under Tools > Resource descriptions > table definitions. Here you can find all the tables within Dynamics (GP) and their structure. It will also show you their physical name (The one used in SQL) and their display name as well as their technical name (Usually the one used in error messages).
Using a combination of these you should be able to get all the info you need. This is a very basic look at integrations and does not cover all the options, it is more of a pointer in the right direction.
And always integrate into a test database first when you’re developing your integration.
Thursday, 28 January 2010
FRx SP11 and 0 budget amounts
We have now discovered a slight difference between sp11 and sp10 and it revolves around the way they deal with column layouts and the use of the Base-1 feature in the column.
It would appear that if you use the base-1, base-2, base-3 etc. feature in the period column and this takes you into the prior year it will look into the prior year for the budget when the year column is set to base (previously in SP10 it did not do this and the year remained constant).
For example if you run a report for January 2010, the period column is set to base-1 and the year is base. It should go back one period which is December but as the year is set to base you would expect data in the budget column for December 2010. What we are getting is zero.
This is because the base-1 in the period is now changing the year as well and trying to display data from December 2009. As there is no data for December 2009 in this budget (yearly budget) it is returning zero.
The workaround is to hardcode the periods 1 through to 12 rather than using the base-1 functionality. We do have this logged with Microsoft at the moment, however a lot of people have just completed their year ends or are in the process of doing so, which means this issue is likely to be more obvious as people change the years in which they are processing.
It would appear that if you use the base-1, base-2, base-3 etc. feature in the period column and this takes you into the prior year it will look into the prior year for the budget when the year column is set to base (previously in SP10 it did not do this and the year remained constant).
For example if you run a report for January 2010, the period column is set to base-1 and the year is base. It should go back one period which is December but as the year is set to base you would expect data in the budget column for December 2010. What we are getting is zero.
This is because the base-1 in the period is now changing the year as well and trying to display data from December 2009. As there is no data for December 2009 in this budget (yearly budget) it is returning zero.
The workaround is to hardcode the periods 1 through to 12 rather than using the base-1 functionality. We do have this logged with Microsoft at the moment, however a lot of people have just completed their year ends or are in the process of doing so, which means this issue is likely to be more obvious as people change the years in which they are processing.
Dynamics (GP) First Aid (MR MUM)
I have spent the last three days on a St John’s ambulance First Aid at Work course and I am now fully qualified to deal with those all too common work place wounds, you know the kind of thing, QWERTY shaped bruises on the forehead etc.
Anyway the First Aid procedures started me thinking that this is very similar to what we do on the helpdesk when trouble shooting an issue with Dynamics.
Within First Aid there are loads of handy acronyms to help you remember what you’re looking for and what takes priority. For instance there is DR ABC. This stands for Danger, Response, Airway, Breathing and Circulation. I thought that we could benefit from something similar to make sure all the bases are covered when investigating a Dynamics issue and after several minutes of deep thought I have come up with MR MUM.
M = Multiple Instance: Does it happen all the time, is this the first time , has it happened before?
R = Replicate: Can you replicate it?
M = Machine: Is it machine specific, does it only happen on one workstation, does it happen on the server?
U = User: Is it user specific, does it happen for one user or all users or just users in a specific role or users in a specific Company?
M = Modifications: Are the screens or reports modified , does it happen if you take away access and use the unmodified objects is there VBA attached?
The answers to the above should provide the consultant with a firm basis to start trouble shooting the rest of the issue, in fact, if the answers to these questions were given at the point of case logging the resolution may present itself straight away, it would certainly speed up the triage stage of a support call.
Anyway the First Aid procedures started me thinking that this is very similar to what we do on the helpdesk when trouble shooting an issue with Dynamics.
Within First Aid there are loads of handy acronyms to help you remember what you’re looking for and what takes priority. For instance there is DR ABC. This stands for Danger, Response, Airway, Breathing and Circulation. I thought that we could benefit from something similar to make sure all the bases are covered when investigating a Dynamics issue and after several minutes of deep thought I have come up with MR MUM.
M = Multiple Instance: Does it happen all the time, is this the first time , has it happened before?
R = Replicate: Can you replicate it?
M = Machine: Is it machine specific, does it only happen on one workstation, does it happen on the server?
U = User: Is it user specific, does it happen for one user or all users or just users in a specific role or users in a specific Company?
M = Modifications: Are the screens or reports modified , does it happen if you take away access and use the unmodified objects is there VBA attached?
The answers to the above should provide the consultant with a firm basis to start trouble shooting the rest of the issue, in fact, if the answers to these questions were given at the point of case logging the resolution may present itself straight away, it would certainly speed up the triage stage of a support call.
Subscribe to:
Posts (Atom)
Posts
