Insert & Update functionality for Power table (Without Button)

Ignition
1

Hello All,

I am getting data into power table through SQL query from a particular table. However I want to give insert & update functionality in this power table.
Update operation : User will click on any cell in power table & edit that particular cell & press enter. That entry should be updated in SQL table.
Insert operation: For this, I want to give one blank row at the end of the power table where user will enter all the details & press enter. That entry should be inserted in SQL table.

Please suggest me how can we achieve this. If not then any alternative for this.

Thanks in advance!!

-- KD

2

Put all of your query logic in the power table's onCellEdited extension function.

To separate your blank row insert statement from your update statements simply qualify it with something like:

if rowIndex == (self.data.rowCount - 1):
#     Insert Statement Here
else:
#     Update Statement Here

You can modify your query that populates the table to include a blank row, and then refresh the table after each update or insert query has completed

1 Like
3

@justinedwards.jle Thanks for your help.

Can you suggest how to add a blank row to the power table? and also delete the selected row functionality for the power table without a button.

I really appreciate any help you can provide.
Priyanka Khandge

4

I would set the query to include the blank row. For a simplified example look at this SQL Fiddle I threw together:

Notice how the result includes a blank row at the end.
#========================

I'm not sure what you mean by this, but the selection behavior is set by the following two properties:
image

1 Like
5

@justinedwards.jle Thanks for the quick response.

image

As you suggest black row was added successfully but the row height not showing properly.

Any idea for that?

Many thanks for considering my request.

1 Like
6

Thanks, @justinedwards.jle.

I got the answer, Set row height using the Auto Row Height property. set this property to false.

image

1 Like
7

Hi @justinedwards.jle,

I have tried to insert and update the row using onCellEdited. The updated row successfully works but the insert row does not work.

Any Idea?

image
image1898×463 40.5 KB

Thanks
Priyanka

8

Your SQL server is throwing the exception. There must be a syntax error of some kind. Which SQL server are you using? ...and could you do me a favor and copy your query and paste it into the forum using "```" on either side of it? It will look like:

Insert into [...] values [...]

It will make it easier for me to accurately replicate the problem.

Additional note:
I typically troubleshoot problems of this nature by testing the query directly in the database. A lot of times a red squiggly line there will tell me the problem with my syntax:

image
image1231×257 25.4 KB

1 Like
9

@justinedwards.jle Thanks for your help.

I got my SQL query error. Currently, insert a row but if I enter any value in the last row then immediately open both popup insert and update. I required when I enter a new value in the last row then only open the insert popup.

and another one I face an error when I enter any value in a single row and then immediately automatically enter the same value in all rows.

image
image1114×293 21.3 KB

10

I'm afraid I'm going to be offline for the next few hours, but looking at what I can see of your code, you are calling your custom method from the onCellEdited extension function and passing in the new value from a single cell. My guess is that you are attempting to insert in all four columns, but you are not getting the values from the surrounding cells using the row index. It would be something like:

valueZero = self.parent.getComponent('NumericTextFieldLfdNummer').floatValue
valueOne = newValue if colIndex == 1 else self.data.getValueAt(rowIndex, 1)
valueTwo = newValue if colIndex == 2 else self.data.getValueAt(rowIndex, 2)
valueThree = newValue if colIndex == 3 else self.data.getValueAt(rowIndex, 3)

#[...]

insertArgs = [valueZero, valueOne, valueTwo, valueThree]

Also, and this is where it would get tricky, I can imagine that you will need something in your onCellEdited call that prevents the insert if there are null values in any of the surrounding columns.

Anyway, it's an interesting problem, so I'm curious about the final result. I'll be sure to check back in on this later in the day.

2 Likes