Developing for Dynamics GP weekly summary

January 24, 2009

Your Developing for Dynamics GP weekly summary is here! I have received possitive feedback from many of you on this weekly review because it presents some of the best articles available on the web on Dynamics GP development and integration techniques. This week’s summary is no different! So let’s get straight into it.

  1. Finding out how to call an existing report. In this article, David Musgrave reviews two techniques for calling and executing existing Dynamics GP reports from custom code. While I am not able to speak for Visual Studio Tools, these techniques are very useful when these calls are performed from Dexterity Sanscript. As an additional resource to his article, you may want to read my articles on Support Debugging Tool and DEX.INI.
  2. Sending Emails with Collaboration Data Objects (CDO) and Dexterity. It’s always good when any of my articles get an entry on Developing for Dynamics GP. David takes a look at the technique I used on one of my projects to submit emails without the use of Outlook or Exchange.
  3. How can I identify the parameters of a procedure or function? This one is a wake up call for all Dexterity and why not, non-Dexterity developers out there. Microsoft offers a number of tools to trace existing code to establish how a Dex procedure or function call is made and parameters that are passed in in those calls. The key here: NO NEED FOR SOURCE CODE! In fact, the less you know about the source code, the better customizations you will develop, why? Because, not knowing what the original code does will force you to build more error handling and validation in your own code. Though, I must admit, working with source code has it’s advantages. :-)
  4. Ever received an error “The stored procedure createSQLTmpTable returned the following results: DBMS: 12″ exceptions“? David answers it here. Some of these pesky errors can be a bit difficult to troubleshoot. However, keep in mind that a DBMS error is a SQL Server related error condition in one of the many stored procedure operations executed in the backend, not a Dynamics GP error in itself. David shows a few troubleshooting techniques to get pass these issues.
  5. Chris Roehrich shows some nifty C# code for Passing data from a Business Portal Result Viewer Web Part to Dynamics GP Web Services. The specific example shows how you can remove a line item from a Sales Order using the UpdateSalesOrder method. The sales order number, line sequence number, and item number can be pulled off the Rich List Result Viewer web part and passed to the web services.

Until next post!

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


Sending emails with Collaboration Data Objects (CDO) and Dexterity

January 19, 2009


One of the things I always tell other Microsoft Dynamics GP developers is not to discard the power of Dexterity. Not because there is .NET and Visual Studio Tools it means that you should abandon Dexterity as a development option (while I confess, I am in the process of increasing my knowledge of C# and so on).

Business Situation

A common request is the ability to submit emails from within your Dexterity-based application. However, emails are not always straight forward. In environments where Dynamics GP resides on a Citrix farm or Terminal Server, companies are reluctant to install Microsoft Office and/or setup email profiles for everyone. After all, that’s what the Exchange Server is for. So, how can you get email messages sent from your application without SMTP, Exchange, or Outlook?

Background

Collaboration Data Objects (CDO) has been around for quite sometime and is Microsoft’s technology for building messaging or collaboration applications or adding these capabilities to existing applications, including Dexterity applications.

CDO is the basis of Microsoft Exchange, and has evolved from OLE Messaging and Active Messaging. Using Microsoft Dexterity with CDO, a developer can, for example, write a script that will exchange e-mail with users or with other Dexterity applications, collaborate in discussions on other applications, or allow employees to schedule meetings with multiple recipients, review existing appointments, schedule new events, and so forth.

Collaborative Data Objects are made available through two CDO libraries. These libraries let you address programmable messaging objects (including folders, messages, recipient addresses, attachments, and other messaging components).

Solution

To implement CDO in Dexterity, the following steps are required:

1) Open Resource Explorer. Click on Base and highlight the Library entry. Click on the New button to continue. This will open the COM Type Library Definition window.

2) Click on the ellipses button and locate and add the Microsoft CDO for Windows 2000 Library in the COM Type Libraries.

* Click on image to enlarge

3) Click on the Select button to continue, click on the OK button to close the COM Type Library Definition window.

4) Now, you will need to create a Data Type that will reference the library. In Resource Explorer, locate the Data Type folder under Base resources. Click on the New button in the Dexterity toolbar. I set up the following information for this example:

NOTE: To select the COM Object Type click on the ellipses button. This will open the Class Browser window. Double-click on the Message class. In reality, Dexterity is able to access other classes for that object without the need for other references, which simplifies things as we will see ahead.

5) With our new data type in place, we can now create a global script that will allow us to send emails from within GP using our CDO object.

Send_Email


{ global script : Send_Email

(C) 2009, created by Mariano Gomez, MVP
This script uses a non-SMTP method of sending emails to receipients
}

in string IN_From_Recipient;
in string IN_To_Recipient;
in string IN_Subject;
in text IN_Body;

local CDO.Message objEmail;
local reference configParams;

local string cdoSendUsingMethod;
local string cdoSMTPServer;
local string cdoSMTPServerPort;
local string smtpServer;

pragma (disable warning LiteralStringUsed);

cdoSendUsingMethod = "http://schemas.microsoft.com/cdo/configuration/sendusing";
cdoSMTPServer = "http://schemas.microsoft.com/cdo/configuration/smtpserver";
cdoSMTPServerPort = "http://schemas.microsoft.com/cdo/configuration/smtpserverport";
smtpServer = "mail.somesmptrelay.com"; {* this is the address of the smtp relay server }

pragma (enable warning LiteralStringUsed);

configParams = new CDO.Configuration();

{ Configure parameters }
configParams.Fields.Item[cdoSendUsingMethod].Value = CDO.cdoSendUsingPort;
configParams.Fields.Item[cdoSMTPServer].Value = smtpServer;
configParams.Fields.Item[cdoSMTPServerPort].Value = 25;
configParams.Fields.Update();

{ Prepare and send the email }

objEmail = new CDO.Message();

{ set up recipient and sender infomation }
objEmail.Configuration = configParams;
objEmail.From = IN_From_Recipient;
objEmail.To = IN_To_Recipient;
objEmail.Subject = IN_Subject;
objEmail.Textbody = IN_Body;
objEmail.Send();

clear objEmail;
clear configParams;

A few things to highlight in the code:

a) Dexterity will not allow you to access the configuration parameters in the Message object directly, as you would in VB or VBA. This is, you will not be able to invoke objEmail.Configuration.Fields.Item directly. Hence, there will be a need to create a separate reference to the Configuration object.

b) While in VB you are able to use the parenthesis () as a way to access the different elements of the Configuration class, in Dexterity is necessary to use the square brackets along with a variable representing the element to be initialized. Also, it is necessary to specify the keyword “.Value” in the scope to avoid a Dexterity crash. VB does not require the specification of the “.Value” in the scope.


{ Configure parameters }
configParams.Fields.Item[cdoSendUsingMethod].Value = CDO.cdoSendUsingPort;
configParams.Fields.Item[cdoSMTPServer].Value = smtpServer;
configParams.Fields.Item[cdoSMTPServerPort].Value = 25;

Also, to access class constants, it will be necessary to add “CDO.” in front of the constant name. I suspect this to be the issue with other objects as well.

c) To pass in the Configuration values to our Message class, it is enough to initialize objEmail.Configuration with the configuration parameter (configParams) reference.

While working with COM objects may not come intuitively in Dexterity — we also don’t have intellisense capabilities — it’s very true that you can still accomplish a great deal of the techniques that are natural in other environments like VB. After all COM is COM.

OTHER RESOURCES:

KB 286431 - How to send HTML formatted mail using CDO for Windows 2000 and a remote SMTP service.

Hey 1129 – Hey, Scripting Guy! – How Can I Attach a File to an Email Sent Using CDO?

MSDN 978698 - Collaboration Data Objects Roadmap

Until next post!

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


Developing for Dynamics GP – Weekly Summary

December 20, 2008


With David Musgrave out on vacations it would seem the articles would cease. Not the case at all! David made sure he left enough scheduled published articles so he did not have to get on the computer during his cruise throughout the South Pacific, perhaps enduring some hard knocks from Jennifer in the process. So let’s see what David left for us.

If you are using Dexterity 10 (build 10.00.0320) Source Code Control with either the Generic Provider or Visual Source Safe, be aware of a bug when checking in multiple resources at once to the code repository. Click here to find out more about this issue and how to resolve it.

On the topic of Source Code Control, David explores the proper use of Index Files. An index file will help with keeping resource IDs in check, more so when you have more than one developer working on your Dexterity product. Make sure you check this article, since it also includes links to published KB articles on the subject.

This one evokes some nostalgic moments. We are also so used to the fancy interfaces: SQL Server Management Studio, Query Analyzer, even Excel. There was a moment in time when working with data in earlier versions of Dynamics GP, especially those running on Ctree and Btrieve, was a royal pain in the rear and identifying and correcting data issues and table corruptions wasn’t as simply as it is today. There was DBrowser, then Dynamic Data Browser (DDB.EXE). David offers some history and provides a real life application of the tool. If you are a consultant and don’t have this tool in your toolbox… you get my point!

Support Debugging Tool comes back with a 1-2 combination punch. This new build (build 10) includes the all-so-awesome Security Profiler and the all-too-cool Screeshot features. Please read David’s Release Notes and download the tool. Check my SDT redux article for more info as well.

Some things may appear to be obvious sometimes, but I have also found people asking about access to CustomerSource and PartnerSource. My old friend, Scott Stephenson from Microsoft Dynamics GP Tools Support in Fargo, and David, both provide some of the criteria required for accessing any of these two Microsoft secure sites. Click here to read more.

Until next post!

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


The DEX_ROW_ID column

December 11, 2008

From time to time the question comes up in newsgroups, informal conversations between developers, and surprise phone calls. The questions come in many flavors:

  • What is the DEX_ROW_ID?
  • Why do all Dynamics GP’s SQL Server tables have a DEX_ROW_ID?
  • How is the DEX_ROW_ID related to IDENTITY columns in SQL Server tables?
  • Can I build reports using the DEX_ROW_ID column?

To start unreeling these questions it is best to start with two key concepts: active and passive record locks.

Active Locks

An active lock allows other users to read a table record, but not make any changes or delete the record. Active locking ensures that the user who has the active lock is the only user who can make changes or delete the record. If other users try to delete or change the record, a table-sharing error will occur. An active lock is applied each time a record is read using the Dexterity change or edit table statements with the lock keyword included.

Passive Locks

A passive lock allows other users to access the record. Other users can delete the record or make changes to it. Passive locking ensures that other users accessing the record can be made aware that the record has been deleted or that the contents of the record have changed. A passive lock is applied each time a record is read using the change or edit table statement.

What is the DEX_ROW_ID?

As part of Dexterity’s table definition requirements, active locking must be enabled on a per-table basis by marking the Allow Active Locking option in the table’s Table Definition window.

For both active and passive locking to work properly in a Dexterity-based application, such as Microsoft Dynamics GP, every SQL table must include a column that is used to track the identity of individual records being locked. This column is the DEX_ROW_ID.

Why do all Dynamics GP’s SQL Server tables have a DEX_ROW_ID?

When Dexterity’s Runtime Engine creates a table, the DEX_ROW_ID column is added to each table created automatically. In addition, this column is hidden to the table definition within Dexterity application.

If tables are created through a method other than allowing the Dexterity runtime engine to create them, then the DEX_ROW_ID column must be added via SQL Server Management Studio or using the Transact-SQL (T-SQL) ALTER TABLE statement, as follows:


ALTER TABLE dbo.TableName ADD DEX_ROW_ID INT IDENTITY;
GO

Dexterity uses the DEX_ROW_ID column internally. Developers need not to manipulate the column at all.

If tables are created outside of the Dexterity environment and do not include the DEX_ROW_ID column in each of your tables, active locking will not function properly for that table.

How is the DEX_ROW_ID related to IDENTITY columns in SQL Server tables?

From a pure SQL Server perspective, the DEX_ROW_ID is an INT type column with the IDENTITY property assigned to it. This enables the developer to specify both an identity number for the first row inserted into the table (Identity Seed property) and an increment (Identity Increment property) to be added to the seed to determine successive identity numbers. When values are inserted into a table that has an identifier column, SQL Server automatically generates the next identity value by adding the increment to the seed. When you add identifier columns to existing tables, the identity numbers are added to the existing rows of the table, with the seed and increment values applied in the order that those rows were originally inserted. Identity numbers are also generated for any new rows that are added. You cannot modify an existing table column to add the IDENTITY property.

Identity columns and their values are managed at the database level and are not controlled by any applications built in Dexterity.

Can I build reports using the DEX_ROW_ID column?

While there is nothing preventing report developers from using the DEX_ROW_ID column, it is strongly recommended to avoid using the values as a method to uniquely identify any given piece of information.

Tables with frequent delete operations will generate gaps between DEX_ROW_ID column values. Deleted DEX_ROW_ID values are not reused.

In addition, during a Dynamics GP upgrade, if a table upgrade changes, the upgrade routines will host the data in a temporary table, the original table will be dropped and recreated, and in the process the DEX_ROW_ID column will be re-seeded. When data is brought back into the upgraded table, the rows will be assigned a new DEX_ROW_ID value as inserted.

Summary

The DEX_ROW_ID column is only useful to Dexterity applications as it provides a way to manage active and passive locking of records, but should no be relied upon for report development, or external processes.

Other Resources

Dynamics GP Blogster – Know thy common table operations with Dexterity – Part I.
Microsoft Developers Network – SQL Server Books Online

Until next post!

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


David Musgrave on Setting Tab Sequence

December 8, 2008

The TAB key is often used to navigate sequentially through multiple controls on a Dexterity window. The order in which you move through the controls depends on their Tab Sequence Index value, not their position on the window. In this article, David provides you with the method and techniques used to set up a required tab sequence on a window. Remember that this is not only a “Dexterity thing” and that it is required when adding fields or changing how you jump from field to field in Modifier.

Until next post!

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


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/


Length of Report Writer Calculated String Fields

November 13, 2008

I spent a good time writing my previous blog article on adding trailing asterisks to the check amount in words. If you followed the article, you will noticed that at some point I created two conditional calculated string fields to fill in the amount in words with trailing asterisks based on a condition. However, it appears that calculated string fields are still affected by the 80 characters limitation, rendering the remaining characters truncated from the report.

Needless to say this limitation is not a limitation with any of the Report Writer user-defined functions. In the case of the RW_PadWithStars function used in the previous article, it accepts any string and returns the padded version with asterisks up to the limit specified. However, when the value is passed to the Report Writer calculated field, it is further truncated to 80 characters.

This issue has been on the table since Dexterity 6.0 (and perhaps earlier) and I am surprised it has not gained traction with the Dexterity development teams at Microsoft. We definately no longer live in the world of 80 x 25 screens and much less in the dot-matrix printer era.

Related Articles

KB article 856435 – Length of Calculated String Fields

Developing for Dynamics GP – Cheque Amount in Words

Until next post!

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


Developing for Dynamics GP – Weekly Summary

October 30, 2008

This was a very active week over at Developing for Dynamics GP. David Musgrave brings 6 excellent articles that, once again, cover a number of interesting topics from ways to execute scripts across multiple GP company databases, all the way to the impact Microsoft’s new Statement of Direction for Dynamics GP will have on everyday product features. So let’s get started!

Article 1: David first article — Running SQL scripts against all GP company databases — explores a batch file he had developed in conjunction with his friend Robert Cavill in the past. This batch file makes use of the OSQL command line utility application (OSQL.EXE) provided with SQL Server to execute a query against all Dynamics GP company databases. In summary, a T-SQL SELECT statement is executed against the Company Master table (DYNAMICS..SY01500) to retrieve the INTERID column values. These values are then used to execute a script repetitively for each company.

Remember, you can always achieve the same thing via T-SQL in Query Analyzer (SQL 2000) or SQL Server Management Studio (SQL 2005). However, this approach involves the use of cursors, as follows:


-- retrieves a list of customers from all companies
declare @companyID char(6)
declare c_company cursor for
select INTERID from SY01500

open c_company
fetch next from c_company into @companyID
while @@fetch_status = 0
begin
EXEC ("select * FROM " + @companyID + "..RM00101")
fetch next from c_company into @company ID
end

close c_company
deallocate c_company

Article 2: How many times have you wished you could just record a macro and just execute it with a large dataset? Most of us, who have been in the trenches for a while, have known of a little dirty secret for quite some time now involving the use of Microsoft Office Word’s Mail Merge functionality to merge datasets into a macro file. In fact, macros are used to stress-test Dynamics GP and you can leverage this feature to do your own stress test.

David previously wrote KB article 953437, but have decided to bring it to the light on his blog site under the title How to Use Word Mail Merge and Macros to Import Data. Be sure to check this out as it is a unique chance to explore another interesting way of importing data and or stress testing your system.

If you are still looking for other ways of importing data into GP, please see my previous article on Table Import.

Article 3: Using VBA with Report Writer comes pack with a complete explanation of the Report Writer bands and how these are associated to Visual Basic for Applications (VBA) events. As David pointed out, “Most people are aware that you can use Visual Basic for Applications (VBA) with Microsoft Dynamics GP forms and the Modifier, but not everyone is aware that VBA can be used with the Report Writer as well.“, and it’s a shame because Report Writer, while very ‘primitive’ in it’s behaviour and architecture, still offers a wealth of possibilities over the more commercial reporting tools. Be sure to check out (as in try) the sample code and evaluate how this can be implemented in your future Report Writer projects.
Don’t forget to check other links posted by David in this same article with tons of examples on how to access data and expose that data onto Report Writer reports.

Article 4: Using ADO with VBA with Report Writer showcases a sample on accessing data stored in tables that cannot be easily linked using standard Report Writer table relationships. By now, many of you probably know that Report Writer only support one 1-to-Many table relationship on a report, which can be a serious limitation for more complex reports. However, the use of old fashioned calculated fields, little VBA, and the new UserInfo connection object can turn Report Writer into a very dangerous tool — just kidding about the dangerous piece :-).

If you are not to engraned with the terminology, be sure to check Microsoft’ ActiveX Data Objects (ADO) frequently asked questions page, or you can download the latest copy from here.

Article 5: I ran across this page last Saturday when working on my Table Import article, trying to dig up all SDKs and did not think of posting a blog about it. However, David was clever enough to put together blog with links to the Developer Documentation for Microsoft Dynamics GP page for releases 9.0 and 10.0.

Article 6: “Should I continue to develop in Dexterity?” That is the eternal question that customers and frankly speaking, developers around the world continue to ask as GP evolves more to a collaborative environment. David answers this by pointing out interesting features highlighted in the latest Statement of Directions for Microsoft Dynamics GP release, as it relates to developers.

From personal experience, let me tell you: Dexterity developers are in high demand! Even Microsoft is looking for a few good ones. So don’t get discouraged — but don’t fall asleep either — if you see everyone else shooting to learn Visual Studio. Dexterity is not going away anytime soon. I promise!

Hope you enjoy this explosion of articles and let David or myself know what you would like to see on our sites.

Until next post!

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


Filtering Third Pary Lookups with Dexterity

October 23, 2008

In the past days I had been struggling with a programming issue that I thought could be resolved very easily with standard cross-dictionary development techniques. In fact, I was convinced that most of what I needed could be found in past materials published by my dear friends David Musgrave and Mark Rockwell. In reading through the material and going through the recipe of steps to implement third-party lookup filtering, I realized the technique was using reject record statements to avoid displaying records in the scrolling window, and while I was able to implement it, performance quickly became an issue.

Business Situation

In principle, I am developing a new piece of code for one of my clients that would allow them to transfer Project Accounting contracts to Field Service contracts. However, I needed to limit the contracts in Field Service only to the contracts for the selected customer.

Challenges

With the implementation of the technique outlined in Pushing the Limits III, the lookup would deliver the filtered results in over a minute! Not acceptable for me, not acceptable for the end-user.

Previously, I had filtered the Item Lookup window using a range where statement based on a few conditions my client needed to setup in Field Service, but this was much easier since the window resided in the DYNAMICS.DIC dictionary file. I was in fact sure that the technique outlined in Pushing the Limits III could be modified to use a range where statement, but was not sure how to accomplish it since the main issue was capturing a reference for the SVC_Contract_HDR (SVC00600) table buffer associated with the form, not any table buffer. To this point, I called my friend Mark — David was sleeping at the time given the time difference — and he pointed out that the client could use SmartFill to lookup any value typed in the Contract Number field associated to the lookup and that SmartFill would display its own lookup. Since my client owns SmartFill, this was certainly an option. However, this approach implied living with my inefficient lookup approach still, since I could not disable my lookup button.

Off I went to chat with David at COB and he pointed out that he had done this before. We started to brainstorm on the technique and he then realized that there were some challenges, but more than anything, realized that the Pushing the Limits material did not effectively address filtering third party lookups.

Giving David’s nature, her came up with perhaps the ultimate article in Cross-Dictionary Third Party Lookups Filtering. Once again, David proves that the boundaries are just in your head and that the power of Dexterity as a customization tool truly relies on the ability to work around what can be many times considered as impossible.

Until next post!

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


Developing for Dynamics GP – Weekly Summary

October 16, 2008

Ok, this may be funny, but trust me, if you have to keep up with David Musgrave, Mark Polino, Victoria Yudin, and all the other talented Dynamics GP bloggers out there you would soon understand why it’s best to do a summary on these guys posts. So today, I will summarize David’s post for the week.

Ever wonder why you receive an Illegal Address for field ‘PowerUser’ exception? Are you going balistic trying to find Dex.ini file after your upgrade to Dynamics GP 10.0? What are you going to do with your Dexterity customizations now that Micrsoft has changed up the Sales Transaction Entry interface buttons for the more sexy SOP Action button? Keep wondering why your DEXSQL.LOG file refers to an inexisting desSPRkmhBBCreh column in some table? Well, all answers are at Developing for Dynamics GP! Be sure to check David’s posts and drop him a note on what you think about the articles.

For more information on some of the above topics please check my articles on Dex.ini and upgrading your VBA customizations to address the new SOP Action button.

Until next post!

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


Follow

Get every new post delivered to your Inbox.

Join 25 other followers