Removing Extender Data when deleting Transactions or Lines
I 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.
- 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.
- 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 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
Comments
Anonymous
January 27, 2010
Thanks David, your timing is impeccable. I just noticed this last week when testing a SOP Import with Extender records--the Extender records were not removed when the SOP batch was deleted during my testing. I assume that the orphaned records are typically not a critical issue for most clients, but I can imagine that if there are a lot of deleted transactions with a lot of Extender records, they can accumulate pretty quickly. Thanks for the KB article references, the additional explanation, and the samples. You are a gentleman, and a scholar!Anonymous
January 27, 2010
David, it's awesome. I have noticed this long time back and had been blogging this as well. I had written my own version of code as well to explicitly delete orphaned records. But never I thought I would generalize and share it with Users/Consultants. Thanks so much for doing that. Vaidy MohanAnonymous
January 28, 2010
Posting from DynamicAccounting.net http://msdynamicsgp.blogspot.com/2010/01/removing-extender-data-when-deleting.htmlAnonymous
January 28, 2010
Posting from Vaidy Mohan http://www.vaidy-dyngp.com/2010/01/removing-extender-records-david.htmlAnonymous
January 31, 2010
The comment has been removedAnonymous
February 03, 2010
The comment has been removedAnonymous
February 03, 2010
Hi Konrad Thanks for your idea. I think your concept can be taken and improved upon. Your script needs to be changed to use joins with the "deleted" table so that it can handle when more than one record is deleted at a time. At this stage your code is not actually set based. We can also use joins rather sub-queries for the delete statements as well. However, I do like your idea for checking the open (or history) table for the transaction to decide if this is a delete or a post event. Thanks DavidAnonymous
August 19, 2010
Post from Jivtesh Singh at About Dynamics, Development and Life www.jivtesh.com/.../dynamics-gp-customizations-best.html