Tuesday, April 28, 2009

Transferring Extender customizations from one company database to another

The Microsoft Dynamics GP public newsgroup is always an amazing source of inspiration for new articles, this time, along the lines of a topic that I have been following closely since the announcements of the additions in Dynamics GP 10.0 Service Pack 4: Extender.

Today, we will explore two methods for transferring Extender customizations between company databases.


Method 1: Extender's Export/Import functionality

Extender offers a native method for transferring customizations between company databases with the following steps:

1. Open Extender's Export window. Go to Microsoft Dynamics GP > Tools > Extender > Export.




a) Click on the Folder button to select the path and enter the file name for your export. Hint: try to use the name of the customization being exported as the file name.

b) Select the customization(s) to be exported by clicking on each of the objects.

2. Click Save to continue. This will create the XML export file with the customizations.

3. Switch companies in Dynamics GP. Click the Company Name on the Status bar and select the company where you would like to import the customizations into from the Company drop-down list. Click the OK button to continue.

4. Open Extender's Import window. Go to Microsoft Dynamics GP > Tools > Extender > Import.



a) Click on the Folder button and select the file previously exported in steps 1 and 2.

b) Click on the Import button to continue. This action will read the XML file and build the customizations in your target company database.


Method 2: Support Debugging Tool

That's right! You can also use Support Debugging Tools' XML Table Export and XML Table Import capabilities. With the aid of the sampling scripts presented in my previous post you should be well on your way to identify the tables that contain data. Once you have identified them, you can now use Support Debugging Tool to extract the data into an XML file from your source company database.

1. Open Support Debugging Tool's XML Table Export window. Click on the Options button, then select XML Table Export.



2. In the Export window, select the tables to be exported.



3. Switch companies in Dynamics GP. Click the Company Name on the Status bar and select the company where you would like to import the customizations into from the Company drop-down list. Click the OK button to continue.

4. Open Support Debugging Tool's XML Table Import window. Click on the Options button, then select XML Table Import.

5. In the Import window, select the file to be imported.



Now, click on the import button and you are done!

Extender allows users to perform easy customizations in their Dynamics GP system with just a few clicks or with minimum assistance from a developer. It's time to harness the power of the tool. I will continue to explore other functions available to Extender user throughout the coming days.

Until next post!

MG.-
Mariano Gomez, MVP
Maximum Global Business, LLC
http://www.maximumglobalbusiness.com/

Congratulations to MVP Mark Polino on his 1000th post



"One thousand is a lot of posts on one subject
and all of them came in less than 4 years..."





If you thought for one instance that I could spit out articles for my blog like crazy, try this: MVP Mark Polino's DynamicAccounting.net has hit 1000 posts! You have to give this guy credit... he has done a lot for the Microsoft Dynamics GP community and is certainly the reason why I started doing this too.

Congratulations Mark! Hope to read the next 1000 posts.

Until next post!

MG.-
Mariano Gomez, MIS, PMP
Maximum Global Business, LLC
http://www.maximumglobalbusiness.com/

Saturday, April 25, 2009

Sampling data from Extender tables

Lets be honest! One of the most confusing aspects of working with Extender is being able to tell where things get stored. Even though all table names are pretty clear and need not too much explanation, the bottom line is people still get confused when trying to figure out what is stored where.

I for once, have spent some time working on a T-SQL script that will show all the data stored in Extender tables. I constantly use this method, because I can clearly see where things are when I need to look around for some way data got stored, without having to go executing SELECT statements one by one on each table.

The method is broken down into two steps: the script that retrieves all the extender tables, producing a formatted SELECT statement, and the script (a result of the former script) which actually displays the data in each table.

GetExtenderTables.sql

-- Created by Mariano Gomez, MVP
-- Code is "AS IS". No warranties expressed or implied and conferes no rights
SELECT '/* ' + RTRIM(TABLE_NAME)
+ '*/ SELECT ''' + RTRIM(TABLE_NAME) + ''' AS Extender_Table, * FROM '
+ RTRIM(TABLE_NAME) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME LIKE 'EXT%'

NOTE: This query is only supported on Microsoft SQL Server 2005 and 2008.

When executed, the above query will produce the following results:

RetrieveExtenderData.sql

-- Created by Mariano Gomez, MVP
-- Code is "AS IS". No warranties expressed or implied and conferes no rights
/* EXT00100*/ SELECT 'EXT00100' as Extender_Table, * FROM EXT00100
/* EXT00101*/ SELECT 'EXT00101' as Extender_Table, * FROM EXT00101
/* EXT00102*/ SELECT 'EXT00102' as Extender_Table, * FROM EXT00102
/* EXT00103*/ SELECT 'EXT00103' as Extender_Table, * FROM EXT00103
/* EXT00104*/ SELECT 'EXT00104' as Extender_Table, * FROM EXT00104
/* EXT00150*/ SELECT 'EXT00150' as Extender_Table, * FROM EXT00150
/* EXT00151*/ SELECT 'EXT00151' as Extender_Table, * FROM EXT00151
/* EXT00180*/ SELECT 'EXT00180' as Extender_Table, * FROM EXT00180
/* EXT00181*/ SELECT 'EXT00181' as Extender_Table, * FROM EXT00181
/* EXT00182*/ SELECT 'EXT00182' as Extender_Table, * FROM EXT00182
/* EXT00183*/ SELECT 'EXT00183' as Extender_Table, * FROM EXT00183
/* EXT00184*/ SELECT 'EXT00184' as Extender_Table, * FROM EXT00184
/* EXT00185*/ SELECT 'EXT00185' as Extender_Table, * FROM EXT00185
/* EXT00900*/ SELECT 'EXT00900' as Extender_Table, * FROM EXT00900
/* EXT10200*/ SELECT 'EXT10200' as Extender_Table, * FROM EXT10200
/* EXT30200*/ SELECT 'EXT30200' as Extender_Table, * FROM EXT30200
/* EXT40010*/ SELECT 'EXT40010' as Extender_Table, * FROM EXT40010
/* EXT40011*/ SELECT 'EXT40011' as Extender_Table, * FROM EXT40011
/* EXT40012*/ SELECT 'EXT40012' as Extender_Table, * FROM EXT40012
/* EXT40013*/ SELECT 'EXT40013' as Extender_Table, * FROM EXT40013
/* EXT40040*/ SELECT 'EXT40040' as Extender_Table, * FROM EXT40040
/* EXT40100*/ SELECT 'EXT40100' as Extender_Table, * FROM EXT40100
/* EXT40101*/ SELECT 'EXT40101' as Extender_Table, * FROM EXT40101
/* EXT40102*/ SELECT 'EXT40102' as Extender_Table, * FROM EXT40102
/* EXT40103*/ SELECT 'EXT40103' as Extender_Table, * FROM EXT40103
/* EXT40104*/ SELECT 'EXT40104' as Extender_Table, * FROM EXT40104
/* EXT40105*/ SELECT 'EXT40105' as Extender_Table, * FROM EXT40105
/* EXT40106*/ SELECT 'EXT40106' as Extender_Table, * FROM EXT40106
/* EXT40107*/ SELECT 'EXT40107' as Extender_Table, * FROM EXT40107
/* EXT40108*/ SELECT 'EXT40108' as Extender_Table, * FROM EXT40108
/* EXT40109*/ SELECT 'EXT40109' as Extender_Table, * FROM EXT40109
/* EXT40110*/ SELECT 'EXT40110' as Extender_Table, * FROM EXT40110
/* EXT40111*/ SELECT 'EXT40111' as Extender_Table, * FROM EXT40111
/* EXT40117*/ SELECT 'EXT40117' as Extender_Table, * FROM EXT40117
/* EXT40150*/ SELECT 'EXT40150' as Extender_Table, * FROM EXT40150
/* EXT40151*/ SELECT 'EXT40151' as Extender_Table, * FROM EXT40151
/* EXT40152*/ SELECT 'EXT40152' as Extender_Table, * FROM EXT40152
/* EXT40153*/ SELECT 'EXT40153' as Extender_Table, * FROM EXT40153
/* EXT40154*/ SELECT 'EXT40154' as Extender_Table, * FROM EXT40154
/* EXT40157*/ SELECT 'EXT40157' as Extender_Table, * FROM EXT40157
/* EXT40160*/ SELECT 'EXT40160' as Extender_Table, * FROM EXT40160
/* EXT40162*/ SELECT 'EXT40162' as Extender_Table, * FROM EXT40162
/* EXT40163*/ SELECT 'EXT40163' as Extender_Table, * FROM EXT40163
/* EXT40164*/ SELECT 'EXT40164' as Extender_Table, * FROM EXT40164
/* EXT40170*/ SELECT 'EXT40170' as Extender_Table, * FROM EXT40170
/* EXT40200*/ SELECT 'EXT40200' as Extender_Table, * FROM EXT40200
/* EXT40201*/ SELECT 'EXT40201' as Extender_Table, * FROM EXT40201
/* EXT40400*/ SELECT 'EXT40400' as Extender_Table, * FROM EXT40400
/* EXT40600*/ SELECT 'EXT40600' as Extender_Table, * FROM EXT40600
/* EXT40601*/ SELECT 'EXT40601' as Extender_Table, * FROM EXT40601
/* EXT40602*/ SELECT 'EXT40602' as Extender_Table, * FROM EXT40602
/* EXT40700*/ SELECT 'EXT40700' as Extender_Table, * FROM EXT40700
/* EXT40701*/ SELECT 'EXT40701' as Extender_Table, * FROM EXT40701
/* EXT40702*/ SELECT 'EXT40702' as Extender_Table, * FROM EXT40702
/* EXT40703*/ SELECT 'EXT40703' as Extender_Table, * FROM EXT40703
/* EXT40900*/ SELECT 'EXT40900' as Extender_Table, * FROM EXT40900
/* EXT40901*/ SELECT 'EXT40901' as Extender_Table, * FROM EXT40901
/* EXT43200*/ SELECT 'EXT43200' as Extender_Table, * FROM EXT43200
/* EXT43201*/ SELECT 'EXT43201' as Extender_Table, * FROM EXT43201
/* EXT43202*/ SELECT 'EXT43202' as Extender_Table, * FROM EXT43202
/* EXT43204*/ SELECT 'EXT43204' as Extender_Table, * FROM EXT43204
/* EXT43205*/ SELECT 'EXT43205' as Extender_Table, * FROM EXT43205
/* EXT43400*/ SELECT 'EXT43400' as Extender_Table, * FROM EXT43400
/* EXT43900*/ SELECT 'EXT43900' as Extender_Table, * FROM EXT43900
/* EXT43901*/ SELECT 'EXT43901' as Extender_Table, * FROM EXT43901
/* EXT43902*/ SELECT 'EXT43902' as Extender_Table, * FROM EXT43902
/* EXT44100*/ SELECT 'EXT44100' as Extender_Table, * FROM EXT44100
/* EXT44200*/ SELECT 'EXT44200' as Extender_Table, * FROM EXT44200
/* EXT44300*/ SELECT 'EXT44300' as Extender_Table, * FROM EXT44300
/* EXT45000*/ SELECT 'EXT45000' as Extender_Table, * FROM EXT45000
/* EXT50100*/ SELECT 'EXT50100' as Extender_Table, * FROM EXT50100
/* EXT60100*/ SELECT 'EXT60100' as Extender_Table, * FROM EXT60100

Now, you can now copy and paste the above results in a new query window and once again execute against your company database. Now you should be able to see how the data is stored. What I appreciate about this query (and not because I wrote it :-) ) is the fact that it displays the table name as part of the result set. This allows you to know directly where a row came from.

If your goal is to produce a SQL Server view that will link this data, then check David Musgrave's article Creating SQL Views of Extender Data for more information. However, if you are seeking to do this via Extender itself, then you need to check MVP Victoria Yudin's post on Creating Extender Views.

Until next post!

MG.-
Mariano Gomez, MVP
Maximum Global Business, LLC
http://www.maximumglobalbusiness.com/

Friday, April 24, 2009

Microsoft Dynamics GP roadmap

For those of you who did not attend Convergence 2009 and have been asking about release dates of service packs and new versions of Dynamics GP, the following shows the anticipated product roadmap. Keep in mind that release dates and product features may change on a dime and by no means can be considered absolute Microsoft commitments.




One things is very sure: Microsoft Dynamics GP 10.0 Service Pack 4 is just right around the corner and with it comes a revamped Extender product. For those of you wondering about the feature of the customization tools, David Musgrave has some reflections on that subject. Click here to read what he has to say. Plus I have the following to add: if you are a Microsoft Dexterity developer or a Visual Basic for Applications guy, Extender is still not the panacea for complex business requirements. Yes, you will be able to add new windows, yes you will have the ability to create transaction workflows. However, there is only so much Extender can do as it still operates within a set framework defined by its programming logic and that's where you come in as a developer.

Until next post!

MG.-
Mariano Gomez, MIS
Maximum Global Business, LLC
http://www.maximumglobalbusiness.com/

Thursday, April 23, 2009

New MSDynamicsWorld article "Microsoft Dynamics GP 3-Tier architecture"

"GP DPS and DPM Can Be Useful Tools"

I just managed to finalize my new article on "Dynamics GP 3-tier architecture" and get it published on MSDynamicsWorld. I will be following up this article with a blog post on Process Server configuration. Stay tuned!

Until next post!

MG.-
Mariano Gomez, MVP
Maximum Global Business, LLC
http://www.maximumglobalbusiness.com

Virtual Convergence Site now available

If you missed out on Convergence 2009 in New Orleans -- I know I did ! -- you can go to the Virtual Convergence site. The site is pretty cool! Once the site loads, you can access almost everything as a non-attendee to the event. If you attended Convergence, you can use your CommNet credentials to unlock the full features of the site and view all sessions.




Enjoy all the information available on the site. I am always a big fan of the keynote session.

Until next post!

MG.-
Mariano Gomez, MVP
Maximum Global Business, LLC
http://www.maximumglobalbusiness.com

Wednesday, April 22, 2009

Using T-SQL and recursive CTE to generate a BOM tree

Ever wonder how to replicate a Dynamics GP Bill of Materials tree with SQL Server? The following query uses recursive CTE to generate a BOM tree.

BOMTree.sql

-- Mariano Gomez, MVP
-- This query is provided "AS IS". No warranties expressed or implied.
WITH BOMCTE (bom_path, tree_path, item, component, qty, effective_date, lvl) AS (
SELECT CAST(RTRIM(ITEMNMBR) AS VARCHAR(MAX)),
CAST(RTRIM(ITEMNMBR) AS VARCHAR(MAX)),
ITEMNMBR,
NULL,
--CAST(NULL AS VARCHAR(MAX)),
CAST(NULL AS NUMERIC(19, 5)),
effective_date,
0 AS lvl
FROM BM00101

UNION ALL

SELECT CAST(RTRIM(H.ITEMNMBR) + '/' +
RTRIM(B.CMPTITNM) AS VARCHAR(MAX)),
CAST(REPLICATE(' ', 13) +
RTRIM(B.CMPTITNM) AS VARCHAR(MAX)),
H.ITEMNMBR,
B.CMPTITNM,
B.Design_Qty,
B.effective_date,
1 AS lvl
FROM BM00101 AS H
JOIN BM00111 AS B ON (H.ITEMNMBR = B.ITEMNMBR)

UNION ALL

SELECT C.bom_path + '/' + RTRIM(B.CMPTITNM),
CAST(REPLICATE(' ', (C.lvl + 1) * 13) + RTRIM(B.CMPTITNM) AS VARCHAR(MAX)),
B.ITEMNMBR,
B.CMPTITNM,
B.Design_Qty,
B.Effective_Date,
C.lvl + 1
FROM BM00111 AS B
JOIN BOMCTE AS C ON (B.CMPTITNM = C.item)
)
SELECT DISTINCT bom_path, tree_path, lvl
FROM BOMCTE
ORDER BY bom_path;

A common table expression (CTE) provides the significant advantage of being able to reference itself, thereby creating a recursive CTE. A recursive CTE is one in which an initial CTE is repeatedly executed to return subsets of data until the complete result set is obtained.

A query is referred to as a recursive query when it references a recursive CTE. Returning hierarchical data is a common use of recursive queries, for example: Displaying employees in an organizational chart, or data in a bill of materials scenario in which a parent product has one or more components and those components may, in turn, have subcomponents or may be components of other parents.

The bottom line is, you can apply this concept to Dynamics GP's BOM header and detail tables in both the Inventory Control and Manufacturing modules to generate a path and tree representation of your bill.

Related Articles

Recursive Queries Using Common Table Expressions - MSDN. Click here.
Hierarchies in SQL Server 2008 - Plamen Ratchev. Click here.

Acknowledgements

Thanks to Microsoft SQL Server MVP Plamen Ratchev at SQL Studio for his contribution to this article. You can visit Plamen's blog at http://pratchev.blogspot.com/




Until next post!

MG.-
Mariano Gomez, MIS, MVP, MCP, PMP
Maximum Global Business, LLC
http://www.maximumglobalbusiness.com/

Tuesday, April 21, 2009

FRx and Enterprise Reporting roadmap

I usually don't talk much about product strategy, because it tends to vary all too often, and what is said today, may not necessarily be what will be delivered tomorrow. However, and I as mentioned yesterday in the ad campaign post, I went to a rehashed sales seminar with excerpts from Convergence 2009 in New Orleans, where the roadmap for FRx and Enterprised Reporter was presented.




Among other things, Management Reporter will become the de facto financial reporting tool built on the ever expanding Business Intelligence and MOSS platforms, and the overall Corporate Performance Management strategy. However, Management Reporting is undergoing some major development to incorporate support for Multicurrency -- currently supported by FRx and Enterprise Reporting.

While Dynamics GP "V11" will offer support for both FRx and Management Reporter, new customers will only be shipped Management Reporter (MR V2), but can continue using the Forecaster product. Dynamics GP "V12" will mark the end of FRx and Forecaster as functionality delivered by both products will be found in Management Reporter (MR V3). The other product to follow suit will be Enterprise Reporting with the release of Management Reporter v4 by CY2014.

Although the timelines seem pretty far out, it is a great time to start developing new skills in the underlaying platform tools like Analysis Cubes and MOSS. They will certainly make transitioning to Management Reporter a breeze.

Related Articles

The Future of FRx and Management Reporter - Jan Harrigan @ FRx Buzz. Click here.
Convergence Thursday Update - Mark Polino @ DynamicAccounting.net. Click here.

Until next post!

MG.-
Mariano Gomez, MVP
Maximum Global Business, LLC
http://www.maximumglobalbusiness.com/

Monday, April 20, 2009

Microsoft Dynamics GP "More and Less" ad campaign

Folks, I just came out of an all day partner sales seminar and thought I would post the link to the Extending the Reach campaign. The site features a bunch of cool demos of the capabilities of Dynamics GP with a host of other resources that I believe you should be introducing and offering to your customers and prospects -- I know I will be! -- but what stands out the most is this, properly named More and Less ad. You may have seen it on certain cable or satellite channels as well.

video

Until next post!

MG.-
Mariano Gomez, MVP
Maximum Global Business, LLC
http://www.maximumglobalbusiness.com/

Saturday, April 18, 2009

Microsoft Dynamics GP 10 POWERUSER role vs Microsoft SQL Server sysadmin role

Dynamics GP system administrators and Microsoft SQL Server DBAs often ask, "Why can't I setup users if I am a member of the POWERUSER role in Dynamics GP?". The question can sometimes be paraphrased as "Why the SQL Server system administrator (sa) user is the only one that can setup users?". The answer is simple: the Dynamics GP POWERUSER role is application specific, while the SQL Server sysadmin role is database specific.

Since sa is a member of both the POWERUSER role in Dynamics GP and the sysadmin role in SQL Server, it can perform all maintenance operations of users in Dynamics GP, along with the setup of additional users. This allows the sa user login, in turn, to create the necessary logins in SQL Server. This is good if you are a DBA, but what happens when you are out and more users need to be added to the system.

So you may now be asking, "How do I make other Dynamics GP users have the same abilities to setup users like the sa user?". You will have to make the Dynamics GP user a member of a role in Dynamics GP with ability to create users -- perhaps, the POWERUSER role or the ADMIN_SYSTEM_001* security role -- and a member of the sysadmin role in SQL Server. To do this follow these steps (assuming you want the user to have full access to all Dynamics GP options throughout the system):

1. Log into Microsoft Dynamics GP as sa.

2. Assign the Dynamics GP user to the POWERUSER role. Go to Microsoft Dynamics GP > Tools > Setup > System > User Security. Choose the user login and mark the POWERUSER role in the access list.





3. The system will warn about the user access to all application functionality. Click on OK to continue.




NOTE: Depending on your security requirements, you may not want to grant access to the POWERUSER role. You can always create a custom role with access to the User Setup window or use the built-in ADMIN_SYSTEM_001* role.

4. Now, proceed to assign the user login to the sysadmin role in SQL Server. Open Microsoft SQL Server Management Studio, open the Security folder, open the Logins subfolder.




5. Double-click on the corresponding user login to open the Login Properties window. Select the Server Roles page and mark the sysadmin role.




6. Click the OK button to finalize the configuration.

Now your Dynamics GP user should be able to setup new users and maintain existing ones, along with performing other SQL Server maintenance activities within the application.

Related Articles
  • The Microsoft Dynamics GP Application Level Security Series. David Musgrave at Developing for Dynamics GP. Click here.

  • Microsoft Dynamics GP Password Implementation. Click here.


Until next post!

MG.-
Mariano Gomez, MIS, PMP, MVP, MCP
Maximum Global Business, LLC
http://www.maximumglobalbusiness.com/

Friday, April 17, 2009

Are you a "Musgravion" follower?

My buddy Doug Pitcher at Rose Business Solutions has published one of the most hilarious anecdotes I have read in recent months. The subject: the all too famous David Musgrave [Ed: broken link fixed]. If you are reading this article and you are asking "Who is David Musgrave?", chances are you got hit by a bus in the last 5 minutes and can't remember anything or you are way too new to the GP community. In any case, if you are not a student or practicioner of the Musgravion Doctrine -- LMAO -- I invite you to get started by visiting Developing for Dynamics GP.


Disclaimer: no characters were harmed while writing this blog. The Musgravion Doctrine is a registered trademarks of David Musgrave in Australia and/or other countries. :-))





[Edit] My friend Robert Cavill in Australia noted that all blog articles where Dexterity or VBA code is exhibited must be preceeded by "Following Musgravion principles and techniques..". This will show the developer has made a concious effort to follow best practices in their development or that the consultant has made all efforts in convincing the client to use the customizations synchronization techniques instead of the shared customizations approach.

Until next (hilarious) post!

MG.-
Mariano Gomez, MVP
Maximum Global Business, LLC
http://www.maximumglobalbusiness.com/

Tuesday, April 14, 2009

Retrieving Dynamics GP user idle time

One of the questions I regularly get from DBAs and Dynamics GP systems administrators alike is, "how do I know how long a GP user has been idle in the system without the use of third party products?". It is sufficient to say that they are good third party products out there that rely on Dexterity scripting to provide this information and perform all sort of actions that the system administrator may want to do, take for example Rockton Software's Omni Tools with it's inactivity timeout feature.

SQL Server offers a great way of tracking idle times from a process perspective. This is very cool, because most of the time we want to know that there is absolutely nothing running on the user's session before we decide to take some action -- most of the time remotely.

The following script displays the time (in seconds) the last batch was executed for a particular Dynamics GP user. It looks at the DEX_SESSION table and cross-reference it with the SPIDs retrieved via the undocumented SQL Server system stored procedure sp_who2.

UserActivity.sql

-- Create by Mariano Gomez, MVP
-- This script conferes no rights and has no warranties express or implied

USE MASTER
GO

IF OBJECT_ID('tempdb.dbo.#GP_UserActivity') IS NOT NULL
DROP TABLE #GP_UserActivity
GO

CREATE TABLE #GP_UserActivity
( SPID int
, [Status] varchar( 50)
, [Login] varchar( 80)
, HostName varchar( 80)
, BlkBy varchar( 10)
, dbanme varchar( 80)
, Command varchar( 500)
, CpuTime int
, DiskIO int
, LastBatch varchar( 22)
, ProgramName varchar( 200)
, SPID2 int
, RequestID int
)

INSERT #GP_UserActivity EXEC sp_who2

SELECT
SPID
, [Login]
, datediff(ss, cast(substring(LastBatch, 1, 5) + '/' + cast( datepart( year, getdate()) as char( 4)) +
' ' + substring( lastbatch, 7, 20) as datetime), getdate() ) 'seconds'
FROM #GP_UserActivity INNER JOIN tempdb.dbo.DEX_SESSION on spid = sqlsvr_spid



The script can be automated to track idleness information periodically and obtain detailed reports of system usage among GP users -- a sort of process activity. I use it a lot to plan server expansions activities and monitoring for my clients in conjunction with other important metrics. I hope you find this script useful in your daily administrative activities.

[Edit] One thing I truly like about blogging is that there is no short of solutions to a problem. My friend Robert Cavill, Systems Analyst in Western Australia offers his own version of determining Dynamics GP users' activity (or lack thereof). Robert adds "I have to point out to you that sometimes I have noticed that on my system the DEX_SESSION table infrequently will not have a row present in it for an active user". His comment is well on as the above script will exclude such users. Why and how a logged on user does not register a session in the DEX_SESSION table is something that's up for research, but the bottom line is it happens, especially on Citrix and Terminal Server environments. Take a look at Robert's elegant script:


-- Created by Robert Cavill
-- This script conferes no rights and has no warranties express or implied

SELECT
CASE
WHEN S.session_id is null
THEN 'Missing DEX_SESSION'
WHEN A.USERID <> P.loginame or P.loginame is null
THEN 'Phantom'
ELSE ''
END MismatchOnUserID,

CASE
WHEN datediff ( mi, P.last_batch, getdate() ) > 90
THEN 'Idle ' + str ( datediff ( mi, P.last_batch, getdate() ) )
ELSE ''
END AS Working
, A.USERID
, A.CMPNYNAM
, INTERID
, LOGINDAT + LOGINTIM LoginDatestamp
, SQLSESID
, P.login_time
, P.last_batch
, datediff ( mi, P.last_batch, getdate() ) SinceLastAction
, S.session_id
, S.sqlsvr_spid
, P.spid
, P.status
, P.net_address
, P.dbid
, P.hostname
, P.loginame
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

If you have a script that you use for this purpose or a variation, please don't hesitate to send it in or attach as a comment. I will be more than glad to publish your solution.

Related Articles

sp_who - Transact-SQL Server Reference. Click here

Until next post!

MG.-
Mariano Gomez, MIS, MCP, MVP
Maximum Global Business, LLC
http://www.maximumglobalbusiness.com/

Article updates:
04/15/2009 - Added script by Robert Cavill and commentary to his solution.

Sunday, April 12, 2009

Microsoft Dynamics GP and the Equipment Rental industry

If you are one of those fortunate souls who happens to work in the Equipment Rental industry, you may already have realized how difficult it is to get by with islands of systems that do not talk to each other: the dispatch system, the contract system, the equipment maintenance system, the invoicing system... ah, and I almost forgot... the accounting guy that comes in every month to try to make sense of what your business have been doing with the stack of invoices and timesheets that are sent to customers.

I have to say that before my last project at a global Security Services and Equipment Installation and Monitoring provider, I had little regards for the Field Services Series (comprised of Field Service, Contract Management, and Depot Management). I thought it was a cumbersome piece of software and had too many moving parts. But, I quickly realized that all those moving parts ensure a level of flexibility and integration required by businesses in this field. Granted, there are a few things that don't talk to each other -- out of the box anyways, for example, Project Accounting contracts and Field Services contracts, or one click setup for things like warehouses vs offices/branches, technicians vs employees/vendors/subcontractors, or the inability to unallocate parts from service calls back to inventory when a call is canceled. These turned out to be minor to moderately complex customizations that made life a lot easier.

However, I did overall like the level of integration between the Field Service suite and SOP, POP, and Inventory modules. I liked the fact that purchasing receipt transactions would clear back ordered items on service calls and that these could in turn be billed out on a SOP invoice once the call was closed. You also have to like the workflow capabilities in the service call arena. While still lacking the ability to enter information specific to each visit on a service call, the ability to cycle through multiple call statuses before the call is closed.

But enough of me... If you are in the Equipment Rental industry, you may want to check a fabulous post by my friend Michael D. Johnson, The MBS Guru about how he implemented and customized the Field Service Series to support the need of one of his clients. Michael explains in great detail and provides lots of screenshots of what he did during the time he spent implementing the solution.

Until next post!

MG.-
Mariano Gomez, MVP
Maximum Global Business, LLC
http://www.maximumglobalbusiness.com/

Friday, April 10, 2009

Why my transactions don't post through GL even when I have the flag selected?

Are you tired of asking why is it that some transactions do post through GL and why some don't? Tired of changing the posting settings to no avail? This issue outta be one of the most confusing issues for users and consultants, new or seasoned alike. If you ever struggle with one issue in GP, please tell me it's this one, because even today, I cannot claim victory over this subject. So let's walk the walk together.

What the theory says?

According to Part 3, Chapter 13, page 87 of the Microsoft Dynamics GP's System Setup guide, "If you mark the Post Through General Ledger Files option, batches posted in other modules automatically will update the module in which the transactions originated and the appropriate posting accounts in General Ledger. Your transactions will post to General Ledger and not through General Ledger when posting transaction individually (without a batch)."



Now lets break it down...

It's important to really pay close attention to the theory: batches will post through GL, individual transactions will not, even if the Post Through General Ledger flag has been marked in the Posting Setup window. What this means is that every module with the capability of batching transactions should be able to post through GL, for example, Sales Order Processing, Purchase Order Processing, Inventory, Receivables Management, and Payables Management, right? Not always...

For example, voided transactions are handled like transactions posted individually. This is, when the void occurs, there will be a batch created in GL waiting to be posted.

The curious case of Bank Rec

The Bank Reconciliation module, even though it allows to save most transactions, it does not allow for Post Through GL to take effect. This is because all transactions in the Bank Rec module are treated as individual transactions.

Then there were the third party products...

Since post through GL was conceived for the core Dynamics dictionary modules, chances are that you will find only a few ISVs following the posting rules. Most integrating products will play safe and allow a batch to be created for review and future posting in the modules they integrate with.

Computer Checks and Analytical Accounting

The reason the option to Post Through to GL is not available for Computer Checks when AA is activated is to ensure that any AA analysis information for distribution accounts linked to accounting classes can be entered before transactions are posted to the GL. Because it is not possible to enter analysis information when building a check batch, the batch must be stopped before posting through so this information can be entered and validated. There is no AA validation on the check batch during a check run, so the validation must be done in the GL before posting. This is the reason why the system does not allow computer check batches to be posted through to the GL.

"But I knew all this and my transactions are still not posting through GL"...

In this case you may want to review the all too famous (or infamous) Origin drop-down in the Posting Setup window. I have seen cases where users have set the Post Through GL flag for a few transaction types in the application series, but had the unfortunate luck to set the Origin drop-down to All and click on the Save button.



This will overwrite every individual setting for each transaction within the series and will change all posting settings! Not a good thing if you have spent a few hours customizing each transaction setting.

Hope this post help in clarifying some of the confusions.

Until next post!

MG.-
Mariano Gomez, MVP
Maximum Global Business, LLC
http://www.maximumglobalbusiness.com/

Updates to this article:

07/28/2009 - Added information on Computer Checks and Analytical Accounting

Monday, April 6, 2009

Where are my toolbar menu shortcuts?

Recently, while working on a personal project, I realized that after more than 1 year of working with Dynamics GP 10.0 I had no idea why my toolbar menu items and menu drop-downs did not show the shortcut characters - the characters with an underline.


I was really used to my shortcuts! Where were they now? Turns out that activating the shortcuts is a "feature"! Yes! You just don't open the application and expect all the menu items to show their shortcuts automatically.

If you press the ALT key on your keyboard - once, the menu will light up with the shortcut characters, but this has to be done from the main toolbar, then all submenus will follow.



Also turns out that, if you have all shortcuts activated and you switch from menu, these will be gone for the next menu. So here is the deal: maybe using shortcuts and drop-down menus will become an issue of the past and you may have to get use to the home page. If not, take a look at MVP Mark Polino's post on Dynamics GP 10 home page and his own experience in his Weekly Dynamic column.

Until next post!

MG.-
Mariano Gomez, MVP
Maximum Global Business, LLC
http://www.maximumglobalbusiness.com/

Wednesday, April 1, 2009

Welcome aboard new MVP Mohammad R. Daoud

The Dynamics GP world has a new Microsoft Most Valuable Professional. Please join me in welcoming MVP Mohammad R. Daoud to the family. Mohammad has worked very hard to attain his MVP status and has display a level of participation and accuracy in his responses that led him to the title of MVP. Mohammad is the founder of Great Package for Business Solutions, in Aman, Jordan, and is a leading Microsoft Gold Certified Partner, his passion for Dynamics GP started with release 7.5, and, in his own words "I have studied every single tip of this application technicalities and have done over 20 successful implementations". Mohammad also runs his own blog.



Congratulations also to MVP Monzer Osama for another successful run at the Microsoft Dynamics GP MVP title. Monzer's title has been ratified and he will continue to exhibit the MVP badge for another year.






Until next post!

MG.-
Mariano Gomez, MVP
Maximum Global Business, LLC
http://www.maximumglobalbusiness.com/