Know thy Common Table Operations with Dexterity - Part I

The first thing most of us learn from working with any relational database and software development language is how to perform common table operations (create, read, update, and delete). It is no different with Microsoft Dexterity. If you've read through David Musgrave's recommendations for getting started with Dexterity, then the next logical question is how to begin working programmatically with tables. This post will not focus on the in and outs of the table creation process, however, a brief explanation of some concepts that is required to move forward.

Creating tables

When you create a table in Dexterity, you’ll use or define several elements required for the table to store and access information properly, such as fields, keys and table relationships. Before you create tables, be sure you have previously defined all the fields that you’ll be using to store information in the table.

If you are creating applications that integrate with Microsoft Dynamics GP, be aware that Microsoft Dynamics GP table structures CANNOT be modified. Because you can do so, does not mean your should. Any additional information (say for example Customer Hotel Preferences) must be stored in extended tables with the same primary key used by the original Dynamics GP table, which in turn will allow you to setup table relationships. Maintaining GP tables intact will facilitate applying service packs and performing upgrades to your Dynamics GP application.

You will use the Table Definition window to create a table.

Table Definition window
I always recommend a careful review of the Dexterity Programmer's Guide Volume I manual provided with Microsoft Dexterity which explains the table creation process in detail.

Understanding Table Buffers

Each time a table is attached to a form, a table buffer is automatically created for that table. If you attach a table to more than one form, each form will have its own table buffer for that table. The following example shows all the tables attached to the RM_Customer_Maintenance form. When the form is opened in the application, each table buffer is loaded into memory ready to receive a record.

Form Definition window
A table buffer usually contains one record which comes from either windows that are part of the form or from the table, depending on whether the record is being read from the table or being stored from the window to the table.

It is perhaps a good time to check David's latest article on discarding changes from transaction windows as it is very intertwined with the implementation of forms, windows, and table buffers, along with some referential integrity issues that may show up when saving information to multiple tables at once.

Common Table Operations

Now that you understand some of the concepts let’s move on to common table operations. Typically, as with other development environments, data in tables is a direct result of users’ interaction with the application windows. Data is constantly transported back and forth from windows to tables and from tables to windows where users can perform subsequent operations on the data such as create new records, retrieve existing records, perform updates, and delete records. In Dexterity, this transport of data is accomplished via scripting -- SanScript in particular.

The SanScript language is equipped with four powerful statements that are the basis for table operations:

- get statement
- change table statement
- save table statement
- remove table statement

My next installment will describe in detail how these statements work and the do’s and don’ts when working with them.

Until next post!

Mariano Gomez, MVP
IntellPartners, LLC


David Musgrave said…
Waiting for the next instalment.

Don't forget the release table command and we have not even started on ranges yet.

Another great topic is how optimistic concurrency control (passive locking) works and how Dexterity uses this to allow the equivalent of field level locking.

David Musgrave [MSFT]
Escalation Engineer - Microsoft Dynamics GP
Microsoft Dynamics Support - Asia Pacific

Microsoft Dynamics (formerly Microsoft Business Solutions)

Any views contained within are my personal views and not necessarily Microsoft policy.
This posting is provided "AS IS" with no warranties, and confers no rights.
Mariano Gomez said…

Thanks for your input! I will certainly discuss these topics in greater detail and will look up to you for more valuable input, perhaps even a review of the article prior publishing.

Mariano Gomez, MIS, MVP, MCP, PMP
Maximum Global Business, LLC
Sigerist R. said…
Did you post de Part II of this article? it would really help me.
Thanks in advance

Popular posts from this blog

Power Apps - Application Monitoring with Azure Application Insights

DBMS: 12 Microsoft Dynamics GP: 0 error when updating to Microsoft Dynamics GP 2013 R2

eConnect Integration Service for Microsoft Dynamics GP 2010