Edit SQL content

Maker
1

Hello

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):
image

Then this row is created:

image
image1832×285 22.3 KB

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

image
image1388×107 8.89 KB

Then it will be updated:

image
image1788×25 941 Bytes

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:
image

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:

2

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.

3

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  = {
"BatchNo":1,
}
system.db.runNamedQuery("BrewHistorySelect", parameters)

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

image
image1310×398 18.9 KB

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:

image
image904×612 65.7 KB

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

So not far out maybe :slight_smile:

4

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).

5

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?

image
image1101×520 44.1 KB

6

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.
7

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....

image
image1263×831 58.2 KB

8

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.

Custom properties
Custom properties344×511 26.5 KB

  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!

9

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?

10

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
view.custom.batchNum
whereas if you bind to a component in, say, a flex container, it becomes more like
../FlexContainer/NumericEntryField.props.value

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.

2 Likes