How to prevent multiple identical DB entries with "on change" collection mode

so our system uses the MQTT engine and reference tags with database attachment. most tags are stored "on change" which should be fine.

the issue i'm having is that when my publisher goes offline, then back online, ignition is registering this as a change and i get multiple database entries with the same value. i have some history tables in my displays that end up full of the same value over and over, and in some cases i get dips in my trend lines that align with the node going offline and back on.

when a node comes online the values change to "null", then return to their present state. should null be considered a change? is there a way to combat this at all?

this "on change" also causes trouble with tag change scripting, i don't want the script to run if the value isn't changing, a republish i don't believe should count as a "Change"

in the DB i could try to mess around with min time between samples but this won't do anything for my scripts as i don't believe i have the option to suppress them.

is there a setting i'm missing here?

Hi will.glancy

The value is initialized to null when it first starts and when the values are received it is considered a change from null to a value. You can check the initialChange flag within your tag change script and only execute the script if it is not the initialChange. For storing history it will always store the initial tag value.

so if i understand you correctly, "initialchange" is the birth message value?

i suppose that there's nothing i can do about the database storage then? it's not great to have 50 of the same value show up in my tables. i can't even use the timestamps on the tag because it updates those and lies about the condition of the device if i use them.

here's an example of what i'm talking about. i poll the "last shutdown" tag and timestamp from the device, and these update very irregularly, but i get a pile of identical database entries because every time the node goes offline and back on, it re-stores these values.

there are dozens of entries for 22:43:53 (this is a tag value, not a tag timestamp as the tag timestamp updates every birth message and the database would lead the user to believe these events weren't all the same)

this correlates with my node online trend

to tackle this would i have to duplicate these tags, then use a tag change script that ignores initial value to write the values into the second tags, then database attach those ones? is there no way to have the database ignore initial values?

a small update,

i prefaced my tag change scripts with

if not initialChange:

and it still runs on node starts. so that's not of any use to me it doesn't look like.

Did you, by chance, define these events in an inheritable project? From which a dozen or so leaf projects inherit?

Events don't run in inheritable projects. They run in leaf projects.

these are tag level issues though?

the project that this is running in inherits properties from two projects beneath it, but it isn't inherited by anything beyond it. however the issues with tag change scripts and database attachment are at the tag level, and should not be subject to any project properties at all.

Are you also prefacing with if currentValue.value != previousValue.value:?

If not, then it's possible that adding this to your tag change script could correct the issue.

What about adding the timestamp as a primary key?

1 Like

There are two different kinds of tag change events. Those defined on the tag, with access to the global scripting project, and those defined in a project, with access to that project's script library. The latter form is inheritable along with other gateway-scope event scripts.

these scripts are in the tag itself. i do understand that you can create on change to a binding within a project, but in my case the script is in the tag properties.

That's a third way, and not gateway scoped (except sort of in Perspective bindings).

@justinedwards.jle i'm not so sure because my suspicion is the sequence of the tag starting (going stale, null, value) is what's triggering the change. so the "null" would not match the previous value. i will try it just to see but i'm not hopeful that will suppress the scripts.

@jlandwerlen how would i go about doing that? the node offline time can vary, and the timestamp associated with the null value and then same value will be different. or do you mean using the timestamp value from the table? sorry i don't quite follow.

sorry perhaps i don't understand if i'm doing something wrong per your claims. my scripts are in the tag properties of the tag in the tag provider. i don't think that should cause an issue. these should be base level and shouldn't cause any issues due to inheritance?

Consider, when seeing initialChange is true, retrieving the last recorded (presumably valid) change from your DB and saving it in a script module top-level cache. Then use that instead of previousValue, updating it as you send fresh values to the DB.

That's the robust solution. Tag change events are otherwise stateless, and cannot read your mind when it comes to null handling.

thanks. i'll call tech support and see if they can assist me on trying that, as i'd have no idea where to start. i have a few other items i need to talk to them about anyway.

Without seeing your table design, the best advice I can give is to search for primary or composite key. It sounds like there should never be a time where you will have a duplicate timestamp. So, let the database handle this, it will prevent duplicates.