Edit SQL content


I'm new to SQL but I've manged to get it to work somehow. But it's not too intuitive to change existing content. This is how it works today:

To add a new row, enter new BatchNo (SQL key):

Then this row is created:

Then select the batchNo that just was created from the drop down, and fill in the blanks. This works okay.

Then it will be updated:

But if i want to change an existing row, i have to enter the batchNo, and fill in ALL the blanks. If not, they will be overwritten by whatever is in the fields.

When i click "Oppdater" button, i get all values from the fields and run a named query like this:

Is there a way that i can select a batchNo, and all input fields gets the value from the SQL, so that I dont need to fill inn all fields? Or any other ideas for how to make this easier? :slight_smile: Typically I only want to change 1 or 2 fields at a time.

Thanks :slight_smile:

Usually when someone is editing a record (an SQL table row) like this the sequence is:

  • The user selects a row number somehow.
  • An SQL SELECT query returns the data for that row.
  • The input form is populated with that data. Now you have all the information you need if any one field is updated.
  • The user updates the information on the form. (This can be one or many fields - but not the table's row ID number.)
  • The user submits the change and the UPDATE SQL query is executed. This allows for use of a single UPDATE query with all fields updated.

Tip: post formatted code rather than pictures of code. That way we can copy and edit in our answers.

Thank you so much for your fast reply! This make alot of sense. Not quite sure how to do this though.

I have a button ("Velg"), which runs this:

def runAction(self, event):

parameters  = {
system.db.runNamedQuery("BrewHistorySelect", parameters)

This return the name of record 1, which is "Lucky Jack" :

Then, I want to show this name in a text field, which is my input field for SQL update. And here is my problem as you see. Any ideas? :slight_smile:

EDIT: Actually what is returned is this:
[{"Name":"Lucky Jack"}]

So not far out maybe :slight_smile:

Go to the Named Query "Authoring" tab and change the query type to "Scalar". This will return a single value instead of a row with a header (a dataset).

Thanks, that worked!

But I have multiple fields, that should get the value from multiple columns. First I tried to make the Select Query dynamic by using parameter for "Select :Column", but that didn't work.

But if i run the query like this, and somehow filter out from the dataset the column I want from this, is that possible?

Change your approach. What I would do:

  • Create two custom properties on the view. e.g. batchNum and batchData.
  • Bind whatever is selecting the batchNum to the custom.batchNum property. Make it bidirectional so that one can update the other.
  • If you haven't already done so, create a named query to return all the columns for a batch. Set Query Type to "Query". Add one parameter, "batchNum".
  • Create your query binding on batchData using the named query above and use custom.batchNum as a parameter for the query. Set the query binding Return Format to JSON.

Try it out. You should now see custom.batchData update as you change the batchNum.

  • Now, for each field in your form, create a bidirectional binding to the relevant item on custom.batchData.

Thanks again. But already at the first step i get stuck. What is a custom property, and how do I create it?

Anyways, isnt it an idea to filter out the column i want for each field? Problem is that i dont know how i can filter out the column....

All views and components have properties. These can be explored using the Perspective Property Editor.

Ignition allows you to create custom properties on views and components. You will see the usefulness of this as you work through your project.

  1. The Perspective Property Editor panel.
  2. CUSTOM section.
  3. Create batchNum. Give it a value of 1 for now.
  4. Create a batchData custom property. The values will be populated by the query so you don't need to create those manually.
  5. Create bindings on batchNum and batchData as I described earlier. Monitor the changes here when you interact with your form.

... isnt it an idea to filter out the column i want for each field?

And run 12 queries instead of one? I don't think so!

Wow! Thank you so much, this now works like a charm. And custom properties can absolutely be useful. Not quite sure which property i should put the custom property on, but I guess it really doesn't matter?

It matters if you want to make things easy and consistent. That's why I recommended that you create the custom properties on the view rather than on a component. Any binding to that custom property is simply
whereas if you bind to a component in, say, a flex container, it becomes more like

While this will work, it's more difficult to trace problems and monitor values. It also makes your application "brittle" and easy to break. If you move your component into another container then the binding breaks. The view's custom properties will always be in the same place.