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/

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/

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/

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

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/

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/

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.

Until next post!

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

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/