# Revolving DB table

**URL:** <https://forum.inductiveautomation.com/t/revolving-db-table/3089>\
**Category:** Ignition\
**Created:** [July 12, 2011, 3:48pm UTC](https://forum.inductiveautomation.com/t/revolving-db-table/3089 "2011-07-12T15:48:46Z")\
**Posts on this page:** 9\
**Page:** 1

<div class="post-metadata">

**Author:** ![scott\_stc](https://avatars.discourse-cdn.com/v4/letter/s/d26b3c/32.png) [@scott\_stc](https://forum.inductiveautomation.com/u/scott_stc)\
**Post date:** [July 12, 2011, 3:48pm UTC](https://forum.inductiveautomation.com/t/revolving-db-table/3089/1 "2011-07-12T15:48:46Z")

</div>

Design question - We would like to track parts through a particular portion of a process. All we want is to graphically illustrate (like on a chart) where the parts are in this one portion of the process (since the parts are obscured from view as they travel through this portion).  
What we have tried so far is setting up a transaction group with three columns - t\_stamp, part\_bit (triggered and enters a 1 when a part enters the machine), and part\_track\_ndx. Everything is tracking and logging correctly, but the problem comes in when we go to graphically represent the position of the parts. Only 1500 parts fit in the machine, so we only want to display the last 1500 triggers (note - a trigger will log either a 1 for a part, or a 0 for a gap). On the charts we seem to only be able to represent parts via the t-stamp or ndx column.  
Question (finally) - Is there a way to limit the number or DB records to 1500, and to have it work in a FIFO fashion? i.e. update the first record, have all others index back one, then drop the 1501st record? Any help or alternative ideas are greatly appreciated.  
Thanks!

---

<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:** [July 13, 2011, 11:52am UTC](https://forum.inductiveautomation.com/t/revolving-db-table/3089/2 "2011-07-13T11:52:24Z")

</div>

Wow! A lot to digest here…  
 ![](https://us1.discourse-cdn.com/inductiveautomation/original/1X/8f903aa9c8b6fcc5103102107e77d8a3437f0e8b.png)

My example does it for 100 samples, but you can easily change it to the 1500 you require… 😉

The other caveat is that this uses MySQL. Syntax will differ slightly for other platforms.

The top priority would be to display the data as you want it. The XY Chart will work best for us because we can make the X-axis however we want. But first we want to get the data by rank. I stuck a timer into the container and used this script in the propertyChange Event:

```auto
query="SELECT Ramp FROM FIFO1 order by t_stamp desc limit 100"
table1 = system.db.runQuery(query,"MySQL")

headers = ["Rank","Value"]
i=0
rows=[]
for row in table1:
	i += 1
	newrow=[i,row[0]]
	rows.append(newrow)

event.source.parent.getComponent('Chart').Data=system.dataset.toDataSet(headers, rows)
```

This gets the data into the XY-chart as rank (1-100) and value (0/1).  
Opening the Chart Customizer, we now need a new Number Axis type for the X-Axis:

 ![](https://us1.discourse-cdn.com/inductiveautomation/original/1X/425055d3c76d7158c92c04ef399349835791959e.png)  
  
In the Dataset Properties tab:  
 ![](https://us1.discourse-cdn.com/inductiveautomation/original/1X/eb9cd6dbd43d713baeb59be0a58e6cd0b295d340.png)  

Presto! You now have a ranked status chart. This is how I used to do Status Charts before Status Charts existed. 😃

---

<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:** [July 13, 2011, 12:08pm UTC](https://forum.inductiveautomation.com/t/revolving-db-table/3089/3 "2011-07-13T12:08:13Z")

</div>

The second part on limiting the table size the the last 100 (still using my example here) rows can go something like this:  
 ![](https://us1.discourse-cdn.com/inductiveautomation/original/1X/1cae7be032d3b8bd2c839f891ec22a8d47c1dab5.png)

In the transaction group that adds rows to the FIFO two Expression Items are added. The first gets the oldest timestamp of the newest 100 rows:

`Select min(t_stamp)
FROM (Select t_stamp
		From FIFO1
		Order by t_stamp desc
		limit 100) as t_stamp
`

The second deletes everything older than the DateTime you just got, leaving 100 rows left in the table:

`DELETE FROM FIFO1
WHERE t_stamp<'{[.]Oldest 100th Date}'`

Borrowing Carl’s tagline: Hope this helps!

Regards,

---

<div class="post-metadata">

**Author:** ![scott\_stc](https://avatars.discourse-cdn.com/v4/letter/s/d26b3c/32.png) [@scott\_stc](https://forum.inductiveautomation.com/u/scott_stc)\
**Post date:** [July 13, 2011, 12:53pm UTC](https://forum.inductiveautomation.com/t/revolving-db-table/3089/4 "2011-07-13T12:53:48Z")

</div>

Wow Jordan!! That is fantastic!!

This is exactly what we are looking to do. The FIFO portion was the main crux of our holdup, and you nailed it perfectly. We’ll be implementing this first thing this morning. Thanks a million for your help and sample.

Regards, 😃

---

<div class="post-metadata">

**Author:** ![Carl.Gould](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/carl.gould/32/7262_2.png) [@Carl.Gould](https://forum.inductiveautomation.com/u/Carl.Gould)\
**Post date:** [July 21, 2011, 5:54pm UTC](https://forum.inductiveautomation.com/t/revolving-db-table/3089/5 "2011-07-21T17:54:37Z")

</div>

Wow, great post Jordan.

---

<div class="post-metadata">

**Author:** ![Robert](https://avatars.discourse-cdn.com/v4/letter/r/e95f7d/32.png) [@Robert](https://forum.inductiveautomation.com/u/Robert)\
**Post date:** [July 21, 2011, 10:17pm UTC](https://forum.inductiveautomation.com/t/revolving-db-table/3089/6 "2011-07-21T22:17:40Z")

</div>

Jordan, I think qualifies for posting in [here](http://www.inductiveautomation.com/forum/viewforum.php?f=81).

![:thumb_right:](https://us1.discourse-cdn.com/inductiveautomation/original/1X/2026e28c0c425034352c1b75807edf956adc75f4.gif ":thumb\_right:")

---

<div class="post-metadata">

**Author:** ![scott\_stc](https://avatars.discourse-cdn.com/v4/letter/s/d26b3c/32.png) [@scott\_stc](https://forum.inductiveautomation.com/u/scott_stc)\
**Post date:** [December 18, 2012, 3:35pm UTC](https://forum.inductiveautomation.com/t/revolving-db-table/3089/7 "2012-12-18T15:35:15Z")

</div>

Hello,

We have been runnign the code that Jordan suggested for about a year, in a Property Change Event Handler (see below):

query=“SELECT L1\_SECA\_ITEM FROM L1\_DRYER\_TRACK ORDER BY t\_stamp desc LIMIT 80”  
table1 = system.db.runQuery(query,“PM\_DATA”)

headers = [“Rank”, “Sticks”]  
i=0  
rows=[]  
for row in table1:  
i += 1  
newrow=[i,row[0]]  
rows.append(newrow)

event.source.parent.getComponent(‘L1\_SECA\_FILL’).Data=system.dataset.toDataSet(headers, rows)

This query works great - it brings back two columns, one as Rank, and one as Sticks, with Rank column automatically filled in, and Sticks as a column. However, now we want to add a column called Shape Change. This column already exists in the DB, but when we add it to our query, and to our headers in the code above, we get an error saying that the number of columns in the table and our headers do not match. We have tried many different configurations of this with no success. Does anybody know how we can add a column to our query and to our headers?  
Thanks.

---

<div class="post-metadata">

**Author:** ![Travis.Cox](https://avatars.discourse-cdn.com/v4/letter/t/b9bd4f/32.png) [@Travis.Cox](https://forum.inductiveautomation.com/u/Travis.Cox)\
**Post date:** [December 18, 2012, 4:50pm UTC](https://forum.inductiveautomation.com/t/revolving-db-table/3089/8 "2012-12-18T16:50:30Z")

</div>

Sure just do the following:[code]query=“SELECT L1\_SECA\_ITEM, SHAPE\_CHANGE FROM L1\_DRYER\_TRACK ORDER BY t\_stamp desc LIMIT 80”  
table1 = system.db.runQuery(query,“PM\_DATA”)

headers = [“Rank”, “Sticks”, “ShapeChange”]  
i=0  
rows=[]  
for row in table1:  
i += 1  
newrow=[i,row[0],row[1]]  
rows.append(newrow)

event.source.parent.getComponent(‘L1\_SECA\_FILL’).Data=system.dataset.toDataSet(headers, rows)[/code]

---

<div class="post-metadata">

**Author:** ![scott\_stc](https://avatars.discourse-cdn.com/v4/letter/s/d26b3c/32.png) [@scott\_stc](https://forum.inductiveautomation.com/u/scott_stc)\
**Post date:** [December 18, 2012, 7:51pm UTC](https://forum.inductiveautomation.com/t/revolving-db-table/3089/9 "2012-12-18T19:51:54Z")

</div>

Travis,

That did it. Thank you for posting the necessary correction, and I see where I was going wrong:  
newrow=[i,row[0],row[1]]

Thank you.