#GPPT Moving on from the Visual Basic for Applications (VBA) Mindset

David Meego - Click for blog homepageAs a follow on from yesterday’s article on how different languages can be used to drive the Microsoft Dynamics GP user interface, this article discusses the need to move away from the old Visual Basic for Applications (VBA) way of thinking. If you missed yesterday’s article, here is the link.

Now that VBA is “End of Life” and many sites are starting to use GP Power Tools – Developer Tools module to replace the VBA business logic with GP Power Tools triggers and scripts. However some developers are still trying to write code as though they were still limited by VBA rather than leveraging the more powerful capabilities of GP Power Tools and Dexterity.

While GP Power Tools is based on Dexterity, it has many features above and beyond what standard Dexterity can do, including:

  • Able to trigger on and run scripts against Modified windows
  • Able to trigger on and control Modal Dialogs
  • Able to access form table buffers on any form
  • Able to work across multiple dictionaries at the same time
  • Able to execute code or run scripts written in C#, Visual Basic .Net and SQL
  • No separate chunking processes, deployment processes or upgrade processes
  • plus much more

What is the VBA Mindset?

Visual Basic for Applications (VBA) only has access to the user interface (windows, visible window fields and modal dialogs) with no access to the underlying tables, table buffers and business logic (functions and procedures), and so the style of coding used is based on what works within those limitations.

For example, here are some common use cases:

Displaying an Existing Record

Uses the Changed event on the primary key field.

Saving a Record

Uses BeforeUserChanged event on the Save Button and Window_AfterModalDialog event to capture saving from a dialog.

Deleting a Record

Uses BeforeUserChanged event and AfterUserChanged event on the Delete Button  combined with the Window_AfterModalDialog event to capture the delete confirmation dialog.

Updating field on another window

Use code to open the window, enter the field value and then click OK or save to close the window. Can also use Window_BeforeOpen event with public variables to make the window hidden during the change.

What should we use with GP Power Tools or Dexterity?

With GP Power Tools and Dexterity we have access to all the underlying code as well as the user interface and can use simpler events and code that catches all possible events.

It is recommended to capture a script log to confirm exactly where the code resides as older code often using a field change script, but newer code might use a form level function or procedure. This is visible in the logs depending on the script run. If it has _CHG it is a field change script, if it as () it is a function otherwise it is a procedure.

For example, for the same use cases:

Displaying an Existing Record

Trigger after the Display Existing Record event. Can check the value of ‘Display Existing Record’ field to see if this is a new or existing record.

Saving a Record

Trigger after the Save Record event. If there is a ‘Save Record’ field, you can check its value to confirm if the record save occurred or if it failed for some reason, such as Not all required fields entered.

Note: The Save Record event is the one event called by all other situations where the record can be saved… Save Button, 4 browse buttons, Lookup button, closing the window, Print Button, etc.

Deleting a Record

Trigger after the table delete event, which only fires after the record is successfully deleted.

Updating field on another window

If the window is on the same form, you could change its value and run the script without even opening the window as once the form is open, all windows on that form are accessible.

If the window is on a separate form, you might be able to update its value by directly changing the value in the form’s table buffer for that field.

Otherwise you could always resort back to opening the window, changing the value and closing the window again. Also with the option to add a trigger and variable to hide the window.

Getting data from the System

While GP Power Tools can use execute SQL code or call saved SQL scripts, you can also access tables directly using Dexterity table commands.

For single record access, using Dexterity table commands is much faster and cleaner. If the table is in the same dictionary, you can just use the commands natively. If the table is in another dictionary, you can use Helper Functions or a Dexterity script executed in the context of the other dictionary.

For data sets, processing large amounts of data, or complex joins, then using the Helper Functions to call SQL code or even better execute SQL scripts is the best approach. You can also use parameter lists to automatically populate the fields in the script. Parameter List data can be set by either prompting the user or programmatically from data in window fields, table fields or script variables.

Examples and More Information

Examples of the GP Power Tools based examples can be found in the articles below:

Conclusion

To move away from the VBA mindset, work out your triggering events not by what is happening on the user interface, but using a script log and triggering on the one location in the underlying code that all the user interface events reference.

Hope this is helpful.

David

05-Oct-2021: Added section on getting data from the system.

This article was originally posted on http://www.winthropdc.com/blog.

Please post feedback or comments

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.