Write the value of a tag to a SQL field

I’m sure this has been covered before, but I just can’t think of the terminology to do a search. What I’m finding isn’t what I’m looking for.

What I’d like to be able to do is essentially have fields on a form that writes to a SQL field. Think of a form in Microsoft Access. The field would display what is currently in the field in the SQL field, or if you change it, it changes the value in the field.

Am I thinking of things wrong? Do I need to make a memory tag and use a transaction group to write to SQL? I’m still really new to Ignition and I feel a bit lost. But I KNOW it should be capable of this.

I think you want to make an editable table, you can find some good instructions here

inductiveautomation.com/supp … /ignition/

Go to the: Tutorials & Helpful Tricks
Then: Creating an Editable Table in Ignition

I’ve built a couple forms like this.(Mostly where an operator might edit a dozen or more values)

I didn’t use tags at all for this btw. You may find a better method!

I use a query to populate a dataset. Reference the dataset to copy data into editable fields for entry.

Then, operator changes fields and hits submit when they have made all the changes they like.
This runs an update/insert statement to update the database and then reruns the select query to refresh the form fields.

I think I figured out how to do what I needed to do. I had it right the first time, I just needed to enable Read/Write mode.

What I’m doing is, the user enters a batch number on the form. Ignition sends the value to a SQL field (using an update transaction group). A view on SQL filters the data table for just that batch number. Tables on the form then display the data for that batch. It’s more efficient doing this than it is to send ALL the data across the network to the end-user and then filter it locally.

I figured out a pretty slick way to do this. I feel like posting my solution just in case it’s useful to anyone else. Maybe this is common knowledge, but it took me a bit to figure out.

Essentially what I’m doing is, when the user selects a batch number from a dropdown list, the value they select is written to a table called “M1_BatchToSelect” (M1 being machine 1). A view on the SQL server filters the BatchData table to just that batch, and the user is shown the data they’re looking for in a table in Ignition.

This is how I did it.

On the SQL server I created a view that selects all the batch numbers. I then used that view to populate a dropdown list in Ignition. Then I went to the scripting menu for the dropdown, and under “propertyChange” I selected “Update Query”. I browsed to the “M1_BatchToSelect” table. I selected the field to update so the update query looked like “UPDATE M1_BatchToSelect SET BatchToSelect = ?”. Then for the “Update value” field, I clicked the browse component button, browsed to the dropdown list and clicked the “Selected String Value” property and clicked OK. The “Update value” field now read {$.selectedStringValue}. On the form, I first tested to make sure that selecting a batch from the list updated the field on the SQL server, and it did.

Then on the form I inserted a Table control and set the data source to the view “view_M1_Data”, which has an inner join between the batch number field in the “M1_Data” table and the batch number field in the “M1_BatchToSelect” table.

Now when I select a batch number from the dropdown list, the table returns the data for that batch.

Of course… now I just realized there’s a fatal flaw in this design. Yes, it’s more efficient to do the query on the SQL end and only send a portion of the data table across the network to the end user. But the problem is, eventually a lot of people will be looking up data. This solution would work great if I only had one end user. People would essentially be fighting with each other to look up the data they want.

I’m sure what I figured out will have some practical solution down the road, but for now I’m going to have to send each end user all the data, and the table on the Ignition form will be filtered based on what is selected in the dropdown list.

If you are comfortable with SQL, you should try creating a stored procedure and pass the values based on whatever the user wants to see. Look into Ignition’s Client SQLTags to understand how each user could have their own unique values. After all, you could do this directly from the table using the SQL Query BINDING in Ignition.

Clearly I have some things left to learn. Thank you for the ideas. When I get a little further into this project I’m sure to investigate further.