Ok, here is the long and the short of it. This is just ONE example of ONE alarm.
SQLtags set to Alert and the columns used:
Lab_Alarm_02:
state_name: (passed with Global Timer)
active_time: (passed with Global Timer)
severity: 1
FT2_Active:
state_name: l11_batch
severity: 4
Report Window:
Dynamic Properties:
active_time : populated with global timer (Lab_Alarm_02)
state_name: populated with global timer (Lab_Alarm_02)
batchQuery: "SELECT state_name FROM alarmlog WHERE active_time = {active_time} AND severity = 4"
(there are 4 lines: l11-l14 And each has a table logging batch #'s. I use the state_name from FT2_Active alarm to tell me which table to look in for the batch #. Both the Lab_Alarm and the FT?_Active alarm happen at the same time.)
batch: "SELECT batch_number FROM {batchQuery} WHERE t_stamp < {active_time} ORDER BY ID DESC LIMIT 1"
(the alarm ALWAYS happens after the start of the batch so I look for a batch with a t_stamp LESS than the alarm, then order in REVERSE order and only select 1. Then I have the correct batch.)
If the Report window doesn’t have any DATA yet passed to it, then I obviously get an ERROR on my queries since the dynamic properties USED in the queries are blank. SO, I set a fall back value in there.
Here is where the problem lies. When I get an alarm and the Report window pops up, it does NOT always query the right TABLE for the batch number, SOMETIMES it queries the table that I entered in for the fall back value. I believe this stems from a timing issue between the QUERY and the PRINT function.
Ok, well you can test your hypothesis. Modify the event script so that it simply opens the window (don’t print or close it) and see if the correct data shows or not.
I have a suspicion that this is not a timing issue - but rather that your fallback value is masking a query that is truly returning zero rows for some other reason, even when the correct data is passed in. You’ll have to dig a little deeper to find out.
Carl,
You were correct. It was not a timing issue. Had some situations where my query had to fall back on my default value.
Which leads me to my next question LOL… I’m just full of them lately 
In my DataBase (mysql), in the table “alarmlog”, there is a column called point_path. And lets say for example I have 4 rows in this table and in the “point_path” column I have:
L11 Lab Alarms/Lab_Alarm_02
L12 Lab Alarms/Lab_Alarm_02
L13 Lab Alarms/Lab_Alarm_02
L14 Lab Alarms/Lab_Alarm_02
I know that I can run a query and get the string value from whichever row I want, but is it possible to query and get “L11 Lab Alarms/Lab_Alarm_02” but then once I have that string use jython/python script on a dynamic property to then extract just “L11” ??
Sure, thats just basic string manipulation.
You could do it anywhere along the chain, in your SQL SELECT statement with MySQL’s LEFT function. See the manual
dev.mysql.com/doc/refman/5.0/en/ … tions.html
Or in Python with a slice. See the manual
python.org/doc/2.1.3/lib/typesseq.html
Or in an expression with the substring function. See the manual
inductiveautomation.com/prod … #substring
Thank you. That helped simplify some code and do away with some of my dynamic properties.
Next question lol:
While in the ALARM HISTORY window of the client, I would like to be able to see what campaign and batch # I was running when the alarm occurred.
I obviously have an “alarmlog” table fed from sqltags (for 5 individual machines). 2 Master batch lines have 750 alarms each, 2 Final mix lines have 450 alarms each and 1 OIL PLC has about 100 Alarms. (approx.)
So in total approx. 2500 alarms. For 2400 of them, I need recipe name and batch #. For each line (L11-L14) I have 1 table for batch # and start time, and 1 table for recipe name and start time. I need to SOME HOW do a JOIN??? perhaps? and show the recipe and batch # in the same table as the alarms.
So, for example i’ll give you 4 alarm entries and some columns:
alarmlog table:
AlarmLog_ndx, active_time, clear_time, item_name, state_name
1, 2009-06-01 12:00:30, 2009-06-01 12:01:00, L11_Alarm_01, Alarm1
2, 2009-06-01 12:00:35, 2009-06-01 12:05:00, L12_Alarm_02, Alarm2
3, 2009-06-01 12:00:32, 2009-06-01 12:01:12, L13_Alarm_03, Alarm3
4, 2009-06-01 12:00:38, 2009-06-01 12:00:55, L14_Alarm_04, Alarm4
(each lines recipe and batch tables are structured the same way)
L11_batch table:
ID, batch_number, start_time
1, 17, 2009-06-01 12:00:00
2, 18, 2009-06-01 12:01:30
L12_recipe table:
ID, recipe_name, start_time
1, blackrubber, 2009-06-01 11:45:00
2, bluerubber, 2009-06-01 12:30:30
So, how do I take a LIST of alarms and get each one to show which recipe and batch was running?
… As a side note, it would be COOL to have the STATE_NAME dynamic. Just as when you add a tag and can use OTHER tags to drive the severity, it would be cool to INSERT information into the state_name when the alarm is stored in alarmlog.
yeah…No obvious query has jumped into my head yet. I’ll take a fresh look tomorrow after morning coffee enhances my query abilities.
Ok, I’ve got a query for you, but it comes with a caveat.
Your table structure violates one of the basic tenets of database design. Your recipe and batch tables are separated by line. This is bad - you never want multiple tables that all have the same structure. Instead of having 4 line recipe tables, you should have one recipe table that has a line column. If this were the case, you could use a query like so:
SELECT
L.item_name,
L.state_name,
L.active_time,
(SELECT R.recipe_name
FROM recipes R
WHERE R.line_id=LEFT(L.item_name, 3)
AND R.start_time < L.active_time
ORDER BY R.start_time DESC
LIMIT 1) as recipe_name,
(SELECT B.batch_number
FROM batches B
WHERE B.line_id=LEFT(L.item_name, 3)
AND B.start_time < L.active_time
ORDER BY B.start_time DESC
LIMIT 1) as batch_number
FROM alarmlog L
You could avoid restructuring your tables by using a unioning VIEW. A VIEW is bascially a virtual calculated table. You could have the tables “batches” and “recipes” in the above example generated dynamically by creating a view like this:
CREATE VIEW batches AS
SELECT 'L11' as line_id, batch_number, start_time FROM L11_batch
UNION
SELECT 'L12' as line_id, batch_number, start_time FROM L12_batch
UNION
SELECT 'L13' as line_id, batch_number, start_time FROM L13_batch
UNION
SELECT 'L14' as line_id, batch_number, start_time FROM L14_batch
;
Thank you Carl… you are a life saver!!! I won’t be able to try this till Monday morning, but just looking at it I can tell this is going to work… I’m not very proficient in ANY of these languages (i.e. I’ve never even SEEN them till this project started) and this forum and all of you at IA are just AWESOME!!!
Thanks
-Chris
[quote=“chris_d_sanders”]all of you at IA are just AWESOME!!!
-Chris[/quote]
+1