I am seeking a little bit of scripting help for my system.
We have a system with an SQL data source reading the following columns: digital inputs (i1 - i3), analogue inputs (an1 - an3) and pulse count inputs (p1 - p3).
I have the system set to update on a timestamp change using the date column as the timestamp override.
Here is a sample of the MySQL table
id . . . . . . date . . . . . . . . . . . . i1 . . i2 . . i3 . . .an1 . . . an2 . . . . an3 . . .p1 . . p2 . . p3
1 . . .09/03/2016 14:30 . . .N . . N . . N . . .186 . . . 329 . . . . 374 . . . N . . .N . . .N
1 . . .09/03/2016 14:40 . . .N . . N . . N . . .188 . . . 331 . . . . 374 . . . N . . .N . . .N
1 . . .09/03/2016 14:43 . . . 1 . . .N . . N . . . N . . . . . N . . . . . . N . . . . N . . .N . . .N
1 . . .09/03/2016 14:50 . . .N . . N . . N . . .201 . . . 345 . . . . 375 . . . N . . .N . . .N
1 . . .09/03/2016 15:00 . . .N . . N . . N . . .199 . . . 355 . . . . 374 . . . N . . .N . . .N
The analogue and pulse channels work great on the system and send data on the 10 minute intervals. The SQL data source is reading properly and the Mango displays show the correct readings and the correct timestamps.
The digital input however, sends its data to the table when the state changes so there is no way to set an interval as you can see from the sample table.
When a digital input comes in it causes 2 problems:
The analogue and pulse channels show a null value and Mango interprets this as a 0.00 and this seriously skews the data as you can understand.
The digital state as displayed in Mango will immediately return to a 0 state which is the opposite value that I need to have displayed.
I have tried several scripts with commands such as COALESCE or IFNULL but can't seem to get these to work.
I have also tried using the the logging properties on each point to ignore extremes such as ignore anything lower than 0.001 on the analogue data point. While this initially appeared to work, it was pointed out to me that sometime the analogue channels will get a 0.000 value in and it will be a valid value such as when a pump turns off. Also this does not help with problem number 2.
Is there a way to write a script in the data point so that the null value will be ignored and thus not updated in mango?
Or is it possible to get another tick box in the logging properties that will give us the ability to ignore null or no data conditions?
Thank you for your advice.