Developing for Dynamics GP

by David Musgrave (Perth, WA, Australia) and the Microsoft Dynamics GP Developer Support Team (Fargo, ND, USA)

Removing Extender Data when deleting Transactions or Lines

Removing Extender Data when deleting Transactions or Lines

Rate This
  • Comments 8

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.

Attachment: v10.00 Item Transfer Delete Extender Data Example.zip
  • 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!

  • 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

  • Posting from DynamicAccounting.net

    http://msdynamicsgp.blogspot.com/2010/01/removing-extender-data-when-deleting.html

  • Posting from Vaidy Mohan

    http://www.vaidy-dyngp.com/2010/01/removing-extender-records-david.html

  • Post from Mohammad Daoud

    http://mohdaoud.blogspot.com/2010/01/removing-extender-data-when-deleting.html

  • 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

  • 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

  • Post from Jivtesh Singh at About Dynamics, Development and Life

    www.jivtesh.com/.../dynamics-gp-customizations-best.html

Page 1 of 1 (8 items)
Comments Information

PLEASE READ BEFORE POSTING

Please only post comments relating to the topic of this page.

If you wish to ask a technical question, please use the links in the links section (scroll down, on right hand side) to ask on the Newsgroups or Forums. If you ask on the Newsgroups or Forums, others in the community can respond and the answers are available for everyone in the future.

Leave a Comment
  • Please add 1 and 3 and type the answer here:
  • Post