Removing Extender Data when deleting Transactions or Lines

David Meego - Click for blog homepageThis is a reposting of an article I originally wrote on my Developing for Dynamics GP blog.

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.

  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 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

This article was originally posted on the Developing for Dynamics GP Blog and has been reposted on http://www.winthropdc.com/blog.

9 thoughts on “Removing Extender Data when deleting Transactions or Lines

  1. 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!

    Like

  2. 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 Mohan

    Like

  3. Hi David
    Thanks for your information here … very useful!
    I have taken a different approach to the issue of Extender with Transactional data. I think the best way is not to create a Table Link in Extender, but to just handle to situation where the user deletes a transaction before being posted. A user cannot delete a transaction after it is posted so there is not really an issue there (except of course through Utilities).
    I suggest that it is better to do this with a SQL Trigger rather than through VBA.
    I have an Extender Window off the GL Journal Lines called ‘HRJOURNAL’. Below is the coding of the SQL Trigger FYI.
    I really feel that this should be built into the application rather than us having to develop a work around for deleting rogue Extender transactions.
    Regards
    Konrad
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    — =============================================
    — Author: Konrad Berger
    — Create date: 2010-04-02
    — Description: Trigger on GL Transaction Work
    — Table to delete Extender Data id User
    — deletes journal
    — =============================================
    CREATE TRIGGER  [dbo].[DE_trgGL10000ExtenderDelete] ON [dbo].[GL10000]  
      AFTER DELETE
    AS
    BEGIN
    SET NOCOUNT ON;
       — Insert statements for trigger here
    declare @JournalEntry int
    declare @Status varchar(10)
    select @JournalEntry = jrnentry from deleted
    — Determine if Journal is not actually deleted by user – it may be being posted by user
    — Note: this can be done because GL20000 is populated before GL10000 is deleted by GP
    select @Status = ‘Posted’ from GL20000 where jrnentry = @JournalEntry group by jrnentry
    if isnull(@Status,’xx’) = ‘xx’ begin set @Status = ‘Deleted’ end
    — Delete Extender Data when User Deleted Unposted Journal
    if @Status = ‘Deleted’
    begin
    delete from ext00104
    where PT_Window_ID = ‘HRJOURNAL’
    and PT_UD_Key in
    (select PT_UD_Key from ext00100 where PT_Window_ID = ‘HRJOURNAL’
    and Key_Strings_1 = @JournalEntry)
    delete from ext00103
    where PT_Window_ID = ‘HRJOURNAL’
    and PT_UD_Key in
    (select PT_UD_Key from ext00100 where PT_Window_ID = ‘HRJOURNAL’
    and Key_Strings_1 = @JournalEntry)
    delete from ext00102
    where PT_Window_ID = ‘HRJOURNAL’
    and PT_UD_Key in
    (select PT_UD_Key from ext00100 where PT_Window_ID = ‘HRJOURNAL’
    and Key_Strings_1 = @JournalEntry)
    delete from ext00101
    where PT_Window_ID = ‘HRJOURNAL’
    and PT_UD_Key in
    (select PT_UD_Key from ext00100 where PT_Window_ID = ‘HRJOURNAL’
    and Key_Strings_1 = @JournalEntry)
    delete from ext00100 where PT_Window_ID = ‘HRJOURNAL’ and Key_Strings_1 = @JournalEntry
    end
    END
    GO

    Like

  4. 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
    David

    Like

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.