Using action items

Archived Topics
1

I am definitely a novice when it comes to FactorySQL so let me explain the situation first.

We have been inserting information from a device into the database every 30 seconds. The fields that are inserted are:

ndx (auto generated index)
t_stamp (timestamp)
step (the step the device is on usually 0-40 and then starts over)
measuredValue1 (some value from the device)
Here is some sample data:

Ndx	t_stamp	step	measuredValue1
1	9:00:00		1	10.2
2	9:00:30		1	10.1
3	9:01:00		1	10.2
4	9:01:30		2	5.0
5	9:02:00		2	5.0
6	9:02:30		3	5.0
7	9:03:00		1	10.0
8	9:03:30		1	10.2
9	9:04:00		2	5.0
10	9:04:30		3	5.0

The goal here is to be able to average the measured value for the first time step 1 happened and the second time and so on as the step gets ran in each cycle. We can then take the average of the measured value for each time the step is run and graph it for analysis and compare the average to make sure the step is staying consistent.
I need to be able to extract the information out by step. If I try to group by step all of the rows of the same step get grouped together. I need to group the rows of steps together by when the step was ran, but have no idea when the step gets started and stopped. I was thinking maybe an action item could be created that would assign the same unique identifier to the group of rows with the same step number and then increment the unique identifier every time the step number changes. Any ideas would be great.

2

I figured out how action items work and how to solve my problem.
In order for an action item to work like an OPC item you have to check “Store result to DB field or OPC item”. Then in the blank box type the name of the field you want the value to be inserted into the table. What is nice is if the column does not exist in the table FactorySQL will ask if you want to add the column to the table when you start the group.
I then created an expression command that would figure out what time a step started and insert it into my new column. Here is my expression:

If({Step}=ExecuteScalarQuery("SELECT Step FROM CIP1 WHERE CIP1_ndx =(SELECT MAX(CIP1_ndx) FROM CIP1)"),ExecuteScalarQuery("SELECT StepStartTime FROM CIP1 WHERE CIP1_ndx =(SELECT MAX(CIP1_ndx) FROM CIP1)"),CurrentDateTime())

The expression will get the last entry in the table and check if the entry’s step matches the current OPC step. If the step does not match then I know the step changed and I return the current date. If the step is the same then I get the last entries step start time.
Hope this helps anyone else that is trying to figure out action items.

3

I’m glad you figured it out, and I’m also glad that I just noticed you posted a reply before I started writing! I was about to outline something like that, but was debating how fancy/difficult I wanted to be.

At any rate, that looks like just about what I would have done.

Regards,