Transferring Extender customizations from one company database to another

April 28, 2009

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/

Advertisements

Congratulations to MVP Mark Polino on his 1000th post

April 28, 2009



“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

April 25, 2009

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

April 24, 2009

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"

April 23, 2009

“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

April 23, 2009

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

April 22, 2009

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/