All about the Dexterity OLE Container

November 26, 2008

Much has been asked about the Microsoft Dexterity OLE Container lately and I wanted to address this topic by providing some background on the technology and how it is used from GP. In addition, I will address how to automate OLE externally from other applications.

Definition

In principle, OLE is a compound document technology from Microsoft based on the Component Object Model (COM). OLE allows an object such as a graphic, video clip, spreadsheet, etc. to be embedded into a document, called the Container Application. If the object is playable such as a video, when it is double clicked by the user, a media player is launched. If the object is allowed to be edited, the application associated with it (the Server Application) is launched.

An object can be linked instead of embedded, in which case the Container Application does not physically hold the object, but provides a pointer to it. If a change is made to a linked object, all the documents that contain that same link are automatically updated the next time you open them. An application can be both client and server, called an Object Packager, which is not something I will cover in this article.

History of OLE

OLE 1.0, released in 1990 and was capable of maintaining active links between two documents or even embedding one type of document within another. The server and client libraries, OLESVR.DLL and OLECLI.DLL, were originally designed to communicate between themselves using the WM_DDE_EXECUTE message. OLE 2.0 followed in the steps of OLE 1.0, sharing many of the same design goals, but was re-implemented on the COM platform. New features were automation, drag-and-drop, in-place activation and structured storage.

OLE custom controls were introduced in 1994 as a replacement for the Visual Basic Extension controls. In particular, any container that supported OLE 2.0 could already embed OLE custom controls, although these controls cannot react to events unless the container supports this. OLE custom controls are usually shipped in the form of a dynamic link library with the .ocx extension. In 1996 all interfaces for controls (except IUnknown) were made optional to keep the file size of controls down, so they would download faster.

The Dexterity OLE Container

The Dexterity OLE Container is part of the Dexterity Shared Components. In OLE compound document technology, it is the OLE client application (CONTAIN.EXE), which holds the linked or embedded objects. The Dexterity OLE Container first surfaced with the release of Dexterity 3.0 in 1993.

The Dexterity OLE Container can be opened via most record notes windows in the Dynamics GP application, by clicking on the paperclip button.

Dexterity OLE Container linked or embedded objects are stored in the path indicated by the OLEPath key in the DEX.INI file.

The following is a typical DEX.INI file layout:


[General]
.
.
OLEPath=C:\Notes\
.
.

When an object is linked or embed in the Dexterity OLE Container window for the first time, Dynamics GP will append the Intercompany ID‘\OLENotes to the OLEPath directory to create a unique physical storage directory for each object attached or embedded to a record note. In turn, the note index value associated to the record note serves as a file reference to the OLE object, this is an 8-character hexadecimal file name is created with the hexadecimal value of the note index.

For example, if the note index associated to the record is 16, a file name is created with the name 00000010.

The note index is also stored in the Records Notes Master table (SY03900) and a text is appended to the note in GP with the legend “OLE Link present”.


SELECT NOTEINDX, DATE1, TIME1, DEX_ROW_ID, TXTFIELD FROM SY03900

The query will produce the following resultset:


NOTEINDX DATE1 TIME1 DEX_ROW_ID TXTFIELD
--------------------------------------- ----------------------- ----------------------- ----------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
16.00000 2008-11-28 00:00:00.000 1900-01-01 15:31:26.000 1 OLE Link present.

(1 row(s) affected)

The following is the physical file representation.

*Click on image to enlarge

NOTE: The next note index value is obtained from NOTEINDX column, in the company master table, DYNAMICS.dbo.SY01500.

Unfortunately, the Dexterity OLE Container application was designed only to work from within Microsoft Dynamics GP, hence no programmatic interface was created to support external integrations from other applications. There are no command lines to the CONTAINER.EXE application, however there are a few DDE commands available: Open, Close, Delete, Save. These correspond to the Dexterity OLE function library to control the OLE Container.

The Dexterity OLE Container does not make use of the Microsoft OLE Container Control, and was implemented as a C/C++ based application. Other sources have alluded to the fact that it was implemented based on an MSDN container sample.

Related Articles

KB article 268470 – “Sample: FramerEx.exe Is an MDI ActiveX Document Container Sample Written in Visual C++”, Microsoft Support.

Visual C++ Samples – OCLIENT Sample: Illustrates a Visual Editing Container Application. Microsoft Developer’s Network (MSDN).

Visual C++ Samples: DRAWCLI Sample: Illustrates Integrating Active Container Support with Application-Specific Features. Microsoft Developer’s Network (MSDN).

While the Dexterity OLE Container is not of much help as an external application, you can always run the CONTAIN.EXE application, then drag-and-drop any of the hexadecimal file references to the application’s desktop. In turn this will open the linked or embedded objects.

In my next installment, I will show you how to automate the OLE Container from other applications in order to read standard Dynamics GP OLE notes.

Until next post!

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


Microsoft Dynamics GP 10 security series

November 24, 2008


The following 2 weeks will bring tons of Microsoft Dynamics GP 10 security articles from David Musgrave over at Developing for Dynamics GP. It’s no secret to anyone that the new role-base security has not been without its shared dosis of confusion and frustrations for companies and users upgrading from previous releases and even new comers to Microsoft Dynamics GP. David has published a list of the topics to be covered, as follows:

Don’t forget to mark your calendars for Monday, Wednesday, and Friday over the next two weeks to get these important topics.

Update 11/12/2008 – Added link to article “How to identify security tasks and roles…”

Update 11/14/2008 – Added link to article “How to identify security tasks and roles using Support Debugging Tool”

Update 11/17/2008 – Added link to article “How to resolve security errors on login”

Update 11/18/2008 – Added entry to a future article to be released on the GP v10 Security Conversion Tool. This article will (possibly) wrap up David’s work with tons of information for those of you converting from earlier versions of GP to v10 and will help pave the road in deciding whether to convert your existing security or simply start from scratch.

Update 11/18/2008 – Added link to article “How to resolve security priviledge errors”

Update 11/21/2008 – Added link to article “How to resolve dictionary not loaded errors”

Update 11/24/2008 – David completes (at least for now) his security series with this not to miss article on the Dynamics GP v10 Security conversion tool. There has been a number of questions and confusions about what the tool does and does not do and David addresses those as well.

Until next post,

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


What does SQL Maintenance really do?

November 22, 2008

This one comes straight off the Microsoft Dynamics GP newsgroup (even the title given to this article).

Business Situation

During the course of a test upgrade the user came across a situation where records in a Smartlist table were not upgrading at all. In his attempts to have the upgrade bypass the problem, the user deleted the records from the SmartList table in question and the upgrade was able to succeed.

Nonetheless, the user attempted to run a few of the built-in SQL Maintenance routines found under Microsoft Dynamics GP > Maintenance > SQL and came back empty handed. This series of routines did not correct the damaged records and did nothing in aiding the upgrade, which brings us to the question on the subject:

What does SQL Maintenance really do?

This question can be answered by looking at what SQL Maintenance does not do. SQL Maintenance does not take any actions on table records, except of course, when you drop a table :-).

The routines found under SQL Maintenance are designed to perform preventative database maintenance and each option can be explained as follows:

Recompile

As a database is changed by such actions as adding indexes or changing data in indexed columns, the original query plans used to access its tables should be optimized again by recompiling them. This optimization happens automatically the first time a stored procedure is run after Microsoft SQL Server is restarted. It also occurs if an underlying table used by the stored procedure changes. But if a new index is added from which the stored procedure might benefit, optimization does not happen until the next time the stored procedure is run after Microsoft SQL Server is restarted. In this situation, it can be useful to force the stored procedure to recompile the next time it executes

Another reason to force a stored procedure to recompile is to counteract, when necessary, the “parameter sniffing” behavior of stored procedure compilation. When SQL Server executes stored procedures, any parameter values used by the procedure when it compiles are included as part of generating the query plan. If these values represent the typical ones with which the procedure is called subsequently, then the stored procedure benefits from the query plan each time it compiles and executes. If not, performance may suffer.

When the Recompile option is selected in the SQL Maintenance window, GP forces the selected tables auto-stored procedures and triggers to recompile by executing the SQL Server sp_recompile statement.

Update Statistics

Update Statistics updates information about the distribution of key values for one or more statistics groups (collections) in the specified table or indexed view.

The Database Engine keeps statistics about the distribution of the key values in each index and uses these statistics to determine which index or indexes to use in query processing. Users can create statistics on nonindexed columns by using the CREATE STATISTICS statement. Query optimization depends on the accuracy of the distribution steps:

  • If there is significant change in the key values in the index, rerun UPDATE STATISTICS on that index.
  • If lots of data in an indexed column has been added, changed, or removed (that is, if the distribution of key values has changed), or the table has been truncated by using the TRUNCATE TABLE statement and then repopulated, use UPDATE STATISTICS.

To see when the statistics were last updated, use the STATS_DATE function from SQL Server.
Drop Table

Removes one or more table definitions and all data, indexes, triggers, constraints, and permission specifications for those tables. Drop table will not remove the associated table views or auto-stored procedures, hence, it is recommended to run the Drop Auto Procedure option in conjunction with this option. The Drop Table option executes SQL Server DROP TABLE statement.

Create Table

The Create Table option creates a Dynamics GP table (that has been dropped with the Drop Table option or dropped from SQL Server) with the structure defined in the DYNAMICS.DIC dictionary file. When this option is executed, GP will check for the table existence and drop if it already exists. In addition, all table auto procedures (zDP_) will be dropped and recreated and security granted to the DYNGRP role.

Drop Auto Procedure

This option will remove all selected tables auto stored procedures. The zDP_ procedures aid Dynamics GP in retrieving, saving, updating, and deleting records from a table.

Create Auto Procedure

This option will create all selected tables auto stored procedures. If the procedures already exist, they are dropped and recreated.

Other Resources

Developing for Dynamics GP – “What do the zDP auto-generated stored procedures do?“, by David Musgrave.

Developing for Dynamics GP – “What is Column desSPRkmhBBCreh?“, by David Musgrave.

Microsoft Developer’s Network (MSDN) – You can always search SQL Server Books Online for all topics outlined in this article.

Until next post!

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


How to automatically send all posting reports to screen in Microsoft Dynamics GP

November 21, 2008

My buddy and fellow MVP Frank Hamelly had been excited about a trick he did for one of his clients, so I figured I would materialize his trick in this article.

Business Situation

In this day and age where the economy seems to be at the center piece of all business decisions, not even Dynamics GP has escaped the turmoil. Customers are demanding more efficient use of all business resources and paper is certainly one of those elements that lend themselves for true money saving. Frank’s customer had wondered if all posting reports could automatically be directed to screen without manually having to go into each series posting option to change the output. In addition, he requested the system not ask for the destination of the report each time it was going to be printed.

Background

The Posting Setup window (Microsoft Dynamics GP > Tools > Setup > Posting > Posting) displays all posting reports based on the series and transaction origin. These settings are then stored in the Posting Journal Destinations table (SY02200) under the company database.

In order to understand how data is stored in the SY02200 table, run the following query against your company database:


select PRNTJRNL, SERIES, TRXSOURC, PTGRPTNM, ASECTMNT, PRTOPRNT, PRTOSCNT, PRTOFLNT from sy02200
GO

In turn, the query will return the following result set:


PRNTJRNL SERIES TRXSOURC PTGRPTNM ASECTMNT PRTOPRNT PRTOSCNT PRTOFLNT
-------- ------ ------------------------- ------------------------------- -------- -------- -------- --------
1 6 Manual Checks Benefit Register 1 0 0 0
1 6 Manual Checks State Tax Register 1 0 0 0
1 6 Manual Checks Local Tax Register 1 0 0 0
1 6 Manual Checks Tip Allocation Posting Register 1 0 0 0
0 6 Manual Checks Shift Code Register 1 0 0 0
1 6 Period-End Reports FUTA Posting Register 1 0 0 0
1 6 Period-End Reports SUTA Posting Register 1 0 0 0
1 6 Period-End Reports Workers' Comp Posting Register 1 0 0 0
1 6 Void Checks Check Register 1 0 0 0
1 6 Void Checks Check Posting Register 1 0 0 0
0 6 Void Checks Vacation/Sick Time Acc Register 1 0 0 0
1 6 Void Checks Pay Type Register 1 0 0 0
1 6 Void Checks Department Register 1 0 0 0
1 6 Void Checks Position Register 1 0 0 0
1 6 Void Checks Deduction Register 1 0 0 0
1 6 Void Checks Benefit Register 1 0 0 0
1 6 Void Checks State Tax Register 1 0 0 0
1 6 Void Checks Local Tax Register 1 0 0 0
1 6 Void Checks Tip Allocation Posting Register 1 0 0 0
0 6 Void Checks Shift Code Register 1 0 0 0
1 6 Computer Checks Checkbook Posting Journal 1 0 0 0
1 6 Manual Checks Checkbook Posting Journal 1 0 0 0
1 6 Void Checks Checkbook Posting Journal 1 0 0 0
1 5 Transaction Entry Inventory Transaction Journal 1 0 0 0
1 5 Transfer Entry Inventory Transfer Journal 1 0 0 0
1 5 Transaction Entry Inventory Transaction Edit List 1 0 0 0
1 5 Transfer Entry Inventory Transfer Edit List 1 0 0 0
1 5 Transaction Entry Transaction GL Register 1 0 0 0
1 5 Transfer Entry Transfer GL Register 1 0 0 0
1 5 Transaction Entry Cost Variance Journal 1 0 0 0
1 5 Transfer Entry Cost Variance Journal 1 0 0 0
1 3 Invoice Entry Invoice Posting Journal 1 0 0 0
1 3 Invoice Entry Dist Breakdown - Detail 1 0 0 0
1 3 Invoice Entry Dist Breakdown - Summary 1 0 0 0
1 3 Invoice Entry Inventory Sales Register 1 0 0 0
1 3 Invoice Entry Salesperson Register 1 0 0 0
1 3 Invoice Entry Cost Variance Journal 1 0 0 0
1 3 Invoice Entry Checkbook Posting Journal 1 0 0 0
1 3 Sales Transaction Entry Sales Posting Journal 1 0 0 0
1 3 Sales Transaction Entry Dist Breakdown - Detail 1 0 0 0
0 3 Sales Transaction Entry Dist Breakdown - Summary 1 0 0 0
0 3 Sales Transaction Entry Inventory Sales Register 1 0 0 0
0 3 Sales Transaction Entry Salesperson Register 1 0 0 0
1 3 Sales Transaction Entry Cost Variance Journal 1 0 0 0
1 3 Sales Voided Transactions Sales Voided Posting Journal 1 0 0 0
1 3 Sales Deposits Sales Deposits Journal 1 0 0 0
1 3 Sales Transaction Entry Checkbook Posting Journal 1 0 0 0
1 3 Sales Deposits Checkbook Posting Journal 1 0 0 0
1 2 Bank Deposit Entry Bank Deposit Posting Journal 1 0 0 0
1 2 Bank Transaction Entry Bank Trx Posting Journal 1 0 0 0
1 2 Reconcile Bank Statement Bank Adj Posting Journal 1 0 0 0
1 2 Reconcile Bank Statement Cleared Transactions Journal 1 0 0 0
1 2 Reconcile Bank Statement Reconciliation Posting Journal 1 0 0 0
1 2 Bank Transfer Entry Bank Transfer Posting Journal 1 0 0 0
1 4 Purchasing Voided Trx Purchasing Voided Journal 1 0 0 0
1 4 Receivings Trx Entry Receivings Posting Journal 1 0 0 0
1 4 Receivings Trx Entry Receivings Distribution Detail 1 0 0 0
1 4 Receivings Trx Entry Cost Variance Journal 1 0 0 0
1 4 Receivings Trx Entry Back-Ordered Items Received 1 0 0 0
1 4 Receivings Voided Trx Receivings Voided Journal 1 0 0 0
1 4 Edit PO Status Edit PO Status Distributions 1 0 0 0
1 4 Edit PO Status Edit PO Variance Journal 1 0 0 0
1 4 Purchasing Invoice Entry POP Invoice Posting Journal 1 0 0 0
1 4 Purchasing Invoice Entry Invoice Distribution Detail 1 0 0 0
1 4 Purchasing Invoice Entry Invoice Cost Variance Journal 1 0 0 0
1 4 Purchasing Invoice Entry Back-Ordered Items Received 1 0 0 0
1 4 Voided Purchase Invoice Voided Purchase Invoice Journal 1 0 0 0
1 7 Equipment Log Entry Dist Breakdown - Detail 1 0 0 0
1 7 Equipment Log Entry Dist Breakdown - Summary 1 0 0 0
1 7 Misc. Log Entry Misc. Log Posting Journal 1 0 0 0
1 5 Assembly Entry Assembly Posting Journal 1 0 0 0
1 5 Assembly Entry Assembly Distribution Detail 1 0 0 0
1 5 Depot Adjustment Transaction GL Register 1 0 0 0
1 5 Depot Adjustment Cost Variance Journal 1 0 0 0
1 5 RMA Adjustment Inventory Transaction Journal 1 0 0 0
1 5 RMA Adjustment Transaction GL Register 1 0 0 0
1 5 RMA Adjustment Cost Variance Journal 1 0 0 0
1 5 RTV Adjustment Inventory Transaction Journal 1 0 0 0
1 5 RTV Adjustment Transaction GL Register 1 0 0 0
1 5 RTV Adjustment Cost Variance Journal 1 0 0 0
1 5 SC C-Line Adjustment Inventory Transaction Journal 1 0 0 0
1 5 SC C-Line Adjustment Transaction GL Register 1 0 0 0
1 5 SC C-Line Adjustment Cost Variance Journal 1 0 0 0
1 5 SC Inventory Adjustment Inventory Transaction Journal 1 0 0 0
1 5 SC Inventory Adjustment Transaction GL Register 1 0 0 0
1 5 SC Inventory Adjustment Cost Variance Journal 1 0 0 0
1 5 SC R-Line Adjustment Inventory Transaction Journal 1 0 0 0
1 5 SC R-Line Adjustment Transaction GL Register 1 0 0 0
1 5 SC R-Line Adjustment Cost Variance Journal 1 0 0 0
1 4 Returns Trx Entry Returns Posting Journal 1 0 0 0
1 4 Returns Trx Entry Returns Distribution Detail 1 0 0 0
1 4 Returns Trx Entry Returns Cost Variance Journal 1 0 0 0
1 7 Timesheet Entry Timesheet Posting Journal 1 0 0 0
1 7 Timesheet Entry Dist Breakdown - Detail 1 0 0 0
1 7 Timesheet Entry Dist Breakdown - Summary 1 0 0 0
1 7 Equipment Log Entry Equipment Log Posting Journal 1 0 0 0
1 5 Assembly Entry Cost Variance Journal 1 0 0 0
1 2 Currency Revaluation Revaluation Register-Detail 1 0 0 0
1 2 Currency Revaluation Revaluation Register-Summary 1 0 0 0
1 2 Currency Revaluation Revaluation Journal 1 0 0 0
1 2 Euro Conversion GL Euro Conv. Journal 1 0 0 0
1 2 Euro Conversion GL Euro Conv. Register-Detail 1 0 0 0
1 2 Euro Conversion GL Euro Conv. Register-Summary 1 0 0 0
1 2 Reconcile Bank Statement Outstanding Transaction Report 1 0 0 0
1 5 SC Inventory Transfer Inventory Transfer Journal 1 0 0 0
1 5 SC Inventory Transfer Transfer GL Register 1 0 0 0
1 5 SC Inventory Transfer Cost Variance Journal 1 0 0 0
1 5 Depot Transfer Inventory Transfer Journal 1 0 0 0
1 5 Depot Transfer Transfer GL Register 1 0 0 0
1 5 Depot Transfer Cost Variance Journal 1 0 0 0
1 5 RMA Transfer Inventory Transfer Journal 1 0 0 0
1 5 RMA Transfer Transfer GL Register 1 0 0 0
1 5 RMA Transfer Cost Variance Journal 1 0 0 0
1 5 RTV Transfer Inventory Transfer Journal 1 0 0 0
1 5 RTV Transfer Transfer GL Register 1 0 0 0
1 5 RTV Transfer Cost Variance Journal 1 0 0 0
1 5 SC R-Line Transfer Inventory Transfer Journal 1 0 0 0
1 5 SC R-Line Transfer Transfer GL Register 1 0 0 0
1 5 SC R-Line Transfer Cost Variance Journal 1 0 0 0
1 5 Depot Adjustment Inventory Transaction Journal 1 0 0 0
1 4 Currency Revaluation Revaluation Register-Summary 1 0 0 0
1 4 Euro Conversion PM Euro Conv. Journal 1 0 0 0
1 4 Euro Conversion PM Euro Conv. Register-Detail 1 0 0 0
1 4 Euro Conversion PM Euro Conv. Register-Summary 1 0 0 0
1 3 Receivables Sales Entry Sales Entry Posting Journal 1 0 0 0
1 3 Receivables Sales Entry Sales Detail GL Register 1 0 0 0
1 3 Receivables Sales Entry Sales Summary GL Register 1 0 0 0
1 3 Receivables Cash Receipts Cash Receipts Posting Journal 1 0 0 0
1 3 Receivables Cash Receipts Cash Detail GL Register 1 0 0 0
1 3 Receivables Cash Receipts Cash Summary GL Register 1 0 0 0
1 3 Receivables Apply Doc. Discounts/Writeoffs Journal 1 0 0 0
1 3 Receivables Apply Doc. Apply Detail GL Register 1 0 0 0
1 3 Receivables Apply Doc. Apply Summary GL Register 1 0 0 0
1 3 Voided Trx Maintenance Voided Trx Posting Journal 1 0 0 0
1 3 Voided Trx Maintenance Voided Trx Detail GL Register 1 0 0 0
1 3 Voided Trx Maintenance Voided Trx Summary GL Register 1 0 0 0
1 3 Transfer Commission Commissions Posting Journal 1 0 0 0
1 3 Receivables Sales Entry Checkbook Posting Journal 1 0 0 0
1 3 Receivables Cash Receipts Checkbook Posting Journal 1 0 0 0
1 3 Voided Trx Maintenance Checkbook Posting Journal 1 0 0 0
1 3 Currency Revaluation Revaluation Journal 1 0 0 0
1 3 Currency Revaluation Revaluation Register-Detail 1 0 0 0
1 3 Currency Revaluation Revaluation Register-Summary 1 0 0 0
1 3 Euro Conversion RM Euro Conv. Journal 1 0 0 0
1 3 Euro Conversion RM Euro Conv. Register-Detail 1 0 0 0
1 3 Euro Conversion RM Euro Conv. Register-Summary 1 0 0 0
1 6 Computer Checks Check Register 1 0 0 0
1 6 Computer Checks Check Posting Register 1 0 0 0
0 6 Computer Checks Vacation/Sick Time Acc Register 1 0 0 0
1 6 Computer Checks Pay Type Register 1 0 0 0
1 6 Computer Checks Department Register 1 0 0 0
1 6 Computer Checks Direct Deposit Check Register 1 0 0 0
1 6 Computer Checks Direct Deposit Register 1 0 0 0
1 6 Computer Checks Direct Deposit Trxs Register 1 0 0 0
1 6 Computer Checks Position Register 1 0 0 0
1 6 Computer Checks Deduction Register 1 0 0 0
1 6 Computer Checks Benefit Register 1 0 0 0
1 6 Computer Checks State Tax Register 1 0 0 0
1 6 Computer Checks Local Tax Register 1 0 0 0
1 6 Computer Checks Tip Allocation Posting Register 1 0 0 0
0 6 Computer Checks Shift Code Register 1 0 0 0
1 6 Manual Checks Check Register 1 0 0 0
1 6 Manual Checks Check Posting Register 1 0 0 0
0 6 Manual Checks Vacation/Sick Time Acc Register 1 0 0 0
1 6 Manual Checks Pay Type Register 1 0 0 0
1 6 Manual Checks Department Register 1 0 0 0
1 6 Manual Checks Position Register 1 0 0 0
1 6 Manual Checks Deduction Register 1 0 0 0
1 2 Clearing Entry Clearing Posting Journal 1 0 0 0
1 2 General Entry General Posting Journal 1 0 0 0
1 2 Quick Entry Quick Posting Journal 1 0 0 0
1 4 Payables Trx Entry Trx Entry Posting Journal 1 0 0 0
1 4 Payables Trx Entry Trx Distribution Detail 1 0 0 0
1 4 Payables Trx Entry Trx Distribution Summary 1 0 0 0
1 4 Payables Trx Entry Trx Entry Check Register 1 0 0 0
1 4 Payment Entry Payment Entry Posting Journal 1 0 0 0
1 4 Payment Entry Payment Distribution Detail 1 0 0 0
1 4 Payment Entry Payment Distribution Summary 1 0 0 0
1 4 Computer Checks Computer Check Posting Journal 1 0 0 0
1 4 Computer Checks Comp Chk Distribution Detail 1 0 0 0
1 4 Computer Checks Comp Chk Distribution Summary 1 0 0 0
1 4 Computer Checks Check Register 1 0 0 0
1 4 Apply To Apply To Posting Journal 1 0 0 0
1 4 Void Open Trx Void Open Posting Journal 1 0 0 0
1 4 Void Historical Trx Void Hist. Posting Journal 1 0 0 0
1 4 Payment Entry Checkbook Posting Journal 1 0 0 0
1 4 Computer Checks Checkbook Posting Journal 1 0 0 0
1 4 Payables Trx Entry Checkbook Posting Journal 1 0 0 0
1 4 Void Historical Trx Checkbook Posting Journal 1 0 0 0
1 4 Void Open Trx Checkbook Posting Journal 1 0 0 0
1 4 Currency Revaluation Revaluation Journal 1 0 0 0
1 4 Currency Revaluation Revaluation Register-Detail 1 0 0 0
1 7 Misc. Log Entry Dist Breakdown - Detail 1 0 0 0
1 7 Misc. Log Entry Dist Breakdown - Summary 1 0 0 0
1 7 Employee Expense Entry Employee Expense Posting Jrnl 1 0 0 0
1 7 Employee Expense Entry Dist Breakdown - Detail 1 0 0 0
1 7 Employee Expense Entry Dist Breakdown - Summary 1 0 0 0
1 7 Employee Expense Entry Check Register 1 0 0 0
1 7 Employee Expense Entry Checkbook Posting Journal 1 0 0 0
1 7 Billing Entry Billing Posting Journal 1 0 0 0
1 7 Billing Entry Dist Breakdown - Detail 1 0 0 0
1 7 Billing Entry Dist Breakdown - Summary 1 0 0 0
1 7 Billing Entry Checkbook Posting Journal 1 0 0 0
1 7 Inventory Transfer Entry Inventory Xfer Posting Journal 1 0 0 0
1 7 Inventory Transfer Entry Dist Breakdown - Detail 1 0 0 0
1 7 Inventory Transfer Entry Dist Breakdown - Summary 1 0 0 0
1 7 Revenue Recognition Entry Rev Recognition Posting Jrnl 1 0 0 0
1 7 Revenue Recognition Entry Dist Breakdown - Detail 1 0 0 0
1 7 Revenue Recognition Entry Dist Breakdown - Summary 1 0 0 0
1 7 Project Closing Project Closing Posting Journal 1 0 0 0
1 7 Project Closing Dist Breakdown - Summary 1 0 0 0
1 7 Project Closing Dist Breakdown - Detail 1 0 0 0
1 7 PA Returns Trx Entry Project Posting Journal 1 0 0 0
1 7 PA Returns Trx Entry Project Dist Breakdown - Detail 1 0 0 0
1 7 PA Returns Trx Entry Project Dist Breakdown - Summar 1 0 0 0
0 3 Receivables Cash Receipts Sales Entry Posting Journal 1 0 0 0
0 3 Receivables Apply Doc. Sales Entry Posting Journal 1 0 0 0
1 4 Edit PO Status Encumbrance Summary Edit List 1 0 0 0
1 4 Edit PO Status Encumbrance Audit Report 1 0 0 0
1 4 Computer Checks EFT Payment Register 1 0 0 0

(220 row(s) affected)

The Table Columns

PRNTJRNL: It’s a flag that will determine whether a posting journal will be printed at all. 0 – Don’t Print, 1 – Print.

ASECTMNT: Ask Each Time – will force Dynamics GP to bring up the Report Destination window.

PRTOPRNT: Print to Printer – 0 – Don’t Print, 1 – Print

PRTOSCNT: Print to Screen – 0 – Don’t Print, 1 – Print

PRTOFLNT: Print to File – 0 – Don’t Print, 1 – Print

Solution

To force all posting reports to screen, we can then run a query that will change the status of the PRTOSCNT column to 1 – Print, while changing the status of all the other output destination flag columns to 0, as follows:


UPDATE SY02200 SET ASECTMNT = 0, PRTOPRNT = 0, PRTOSCNT = 1, PRTOFLNT = 0
GO

Remember: You can always filter this UPDATE statement by product series in order to control the posting jounals output more granularly.

Until next post!

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


How to split Dynamics GP’s alphanumeric column values in SQL Server

November 19, 2008

I decided to go back to my roots! Yes, my blog started with SQL Server tricks around Dynamics GP and slowly (but surely!) progressed into other interesting technical topics, so I decided to scout my bag of tricks and found two useful user-defined function scripts that I regularly use on most of my SQL Server developement projects around GP.

Today, I am featuring two scalar-valued functions that allow you to separate numeric values from character values in alphanumeric fields such as purchase order numbers, receipt numbers, audit trail codes, among others. This can be particularly useful when developing integrating SQL Server applications that need to grab the next document number and increment the numeric portion. While I have seen many solutions out there, most of them use some sort of loop structure to get to evaluate each alphanumeric character in the string sequence to determine whether it’s a numeric value or an alpha value. Any loop structure, as insignificant as it may seem, always takes a toll on the way your application or script performs.

The following two scalar-valued functions are only compatible with SQL Server 2005 and above, and make use of the constant values associated to certain SQL Server bit options stored in the master.dbo.spt_values table to validate whether a character is a number or an alpha. To make this script more international, you can decide to setup your own table of numbers (or characters) and modify the script to assert each character in the string against your table. Let’s take a look at the two functions:

First, the function script to retrieve the alpha portion of the string.

dbo.fGetAlpha


create function dbo.fGetAlpha(@string nvarchar(100))
returns nvarchar(100)
begin
return
( select cast(
( select case
when substring(@string, n, 1) like '[^0-9]' then
substring(@string, n, 1)
else ''
end
from ( select number from master..spt_values
where type = 'P' and number between 1 and 100) AS Nums(n)
where n <= LEN(@string) for XML path('')) AS nvarchar(100) ) ); end GO

With a minor tweak to the previous script — note the use of ‘[0-9]’ — we have a script that can retrieve the numeric portion of the string.

dbo.fGetNumber


create function dbo.fGetNumber(@string nvarchar(100))
returns nvarchar(100)
begin
return
( select cast(
( select case
when substring(@string, n, 1) like '[0-9]' then
substring(@string, n, 1)
else ''
end
from ( select number from master..spt_values
where type = 'P' and number between 1 and 100) AS Nums(n)
where n <= LEN(@string) for XML path('')) AS nvarchar(100) ) ); end GO

One thing to highlight is the LIKE clause suppors RegEx-type ranges like [0-9], [^0-9], [a-z], [A-Z] or anything you may be able to think of in terms of ranges.

Practical Use Case

Say for example you want to separate the value UPR0000010102 into it’s alpha and numeric portions, this is UPR and 0000010102. You can use the functions as follows to accomplish this:

Sample Use Code



declare @uprTransactionNumber char(30), @uprAlpha char(30), @uprNumber char(30)

set @uprTransactionNumber = 'UPR0000010102'
select @uprAlpha = dbo.fGetAlpha(@uprTransactionNumber), @uprNumber = dbo.fGetNumber(@uprTransactionNumber)

When the script is executed, the numeric portion of the string will be stored in the @uprNumber variable and the alpha portion of the string will be stored in the @uprAlpha variable.

Downloads

Click here to download the SQL script file with the create function statements.

Until next post

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


Web Access to the Microsoft Dynamics GP Public Newsgroups has changed

November 18, 2008

As of today, it seems that the old links for web access to the Business Solutions (Microsoft Dynamics) related Microsoft public newsgroups are no longer working. For the Microsoft Dynamics GP community this affects the following newsgroups:

  • microsoft.public.greatplains
  • microsoft.public.dynamics.gp.developer

There have been new links to these newsgroups for a while, but it looks like the old web address has been discontinued.

So, if you were using the two URLs below:

http://www.microsoft.com/Businesssolutions/Community/Newsgroups/dgbrowser/en-us/default.mspx?dg=microsoft.public.greatplains

http://www.microsoft.com/Businesssolutions/Community/Newsgroups/dgbrowser/en-us/default.mspx?dg=microsoft.public.dynamics.gp.developer

You will need to update your favourites to the new URLs below:

http://www.microsoft.com/communities/newsgroups/en-us/default.aspx?dg=microsoft.public.greatplains

http://www.microsoft.com/communities/newsgroups/en-us/default.aspx?dg=microsoft.public.dynamics.gp.developer

I have updated the URLs on the links section of the blog’s right hand column to use the new addresses.

UPDATE 11/18/08 – All newsgroups are up and running! However, you may still experience issues navigating from page to page. I am sure Microsoft is diligently working to resolve all issues. For those accessing the newsgroups via Google Groups, you shouldn’t have seen any changes.

Until next post!

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


New article on MSDynamicsWorld: Frank Hamelly on "Customer Item Numbers"

November 17, 2008

Fellow MVP Frank Hamelly has decided to come out of hiding (sometimes he needs a little push 🙂 ) with this new article on Customer Item Numbers on MSDynamicsWorld. This piece addresses an important issue sorrounding data entry and tracking in SOP, especially with customers who love to send in only their part numbers.

Until next post!

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