Skip to content


Extender Cascading Delete

Jody Wood asked 4 years ago
Are there any plans to build cascading delete functionality into the Extender module/tables? Situation: When a user adds Extender data to a record (for instance, a Payables Transaction) and then, for whatever reason, deletes the transaction, the Extender data is now orphaned. Problem: Sometimes, when GP re-uses an INV number or something (there is a workaround/setting for this inside GP, but let\’s say it happens from time to time) it associates the old extender data with the new GP record.Current work-around (periodic maintenance):[code]– Display orphans SELECT * FROM dbo.EXT01100WHERE CASEWHEN LEFT(Extender_Key_Values_1, 3)=\’INV\’ THEN Extender_Key_Values_1ELSE Extender_Key_Values_2END   NOT IN (SELECT DISTINCT q1.POPRCTNMFROM (SELECT POPRCTNM FROM POP10300UNIONSELECT POPRCTNM FROM POP30300UNIONSELECT POPRCTNM FROM POP10330UNIONSELECT POPRCTNM FROM POP30330UNIONSELECT POPRCTNM FROM POP10390UNIONSELECT POPRCTNM FROM POP30390)q1)[/code]Then, delete those orphans. *This table is the LAST to be deleted from because all of the other related data in the other tables must be deleted first*The issue with the above maintenance process is that when new extender windows are created, the maintenance process must be updated to include the new window/form references. Requested solution:1) Create cascading delete in the EXT01100 and EXT01200 tables on foreign key – or maybe new tables in a future build2) Implement triggers on the Work/Open/History tables (though, history should NEVER be deleted) to delete from the Window Records (EXT01100) and Forms Records (EXT01200) table when a parent record is deleted**As a note to anyone who wants to use this to build a maintenance script, deleting the record from the EXT01100 table may prevent it from being displayed in the GP window, but it leaves the actual STRGA255, DATE and other related data orphaned.**
Nicole Albertson Staff answered 4 years ago
I believe the feature you are looking for is already built into Extender and is called “Table Links”.  You will find the setup for it on the Options button on the Windows Setup.  It will allow you to link on specific GP tables that you want that particular Extender window to trigger off of to remove date.  If the data is removed from the tables you add as links, then the Extender data for that record will be removed as well.  It will remove it from the EXT01100 – EXT01114 tables where appropriate. 
Here is a link to the section of the 2018 manual for Extender that talks about it.  It is the same in 2013, 2015, and 2016 as well.
There is not a feature like this for forms as the forms aren’t linked to anything for us to trigger that removal off of.  We could create a “clean-up” type maintenance window that would show you stranded records if you have the forms using lookups to GP data perhaps.  I will enter that as a suggestion.
Jody Wood replied 4 years ago

Excellent! Thanks, Nicole, I (obviously) didn’t know this lol. Off I go to do some linking!

Jody Wood replied 4 years ago

Scenario: User is inputting a payables transaction. User adds Extender data. Before the transaction, the user decides they need more inf (say, vendor is not set up) , and “deletes” the transaction before they saved it. Transaction is gone, but Extender data is still there and will be used on the next entered transaction because that is the “last receipt number”.

I have looked, and can’t find a way to setup table links on TEMP tables (which is what I am assumed is being used while the entry is being made and before it is saved). Do you know how to set a table link to a temp table, or is it more advisable to change the business process (at which time the user enters the Extender info)?

Jody Wood replied 4 years ago

I think I answered my own question: Change the process. Through monitoring SQL Trace, the TEMP tables are Global Temp Tables created on the fly by the GP process, and seem to change per entry (which makes sense).

For example, User 1 begins a transaction and ##2001231 is created, while User 2 begins a transaction and ##20011232 is created. There’s really no way to *easily* link temp tables, so this is an exception to the rule of cascading deletes.

The GP record must be SAVED in order for the Extender Table Links to function properly.

If you would like to submit an answer or comment, please sign in to the eOne portal.