Today is full with great tips! Frank posted a great article about smart list search, where you can use SQL Wildcards when filtering the Smart List:
Saturday, February 20, 2010
Great script to get idle users that have GP open with no activities worth testing!!
Thanks to Frank for this script who in return did thank Ron Wilson and Sivakumar Venkataraman for this great tip!
CASE WHEN S.session_id IS NULL THEN 'Missing DEX_SESSION' ELSE '' END MISSING_SESSION,
CASE WHEN DATEDIFF(mi, P.last_batch, GETDATE()) > 1 THEN 'Idle for ' + LTRIM(RTRIM(STR(DATEDIFF(mi, P.last_batch, GETDATE())))) + ' minutes.' ELSE '' END AS IDLE_TIME_DESC,
CASE WHEN DATEDIFF(mi, P.last_batch, GETDATE()) > 1 THEN DATEDIFF(mi, P.last_batch, GETDATE()) ELSE 0 END AS IDLE_TIME,
LOGINDAT + LOGINTIM LOGIN_DATE_TIME,
DATEDIFF(mi, P.last_batch, GETDATE()) TIME_SINCE_LAST_ACTION,
FROM DYNAMICS..ACTIVITY A
LEFT JOIN DYNAMICS..SY01400 U ON A.USERID = U.USERID
LEFT JOIN DYNAMICS..SY01500 C ON A.CMPNYNAM = C.CMPNYNAM
LEFT JOIN tempdb..DEX_SESSION S ON A.SQLSESID = S.session_id
LEFT JOIN master..sysprocesses P ON S.sqlsvr_spid = P.spid AND ecid = 0
LEFT JOIN master..sysdatabases D ON P.dbid = D.dbid
This subject published almost on all the GP blogs! Below are the reasons:
1. Reversing journal entries use the same JE number
2. Recurring journal entries (actually, recurring batches) repeat journal entry numbers with each recurrence
3. If you post a journal entry to a closed year, GP uses the same JE number in the current year to close the activity to Retained Earnings.
Another workaround by Frank, check it out here.
Frank posted a great tip about service items backorder, as the application does not automatically check for stock availability for such type of items, checkout Frank post here.
Great article by Amy Walsh on how to create simple pivot table in Excel, good article for newbie's as he described the process step by step, checkout the article here.
“You have too many note windows open. Close a note window”
Mariano in his post about Notes in Microsoft Dynamics GP get me a new tip that only 5 Notes Forms could be open at the same time, checkout the technicalities behind this by reading his article.
Mariano came across a question he got on how to group transactions by date in Integration Manager, checkout how did he resolve this here.
Steve Chapman posted an article with videos about Requisition Management module, checkout his post here.
Friday, February 19, 2010
Great article by Waqas where he explained how to handle By Products in the manufacturing bill of material, checkout his post.
To get the answer, read this post by Steve Endow.
Great article at “In Touch with Dynamics GP” blog, it resolves the question in the subject, thanks to Vaidy for pointing me to this post.
Great list by Leslie at the Dynamics Confessor blog, she listed allot of tools available for purchase that are not a part of Dynamics GP out of the box, check the list in her post by clicking this link.
David in his “Fixing missing Note Index Values” post resolved the notes indexing errors, checkout his post here.
Patrick Roth summarized the new features of the VS Tools 2010 for Microsoft Dynamics GP, check his post here.
It been a while since my last post, actually I been engaged in upgrading Microsoft Dynamics GP from version 9.0 to version 10 SP4 for one of my clients.
The problem was not in the upgrade as is, the client is running 14 companies in different locations and each company has its own installed modules! Normally such case must not exist, but when I went into the investigation, I found that the consultants whose been working on this used to install “unwanted” modules for testing on one of the companies and then they “removes” it from the “Add/Remove Features” when they found it unwanted.
However, such operation leaves the database with the modules records, where the modules will need to be upgraded with the upgrade process.
I will try to catch what I missed the last few days! Wish me luck.
Wednesday, February 3, 2010
Fixed Assets Depreciation Error: Target Date’s fiscal year may not be greater than the current fiscal year for the selected book
You might get an error when trying to depreciate your assets upon the new year as below:
“Target Date’s fiscal year may not be greater than the current fiscal year for the selected book”
Current Fiscal Year for your book is not the current year, to correct this, just go to "Microsoft Dynamics GP menu >> Tools >> Setup >> Fixed Assets >> Book" and make sure that the "Current Fiscal Year" is set to the current year as shown below:
Sometimes you might need to restrict access to some items in your store for certain machines, which is currently not possible out of the box, this method is not totally restriction but it could help, I am using the smart list favorites to filter lookups, follow steps below to implement:
1. Go to smart list and add some filters on the inventory items smart list then save it as a favorite smart list.
2. Go to Items lookup and add the form to VBA, then add “ViewAll” button to the VBA.
3. Write the following code segments in VBA to disable the “ViewAll” and “Advanced Search” buttons after selecting the needed favorite:
Private Sub ViewAllItemNumbersbyItemNumber_AfterUserChanged()
ViewAllItemNumbersbyItemNumber.Enabled = False
PBAdvancedSearch.Enabled = False
5. After loading the window we’ll need to register a macro that selects the needed favorite, the code will look like the following:
Private Sub Window_AfterOpen()
'Dim CompilerApp As New Dynamics.Application
Dim CompilerApp As Object
Dim CompilerMessage As String
Dim CompilerError As Integer
Dim Commands As String
' Create link without having reference marked
Set CompilerApp = CreateObject("Dynamics.Application")
Commands = ""
Commands = Commands & "local integer l_file_id; " & vbCrLf
Commands = Commands & "local string pathname; " & vbCrLf
Commands = Commands & "pathname = Path_GetForApp(1) + ""TEMP.MAC""; " & vbCrLf
Commands = Commands & "l_file_id = TextFile_Open(pathname, 0, 0); " & vbCrLf
Commands = Commands & "TextFile_WriteLine(l_file_id, "" ActivateWindow dictionary 'SmartList' form 'IV_Item_Number_Lookup' window 'IV_Item_Number_Lookup' ""); " & vbCrLf
Commands = Commands & "TextFile_WriteLine(l_file_id, "" MoveTo field 'ASI_LU_View_Button' item 5 # 'Restricted List...' ""); " & vbCrLf
Commands = Commands & "TextFile_WriteLine(l_file_id, "" ClickHit field 'ASI_LU_View_Button' item 8 # 'Rams' ""); " & vbCrLf
Commands = Commands & "TextFile_Close(l_file_id); " & vbCrLf
Commands = Commands & "if File_Probe(pathname) then " & vbCrLf
Commands = Commands & " run macro pathname; " & vbCrLf
Commands = Commands & "end if; " & vbCrLf
' Execute SanScript
CompilerError = CompilerApp.ExecuteSanscript(Commands, CompilerMessage)
If CompilerError <> 0 Then
6. Give your use the access to the modified form instead of the original and enjoy having your form looks like the below:
Hope that this helps.