End of year report for auditors for Dynamics GP

blog pictureIt’s getting hard to find all the blog posts I once did a long time ago. I need these things so I don’t have to figure everything out again.

Here is a post I have used often in the past.

**************

I often have the request to get all the journal entries for a particular period of time. Usually this request comes from auditors. You can export the GL detail report but formatting is usually an issue. You can export all the transactions from a Smartlist (account transactions) but that usually takes a long time if you want the data for a long date range, say 6 months to a year.

Here is a script I’ve used in the past to export the data straight from SQL. It includes the user who posted the transaction and the actual date it was posted on. You will have to change the bolded red date and year for your particular needs. You will also need to add GL00100.ACTNUMBR_X to include however many account segments you have in your GL account.

When running the query in SQL have the output results set to file.

Note this is for open year transactions. If you want data from a historical year, find and replace the table from GL20000 to GL30000 and OPENYEAR to HSTYEAR.

Open Year Query

SELECT GL20000.JRNENTRY AS Journal_Entry_Number, GL20000.SOURCDOC AS Module, GL20000.TRXDATE AS Date,
GL00100.ACTNUMBR_1 AS Account_Segment1, GL00100.ACTNUMBR_2 AS Account_Segment2, GL00100.ACTNUMBR_3 AS Account_Segment3, GL20000.OPENYEAR AS Year,
GL00100.ACTDESCR AS Acct_Description, GL20000.DEBITAMT AS Debit, GL20000.CRDTAMNT AS Credit,
GL20000.TIME1 AS Time, GL20000.USWHPSTD AS User_Who_posted
FROM GL20000 INNER JOIN
GL00100 ON GL20000.ACTINDX = GL00100.ACTINDX
WHERE (GL20000.OPENYEAR = ‘XXX‘) AND (GL20000.TRXDATE => ‘11 / 30 / 2015’) AND (GL20000.TRXDATE <= ‘12 / 31 / 2015’)

Historical Year Query

SELECT gl30000.JRNENTRY AS Journal_Entry_Number, gl30000.SOURCDOC AS Module, gl30000.TRXDATE AS Date,
GL00100.ACTNUMBR_1 AS Account_Segment1, GL00100.ACTNUMBR_2 AS Account_Segment2, GL00100.ACTNUMBR_3 AS Account_Segment3, gl30000.HSTYEAR AS Year,
GL00100.ACTDESCR AS Acct_Description, gl30000.DEBITAMT AS Debit, gl30000.CRDTAMNT AS Credit,
gl30000.TIME1 AS Time, gl30000.USWHPSTD AS User_Who_posted
FROM gl30000 INNER JOIN
GL00100 ON gl30000.ACTINDX = GL00100.ACTINDX
WHERE (gl30000.HSTYEAR = ‘2014’) AND (gl30000.TRXDATE <= ‘01 / 01 / 2014’) AND (gl30000.TRXDATE <= ‘12 / 31 / 2014’)

Happy Exporting.

Error Registering Table GL_Account_MSTR

I had this happen to me again and ran across an old blog post I did years ago.

A lot of people think I just blog so I can live the rich and famous lifestyle but in all honesty it’s because I have a bad memory and I know I’m going to come across issues like this again.

Here’s the old post I wrote:

********************************

I don’t usually like posting on break/fix type issues. It’s pretty hard making GL_Account_MSTR posts entertaining and most of the usual Dynamics headaches are documented on the knowledge base or someone else’s blog. But for some reason I couldn’t find this error anywhere.

So instead of giving you the answer right off I need to at least tell a story to make this a little more interesting.

Most of the time I work directly with our clients to fix a wide range of issues.  Over the weekend our CEO came in to work but was met with a whole litany of errors when running reports in Dynamics. Dealing with a client is one thing but I always feel added pressure to resolve issues quickly when dealing with the big boss lady.

Hoping to impress I jump into action thinking maybe, just maybe, if I do this right I can secure a pay raise or something. Much to my chagrin not a single soul has posted about this error. I did find posts of similar errors and one was from my most trusted and famous Steve Chapman.

After a bit of hymning and hawing I dusted off the cobwebs from way before the weekend and remember our most excellent payroll guru applied the new HIRE Act update for payroll to our own system late last week.

Now, my number one rule for doing any type of update or upgrade is torecreate the reports.dic file. It never fails (well, at least 50% of the time) Reports.dic gets corrupted. Don’t even say it David Musgrave. I know you are right about shared reports.dic files.

After recreating the reports.dic file everything was peachy keen. In the recreation process I found the 3 reports that had issues were:

  • Tax Schedule List
  • Trial Balance Detail
  • Trial Balance Detail by Period

I’m still waiting to hear about that pay raise.

*****************************

I ran this recreation process for the customer and it solved all of her problems. I’m not kidding. Every problem she had in life was gone. It’s amazing what recreating the reports.dic file can do for you. It even solves memory problems so I’m told.

I think I’ll try it on my system to see if it helps me gain some richness and famousness. Heck, I’d settle for my memory back.

Frequently asked questions for students using DynamicsCloud

This FAQ sheet is intended for students but professors may find this useful as well.

1. Signing up for DynamicsCloud

Please read the initial chapter in your book for detailed instructions on this. In summary navigate to www.DynamicsCloud.com and choose the Signup. You will be taken to the signup sheet where you should enter your Name, email address, University  and book publisher. If you choose No Book you will be directed to a PayPal page where you will be asked to pay for a semester hosting fee.

If you’ve purchased a book you should not pay anything extra as the hosting fee is included in your book cost.

The book code serial number can only be used once and after submission you will receive your login credentials by email by 9 am PST the next working business day.

Please email support@dynamicscloud.com if you do not receive an email within that time frame.

image

2. Logging into DynamicsCloud

You can go to www.dynamicscloud.com and choose login or you can go directly to https://dynaa.dynamicscloud.com/GP/Views/LogOn.aspx. You will have two login credentials. The first login always has a dynamicscloud\ in front of the user name where the second login is just the user name. An email will be sent outlining credentials and login instructions after submitting the book code serial number on the front of your book.

3. Not all required fields error in Dynamics GP- This is the most common question asked by students when using Dynamics GP. Please look at this blog post outlining how to overcome this persistent warning message: http://www.dynamicscloud.com/not-all-required-fields-have-been-entered-required-fields-appear-in-bold-black-type/

4. Sending reports to screen error, especially with financial statements – Another common issue where GP crashes and makes the user log out of the system: http://www.dynamicscloud.com/printing-financial-statement-with-the-web-client/

5. Error in communicating with server

This usually means your internet connection is too slow to reach the server. If you are on a wireless internet connection try plugging in with an Ethernet cable. We often see this where classrooms have everyone connected via wireless or at homes with slow internet connections.

Another common issue with slow internet connect includes some kind of variable of error like the following. The error itself is kind of pointless and the only thing you can do is to X out of the browser. Try connecting via an Ethernet cable to see if performance improves.

image

6. Can't login - http://www.dynamicscloud.com/cant-login-to-dynamicscloud/

7. Should I create a new session or connect to an old session?

In most cases you should select Create a New Session. You will be asked in GP to view your current sessions where you can delete your old session and reenter the company as desired.

If you get an error when you try to navigate around the GP system (Sales homepage for example) you should log out and try creating a new session.

If you try connecting to an existing session and it errors out it may cause issues as you try to login to certain homepages and this script will need to be run on the backend by DynamicsCloud support before you can navigate to these homepages again:

USE DYNAMICS;
GO
TRUNCATE TABLE SY07140

Error when logging into area pages (financials, Sales, etc.)

image

8. How do I properly log out of DynamicsCloud?

If you don’t want stuck sessions you should use the Exit GP option at the top right of your window as shown below.

image

9. Unexpected Error

This often occurs when the user ID is not entered correctly. Common variation are a space at the front of the user id (caused from copying and pasting in the username incorrectly) or a / instead of a \ between dynamicscloud\username.

Example – incorrectly putting in dynamicscloud/username will not work. It needs to be a backslash.

Spelling the user name incorrectly is another common thing that will cause this error. Example – dynamiccloud\username wont work. Neither will dynamicsclouds\username will not work.

We’ve seen some students have workstation issues.

Try clearing your browser history and deleting temp files. Enable Silverlight if you haven’t already. Otherwise email support@dynamicscloud.com and we will refresh the GP Session Services.

image

10. Changing passwords

Your initial password will always be the same but you can change your second password (login to GP). Navigate to Home page>>user preferences>>password.

If you forget your GP password you can contact your professor who can reset it or email support@dynamicscloud.com

11. How do I start over?

Choose the database restore option and when you choose submit it will prompt you for a $10 restore fee through paypal. You will then need to let us know what your user ID is so we know which database to restore.

image

12. What if I don’t have a PayPal account?

You can pay with a debit or credit card by choosing the option “Don’t have a PayPal Account?” as shown below.

image

13. Can I sell my book after I'm done with it?

No. The book validation code can not be used more than once.

14. How do I contact support and what are the support hours?

Email support@dynamicscloud.com. Office hours are 5:30 am PST to 5 PM PST. Please email support and we will do our best to respond during the same day as the support request is generated.

15. Can I use my Mac?

Yes. Please use Firefox with Silverlight enabled.

More details - You will have to download and install Silverlight (click on blue down arrow as shown in screenshot below and install Silverlight). After Silverlight is installed click on the tools button at the top right hand of the screen and choose Add-ons. Go down until you see Silverlight and choose Always Activate.

image

16. Can I use Dynamics with my own business ventures?

You cannot use this service for your own personal business. You can contact sales@dynamicscloud.com if you would like to discuss hosting fees for your own business venture. Typical monthly hosting fees are anywhere between $165 –$300 per user per month. You read that right. Students get a screaming deal to learn Dynamics in an academic setting.

17. Can I go to Microsoft Convergence?

Yes, please consider investing the time and money to attend. Microsoft Convergence is the premier customer conference where any one who’s anyone in the Dynamics community attend. This is a great time for students to get in front of perspective employers as well as have many learning opportunities throughout the week. Contact support@dynamicscloud.com and we can put you in touch with the Microsoft representatives who can provide guidance and discounts for students wanting to attend.

See http://www.microsoft.com/en/convergence/atlanta15/#fbid=AGOm2Evqj4l for more information.

18. If I retake the course to improve my grade can I use the same book?

With professor approval (email from the professor is sufficient) the student can go to the DynamicsCloud website and choose the “Course Retake with Professor email” as shown in the picture below. After paying for the $30 semester hosting fee we'll send out a fresh set of credentials.

Here is the page to pay for the semester hosting fee: http://www.dynamicscloud.com/signup/

It's not intended to let students sell the book however. The book publisher won't allow us to reissue hosting credentials except for the case of retaking a course.

The fee is $30 for the semester and the student will be sent to a PayPal site to make the payment after filling out the course retake option.

See above about paying through PayPal if you don’t have a PayPal account.

retake

19. Careers students can work towards in the Dynamics world

There are many exciting careers focused on ERP implementation, support, consulting, and training. With the right initiative students can find Microsoft partners willing to train and mentor newly graduated students all over the country. We usually work with professors who recommend candidates and team up with partners looking to hire resources.

Drop us an email with the subject “I want a Job” and include location, university you attend, and resume and we can pass your information on to hiring employers. Recommendations from professors go a long way in this pursuit.

20. I can’t see any options when I log into GP

This is most likely security related. Please try this: http://www.dynamicscloud.com/you-dont-have-security-privileges-to-this-window-contact-your-system-administrator-for-assistance/

If that doesn’t work please email support@dynamicscloud.com with your user ID and university you attend and we will fix.

21. I can’t save files on my workstation

For some actions, the Silverlight application that is used for the Microsoft Dynamics GP web client must access resources on the local machine.

For example, when a user generates a report that uses a Microsoft Word template, the Microsoft Word document that is created must be stored locally so that it can be viewed. Users also need to access the local file system, such as when using the attachments feature in Microsoft Dynamics GP.

By default, the Silverlight application for the web client runs in “sandboxed” mode, which restricts the access the application has to local machine resources. In this mode, prompts are displayed to the user each time that local resources are accessed. You can find out if the web client is running in sandboxed mode by looking at the status bar. If you see the padlock icon, then you are running in sandboxed mode.

The local machine can be configured to allow the Silverlight application to be run in “trusted” mode, which allows the application to access local system resources with fewer prompts for the user. An installer file named DynamicsGPTrustedApp.msi is provided on the web site for the Microsoft Dynamics GP web client installation. Users can download and run this installer, which configures the local system to trust Silverlight applications that run in the web browser. It also installs a security certificate that is used to sign the Silverlight application. This is also necessary so the application can be run in fulltrust mode. The easiest way to access this installer is to click on the padlock icon, which displays an information dialog about trusted applications. In sandboxed mode, prompts like this are displayed each time local resources are accessed. This icon in the status area indicates that the web client is running in sandboxed mode. Click this link to launch the installer.Click the Install Trusted Configuration link to run the DynamicsGPTrustedApp.msi. Elevated privileges are required to run this installer. After you have completed the installation, you will have to restart any web client session on the machine. In some cases, you may not be able to click on the padlock icon. Another way to access this installer is to open Internet Explorer, and begin entering the URL that you typically use to access the web client site. At the end of the URL, append the following: /DynamicsGPTrustedApp.msi As an example, the following is the full URL to access this installer file for the Contoso sample web client installation: https://gpuaweb.contoso.com/GP/DynamicsGPTrustedApp.msi

22. Silverlight won’t enable but I swear I’ve installed it

clip_image002

Please follow this blog post or try using Internet Explorer or Firefox: http://www.dynamicscloud.com/enable-silverlight-in-chrome/

23. When I run a Smartlist it flashes then nothing shows up

This is a known issue In the Web Client version we are running. Go into Smartlist | click on Smartlist next to Help | Go to Favorite Pane | uncheck Enable Auto-hide.

autohide

Conclusion

This FAQ sheet will be updated from time to time as other issues and questions arise. Please let us know if other issues need to be added to this list.

You don’t have security privileges to this window. Contact your system administrator for assistance

This error sometimes happens when a user is not assigned the proper security rights. If you log into another company and go to Administration>>user security you can add the proper role and reports ID to the user as shown below.

image

image

If you are not able to navigate to any of these windows you will have to contact Support@dynamicscloud.com for assistance.

Error when installing Dynamics GP Service Pack “Access to the path 'C:\Program Files (x86)\MicrosoftDynamics\GP\ Dynamics.exe.config’ is denied”

image This might be common sense to IT people. Otherwise, this will save you a lot of headache. By now, you’ve probably clicked on  Program Files (x86) a few times and wonder why you cannot give yourself Full Control security to this folder, and the answer is you cannot by design.

Solution

1.Double click to open Program File (x86) 2.Right Click on Microsoft Dynamics folder, click on Security Tab>>Edit>>select the correct Group or usernames. 3.Give yourself Full Control security to the Microsoft Dynamics folder, click OK 4. Try installing your Service Pack again If Error still persists, check these:
  • Repeat steps 1-4 above and make sure that you selected the correct Group or usernames in step 2
  • Double click on Microsoft Dynamics folder, right click on GP folder and make sure you have Full control under the security tab.
image image Good Luck!

Error When Changing Decimal Places for Inventory :[You cannot complete this process while invoices are being processed]

This can be very frustrating when you’ve already confirmed that there are no pending transactions in the activity tables. You already cleared DEX_LOCK, DEX_SESSION, ACTIVITY, SY00800, SY00801 and it still doesn't fix the issue.  I know how it feels.

Cause

This error is referring to the SOP/POP documents that remain in the OPEN tables even after they are posted. Here’s a great article on how documents move from Work to Open to History regarding the core modules of GP.  http://dynamicsconfessions.blogspot.com/2009/11/moving-from-work-to-open-to-history.html

Solution

You need to run the 'Paid Transaction Removal' and “Remove Completed Purchase Orders” routine in order to move transactions to History. Nothing automatically moves to history. This routine will only move fully applied, closed and cancelled  documents to history. So if you still have a balance on an invoice it stays in the Open Table. If a credit memo or receipt has not been applied to an invoice, it will stay in the Open table. The fact that the customer's net balance is zero does not impact whether the document moves to history. 1. Make a backup 2. Tools>Utilities>>Sales>>Routine>Paid Transaction Removal image 3. Tools>Utilities>>Purchasing>>Remove Completed Purchase Orders image 4. Now you should be able to go through and change your decimal places. If the error still persists, then there are probably Sales documents remaining in the open table. You need to close them before they can be moved to history as shown in step #2 above. Good luck!

Sign Up2

[formidable id=22]

Thank You!

We will contact you Shortly.