Thanks for the compliment!
Good eye. You actually have happened upon what is one of our architecture's few weaknesses. Because of the highly de-coupled nature of FactorySQL and FactoryPMI, FactoryPMI doesn't have much contextual information about the data it is reading and writing. Before I go into some ways to add this contextual information, I just want to mention that this issue is completely resolved with the addition of our much-anticipated SQLTags feature, which will be part of FactoryPMI 3.0 and FactorySQL 4.0.
Ok, so, first thing to know is that in FactorySQL, in the group configuration, you can tell it to store the quality code in the table. FactoryPMI can then check this quality code to know if the quality is good (good=192). This covers cases like losing comunication to the PLC.
Now, for detecting whether or not FactorySQL is running. Make a new group that simply has one action item in it that runs a SQL query like "SELECT CURRENT_TIMESTAMP", and stores it to a database column named FSQL_Tstamp. Have this group update the first row of a table, say every 5 seconds or so (lets call the table FSQLStatus. Now a query like this in FactoryPMI will return 0 or 1 indicating whether or not FactorySQL is running: (this example is using MySQL syntax. Your syntax may vary slightly if you are using a different database)
SELECT CASE WHEN TIMESTAMPDIFF(SECOND, CURRENT_TIMESTAMP, FSQL_Tstamp) > 20 THEN 0 ELSE 1 END as FSQL_Running FROM FSQLStatus
This query will return 1 if the timestamp is less than 20 seconds old, and 0 otherwise. You can play with the timing here if you want a faster feedback. Like I said before, we know this isn't the most intuitive thing in the world, but FactoryPMI 3.0 will fix that (due out in a few months)
Now, for your second question about alarms: Yes, this a fairly common task. You just need to make sure that your FactorySQL project's groups are separated into folders that correspond with your plant areas. This way you can run SQL queries on the alarm log table that can give you this information. Typically when you want to color something like a button background dynamically in FactoryPMI, you want to boil your information down to a code, something like: 0=No alarms, 1=Unacked In Alm, 2=Ack In Alm, 3=Unack out of Alm
In this case we are going to run 3 queries(per plant area) that will tell us the following:
Are there any unacked alarms?
Are there any acked alarms?
Are there any unacked cleared alarms?
The queries would look something like this:
SELECT COUNT(*)>0 FROM alarm_log WHERE clear_time IS NULL AND ack_time IS NULL AND group_folder LIKE 'EastArea%'
SELECT COUNT(*)>0 FROM alarm_log WHERE clear_time IS NULL AND ack_time IS NOT NULL AND group_folder LIKE 'EastArea%'
SELECT COUNT(*)>0 FROM alarm_log WHERE clear_time IS NOT NULL AND ack_time IS NULL AND group_folder LIKE 'EastArea%'
Now, the best way to put this all together is to make a button with the following dynamic properties:
[ul]
[li] AreaName (String)[/li]
[li] UnAcked (Integer)[/li]
[li] Acked (Integer)[/li]
[li] ClrUnAcked (Integer)[/li]
[li] State (Integer)[/li][/ul]
Now, bind the UnAcked, Acked, and ClrUnAcked to queries like the ones above, except replace "EastArea" with the value of the AreaName property (using the property binding button in the SQL Query binding window)
Now bind the State integer property to an expression like:
binEnum({Root Container.MyButton.UnAcked}, {Root Container.MyButton.Acked}, {Root Container.MyButton.ClrUnAcked})
(You can look up what the binEnum expression does in the manual under:
Technical Reference / Expression Langauge / Logic Functions)
Now your button's State property has it's zone's alarm state encoded like I mentioned at the beginning, and you can directly bind it's background color to this state, and set up your color mapping. Then you just copy this button for your other areas and only have to modify their AreaName property.
I know that sounded like a lot, but its really not too bad. Give us a call if you'd like us to walk you through it.
Thanks for your interest,
).