# Previous Value Record From Sql Tag **URL:** \ **Category:** Ignition\ **Tags:** ignition79, database\ **Created:** [August 22, 2019, 2:15pm UTC](https://forum.inductiveautomation.com/t/previous-value-record-from-sql-tag/27666 "2019-08-22T14:15:33Z")\ **Posts on this page:** 10\ **Page:** 1
**Author:** ![tshaw](https://avatars.discourse-cdn.com/v4/letter/t/b5e925/32.png) [@tshaw](https://forum.inductiveautomation.com/u/tshaw)\ **Post date:** [August 22, 2019, 2:15pm UTC](https://forum.inductiveautomation.com/t/previous-value-record-from-sql-tag/27666/1 "2019-08-22T14:15:33Z")
Hi All, I am working on a project that I am recording the linear feet(LF) of a roll of plastic film for a scrap recording project. This is my first Ignition project and I am really close to having everything I need. There is one problem that seems very doable but with my lack of knowledge I am looking for some help. So here is the scenario I have a PLC that i am getting the LF from. I also have many Query tags from a MSSQL DB that our shop floor management runs on ( ex. est. film issued to a job, job ID running, logged in emp, and the state of the job (run, setup, maintenance downtime)) The Problem: I am writing to a transaction group the important information to be reported on. One of the most important metrics is calculating “Running film” and “Setup Film” the run and setup values are coming from a Query tag. Currently when the value changes from Run to Setup i have it triggering a reset to the PLC and also writing to the DB via a transaction group. The issue is when the value changes Setup to Run and the reset triggers I am writing The new value with the correct LF. What I need to be capturing is the Previous value with the current LF and then Reset the counter and start counting for the run state. Expression Tag to trigger Reset: hasChanged({[default]Prod/Machines/22-4/Shopfloor\_TimeDetail})| Write Value Back to OPC item? True OPC item path : ns=1;s=[AB-PLC]RESET\_22\_4 In summary: I need to write the previous Run/Setup detail when the the value changes: When the Job goes from Setup to Run, I want to trigger OPC Reset and write Setup and the last counter value. Then start recording the Run data Hopefully my ramblings make some sense! Thanks in advanced for any thoughts or help you can provide! ---
**Author:** ![bpreston](https://avatars.discourse-cdn.com/v4/letter/b/4af34b/32.png) [@bpreston](https://forum.inductiveautomation.com/u/bpreston)\ **Post date:** [August 22, 2019, 2:31pm UTC](https://forum.inductiveautomation.com/t/previous-value-record-from-sql-tag/27666/2 "2019-08-22T14:31:52Z")
If I read that right, your resetting out of order and not getting the correct data recorded before the PLC is reset? If so how are you triggering your reset? If you want to make sure you capture the data before the reset is triggered I’d use the write handshake on success function under the triggers tag in your transaction group. I know your looking for the previous Run/Setup detail though so I’m not positive that would do it because you said your getting that from a query. If the data your trying to get is from a query and the data being queried changes before the transaction group can log it, you may need a separate transaction group that just looks at the previous query data points and only runs when a reset is triggered. Hope this helps but I fully admit I may be reading what your looking for wrong. ---
**Author:** ![tshaw](https://avatars.discourse-cdn.com/v4/letter/t/b5e925/32.png) [@tshaw](https://forum.inductiveautomation.com/u/tshaw)\ **Post date:** [August 22, 2019, 2:41pm UTC](https://forum.inductiveautomation.com/t/previous-value-record-from-sql-tag/27666/3 "2019-08-22T14:41:38Z")
![image](https://us1.discourse-cdn.com/inductiveautomation/original/2X/d/d397afae4693a7ff2f823218fd5511fb2e7ef3cb.png) When 1 changes is triggers 2 which triggers 3. ---
**Author:** ![bpreston](https://avatars.discourse-cdn.com/v4/letter/b/4af34b/32.png) [@bpreston](https://forum.inductiveautomation.com/u/bpreston)\ **Post date:** [August 22, 2019, 2:46pm UTC](https://forum.inductiveautomation.com/t/previous-value-record-from-sql-tag/27666/4 "2019-08-22T14:46:11Z")
It looks like in the bottom right you have the tag for 3 set in the write handshake spot but you don’t have that turned on. What triggers the reset? Which part of what you showed are you not getting the correct data from? ---
**Author:** ![tshaw](https://avatars.discourse-cdn.com/v4/letter/t/b5e925/32.png) [@tshaw](https://forum.inductiveautomation.com/u/tshaw)\ **Post date:** [August 22, 2019, 2:50pm UTC](https://forum.inductiveautomation.com/t/previous-value-record-from-sql-tag/27666/5 "2019-08-22T14:50:47Z")
![image](https://us1.discourse-cdn.com/inductiveautomation/original/2X/2/27d89c42404a867d5f7d0cd57e73a61007c467fc.png) The value Change of the Query Tag Shopfloor\_TimeDetail triggers the reset. and it writes to the DB the new TimeDetail Value and i need the previous. ---
**Author:** ![bpreston](https://avatars.discourse-cdn.com/v4/letter/b/4af34b/32.png) [@bpreston](https://forum.inductiveautomation.com/u/bpreston)\ **Post date:** [August 22, 2019, 2:55pm UTC](https://forum.inductiveautomation.com/t/previous-value-record-from-sql-tag/27666/6 "2019-08-22T14:55:00Z")
So that triggers your resets for the PLC but also is one of your triggers for the transaction group to run? The reason I asked about that is if the reset tag is triggered and runs causing the reset in your PLC before your transaction group runs again, your transaction group would log the values after the reset is complete. If your trying to get one last set of data before the reset thats where I would use the write on handshake on success. If your getting the correct values from the PLC but not from the query tags that can be a little more fun to get around. Thats why I’m asking a lot of questions trying to get an understanding. ---
**Author:** ![bpreston](https://avatars.discourse-cdn.com/v4/letter/b/4af34b/32.png) [@bpreston](https://forum.inductiveautomation.com/u/bpreston)\ **Post date:** [August 22, 2019, 3:07pm UTC](https://forum.inductiveautomation.com/t/previous-value-record-from-sql-tag/27666/7 "2019-08-22T15:07:44Z")
With the reset being a trigger and values being updated based on it, your scan class and execution time of the transaction group can come into play. The execution scheduling for the transaction group is similar to the scan class for the tag but if that tag updates faster than the transaction group schedule it can cause timing issues where you can miss entries you may want or with doing a reset you may miss the data before the reset. That is where the sequence becomes important. If it is information from your query tags that are missing, I’m assuming your queries are set up so your looking for the most recent session id then pulling in the rest based on that ID number. When the reset occurs I would assume that a new session ID is created which would change the data you see in your tags right away which may be before the reset was seen to do a last entry with your transaction group. To get around this you can get creative in the tags or scripting to hold the last ID long enough to ensure the transaction group runs or you can set the ID that runs the rest of your queries using an expression in your transaction group that fills in your ID tag with the current ID. Only have that one run when the transaction group runs instead of run always. From there you can do your same query but write back to a memory tag in your tag folder that holds the current ID. This would make it where your other query tags would be running with the old ID until after the data is collected then it would write the new one back so when the tags run again they would update with the new information. Let me know if I’m way off on what your looking for. ---
**Author:** ![tshaw](https://avatars.discourse-cdn.com/v4/letter/t/b5e925/32.png) [@tshaw](https://forum.inductiveautomation.com/u/tshaw)\ **Post date:** [August 22, 2019, 7:38pm UTC](https://forum.inductiveautomation.com/t/previous-value-record-from-sql-tag/27666/8 "2019-08-22T19:38:17Z")
> "To get around this you can get creative in the tags or scripting to hold the last ID long enough to ensure the transaction group runs or you can set the ID that runs the rest of your queries using an expression in your transaction group that fills in your ID tag with the current ID. Only have that one run when the transaction group runs instead of run always. From there you can do your same query but write back to a memory tag in your tag folder that holds the current ID. This would make it where your other query tags would be running with the old ID until after the data is collected then it would write the new one back so when the tags run again they would update with the new information." This sounds like what I need to do, not sure how to accomplish. I am looking at the top 1 query below: ```auto select top 1 TD.TimeType FROM shopfloor.dbo.timedetail TD WHERE TD.EmpType = 'Machine' AND TD.EMPNUM = '{[.]WorkCenterID}' AND TD.Timetype in ( 'run','setup') ORDER BY Td.CreatedDate Desc, TD.TIMEID DESC ``` When this changes the PLC has the right value because i am delaying it 2 sec to reset. However it is the query value that is wrong. Thanks Again ---
**Author:** ![bpreston](https://avatars.discourse-cdn.com/v4/letter/b/4af34b/32.png) [@bpreston](https://forum.inductiveautomation.com/u/bpreston)\ **Post date:** [August 23, 2019, 1:00pm UTC](https://forum.inductiveautomation.com/t/previous-value-record-from-sql-tag/27666/9 "2019-08-23T13:00:26Z")
To start with you would want to drag in the memory tag you want to write to into the OPC section of your transaction group. Then to put the query in your transaction group you would right click in the expression area, then New Item/New Expression Item. Under the Expression/SQL section you would pick the expression type SQL query and your datasourse at the bottom. Put your query in there. ![image](https://us1.discourse-cdn.com/inductiveautomation/original/2X/2/2fd6ec7312459df201860f3b3d3c490756b4a415.png) On the General tab pick the name you want the item to be called. By your picture I’m assuming you would want your data type to be string. Then at the bottom for Target Type pick Other tag. For the target name you should be able to pick the OPC tag your pulled into your transaction group. I would leave Run-Always unchecked since you only want it to update as the transaction runs. By setting it this way, when the transaction group runs it will write what the query found back to your memory tag. This should prevent your other query tags from using the new ID in their query before the data has been recorded. ---
**Author:** ![tshaw](https://avatars.discourse-cdn.com/v4/letter/t/b5e925/32.png) [@tshaw](https://forum.inductiveautomation.com/u/tshaw)\ **Post date:** [August 23, 2019, 8:45pm UTC](https://forum.inductiveautomation.com/t/previous-value-record-from-sql-tag/27666/10 "2019-08-23T20:45:39Z")
Thanks for the help. I was not able to figure out the Expression trigger to change the value I think it may have something to do with using a Historical Transaction Group Vs Standard Transaction Group( we are only licensed for Historical) So I ended up making another SQL Query tag with the previous value using: ```auto SELECT TOP 1 TD2.TimeDetail PreviousTimeDetail FROM Ignition.dbo.DP224 TD INNER JOIN Ignition.dbo.DP224 TD2 ON TD.dp224_ndx-1 = TD2.dp224_ndx AND TD.TimeDetail IS NOT NULL ORDER BY TD.TimeStamp DESC ``` and then will use the logic below to make sure the records are correct ```auto SELECT CASE WHEN D.TimeDetail <> D.TimeDetail_Previous THEN D.TimeDetail_Previous ELSE D.TimeDetail END AS TIMEDETAIL , D.FilmLength FROM Ignition.dbo.DP224 D ``` I am sure there is a better way but I know SQL way better than any other programming so this will have to work. Thanks Again!