Removing Extender Data when deleting Transactions or Lines

David Meego - Click for blog homepageI was recently asked to assist a colleague with an Extender case.  The problem was that Extender data was not being removed from the Extender SQL tables when its parent record was deleted.

The issue of Extender data being orphaned when the parent data is deleted is a known issue (KB 906242 see link below) and can be resolved fairly easily:

  • For Version 10.0 onwards you can use the Table Links feature (when editing an Extender object, from the menus select Options >> Table Links).  This allows you to define a parent table so that Extender knows to remove the additional data when the parent record is deleted.
     
  • For Version 9.0 and before, your option was to use a SQL Trigger on the parent table's delete event to clean up the data. Examples of the triggers are included in the attached archive at the bottom of this article.

These techniques work well for Master records (Customers, Vendors, Items, etc.) but do not always work as desired for Transaction records.


The reason Transaction records are different is that they move from table to table as the document moves through the workflow. They move from Work to Open when posted and then from Open to History when fully applied or archived.  Some transactions move straight from Work to History, but still have the same issue.

So if you place the Table Link or SQL Trigger on the Work table, when the transaction is posted and the record is added to the Open table and deleted from the Work table, the Extender data is deleted at the same time.  In summary, this means that the Extender data is lost once the transaction is posted.

This issue is described in KB 967740 (see link below) and provides the solution to add the Table Link or the SQL Trigger to the History table so the data is only removed when the transaction gets removed from History.

This solution prevents the Extender data from being removed prematurely but it does not handle the situation when Transaction Lines or the entire Transaction is deleted by the user while it is still unposted and stored in the Work tables.

So what is needed in addition to the Table Link or SQL Trigger on the History table, is a method to handle when a transaction is deleted by the user from the Work table which will not be fired when the transaction is posted.  This means we need to use events at the user interface instead of at the table level.

So we can use any of the customisation tools which work at the user interface. For our example we will use Visual Basic for Applications (VBA).

The concept behind the code is to use VBA to capture the delete line and delete transaction events via the Modal Dialogs and then use ActiveX Data Object (ADO) to open a connection to SQL Server and execute the appropriate Transact-SQL delete statements to remove the data from the appropriate tables.

Below is a list of the tables used to store data for normal Extender Windows:

Dexterity Technical Name SQL Table Description
PT_Extender_Window_Key_Values EXT00100 Stores the Mapping between the parent data's keys and the Extender key
PT_User_Window_Field_Strings EXT00101 Stores String values based on the Extender key
PT_User_Window_Field_Dates EXT00102 Stores Date values based on the Extender key
PT_User_Window_Field_Numbers EXT00103 Stores Numeric (Currency & Integer) values based on the Extender key
PT_User_Window_Field_Times EXT00104 Stores Time values based on the Extender key

Below is a list of the tables used to store data for Extender Detail Windows:

Dexterity Technical Name SQL Table Description
PT_UD_Detail_Window_Lines EXT00180 Stores the mapping for the line sequence numbers for the Extender key
PT_Detail_Window_Dates EXT00181 Stores String values based on the Extender key and line sequence
PT_Detail_Window_Dates EXT00182 Stores Date values based on the Extender key and line sequence
PT_Detail_Window_Numbers EXT00183 Stores Numeric (Currency & Integer) values based on the Extender key and line sequence
PT_Detail_Window_Times EXT00184 Stores Time values based on the Extender key and line sequence
PT_Extender_Detail_Window_Key_Values EXT00185 Stores the Mapping between the parent data's keys and the Extender key

 


The example code uses the Inventory Transfer Entry (IV_Transfer_Entry) window and includes two different examples. This is so we can demonstrate the code needed for both normal Extender Windows and Extender Detail Windows.

  1. Adding normal Extender Windows against the Transaction Line and Transaction Header
     
    Extender Windows.xml - Contains Extender Settings for the Extender Windows IVTRF_1 & IVTRF_2.
    IV_Transfer_Entry (Extender Windows).Package - Contains VBA code to delete Extender data when deleted by the user.
    Extender Windows.sql - Contains example SQL Triggers on work tables - For reference only, should not be used for live system.
     
  2. Adding an Extender Detail Window against the Transaction Lines
     
    Extender Detail Window.xml - Contains Extender Settings for the Extender Detail Window IV TRANSFER FORM.
    IV_Transfer_Entry (Extender Detail Window).Package - Contains VBA code to delete Extender data when deleted by the user.
    Extender Detail Window.sql - Contains example SQL Trigger on work table - For reference only, should not be used for live system.
     

NOTE: The SQL scripts provided above are examples to show how SQL triggers can be used to remove Extender data when a record is deleted from a SQL table. They are coded to trigger on the Work tables to demonstrate the issue when posting a transaction removes the data. They should NOT be used in a live system. They can be modified to work with history tables if required for v9.00 systems, but for v10.00 systems we suggest using the Table Links feature with the History table.

Example v10.0 Extender Settings and VBA code for the Inventory Transfer Entry (IV_Transfer_Entry) window is attached to the bottom of this article.

For related information please see the following Knowledge Base (KB) articles:

The associated Extender record is not deleted when you delete a Microsoft Great Plains record (KB 906242) Customersource

Extender data is removed from the EXT00100 table when a batch is posted in Microsoft Dynamics GP 10.0 (KB 967740) Customersource

The bottom line is that for the system to work 100%, you need both VBA (or equivalent) code to remove the Extender data when the user deletes the transaction from the Work tables AND you need to have a Table Link (or SQL Trigger) on the History table to handle if the transaction is removed from history.

Post a comment if you find this information and sample code useful.

David

01-Feb-2010: Added extra explanations about SQL Trigger Scripts included in attachment.

v10.00 Item Transfer Delete Extender Data Example.zip