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:
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:
Below is a list of the tables used to store data for Extender Detail Windows:
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.
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) Extender data is removed from the EXT00100 table when a batch is posted in Microsoft Dynamics GP 10.0 (KB 967740)
The associated Extender record is not deleted when you delete a Microsoft Great Plains record (KB 906242)
Extender data is removed from the EXT00100 table when a batch is posted in Microsoft Dynamics GP 10.0 (KB 967740)
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.
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
-- =============================================
-- 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
delete from ext00102
delete from ext00101
delete from ext00100 where PT_Window_ID = 'HRJOURNAL' and Key_Strings_1 = @JournalEntry
end
END
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
Post from Jivtesh Singh at About Dynamics, Development and Life
www.jivtesh.com/.../dynamics-gp-customizations-best.html
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.