Previous Value Record From Sql Tag

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!

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.

When 1 changes is triggers 2 which triggers 3.

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?


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.

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.

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.

“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:

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

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.

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.

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:

 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

  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!