Bidirectional SQL binding firing too soon

I thought the "Update query" on a bidirectional component binding was only fired when the user actually updated the component.

I defined such a binding on a popup calendar component, and suddenly noticed a date on an order was set to the year 1900.

After checking the audit logs, it appears the client is executing that update query too early. I guess it happens during loading, or after an update, as most of the time it tries to update order 0 (which is the value before any other is clicked in a list). But sometimes it did update the value of an existing order.

I now added an additional check to the update query ... AND '{this}' != '', to avoid updating with zero values, so we don't run into this again.

But I was wondering if this is a known issue, and if there are better ways to mitigate this.Text fields don't seem to have this problem, so I guess it's actually related to the popup calendar component.

PS. this project is still using 7.9.4, perhaps it's already fixed in a more recent version. I need to test that.


Psssst! In 13 years of work with IA products, I have used a bidirectional query binding exactly zero times. Binding order of execution is virtually random.

1 Like

Then how do you make such an interface?

The operators can select orders from a list, and for the selected order, there’s a container showing extra details. Some of those details can be altered by the operator (quantities, dates, texts, …).

On a webpage, I would subscribe to the oninput event (which only reacts on user input, and not on other changes, as unlike the onchange event). And then fire an update query (or add a small delay to it before firing).

But here, I’d need a separate button they have to click to write the change back. This wastes a lot of screen space IMO (the button should be big enough to easily click), it clutters the interface if every component needs an additional button, and they will forget to press it from time to time.

I could work with popups instead of a container for showing the details, but then they have to click once more to show the popup and once more to close the popup.

I honestly don’t know a good alternative for this.

The technique I use in Ignition to distinguish regular binding traffic from user input is to never bind a user input field directly to a source of data. I always place a custom property of the appropriate type on that field, usually named “raw”, and bind that to the source. Bidirectionally if a tag. Then I bind the input field’s editable property uni-directionally to the raw property.

With the above, a property change script that captures a change to the input field property first checks if the new value matches raw. If so, the value came from the binding and nothing need be done. Otherwise, it is a user input and action is appropriate. Calling an update SQL and then refreshing the source binding would be the action in your case.

Personally, I hate automatic writeback. To facility a great user experience, I add another custom property to each input field, boolean, called “dirty”, and bind it to an expression that is just {Root Container.SomeField.raw} != {Root Container.SomeField.text}. I then style the input field to have a colored background when dirty.

I then have a save button and a cancel button. Each is enabled when any input field is dirty. The cancel button just loops through the container recopying raw to the input property. The save button assembles the appropriate SQL, fires it, and then refreshes the source binding. When that binding finishes, the colored background disappears everywhere. If the source for the fields are tags, bidirectionally bound, the save script simply loops through the container copying from input property to raw (opposite of cancel).

3 Likes

Any update on this use case - build a table from data (ideally some tag and some sql), then let the user edit the sql data columns, write an update on change - now that we are in Perspective 8.1

(seems like bi-directional is still just for tags)

Does sql query update still need this raw property step?

I have a ticket open w Ignition support - they pointed me to here.
Seems odd in 2024 not to be able to craft a web screen/table-ish with
some tag data, and some user (sql) data -

ex - tank barrels are midnight from tag query
w
truck tickets from sql - that the user can edit and save

There are so many variations in how it would be done that there's no sane way to standardize. Ignition lets you glue the pieces together with bindings and scripts. But you have to spend at least that much effort.

i see a Perspective Spreadsheet object
(since that is what the user wants - a spreadsheet experience)

can set each column (or row) to a specific query
tag query for data already have (some math on that data just like report)
and
a new (real) sql update on content edit query (or message handler)
(avoid all this scripting and states)

Ignition just points me to the Ideas website.

Not simple at all. At the very least, you will need to script the on cell edit event, to get the new cell value to pass on where needed. You will very likely need cell view renderers for everything editable, to avoid the table's "extra click to edit" behavior.

:man_shrugging: