# Program control of Transaction Group delete of old data

**URL:** <https://forum.inductiveautomation.com/t/program-control-of-transaction-group-delete-of-old-data/10083>\
**Category:** Ignition\
**Created:** [June 2, 2015, 4:13pm UTC](https://forum.inductiveautomation.com/t/program-control-of-transaction-group-delete-of-old-data/10083 "2015-06-02T16:13:33Z")\
**Posts on this page:** 7\
**Page:** 1

<div class="post-metadata">

**Author:** ![McAlisterD](https://avatars.discourse-cdn.com/v4/letter/m/4491bb/32.png) [@McAlisterD](https://forum.inductiveautomation.com/u/McAlisterD)\
**Post date:** [June 2, 2015, 4:13pm UTC](https://forum.inductiveautomation.com/t/program-control-of-transaction-group-delete-of-old-data/10083/1 "2015-06-02T16:13:33Z")

</div>

Can a script change the delete older than time of a transaction group?

---

<div class="post-metadata">

**Author:** ![AlThePal](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/althepal/32/3603_2.png) [@AlThePal](https://forum.inductiveautomation.com/u/AlThePal)\
**Post date:** [August 22, 2015, 2:26pm UTC](https://forum.inductiveautomation.com/t/program-control-of-transaction-group-delete-of-old-data/10083/2 "2015-08-22T14:26:56Z")

</div>

Not directly as far as I know, but the Transaction Group is logging to a standard database table. Why don’t you write a timer script to run occasionally and delete all records from the database table which are older than a set value? You would then be in control of this value and could set it to whatever you wanted.

---

<div class="post-metadata">

**Author:** ![JordanCClark](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/jordancclark/32/83362_2.png) [@JordanCClark](https://forum.inductiveautomation.com/u/JordanCClark)\
**Post date:** [August 24, 2015, 11:00am UTC](https://forum.inductiveautomation.com/t/program-control-of-transaction-group-delete-of-old-data/10083/3 "2015-08-24T11:00:48Z")

</div>

If I’m understanding you correctly, you can make it part of the transaction group. Deleting records older than a time period is already built in.

 ![](https://us1.discourse-cdn.com/inductiveautomation/original/2X/4/4820249972827dffcfb721d2a98bb385595c1276.png)  
  
Other possibilities exist. Here’s an example of limiting the number of rows in a table:  
[https://inductiveautomation.com/forum/viewtopic.php?f=70&t=6671&p=19065&hilit=100#p19065](https://inductiveautomation.com/forum/viewtopic.php?f=70&t=6671&p=19065&hilit=100#p19065)  
Nothing says it couldn’t be customized for a specific datestamp. 🙂

---

<div class="post-metadata">

**Author:** ![McAlisterD](https://avatars.discourse-cdn.com/v4/letter/m/4491bb/32.png) [@McAlisterD](https://forum.inductiveautomation.com/u/McAlisterD)\
**Post date:** [September 15, 2015, 2:01pm UTC](https://forum.inductiveautomation.com/t/program-control-of-transaction-group-delete-of-old-data/10083/4 "2015-09-15T14:01:24Z")

</div>

Yes the transaction group can delete records older than the set time. However, I would like a client to be able to control how long to keep records. The oldest time in the transaction group can apparently only be changed by the designer.

---

<div class="post-metadata">

**Author:** ![AlThePal](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/althepal/32/3603_2.png) [@AlThePal](https://forum.inductiveautomation.com/u/AlThePal)\
**Post date:** [September 15, 2015, 3:29pm UTC](https://forum.inductiveautomation.com/t/program-control-of-transaction-group-delete-of-old-data/10083/5 "2015-09-15T15:29:35Z")

</div>

This is where Ignition’s tight integration with SQL databases really shines! You can write a script to do exactly this, running a SQL query like`DELETE FROM TableName WHERE date<'2015-03-15'`You can either let the users select the date using a control (with appropriate safeguards!) or calculate it automatically to be for example 6 months before the current date. This script can then be run manually from a button or automatically from a timed script.

---

<div class="post-metadata">

**Author:** ![Duffanator](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/duffanator/32/3560_2.png) [@Duffanator](https://forum.inductiveautomation.com/u/Duffanator)\
**Post date:** [September 15, 2015, 8:50pm UTC](https://forum.inductiveautomation.com/t/program-control-of-transaction-group-delete-of-old-data/10083/6 "2015-09-15T20:50:37Z")

</div>

I do something similar where I clear records that are older than X number of months (controllable with the function). Here is the function that I wrote:

[code]def tableScrub(tableName, timeColumn, numMonths):  
“”"  
Scrubs a table of data older than the number of months passed into the fuction  
tableName = The table name in the database to scrub  
numMonths = The number of months to keep data for  
“”"

```
import system
scrubLogger = system.util.getLogger("Database Table Scrubbing")
from java.util import Calendar
scrubDate = Calendar.getInstance()
scrubDate.add(Calendar.MONTH, -numMonths)
	
query = "DELETE FROM %s WHERE %s <= ?" %(tableName, timeColumn)
	
try:
	rowsAffected = system.db.runPrepUpdate(query, [scrubDate.getTime()])
	scrubLogger.info("%s rows were scrubbed from database table %s" %(str(rowsAffected), tableName))
except:
	scrubLogger.error("Data Scrubbing encountered an error deleting data from table: %s, time column: %s, date: %s" %(tableName, timeColumn, scrubDate.getTime()))[/code]

```

Then I call the function in a gateway timer script:

`# Clear old data from ABB inverter fault log
shared.genFunctions.tableScrub("ABB_Inverter_Faults", "t_stamp", 12)`

Perhaps that will be useful for you, perhaps not. Either way it displays an example of how to clear out old data your own way.

---

<div class="post-metadata">

**Author:** ![McAlisterD](https://avatars.discourse-cdn.com/v4/letter/m/4491bb/32.png) [@McAlisterD](https://forum.inductiveautomation.com/u/McAlisterD)\
**Post date:** [September 16, 2015, 12:33am UTC](https://forum.inductiveautomation.com/t/program-control-of-transaction-group-delete-of-old-data/10083/7 "2015-09-16T00:33:07Z")

</div>

I know I can write a script, in fact I did. I was trying to use functionality already built into the transaction group to simplify the application. However, it looks like the script is the best solution.

Thanks to all for the suggestions. I will consider this closed.