Saturday, February 20, 2010

Using SQL Wildcards [%_] in Smart List

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:

SQL_wildcards

Regards,
--
Mohammad R. Daoud - CTO
MVP, MCP, MCT, MCBMSP, MCTS, MCBMSS
+962 - 79 - 999 65 85 
mohdaoud@gmail.com
mohdaoud.blogspot.com

Script to Get Users Logged in to GP with no activities!?

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!

SELECT
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,
A.USERID,
A.CMPNYNAM COMPANY_NAME,
INTERID COMPANY_ID,
LOGINDAT + LOGINTIM LOGIN_DATE_TIME,
SQLSESID SQL_SESSIONID,
P.login_time SQL_LOGINTIME,
P.last_batch SQL_LAST_BATCH,
DATEDIFF(mi, P.last_batch, GETDATE()) TIME_SINCE_LAST_ACTION,
S.session_id SQLSERVER_SESSIONID,
S.sqlsvr_spid SQLSERVER_PROCESSID,
P.spid PROCESSID,
P.status PROCESS_STATUS,
P.net_address NET_ADDRESS,
P.dbid DATABASE_ID,
P.hostname HOSTNAME
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

Regards,
--
Mohammad R. Daoud - CTO
MVP, MCP, MCT, MCBMSP, MCTS, MCBMSS
+962 - 79 - 999 65 85 
mohdaoud@gmail.com
mohdaoud.blogspot.com

Duplicate Journal Entry Numbers!?!

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.

Regards,
--
Mohammad R. Daoud - CTO
MVP, MCP, MCT, MCBMSP, MCTS, MCBMSS
+962 - 79 - 999 65 85 
mohdaoud@gmail.com
mohdaoud.blogspot.com

Reconcile Checkbook with no transactions

Another workaround by Frank, check it out here.

Regards,
--
Mohammad R. Daoud - CTO
MVP, MCP, MCT, MCBMSP, MCTS, MCBMSS
+962 - 79 - 999 65 85 
mohdaoud@gmail.com
mohdaoud.blogspot.com

Backorder Service Items

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.

Regards,
--
Mohammad R. Daoud - CTO
MVP, MCP, MCT, MCBMSP, MCTS, MCBMSS
+962 - 79 - 999 65 85 
mohdaoud@gmail.com
mohdaoud.blogspot.com

Excel Pivot Tables

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.

Regards,
--
Mohammad R. Daoud - CTO
MVP, MCP, MCT, MCBMSP, MCTS, MCBMSS
+962 - 79 - 999 65 85 
mohdaoud@gmail.com
mohdaoud.blogspot.com

Maximum Number of Notes Forms

“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.

Regards,
--
Mohammad R. Daoud - CTO
MVP, MCP, MCT, MCBMSP, MCTS, MCBMSS
+962 - 79 - 999 65 85 
mohdaoud@gmail.com
mohdaoud.blogspot.com

Group Transactions in Integration Manager

Mariano came across a question he got on how to group transactions by date in Integration Manager, checkout how did he resolve this here.

Regards,
--
Mohammad R. Daoud - CTO
MVP, MCP, MCT, MCBMSP, MCTS, MCBMSS
+962 - 79 - 999 65 85 
mohdaoud@gmail.com
mohdaoud.blogspot.com

Requisition Management

Steve Chapman posted an article with videos about Requisition Management module, checkout his post here.

Regards,
--
Mohammad R. Daoud - CTO
MVP, MCP, MCT, MCBMSP, MCTS, MCBMSS
+962 - 79 - 999 65 85 
mohdaoud@gmail.com
mohdaoud.blogspot.com

Friday, February 19, 2010

Manufacturing “By Products”

Great article by Waqas where he explained how to handle By Products in the manufacturing bill of material, checkout his post.

Regards,
--
Mohammad R. Daoud - CTO
MVP, MCP, MCT, MCBMSP, MCTS, MCBMSS
+962 - 79 - 999 65 85 
mohdaoud@gmail.com
mohdaoud.blogspot.com

What is the Currency ISO code used for?

To get the answer, read this post by Steve Endow.

Regards,
--
Mohammad R. Daoud - CTO
MVP, MCP, MCT, MCBMSP, MCTS, MCBMSS
+962 - 79 - 999 65 85 
mohdaoud@gmail.com
mohdaoud.blogspot.com

How to edit VBA code without having VBA module registered?!?

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.

Regards,
--
Mohammad R. Daoud - CTO
MVP, MCP, MCT, MCBMSP, MCTS, MCBMSS
+962 - 79 - 999 65 85 
mohdaoud@gmail.com
mohdaoud.blogspot.com

Professional Services Tools

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.

Regards,
--
Mohammad R. Daoud - CTO
MVP, MCP, MCT, MCBMSP, MCTS, MCBMSS
+962 - 79 - 999 65 85 
mohdaoud@gmail.com
mohdaoud.blogspot.com

“The note ID has not been set”

David in his “Fixing missing Note Index Values” post resolved the notes indexing errors, checkout his post here.

Regards,
--
Mohammad R. Daoud - CTO
MVP, MCP, MCT, MCBMSP, MCTS, MCBMSS
+962 - 79 - 999 65 85 
mohdaoud@gmail.com
mohdaoud.blogspot.com

Dynamics GP VS Tools 2010

Patrick Roth summarized the new features of the VS Tools 2010 for Microsoft Dynamics GP, check his post here.

Regards,
--
Mohammad R. Daoud - CTO
MVP, MCP, MCT, MCBMSP, MCTS, MCBMSS
+962 - 79 - 999 65 85 
mohdaoud@gmail.com
mohdaoud.blogspot.com

Support Debugging Tool

David at the Developing for Microsoft Dynamics GP blog announced the availability of the Support Debugging Tool Build 12, he summarized the enhancements and fixes in this post.

Regards,
--
Mohammad R. Daoud - CTO
MVP, MCP, MCT, MCBMSP, MCTS, MCBMSS
+962 - 79 - 999 65 85 
mohdaoud@gmail.com
mohdaoud.blogspot.com

Long time no posts!

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.

Regards,
--
Mohammad R. Daoud - CTO
MVP, MCP, MCT, MCBMSP, MCTS, MCBMSS
+962 - 79 - 999 65 85 
mohdaoud@gmail.com
mohdaoud.blogspot.com

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”

image

Solution:

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:

clip_image002

Regards,
--
Mohammad R. Daoud - CTO
MVP, MCP, MCT, MCBMSP, MCTS, MCBMSS
+962 - 79 - 999 65 85 
mohdaoud@gmail.com
mohdaoud.blogspot.com

Filter Lookups By Predefined Smart-list Favorite “Simple data level security method”

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
End Sub

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
        MsgBox CompilerMessage
    End If
End Sub

6. Give your use the access to the modified form instead of the original and enjoy having your form looks like the below:

image 

Hope that this helps.

Regards,
--
Mohammad R. Daoud - CTO
MVP, MCP, MCT, MCBMSP, MCTS, MCBMSS
+962 - 79 - 999 65 85 
mohdaoud@gmail.com
mohdaoud.blogspot.com

Related Posts:

Related Posts with Thumbnails